Enable managed identities

Note: You must have you users mapped into Microsoft Entra in order to use Microsoft Entra authentication modes.

Connectivity Hub supports the following Microsoft Entra authentication modes:

  • Microsoft Azure Active Directory username and password authentication

    • If you are using this authentication method, in the connection string, you must provide valid credentials for an account that has access to your Microsoft Entra application registration.

    • For example, Server=myServerAddress; Database=myDataBase; User Id=myUsername; Password=myPassword; Encrypt=false;

  • Microsoft Azure Active Directory Service Principal authentication

    • If you are using this authentication method, in the connection string, you must specify the following:

      • Enter the app ID for your Microsoft Entra application registration in the User ID field.

      • Enter the secret key for your Microsoft Entra application registration in the Password field.

      • For example, Server=myServerAddress; Database=myDataBase; Authentication=Active Directory Service Principal; Encrypt=False; User Id=AppId; Password=secret.

  • Microsoft Azure Active Directory Managed Identity or Managed Service Identity (MSI) authentication

Important: Of the modes listed above, only Microsoft Azure Active Directory Managed Identity or Managed Service Identity (MSI) authentication requires additional managed identity configuration. The remaining modes require that you specify the appropriate authentication connection property in your connection string. For more information, see Connect to Azure SQL with Microsoft Entra authentication and SqlClient in the Microsoft documentation.

To enable managed identities for your SQL server, use the following steps:

  1. Create your user-assigned managed identity object in Azure. For more information, see Create a user-assigned managed identity in the Microsoft documentation.

  2. Ensure that your SQL server is in the same resource group as the managed identity object that you want to use. For more information Azure resource groups, see Manage Azure resource groups by using the Azure portal in the Microsoft documentation.

  3. Ensure that your SQL server has managed identity turned on and that the managed identity assigned to the server is user-assigned and primary identity:

    1. In your SQL server, on the left panel, click Identity under Security.

    2. In the User assigned managed identity section, click Add and select your managed identity subscription.

    3. In the Primary identity section, select your managed identity subscription from the drop-down list.

    4. For more information, see Managed identities in Microsoft Entra for Azure SQL in the Microsoft documentation.

  4. If you have not done so already, create your Configuration and UserProfiles databases.

  5. On the Master database, run the following script to add a managed identity user:

    Copy 
    CREATE USER [<Managed Identity Name>] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA=[dbo]
    GO

  6. On the Configuration and UserProfiles databases, run the following script to add a managed identity user and add it to the required roles:

    Copy 
    CREATE USER [<Managed Identity Name>] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA=[dbo]
    GO
    sys.sp_addrolemember @rolename = N'db_ddladmin', @membername = N'<Managed Identity Name>'
    GO
    sys.sp_addrolemember @rolename = N'db_datareader', @membername = N'<Managed Identity Name>'
    GO
    sys.sp_addrolemember @rolename = N'db_datawriter', @membername = N'<Managed Identity Name>'
    GO
    GRANT EXEC TO <Managed Identity Name>
    GO

  7. Finally, when providing your database connection parameters, click Advanced Settings and provide a connection string similar to the following:

    Copy 
    Data Source=<SQL Server>.database.windows.net,1433;Initial Catalog=<Database Name>;Trust Server Certificate=True;Authentication=ActiveDirectoryManagedIdentity