How to connect to Azure SQL

Introduction

With Anywhere365 you can also connect to Azure SQL for your CDR information.

 

Requirements

  • Database in Azure SQL

  • Azure Admin Account

  • Whitelist IP

 

Create account

You can choose between:

  • SQL Account

  • Active Directory account

Below the manuals on how to create both of them. (Only one is needed).

 

Create SQL Account for Azure SQL

  1. Connect SQL Server Management Studio (SSMS) to the azure database server with the admin account (see azure)

  2. Add login for the user and create to user for the master database, by submitting the following query to the master database: (change username)

    CREATE LOGIN sa_username

    WITH PASSWORD = 'Password'

    GO

    CREATE USER sa_username FROM LOGIN sa_username;

  3. Create a user for the database and give permissions, by submitting the following query to the CDR database

    CREATE USER sa_username FROM LOGIN sa_username;

    EXEC sp_addrolemember 'db_owner', 'sa_username';

More information on:

https://azure.microsoft.com/nl-nl/blog/adding-users-to-your-sql-azure-database/

 

Create Active Directory Account for Azure SQL

  1. Set Active Directory Admin from with the Azure Portal

  2. Connect to server via SQL Server Management Studio (SSMS) with account set on step 1

    Use the ‘Active Directory – Universal with MFA support’ authentication method.

    Choose for Connect (and optionally follow the MFA steps to complete the login)

  3. Run the following queries

    1. Run this query against the Master (instance):

      CREATE USER "<AAD user address>" FROM EXTERNAL PROVIDER;

      ALTER ROLE dbmanager ADD MEMBER [AAD user address];

      (e.g.

      CREATE USER "user@anywhere365.net" FROM EXTERNAL PROVIDER;)

      ALTER ROLE dbmanager ADD MEMBER [user@anywhere365.net]; )

    2. Run this query against the UCCUCC stands for Unified Contact Center and consists of a queue that can be handled by Agents Each Contact Center has its own settings, interactive voice response questions and Agent with specific skills. Agents can be member of, or sign up to, one or more Contact Centers. database:

      CREATE USER "<AAD user address>" FROM EXTERNAL PROVIDER;

      EXEC sp_addrolemember 'db_owner', "<AAD user address>";

      (e.g.

      CREATE USER "user@anywhere365.net" FROM EXTERNAL PROVIDER;)

      EXEC sp_addrolemember 'db_owner', “user@anywhere365.net";)

 

Connect UCC to Azure SQL

  1. Navigate to the UCC Config page

  2. Open the Settings list

  3. Edit the setting "CDRConnectionString"

  4. Enter the connection string with username and password for Azure SQL

    Example AD Account: Server=tcp:server.database.windows.net;Initial Catalog=UCC;Persist Security Info=False;User ID=sa_username;Password=password;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Password";

    Example SQL Account: Server=tcp:server.database.windows.net;Initial Catalog=UCC;User ID=sa_username;Password=password