Skip to main content
Version: 10.2

Azure SQL Database Connection Configuration

Requirements​

To connect to the Azure SQL Database, make sure to have all the following before proceeding:

  • Azure Subscription
  • Azure SQL Server
  • Azure SQL Database
  • Server Login Credentials
    • DBMANAGER Role

Server Login Creation Requirements​

Create a Server Login with the DBMANAGER server role.

To create and manage your Azure SQL Database, you need to set up a server login with the dbmanager role.

Follow these instructions:

  1. Create a Server Login by logging into your Azure SQL Server using SQL Server Management Studio (SSMS). Run the following SQL commands to create a new server login and assign it the dbmanager role:

    Example
    USE [master] 
    GO 

    --Create Server-login 
    CREATE LOGIN [openitsa] WITH PASSWORD = '0p3n!T$@'; 

    --Create database user based on server-login in master database 
    CREATE USER [openitsa] FOR LOGIN [openitsa]; 

    -- Grant necessary permissions to the user 
    ALTER ROLE dbmanager ADD MEMBER [openitsa];

  2. Make sure that the server login is created by expanding the Security > Logins in the Object Explorer.

    Verify Server Login

    Verify Server Login


    NOTE

    If your database is pre-created without using the Analysis Server console application, please proceed to the next step; otherwise, end the instructions.

  3. Create a Database User Based on a Server Login. To create a user in your specific database that corresponds to a server login, run the following command:

    Example
    USE [openitdatabase] 
    GO 

    CREATE USER [openitsa] FOR LOGIN [openitsa]; 
    EXEC sp_addrolemember 'db_datawriter', 'openitsa'; 
    EXEC sp_addrolemember 'db_datareader', 'openitsa';

  4. Make sure that the database login is created by expanding the <OpenitDatabase> > Security > Users.

When changing from On-prem to Azure SQL Server​

  1. Using the Analysis Server console application, run the command SetAzureSQLCredentials. Use the instructions in the section SetAzureSQLCredentials in performing the command.

  2. Provide the Azure SQL Server Login Credentials. Save the changes.

    NOTE

    Make sure to provide valid credentials.

  3. Run the command Install and set the SQL server and Database name. Use the instructions in the section Install - 2 in performing the command.

  4. Save the configuration.

  5. Once all set, run the common commands to create and deploy the SQL database.

    • CreateSql
      Use the instructions in the section CreateSql as guide in performing the command.
    • DeploySql
      Use the instructions in the section DeploySql as guide in performing the command.

    NOTE

    Some of the commands may take time since they are connecting to an Azure SQL Server. You may immediately load and process the data in the database or wait for the next daily processing to execute.

  6. Access the Analysis Server web interface to check if the connection to the database is successful.