Configuring Data Source for Azure Synapse Serverless SQL Pool in Azure AI Search

Question

Configuring Data Source for Azure Synapse Serverless SQL Pool in Azure AI Search

Suyash Dwivedi 20 Microsoft Employee

Hi Team, I am trying to add a Synapse serverless SQL pool database as a data source for indexing in Azure AI Search, utilizing managed identity for authentication between services.

The following prerequisites have been completed:

Azure AI Search's managed identity has the DB Reader role on the view in the Synapse serverless database.
Azure AI Search's identity is assigned as a reader on the Synapse workspace.
Networking options in the Synapse workspace permit connections from Azure services.

According to the SQL database data source setup documentation for managed identity, the resource ID must be formatted correctly to configure the data source and run the indexer:

{
  "@odata.context": "xxxx",
  "@odata.etag": "\"0xxxx\"",
  "name": "xxxx",
  "description": null,
  "type": "azuresql",
  "subtype": null,
  "credentials": {
    "connectionString": "Database=<dbname>;ResourceId=<resource id>;"
  },
  "container": {
    "name": "view name",
    "query": null
  },
  "dataChangeDetectionPolicy": null,
  "dataDeletionDetectionPolicy": null,
  "encryptionKey": null
}

The above JSON snippet is functional for adding SQL databases with the specified connection string format. However, I am facing challenges in configuring it for the serverless Synapse SQL database. I have tried various connection string format, including:

Database=<dbname>;ResourceId=<synapse workspace id>
Database=<dbname>;ResourceId=<synapse pool resource id>
Database=<dbname>;ResourceId=<synapse workspace id>/sqlpools/<serverless sql endpoint>
Server=<serverless sql endpoint>;Database=<testdb>;Authentication=<Auth option>;

When trying to create the indexer, a bad request response is received, lacking clarity on the issue for the first three connection strings, or an error indicating that login failed for the user in the last one. I could not find any specific docs on adding synapse db view. Any suggestions regarding the correct connection string format or links to relevant documentation would be appreciated.

I have tried using C# sdk and azure portal for setting up indexers both are giving same results.

Sina Salam 22,031 Reputation points Volunteer Moderator

2024-11-26T19:41:20.4066667+00:00
Hello Suyash Dwivedi,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you are having issue configuring Data Source for Azure Synapse Serverless SQL Pool in Azure AI Search.

To configure a Synapse serverless SQL pool as a data source for Azure AI Search, the connection string needs to be formatted specifically for managed identity authentication. The recommended format is:

{ "credentials": { "connectionString": "Server=<serverless-sql-endpoint>.database.windows.net;Authentication=Active Directory Managed Identity;Database=<dbname>;" } }

In this configuration: Replace <all-tags> with the necessary mysynapseworkspace, and dbname. This approach ensures that the connection string adheres to serverless SQL pool requirements by specifying the correct server endpoint and authentication method.

For Synapse serverless SQL pools, the ResourceId parameter does not apply. This parameter is designed for dedicated SQL pools and should not be included in your connection string. Removing unnecessary elements like ResourceId simplifies the configuration process and avoids errors related to unsupported fields.

The managed identity used by Azure AI Search must have the correct database-level permissions. To grant these permissions, execute the following SQL commands in Synapse Studio:

CREATE USER [<Managed Identity Name>] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [<Managed Identity Name>];

Replace <Managed Identity Name> with the name of your Azure AI Search managed identity. Additionally, ensure that this managed identity has a Reader role assigned at the Synapse workspace level within the Azure portal. Proper permission settings allow the managed identity to access the database and query the view.

Verify that the Synapse workspace networking settings allow Azure AI Search to connect. To do this, enable the "Allow Azure services and resources to access this workspace" option in the Synapse workspace networking configuration. This setting ensures that Azure services can communicate with the Synapse environment without additional firewall or VNet configurations.

Before configuring the Azure AI Search indexer, test the connection using tools like SQL Server Management Studio (SSMS) or Azure Data Studio. Use the same connection string as previously defined and set the authentication method to Azure Active Directory - Managed Identity. Successfully connecting through these tools confirms that your configuration is correct and ready for use in Azure AI Search.

Once connectivity is established:

Create the data source in Azure AI Search by providing the verified connection string.

Set up the indexer in the Azure portal or via SDK to point to the view in the database container.

This step-by-step validation ensures that your setup is complete, and that data indexing can proceed without issues.

For more information and reading use the following:

https://learn.microsoft.com/en-us/azure/search/search-howto-managed-identities-sql

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/database-security-overview

https://learn.microsoft.com/en-us/azure/search/search-howto-managed-identities-data-sources

https://learn.microsoft.com/en-us/azure/search/search-howto-managed-identities-sql

https://learn.microsoft.com/en-us/answers/questions/964966/access-synapse-sql-serverless-pool-database-object

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.
Suyash Dwivedi 20 Reputation points Microsoft Employee

2024-11-27T08:55:51.13+00:00

@Sina Salam , I have tried that connection string format, it does not work. If I don't mention resource id in connection string, it takes auth type as SQL authentication instead of managed identity.
Sina Salam 22,031 Reputation points Volunteer Moderator

