Managed Identity Authentication from Synapse notebook using pyodbc library to Azure SQL DB

Question

Managed Identity Authentication from Synapse notebook using pyodbc library to Azure SQL DB

bkotz 15

I have an Azure Synapse workspace and Azure SQL DB set up. What I'm trying to do is use python's pyodbc library to connect from an Azure Synapse notebook to a table in my Azure SQL DB using the managed identity of the Synapse workspace to authenticate. I've followed many instructions and I've tried a few different changes here and there, but I keep getting this same error:

Error: ('FA004', "[FA004] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Failed to authenticate the user '' in Active Directory (Authentication option is 'ActiveDirectoryMSI').\nError code 0xA190; state 41360\n (0) (SQLDriverConnect)")

I'm unsure why it is trying to use the user field when authentication type is set to MSI.

I added the synapse workspace name associated to the synapse managed identity to my SQL DB as a user and I have added it roles.
I created a security group with my alias and synapse msi to administrator for SQL server under Settings > Microsoft Entra ID then click set admin
On SQL server I switched to system assigned managed identity under Security > Identity pane

I don't know if it is possible to use the synapse MSI directly, but assumed that since it is an MSI it would work. Below is some of the code I am using on the pyodbc side:

SERVER = 'tcp:<my_server_name>.database.windows.net'
DATABASE = '<my_db_name>'
DRIVER = '{ODBC Driver 18 for SQL Server}'

connectionString = f"Driver={DRIVER};Server={SERVER};Database={DATABASE};Authentication=ActiveDirectoryMsi;Connection Timeout=30;"

conn = pyodbc.connect(connectionString) #This is where it fails with above error.

How do I use the Synapse workspace managed identity to authenticate to Azure SQL DB? I haven't been able to find directions specifically for this scenario. I appreciate all your time and help in advance!

Vinodh247 40,051 Reputation points MVP Volunteer Moderator

2023-10-07T13:57:49.31+00:00

Hi bkotz:

did you enable the session to run as msi?
Vinodh247 40,051 Reputation points MVP Volunteer Moderator

2023-10-08T13:12:47.15+00:00

Hi bkotz:

Just checking if you had a chance to look at this. If so, can you please update?
Smaran Thoomu 32,525 Reputation points Microsoft External Staff Moderator

2023-10-09T14:14:25.1833333+00:00
@bkotz In addition to the above comment, The error message you are seeing indicates that the authentication failed for the user '' in Active Directory, even though the authentication type is set to ActiveDirectoryMSI. This error can occur if the user associated with the managed identity of your Synapse workspace is not added to your Azure SQL DB as a user with the appropriate roles.
To use the Synapse workspace managed identity to authenticate to Azure SQL DB, you need to follow these steps:

Add the Synapse workspace managed identity to your Azure SQL DB as a user with the appropriate roles. You mentioned that you have already done this, but please double-check that the user is added with the correct roles.

Update your connection string to use the managed identity authentication method.

Ensure that your Synapse workspace is granted the appropriate permissions to access the Azure SQL DB. You can do this by adding the Synapse workspace managed identity to a security group with the appropriate permissions, as you mentioned you have already done.

To find more information on this topic please refer this Use a managed identity to connect to Azure SQL Database

I hope this helps! Let me know if you have any further questions or concerns.
Smaran Thoomu 32,525 Reputation points Microsoft External Staff Moderator

2023-10-10T15:54:46.8966667+00:00

@bkotz We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
bkotz 15 Reputation points

2023-10-10T17:03:37.4766667+00:00
Apologies for the delayed response. I am currently on vacation.

@Vinodh247 Yes, the session is set to "use managed identity" and gets the same error mentioned above.

@Smaran Thoomu I believe I have all of these steps in place. I will share in more depth the settings I have done:

I set up a security group that additionally has the synapse managed identity in it that I have assigned as admin to the SQL server.

After this assignment was complete, I then selected to use system assigned managed identity on the SQL server.

I then created a user and assigned the user roles in the SQL DB. I added both the "fb-synapsed-dev" name as a user and tried adding the administrator group, "FB_SqlAdmin_FTE", as a user with assigned roles as well. With both of those done, I still get an error. Both of these users are assigned datareader and datawriter roles.

I then set up the above python code and connection string from a Synapse notebook (set to use managed identity on the session) and still receiving error.

I hope this helps shed more light on to what could be missing. Thanks for your patience in my response.
bkotz 15 Reputation points

2023-10-10T17:13:57.63+00:00
Apologies for the delayed response, I am on vacation currently.

@Vinodh247 I have this set and it has the same error provided above.

@Smaran Thoomu I believe I have followed these steps, but will provide the steps in more detail:

I created a security group that contains the msi of the synapse workspace.

I then assigned this group as administrator to the SQL server

I set the SQL server to use system-assigned managed identity

I added users for the msi name and the name of the security group I assigned to administrator of the SQL db to the database (fb-synapse-dev & FB_SqlAdmin_FTE). They are both assigned data reader / writer roles.

Finally, I tried running the above python code to connect to the database via msi connection string using pyodbc and ensured the session was set to use the system-assigned managed identity.

Let me know if you see anything that I may have missed. Hope this helps, thanks.
Smaran Thoomu 32,525 Reputation points Microsoft External Staff Moderator

2023-10-11T17:37:48.6066667+00:00
@bkotz Thank you for providing additional information. If you have verified that the managed identity has the appropriate permissions and the connection string is correct, you can try the following troubleshooting steps:

