Access Synapse SQL Serverless Pool database objects using Managed identity

Question

Hi,

I am trying to access Synapse SQL Serverless Pool database obhejcts via a 1) Azure function written in Python and 2) From the synapse notebook using pyodbc driver. So far I have enabled the managed identity for Azure function, and granted the managed identity dbreader/writer permissions along with contributor role(this was done to just test if elevated privileges can help resolve the connectivity issue).

However, I am getting authentication failure message.

Can you share some references which provides details on how to use managed identities for authenticating an Azure function to Synapse sql serverless pool database?

Answer 1

Hi @saurabh ,

Thank you for the reply. As per the error message, it looks like your managed Identity doesn't have access to the Synapse SQL pool, or the SQL connection string in the Azure function might not be correct.

Can you please validate them both?

On Synapse SQL end:

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;  
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];  
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];  
GO  

On the Azure function end:
The managed identity connection string should be in this format:

Server=demo.database.windows.net; Authentication=Active Directory Managed Identity; Database=testdb  

If both are configured correctly and still having issues, If you have a support plan, you may file a support ticket for deeper investigation; if you don't have a support plan, please let me know. I can enable one-time free support for you to work closely on this matter.

I am looking forward to hearing from you.