Reconnect Issues in SSMS to Azure SQL DB with Microsoft Entra MFA After Role Reactivation

Question

Reconnect Issues in SSMS to Azure SQL DB with Microsoft Entra MFA After Role Reactivation

Jermy Hoffman 0

Connecting to Azure SQL DB with Microsoft Entra MFA from SSMS was successful previously. However, after the role assignment to the Privileged Identity Management Group expired and was re-activated, reconnection from the same SSMS instance fails to invoke the Azure login page.

The following error is encountered:

TITLE: Microsoft SQL Server Management Studio Error connecting to '

Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-17T12:16:55.29+00:00

Hi Jermy Hoffman

Greeting!

As I understand it, you are experiencing connection failures from the SSMS tool after Privileged Identity Management was re-activated.

Please make sure that the users were added as an Azure SQL Administrator - you can do this in the portal

navigate to the Azure SQL logical Server -> in the left menu select Azure Active Directory -> confirm your users are registered there or add them using Set admin

if you still face the same issue:

Please restart the SSMS and try again to connect.

I hope this information helps. Please do let us know if you have any further queries.
Saraswathi Devadula 5,570 Reputation points Microsoft External Staff Moderator

2025-04-17T17:26:15.34+00:00

Hi Jermy Hoffman

As I understand it, you are experiencing connection failures from the SSMS tool after Privileged Identity Management was re-activated.

Please make sure that the users were added as an Azure SQL Administrator - you can do this in the portal

navigate to the Azure SQL logical Server -> in the left menu select Azure Active Directory -> confirm your users are registered there or add them using Set admin

if you still face the same issue:

Please restart the SSMS and try again to connect.

I hope this information helps. Please do let us know if you have any further queries.
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-18T07:41:56.2666667+00:00

Hi Hi Jermy Hoffman

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.
Jermy Hoffman 0 Reputation points

2025-04-18T08:01:33.5766667+00:00

Thanks for the response, I'll be able to check it only on Sunday. Anyway, restart SSMS, works, but, I don't always want to close all my work and restart...
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-21T09:47:39.7833333+00:00

Hi Jermy Hoffman

Thank you for getting back your question.

It's great to know that a restart for SQL Server Management Studio (SSMS) is only necessary after specific configuration changes in the Identity Management Group. That can help save time during troubleshooting or setup.

Kindly check if you are able to connect successfully with SSMS.

If there's anything more you'd like to clarify, troubleshoot, or explore, feel free to reach out—I’m always here to assist
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-22T08:44:36.4266667+00:00

Hi Jermy Hoffman

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.

Jermy Hoffman 0

Hi @Mallaiah Sangi and @Saraswathi Devadula ,

I moved to "SQL Server Management Studio 21 Preview", and I thought it was fixed, but today it happened again in also in SSMS 21:

