Use managed identities to access Azure SQL Database from an Azure Stream Analytics job

  • Article

Azure Stream Analytics supports Managed Identity authentication for Azure SQL Database output sink. Managed identities eliminate the limitations of user-based authentication methods, like the need to reauthenticate due to password changes or user token expirations that occur every 90 days. When you remove the need to manually authenticate, your Stream Analytics deployments can be fully automated.

A managed identity is a managed application registered in Microsoft Entra ID that represents a given Stream Analytics job. The managed application is used to authenticate to a targeted resource. This article shows you how to enable Managed Identity for an Azure SQL Database output(s) of a Stream Analytics job through the Azure portal.

Overview

This article shows you the steps needed to connect your Stream Analytics job to your Azure SQL Database SQL pool using Managed Identity authentication mode.

  • You first create a system-assigned managed identity for your Stream Analytics job. This is your job’s identity in Microsoft Entra ID.

  • Add an Active Directory admin to your SQL server, which enables Microsoft Entra ID (Managed Identity) authentication for that resource.

  • Next, create a contained user representing the Stream Analytics job's identity in the database. Whenever the Stream Analytics job interacts with your SQL DB resource, this is the identity it will refer to for checking what permissions your Stream Analytics job has.

  • Grant permissions to your Stream Analytics job to access your SQL Database pools.

  • Finally, add your Azure SQL Database as output in the Stream Analytics job.

Prerequisites

The following are required to use this feature:

  • An Azure Stream Analytics job.

  • An Azure SQL Database resource.

Create a managed identity

First, you create a managed identity for your Azure Stream Analytics job.

  1. In the Azure portal, open your Azure Stream Analytics job.

  2. From the left navigation menu, select Managed Identity located under Configure. Then, check the box next to Use System-assigned Managed Identity and select Save.

    Select system-assigned managed identity

    A service principal for the Stream Analytics job's identity is created in Microsoft Entra ID. The life cycle of the newly created identity is managed by Azure. When the Stream Analytics job is deleted, the associated identity (that is, the service principal) is automatically deleted by Azure.

  3. You can also switch to user-assigned managed identities.

  4. When you save the configuration, the Object ID (OID) of the service principal is listed as the Principal ID as shown below:

    Object ID shown as Principal ID

    The service principal has the same name as the Stream Analytics job. For example, if the name of your job is MyASAJob, the name of the service principal is also MyASAJob.

Select an Active Directory admin

After you've created a managed identity, you select an Active Directory admin.

  1. Navigate to your Azure SQL Database SQL Pool resource and select the SQL Server, respectively. You can find the link to these in the resource overview page next to Server name or Workspace name.

  2. Select Active Directory Admin or SQL Active Directory Admin under Settings, for SQL Server. Then, select Set admin.

    Active Directory admin page

  3. On the Active Directory admin page, search for a user or group to be an administrator for the SQL Server and click Select. This will be the user who will be able to create the Contained Database User in the next section.

    Add Active Directory admin

    The Active Directory admin page shows all members and groups of your Active Directory. Grayed out users or groups can't be selected as they're not supported as Microsoft Entra administrators. See the list of supported admins in the Microsoft Entra features and Limitations section of Use Microsoft Entra authentication for authentication with SQL Database.

  4. Select Save on the Active Directory admin page. The process for changing admin takes a few minutes.

Create a contained database user

Next, you create a contained database user in your Azure SQL that is mapped to the Microsoft Entra identity. The contained database user doesn't have a login for the primary database, but it maps to an identity in the directory that is associated with the database. The Microsoft Entra identity can be an individual user account or a group. In this case, you want to create a contained database user for your Stream Analytics job.

For more information, review the following article for background on Microsoft Entra integration: Universal Authentication with SQL Database and Azure Synapse Analytics (SSMS support for MFA)

  1. Connect to your Azure SQL database using SQL Server Management Studio. The User name is a Microsoft Entra user with the ALTER ANY USER permission. The admin you set on the SQL Server is an example. Use Microsoft Entra ID – Universal with MFA authentication.

    Connect to SQL Server

    The server name <SQL Server name>.database.chinacloudapi.cn may be different in different regions. For example, the China region should use <SQL Server name>.database.chinacloudapi.cn.

    You can specify a specific Azure SQL database by going to Options > Connection Properties > Connect to Database.

    SQL Server connection properties

  2. When you connect for the first time, you may encounter the following window:

    New firewall rule window

    1. If so, go to your SQL Server resource on the Azure portal. Under the Security section, open the Firewalls and virtual network/Firewalls page.
    2. Add a new rule with any rule name.
    3. Use the From IP address from the New Firewall Rule window for the Start IP.
    4. Use the To IP address from the New Firewall Rule window for End IP.
    5. Select Save and attempt to connect from SQL Server Management Studio again.

  3. Once you're connected, create the contained database user. The following SQL command creates a contained database user that has the same name as your Stream Analytics job. Be sure to include the brackets around the ASA_JOB_NAME. Use the following T-SQL syntax and run the query.

    CREATE USER [ASA_JOB_NAME] FROM EXTERNAL PROVIDER;

    To verify if you have added the contained database user correctly, run the following command in SSMS under the pertaining database and check if your ASA_JOB_NAME is under the “name” column.

    SELECT * FROM <SQL_DB_NAME>.sys.database_principals
