Admin: Azure SQL Managed Identity Configuration

Overview

This section describes how to prepare Azure SQL Managed Identity for use with RPI.

Prerequisites

• Grant the VM access to Azure SQL Database.

• Enable Azure AD authentication.

• Create a contained user in the database that represents the VM's system assigned identity.

• Get an access token using the VM identity and use it to query Azure SQL Database.

• Additional details can be found at: https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azureresources/tutorial-windows-vm-access-sql

Steps

1. Log into SSMS using the AD account and run the following query in the primary data warehouse:
   CREATE USER [<VMName-FQDN>] FROM EXTERNAL PROVIDER

2. Run this query for all VMs in the cluster.  This will create the contained user for each VM.

3. Run the following query in the primary data warehouse:
   ALTER ROLE db_datareader ADD MEMBER [<VMName-FQND>]

4. Again, repeat his query for all VMs in the cluster.   This will grant the VM access to read the entire database.

5. Repeat steps 1 -4 for all RPI operational databases: 

   • Interaction_XXX 

   • InteractionAudit_XXX 

   • Pulse 

   • Pulse_Logging 

6. Open the Node Manager config file: 
   C:\Program Files\RedPoint Global\RedPoint Interaction\Node Manager\Resonance.NodeManagerService.exe 

7. Edit the connection strings as per the following (replacing the server name): 

   XML

   <connectionStrings>
   
     <add 
       name="LoggingDatabase" 
       connectionString="
         Server=<AzureSQLDBServer>;
         Database=Pulse_Logging;
         UID=AnyString;
         Encrypt=True;
         Authentication=Active Directory Interactive;
         ConnectRetryCount=12;
         ConnectRetryInterval=10;
       " 
       providerName="System.Data.SqlClient" 
     />
   
     <add 
       name="OperationalDatabase" 
       connectionString="
         Server=<AzureSQLDBServer>;
         Database=Pulse;
         UID=AnyString;
         Encrypt=True;
         Authentication=Active Directory Interactive;
         ConnectRetryCount=12;
         ConnectRetryInterval=10;
       " 
       providerName="System.Data.SqlClient" 
     />
   
   </connectionStrings>
   

8. Open the Execution Service config file: 
   C:\Program Files\RedPoint Global\RedPoint Interaction\ExecutionService\Resonance.ExecutionService.exe 

9.  Edit the connection strings as per the following (replacing the server name): 

   XML

   <connectionStrings>
   
     <add 
       name="LoggingDatabase" 
       connectionString="
         Server=<AzureSQLDBServer>;
         Database=Pulse_Logging;
         UID=AnyString;
         Encrypt=True;
         Authentication=Active Directory Interactive;
         ConnectRetryCount=12;
         ConnectRetryInterval=10;
       " 
       providerName="System.Data.SqlClient" 
     />
   
     <add 
       name="OperationalDatabase" 
       connectionString="
         Server=<AzureSQLDBServer>;
         Database=Pulse;
         UID=AnyString;
         Encrypt=True;
         Authentication=Active Directory Interactive;
         ConnectRetryCount=12;
         ConnectRetryInterval=10;
       " 
       providerName="System.Data.SqlClient" 
     />
   
   </connectionStrings>
   

10. Open the Interaction Web.config file: C:\inetpub\wwwrpi\Interaction\Web.config 

11. Edit the connection strings as per the following (replacing the server name): 

    XML

    <connectionStrings>
    
      <add 
        name="LoggingDatabase" 
        connectionString="
          Server=<AzureSQLDBServer>;
          Database=Pulse_Logging;
          UID=AnyString;
          Encrypt=True;
          Authentication=Active Directory Interactive;
          ConnectRetryCount=12;
          ConnectRetryInterval=10;" 
        providerName="System.Data.SqlClient" 
      />
    
      <add 
        name="OperationalDatabase" 
        connectionString="
          Server=<AzureSQLDBServer>;
          Database=Pulse;
          UID=AnyString;
          Encrypt=True;
          Authentication=Active Directory Interactive;
          ConnectRetryCount=12;
          ConnectRetryInterval=10;" 
        providerName="System.Data.SqlClient" 
      />
    
    </connectionStrings>
    

12. Restart IIS, the Node Manager Service, and the Execution Service to pick up the changes.

13. Repeat steps 6-12 for all VMs on the cluster.

14. Log into Server Workbench and navigate to the Clients tab.  Update the following connection strings (updating the server and databases):

    • Operational Database: 

      Server=<AzureSQLDBServer>;
      Database=<interaction DB>;
      UID=AnyString;
      Encrypt=True;
      Authentication=Active Directory Interactive;
      ConnectRetryCount=12;
      ConnectRetryInterval=10  
      

    •  Auditing Database:  

      Server=<AzureSQLDBServer>;
      Database=<interaction audit DB>;
      UID=AnyString;
      Encrypt=True;
      Authentication=Active Directory Interactive;
      ConnectRetryCount=12;
      ConnectRetryInterval=10
      

    • Data Warehouse Database:

      Server=<AzureSQLDBServer>;
      Database=<data warehouse DB>;
      UID=AnyString;
      Encrypt=True;
      Authentication=Active Directory Interactive;
      ConnectRetryCount=12;
      ConnectRetryInterval=10  
      

    • If applicable, update the Read-Only data warehouse database as well 

15. Execute tests to validate that RPI can connect to the databases.