Check the firewall settings for your Azure SQL DB to ensure that the IP address of your Synapse workspace is allowed to access the database.

Try using a different authentication method, such as SQL authentication or Active Directory Password authentication, to see if the issue is specific to the managed identity authentication method.
Admin (KK) 136 Reputation points

2024-01-22T14:08:44.4+00:00

Anyone was able to fix this issue. I am using Managed Identity to connect to dedicated pool and i see the same issue.
Wurm, Yvonne L 5 Reputation points

2025-03-28T21:57:35.06+00:00

I am also experiencing the same issue and have tried everything above with no luck. Still get this

Error connecting to Azure SQL Database: ('FA004', "[FA004] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Failed to authenticate the user '' in Active Directory (Authentication option is 'ActiveDirectoryMSI').\nError code 0xA190; state 41360\n (0) (SQLDriverConnect)")

Your answer

Vinodh247 40,051 Reputation points MVP Volunteer Moderator

2023-10-07T13:57:49.31+00:00

Hi bkotz:

did you enable the session to run as msi?
Vinodh247 40,051 Reputation points MVP Volunteer Moderator

2023-10-08T13:12:47.15+00:00

Hi bkotz:

Just checking if you had a chance to look at this. If so, can you please update?
Smaran Thoomu 32,525 Reputation points Microsoft External Staff Moderator

2023-10-09T14:14:25.1833333+00:00

@bkotz In addition to the above comment, The error message you are seeing indicates that the authentication failed for the user '' in Active Directory, even though the authentication type is set to ActiveDirectoryMSI. This error can occur if the user associated with the managed identity of your Synapse workspace is not added to your Azure SQL DB as a user with the appropriate roles.
To use the Synapse workspace managed identity to authenticate to Azure SQL DB, you need to follow these steps:

Add the Synapse workspace managed identity to your Azure SQL DB as a user with the appropriate roles. You mentioned that you have already done this, but please double-check that the user is added with the correct roles.

Update your connection string to use the managed identity authentication method.

Ensure that your Synapse workspace is granted the appropriate permissions to access the Azure SQL DB. You can do this by adding the Synapse workspace managed identity to a security group with the appropriate permissions, as you mentioned you have already done.

To find more information on this topic please refer this Use a managed identity to connect to Azure SQL Database

I hope this helps! Let me know if you have any further questions or concerns.
Smaran Thoomu 32,525 Reputation points Microsoft External Staff Moderator

2023-10-10T15:54:46.8966667+00:00

@bkotz We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
bkotz 15 Reputation points

2023-10-10T17:03:37.4766667+00:00

Apologies for the delayed response. I am currently on vacation.

@Vinodh247 Yes, the session is set to "use managed identity" and gets the same error mentioned above.

@Smaran Thoomu I believe I have all of these steps in place. I will share in more depth the settings I have done:

I set up a security group that additionally has the synapse managed identity in it that I have assigned as admin to the SQL server.

After this assignment was complete, I then selected to use system assigned managed identity on the SQL server.

I then created a user and assigned the user roles in the SQL DB. I added both the "fb-synapsed-dev" name as a user and tried adding the administrator group, "FB_SqlAdmin_FTE", as a user with assigned roles as well. With both of those done, I still get an error. Both of these users are assigned datareader and datawriter roles.

I then set up the above python code and connection string from a Synapse notebook (set to use managed identity on the session) and still receiving error.

I hope this helps shed more light on to what could be missing. Thanks for your patience in my response.
bkotz 15 Reputation points

2023-10-10T17:13:57.63+00:00

Apologies for the delayed response, I am on vacation currently.

@Vinodh247 I have this set and it has the same error provided above.

@Smaran Thoomu I believe I have followed these steps, but will provide the steps in more detail:

I created a security group that contains the msi of the synapse workspace.

I then assigned this group as administrator to the SQL server

I set the SQL server to use system-assigned managed identity

I added users for the msi name and the name of the security group I assigned to administrator of the SQL db to the database (fb-synapse-dev & FB_SqlAdmin_FTE). They are both assigned data reader / writer roles.

Finally, I tried running the above python code to connect to the database via msi connection string using pyodbc and ensured the session was set to use the system-assigned managed identity.

Let me know if you see anything that I may have missed. Hope this helps, thanks.
Smaran Thoomu 32,525 Reputation points Microsoft External Staff Moderator

2023-10-11T17:37:48.6066667+00:00

@bkotz Thank you for providing additional information. If you have verified that the managed identity has the appropriate permissions and the connection string is correct, you can try the following troubleshooting steps:

Check the firewall settings for your Azure SQL DB to ensure that the IP address of your Synapse workspace is allowed to access the database.

Try using a different authentication method, such as SQL authentication or Active Directory Password authentication, to see if the issue is specific to the managed identity authentication method.
Admin (KK) 136 Reputation points

2024-01-22T14:08:44.4+00:00

Anyone was able to fix this issue. I am using Managed Identity to connect to dedicated pool and i see the same issue.
Wurm, Yvonne L 5 Reputation points

2025-03-28T21:57:35.06+00:00

I am also experiencing the same issue and have tried everything above with no luck. Still get this

Error connecting to Azure SQL Database: ('FA004', "[FA004] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Failed to authenticate the user '' in Active Directory (Authentication option is 'ActiveDirectoryMSI').\nError code 0xA190; state 41360\n (0) (SQLDriverConnect)")

Share via

Managed Identity Authentication from Synapse notebook using pyodbc library to Azure SQL DB

Your answer