WHERE type_desc = 'EXTERNAL_USER'

  4. For Microsoft's Microsoft Entra ID to verify if the Stream Analytics job has access to the SQL Database, we need to give Microsoft Entra permission to communicate with the database. To do this, go to the "Firewalls and virtual network"/”Firewalls” page in Azure portal again, and enable "Allow Azure services and resources to access this server/workspace."

    Firewall and virtual network

Grant Stream Analytics job permissions

Once you've created a contained database user and given access to Azure services in the portal as described in the previous section, your Stream Analytics job has permission from Managed Identity to CONNECT to your Azure SQL database resource via managed identity. We recommend that you grant the SELECT and INSERT permissions to the Stream Analytics job as those will be needed later in the Stream Analytics workflow. The SELECT permission allows the job to test its connection to the table in the Azure SQL database. The INSERT permission allows testing end-to-end Stream Analytics queries once you have configured an input and the Azure SQL database output.

You can grant those permissions to the Stream Analytics job using SQL Server Management Studio. For more information, see the GRANT (Transact-SQL) reference.

To only grant permission to a certain table or object in the database, use the following T-SQL syntax and run the query.

GRANT CONNECT TO ASA_JOB_NAME;
GRANT SELECT, INSERT ON OBJECT::TABLE_NAME TO ASA_JOB_NAME;

Alternatively, you can right-click on your Azure SQL database in SQL Server Management Studio and select Properties > Permissions. From the permissions menu, you can see the Stream Analytics job you added previously, and you can manually grant or deny permissions as you see fit.

To look at all the permissions you have added to your ASA_JOB_NAME user, run the following command in SSMS under the pertaining DB:

SELECT dbprin.name, dbprin.type_desc, dbperm.permission_name, dbperm.state_desc, dbperm.class_desc, object_name(dbperm.major_id)
FROM sys.database_principals dbprin
LEFT JOIN sys.database_permissions dbperm
ON dbperm.grantee_principal_id = dbprin.principal_id
WHERE dbprin.name = '<ASA_JOB_NAME>'

Create an Azure SQL Database output

Note

When using SQL Managed Instance (MI) as a reference input, you must configure a public endpoint in your SQL Managed Instance. You must specify the fully qualified domain name with the port when configuring the database property. For example: sampleserver.public.database.chinacloudapi.cn,3342.

Now that your managed identity is configured, you're ready to add an Azure SQL Database output to your Stream Analytics job.

Ensure you have created a table in your SQL Database with the appropriate output schema. The name of this table is one of the required properties that has to be filled out when you add the SQL Database output to the Stream Analytics job. Also, ensure that the job has SELECT and INSERT permissions to test the connection and run Stream Analytics queries. Refer to the Grant Stream Analytics job permissions section if you haven't already done so.

  1. Go back to your Stream Analytics job, and navigate to the Outputs page under Job Topology.

  2. Select Add > SQL Database. In the output properties window of the SQL Database output sink, select Managed Identity from the Authentication mode drop-down.

  3. Fill out the rest of the properties. To learn more about creating an SQL Database output, see Create a SQL Database output with Stream Analytics. When you are finished, select Save.

  4. After clicking Save, a connection test to your resource should automatically trigger. Once that successfully completes, you have successfully configured your Stream Analytics job to connect to you Azure SQL Database using managed identity authentication mode.

Additional Steps for SQL Reference Data

Azure Stream Analytics requires you to configure your job's storage account when using SQL Reference data. This storage account is used for storing content related to your Stream Analytics job, such as SQL reference data snapshots.

Follow the following steps to set up an associated storage account:

  1. On the Stream Analytics job page, select Storage account settings under Configure on the left menu.

  2. On the Storage account settings page, select Add storage account.

  3. Follow the instructions to configure your storage account settings.

    Screenshot of the Storage account settings page of a Stream Analytics job.

Important

  • To authenticate with connection string, you must disable the storage account firewall settings.
  • To authenticate with Managed Identity, you must add your Stream Analytics job to the storage account's access control list for Storage Blob Data Contributor role and Storage Table Data Contributor role. If you do not give your job access, the job will not be able to perform any operations. For more information on how to grant access, see Use Azure RBAC to assign a managed identity access to another resource.

Additional Steps with User-Assigned Managed Identity

Repeat the steps if you selected user-assigned managed identity to connect ASA to Synapse:

  1. Create a contained database user. Replace ASA_Job_Name with User-Assigned Managed Identity. See the example below. 
    CREATE USER [User-Assigned Managed Identit] FROM EXTERNAL PROVIDER;
  2. Grant permissions to the User-Assigned Managed Identity. Replace ASA_Job_Name with User-Assigned Managed Identity.

For more details, please refer to the sections above.

Remove Managed Identity

The Managed Identity created for a Stream Analytics job is deleted only when the job is deleted. There is no way to delete the Managed Identity without deleting the job. If you no longer want to use the Managed Identity, you can change the authentication method for the output. The Managed Identity will continue to exist until the job is deleted, and will be used if you decide to use Managed Identity authentication again.

Next steps