How the Search Service Authenticates to Access an Azure SQL Database

When using Azure Cognitive Search to index data from an Azure SQL database, it's important to understand the different authentication methods that can be used to connect to the database. In this blog post, we'll explain the different authentication methods that the Search service can use to access an Azure SQL database.

Authentication Methods in SQL DB

There are several authentication methods that can be used to connect to an Azure SQL database, including:

SQL Authentication

SQL authentication is a method of authentication that uses a username and password to connect to an Azure SQL database. This method is commonly used when connected to a database from an application or service that does not support integrated Windows authentication.

Azure Active Directory Authentication:

This method of authentication uses Azure Active Directory to authenticate users and services to an Azure SQL database. It is commonly used when connecting to a database from a cloud-based application or service.

You can find it in Azure portal of your SQL database Connection String.

Managed Identity

Managed identity is a feature of Azure Active Directory that allows you to authenticate to other Azure services without needing to store credentials in your code or configuration files. When you create a managed identity, Azure creates an identity for your service that can be used to authenticate to other Azure services within the same tenant and subscription.

How the Search Service Authenticates to Access an Azure SQL Database

When you create a data source to index data from an Azure SQL database, you can choose to use SQL authentication with your username and password.

Azure Active Directory Authentication

When we attempted to use Active Directory Authentication for users with the role of connecting to Azure Search service, an error occurred. The error message displayed was as follows:

"error": {

    "code": "",

    "message": "Failed to authenticate the {user email} in Active Directory (Authentication=ActiveDirectoryPassword).

Error code 0xparsing_wstrust_response_failed\r\nThere was an error parsing WS-Trust response from the endpoint.

Error Message: Federated service at {endpoint} returned an error: ID3242: The security token could not be authenticated or authorized. "

}

If you test in portal, it will show “Login failed for user {user email}”

What’s the reason? As when Search service connects with Azure SQL DB as a data source, it directly connects with the SQL tables. However, there isn't an AAD user account on the SQL table. Other applications, such as Azure Data Studio, check SQL AAD users and then connect with tables. So, this error indicates that the AAD user account assigned in Azure SQL server account cannot be used to connect from the Azure Search service.

Any workarounds? Using a managed identity!

Set up an indexer connection to Azure SQL using a managed identity!

The diagram below illustrates how the Search Service connects to an SQL server through Managed Identity.

To connect to an SQL database using a managed identity, you need to create a user with the name of the Search service or user-managed identity in the SQL database. Then, use the resource ID of the SQL database as the connection string. Here is the example to set up an indexer connection to an Azure SQL database using a User Assigned Managed Identity, follow these steps:

• First, please assign a reader role in your managed identity to select data from SQL DB.

• And then you need to log in using the AAD admin and create the user in the database to create a user in an SQL database. You can use this SQL query below:

• After that please use the resource id of SQL server as the Connection String.

Attention:

Make sure to check whether the managed identity has been added to the Search service. When adding this data source from Rest API Create or Update Data Source (2021-04-30-Preview) - Azure Cognitive Search | Microsoft Learn, also verify whether it has been added to the identity in Search Service.

Another thing to keep in mind is to ensure that you add your Search service IP to the firewall list of the Azure SQL service. Otherwise, you will receive the following error:

Change the SQL server to “Selected networks” and add the firewall rules with your Search Service IP or using a shared private link instead.

For the details, please check the reference documents:

Connect through firewalls - Azure Cognitive Search | Microsoft Learn

Connect through a private endpoint - Azure Cognitive Search | Microsoft Learn

Conclusion

In this blog post, we've explained the different authentication methods that can be used to connect to an Azure SQL database, and how the Search service can use managed identity to authenticate to the database. Using managed identity to authenticate to an Azure SQL database is a secure and scalable way to connect to the database from Azure Cognitive Search. By following the steps outlined in this blog post, you can set up an indexer connection to an Azure SQL database using a managed identity and start indexing your data in no time.

Reference: Connect to Azure SQL - Azure Cognitive Search | Microsoft Learn