> ===================================
> 
> Login failed for user '\<token-identified principal\>'. (Framework Microsoft SqlClient Data Provider)
> 
> ------------------------------
> For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
> 
> ------------------------------
> Server Name: \{my_server_name\}.database.windows.net\
> Error Number: 18456\
> Severity: 14\
> State: 1\
> Line Number: 65536
>  
> ------------------------------
> Program Location:
> 
>    at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action\`1 wrapCloseInAction)\
>    at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\
>    at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\
>    at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)\
>    at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)\
>    at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir)\
>    at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)\
>    at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)\
>    at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling)\
>    at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)\
>    at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)\
>    at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource\`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)\
>    at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource\`1 retry, DbConnectionOptions userOptions)\
>    at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource\`1 retry)\
>    at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource\`1 retry, SqlConnectionOverrides overrides)\
>    at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)\
>    at Microsoft.SqlServer.ConnectionDlg.UI.ConnectionDialogViewModel.OpenConnection(IDbConnection connection, IServerConnectionProvider serverConnectionProvider)

About your suggestion @Saraswathi Devadula :

Please make sure that the users were added as an Azure SQL Administrator - you can do this in the portal navigate to the Azure SQL logical Server -> in the left menu select Azure Active Directory -> confirm your users are registered there or add them using Set admin

I couldn't find in Azure Portal, "Azure Active Directory" in the left menu of my "Azure SQL logical Server" page.
However, I found it in "Microsoft Entra ID" (Azure Active Directory (Azure AD) is now Microsoft Entra ID. Learn more)

Microsoft Entra admin Microsoft Entra authentication allows you to centrally manage identity and access to your Azure SQL Database. Learn more Admin name: {my_id} (Admin Object/App ID:{my_guid})

Anyways, if I can connect after restart, it means my Entra Identity have the required permission. It just UI issue, that don't pop-up the web login page after connection lost and the only way I found is to restart the SSMS.
I hope you have more ideas, as I have many times that quite a few tabs are open in SSMS, and restart, means to close all my work.

Thanks again,

Jermy

Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-05-23T02:33:34.18+00:00
Hi Jermy Hoffman

Thank you for reaching out with your issue.

You are experiencing an error while connecting to the SQL server via Management Studio, and the error message you received is: Server Name: {my_server_name}.database.windows.net, Error Number: 18456."

This error typically occurs when trying to authenticate using Azure Active Directory (AAD) but the user isn't properly configured in the database. Here are some possible reasons and solutions:

User Not Added to Database – If you're using AAD authentication, ensure that the user is added to the database. You can do this by running:
CREATE USER [******@domain.com] FROM EXTERNAL PROVIDER; EXEC sp_addrolemember N'db_datareader', N'******@domain.com';

Database Not Specified – When connecting via SSMS, manually specify the database name in Options → Connection Properties. If you only have access to a specific database, SSMS may default to master, causing login failures.

Managed Identity Issues – If you're using Managed Identity, ensure that the identity has the correct permissions assigned in Azure SQL Database.

AAD Server Admin Role – If the user isn't an AAD Server Admin, they may not have access to the database. You can check and assign roles accordingly. I hope this helps outline the options and guides you toward the best solution for your scenario. Please let me know if you have any further questions or need additional details on any of these approaches.
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-05-26T01:42:24.2233333+00:00

Hi Jermy Hoffman

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.
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-05-27T00:38:09.54+00:00

Hi Hi Jermy Hoffman

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.
Jermy Hoffman 0 Reputation points

2025-05-27T09:20:43.15+00:00
None of the 4 scenarios you have mentioned, are not my case ("User Not Added to Database", "Database Not Specified", "Managed Identity Issues" or "AAD Server Admin Role"), as in the same time, when I run new SSMS (20/21) and try to connect with the same connection, a login webpage pops-up and I can login to AAD, and then my SSMS connected successfully.

It seems, that if the login token expires, the SSMS, do not trigger a new login page, and the error above is raised.

This is how I connect normally, step by step:

Activate My Role in PIM:

Verify My Role Group assignment was activated:

Open SSMS and Connect:

Login Page Pops-Up:

SSMS Connected:

When my activation expires, and I re-activate it, the #4 step, doesn't happen and I get the error.
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-05-28T01:03:13.79+00:00

Hi Jermy Hoffman

Thank you for getting back to me with your questions.

Could you please share the error message or error number at step 4? That would make it easier to investigate the issue thoroughly.
Jermy Hoffman 0 Reputation points

2025-05-28T08:38:21.57+00:00

Included above https://learn.microsoft.com/en-us/answers/questions/2258865/reconnect-issues-in-ssms-to-azure-sql-db-with-micr?comment=question#comment-2051298:~:text=May%2022%2C%202025%2C%204%3A10%20PM
SSingh-MSFT 16,371 Reputation points Moderator

2025-06-10T05:02:20.29+00:00

Hi Jermy Hoffman

Just checking if below reply by Pratik helped or you are still facing issues or if you have a product feedback, feel free to share here: https://feedback.azure.com/d365community/forum/ef2b2b38-2f25-ec11-b6e6-000d3a4f0f84

thanks.

1 answer

Your answer

Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-17T12:16:55.29+00:00

Hi Jermy Hoffman

Greeting!

As I understand it, you are experiencing connection failures from the SSMS tool after Privileged Identity Management was re-activated.

Please make sure that the users were added as an Azure SQL Administrator - you can do this in the portal

navigate to the Azure SQL logical Server -> in the left menu select Azure Active Directory -> confirm your users are registered there or add them using Set admin

if you still face the same issue:

Please restart the SSMS and try again to connect.

I hope this information helps. Please do let us know if you have any further queries.
Saraswathi Devadula 5,570 Reputation points Microsoft External Staff Moderator

2025-04-17T17:26:15.34+00:00

Hi Jermy Hoffman

As I understand it, you are experiencing connection failures from the SSMS tool after Privileged Identity Management was re-activated.

Please make sure that the users were added as an Azure SQL Administrator - you can do this in the portal

navigate to the Azure SQL logical Server -> in the left menu select Azure Active Directory -> confirm your users are registered there or add them using Set admin

if you still face the same issue:

Please restart the SSMS and try again to connect.

I hope this information helps. Please do let us know if you have any further queries.
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-18T07:41:56.2666667+00:00

Hi Hi Jermy Hoffman

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.
Jermy Hoffman 0 Reputation points

2025-04-18T08:01:33.5766667+00:00

Thanks for the response, I'll be able to check it only on Sunday. Anyway, restart SSMS, works, but, I don't always want to close all my work and restart...
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-21T09:47:39.7833333+00:00

Hi Jermy Hoffman

Thank you for getting back your question.

It's great to know that a restart for SQL Server Management Studio (SSMS) is only necessary after specific configuration changes in the Identity Management Group. That can help save time during troubleshooting or setup.

Kindly check if you are able to connect successfully with SSMS.

If there's anything more you'd like to clarify, troubleshoot, or explore, feel free to reach out—I’m always here to assist
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-04-22T08:44:36.4266667+00:00

Hi Jermy Hoffman

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.
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-05-23T02:33:34.18+00:00

Hi Jermy Hoffman

Thank you for reaching out with your issue.

You are experiencing an error while connecting to the SQL server via Management Studio, and the error message you received is: Server Name: {my_server_name}.database.windows.net, Error Number: 18456."

This error typically occurs when trying to authenticate using Azure Active Directory (AAD) but the user isn't properly configured in the database. Here are some possible reasons and solutions:

User Not Added to Database – If you're using AAD authentication, ensure that the user is added to the database. You can do this by running:
CREATE USER [******@domain.com] FROM EXTERNAL PROVIDER; EXEC sp_addrolemember N'db_datareader', N'******@domain.com';

Database Not Specified – When connecting via SSMS, manually specify the database name in Options → Connection Properties. If you only have access to a specific database, SSMS may default to master, causing login failures.

Managed Identity Issues – If you're using Managed Identity, ensure that the identity has the correct permissions assigned in Azure SQL Database.

AAD Server Admin Role – If the user isn't an AAD Server Admin, they may not have access to the database. You can check and assign roles accordingly. I hope this helps outline the options and guides you toward the best solution for your scenario. Please let me know if you have any further questions or need additional details on any of these approaches.
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-05-26T01:42:24.2233333+00:00

Hi Jermy Hoffman

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.
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-05-27T00:38:09.54+00:00

Hi Hi Jermy Hoffman

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.
Jermy Hoffman 0 Reputation points

2025-05-27T09:20:43.15+00:00

None of the 4 scenarios you have mentioned, are not my case ("User Not Added to Database", "Database Not Specified", "Managed Identity Issues" or "AAD Server Admin Role"), as in the same time, when I run new SSMS (20/21) and try to connect with the same connection, a login webpage pops-up and I can login to AAD, and then my SSMS connected successfully.

It seems, that if the login token expires, the SSMS, do not trigger a new login page, and the error above is raised.

This is how I connect normally, step by step:

Activate My Role in PIM:

Verify My Role Group assignment was activated:

Open SSMS and Connect:

Login Page Pops-Up:

SSMS Connected:

When my activation expires, and I re-activate it, the #4 step, doesn't happen and I get the error.
Mallaiah Sangi 1,145 Reputation points Microsoft External Staff Moderator

2025-05-28T01:03:13.79+00:00

Hi Jermy Hoffman

Thank you for getting back to me with your questions.

Could you please share the error message or error number at step 4? That would make it easier to investigate the issue thoroughly.
Jermy Hoffman 0 Reputation points

2025-05-28T08:38:21.57+00:00

Included above https://learn.microsoft.com/en-us/answers/questions/2258865/reconnect-issues-in-ssms-to-azure-sql-db-with-micr?comment=question#comment-2051298:~:text=May%2022%2C%202025%2C%204%3A10%20PM
SSingh-MSFT 16,371 Reputation points Moderator

2025-06-10T05:02:20.29+00:00

Hi Jermy Hoffman

Just checking if below reply by Pratik helped or you are still facing issues or if you have a product feedback, feel free to share here: https://feedback.azure.com/d365community/forum/ef2b2b38-2f25-ec11-b6e6-000d3a4f0f84

thanks.

Answer 1

Hi Jermy Hoffman

Microsoft Entra PIM assigns and removes roles within seconds. However, applications may cache the user's role membership and not reflect updates immediately. Signing out and signing back in may help.

When connecting to Azure SQL Database via SQL Server Management Studio (SSMS), the tool uses a cached Microsoft Entra token that includes your role assignments. One important detail to understand is that these tokens do not automatically refresh when your role is changed such as after activating a role through Microsoft Entra Privileged Identity Management (PIM).

The cached token will remain valid for its original duration, regardless of whether your PIM role is currently active. SSMS uses this token silently, and unless the application is restarted or the token cache is cleared, it won’t request a fresh login or token update.

Because of this behavior, after PIM role activation, SSMS may continue using a stale token without the updated role claims. This results in the Microsoft Entra login page not appearing, and the connection attempt fails with a generic "Error 18456", which typically indicates insufficient permissions.

To resolve this, you should clear the Entra token cache or restart SSMS, both of which will force the application to retrieve a new token with the correct role assignments. This explains why simply restarting SSMS fixes the issue, and highlights the importance of refreshing tokens after role activation.

Jermy Hoffman 0 Reputation points

2025-06-10T11:05:04.77+00:00

How can I "clear the Entra token cache"?
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Share via

Reconnect Issues in SSMS to Azure SQL DB with Microsoft Entra MFA After Role Reactivation

1 answer

Your answer