2024-11-27T15:05:19.9033333+00:00
Hello

Thank you for feedback and time given to review provided answers.

Regarding your responses and questions so far, the below is my best conclusion and advice:

Synapse Serverless SQL pools cannot be used as a data source for Azure AI Search, even when programmatically configuring the data source using the C# SDK. This limitation stems from how Synapse Serverless SQL pools are implemented—they do not expose a standard SQL database endpoint that Azure AI Search can interface with.

For serverless SQL pools, the recommended connection string for managed identity is:

{ "credentials": { "connectionString": "Server=<serverless-sql-endpoint>.database.windows.net;Authentication=Active Directory Managed Identity;Database=<dbname>;" } }

However, this will not work with Azure AI Search due to the inherent limitations.

Create a dedicated SQL pool within the Synapse workspace or use a separate Azure SQL Database in serverless mode. These options allow the database to function as a proper Azure SQL endpoint, which can be used as a data source in Azure AI Search.

Before proceeding, ensure that the managed identity permissions are correctly configured and connectivity can be validated using external tools like SSMS or Azure Data Studio.

Given the limitation of Synapse Serverless SQL pools, you should:

Switch to a Dedicated SQL Pool or Azure SQL Database. If indexing data from Synapse is critical, create a dedicated SQL pool in the Synapse workspace or migrate the necessary views to an Azure SQL Database in serverless mode.

Make sure that the Azure AI Search managed identity is granted appropriate permissions (e.g., db_datareader) on the new SQL pool or Azure SQL Database as you configure Managed Identity for the New Data Source.

Use the following connection string format for the new SQL resource for update the Data Source Configuration:

{ "credentials": { "connectionString": "Server=<server-endpoint>.database.windows.net;Authentication=Active Directory Managed Identity;Database=<dbname>;" } }

For more details, kindly use these official resources:

Configure managed identity for SQL in Azure AI Search - https://learn.microsoft.com/en-us/azure/search/search-howto-managed-identities-sql

Synapse Serverless SQL security - https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/database-security-overview

Azure AI Search with Azure SQL - https://learn.microsoft.com/en-us/azure/search/search-howto-managed-identities-data-sources

Success.
VenkateshDodda-MSFT 24,951 Reputation points Microsoft Employee Moderator

2024-12-02T03:48:27.1533333+00:00

@Suyash Dwivedi Following up to see if you have a chance to check my previous response and helped. Do let me know if you have any further questions on this.

Accepted answer

0 additional answers

Your answer

Suyash Dwivedi 20 Reputation points Microsoft Employee

2024-11-27T08:55:51.13+00:00

@Sina Salam , I have tried that connection string format, it does not work. If I don't mention resource id in connection string, it takes auth type as SQL authentication instead of managed identity.
VenkateshDodda-MSFT 24,951 Reputation points Microsoft Employee Moderator

2024-12-02T03:48:27.1533333+00:00

@Suyash Dwivedi Following up to see if you have a chance to check my previous response and helped. Do let me know if you have any further questions on this.

Answer 1

VenkateshDodda-MSFT 24,951 Microsoft Employee Moderator

@Suyash Dwivedi Thanks for posting your question in Microsoft Q&A, apologize for any inconvenience caused on this.

Based on the shared information, I understand that you are trying to create a DataSource & index using the built-in serverless sql pool which is getting failed with internal server error.

If my understanding is correct, then this is an expected behavior.

All the built-in SQL serverless pool that is created in synapse won't be shown under your subscription and that is reason you are not able to create a data source with Azure AI search service.

Also, the scope of this serverless pool will be within the workspace only.

We have observed that we can create the data source in AI search with dedicated SQL pool in synapse workspace.

If you want to create a data source with Serverless SQL I would suggest you create an Azure SQL database in serverless mode and try connecting to search service from it.

Refer to the similar thread here : https://learn.microsoft.com/en-us/answers/questions/2088971/unable-to-connect-with-azure-sql-database-through

Hope this helps, let me know if you have any further questions on this.

Suyash Dwivedi 20 Reputation points Microsoft Employee

2024-11-27T08:54:11.3833333+00:00

@VenkateshDodda-MSFT , just clarification this restriction will apply, even when creating data source programmatically using c# sdk? If it's a synapse serverless SQL pool, we cannot use that as datasource in Azure AI Search?
VenkateshDodda-MSFT 24,951 Reputation points Microsoft Employee Moderator

2024-11-27T13:23:52.02+00:00

@Suyash Dwivedi Thanks for your response I am checking with the internal team and will update you here once I hear back from them.
VenkateshDodda-MSFT 24,951 Reputation points Microsoft Employee Moderator

2024-11-28T04:55:36.19+00:00

@Suyash Dwivedi Thanks for your patience. You can use the Push API method to create an index and which doesn't require to create the data source.

Push API : https://learn.microsoft.com/en-us/azure/search/tutorial-optimize-indexing-push-api

More information about Push Model API you can through this : https://learn.microsoft.com/en-us/azure/search/search-what-is-data-import#pushing-data-to-an-index

Share via

Configuring Data Source for Azure Synapse Serverless SQL Pool in Azure AI Search

0 additional answers

Your answer