question

Marie avatar image
Marie asked

SSMS 19.1.56 can't connect to SQL server on Windows 11

I have Windows 11 22H2 and SSMS 19.1.56 on my new laptop. Sometimes (I've only had the new laptop one week) I can connect to SQL server in SSMS and some days (or part of days) I get "The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)" Today I was able to connect successfully (after rebooting laptop twice), see databases, expand databases to view tables and run queries. About an hour later, when attempting to expand one of the databases to take a look at a stored procedure, I got the following message when attempting to expand the database to access the stored procedure in the Programmability folder :

"Failed to connect to server. The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)" And the + sign next to database name disappears.


I then tried to refresh the connection to the server by right clicking the server name in the Server Explorer and select Refresh - and got this message:

"Failed to retrieve data for this request. ...The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)"


However, in the query window I can still run querier toward the database that generated the first error message when I tried to expand it.

I can run queries against any of the databases, even all those that threw "failed to connect" errors when attempting to expand to see tables. But I can't expand them to see the tables or stored procedures.


Why does Server Explorer throw errors, when I can target the databases fine using Query window?


I have the identical setup on Windows 10 and SSMS 19.1, targeting the identical server and it works like a charm.

Why is it not working with Windows 11? I tried connecting via IP as well, but that renders SSMS useless. It becomes so slow I can't do anything, not even expanding a database.


Right now I'm unable to transition to my new laptop because I can't get SQL to work.

ssms
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
ThomasRushton avatar image
ThomasRushton answered

Out of interest, what settings are you using in your SSMS connection? Check the "encrypt" and "trust certificate" options in particular. Are they different between the two clients?

https://learn.microsoft.com/en-us/sql/ssms/f1-help/connect-to-server-connection-properties-page-database-engine?view=sql-server-ver16#trust-server-certificate

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Marie avatar image
Marie answered


Hi Thomas, checking the encrypt and trust certificate has no effect on this issue.

I'm pretty certain the issue is the Windows 11 is using Microsoft account creds behind the scenes instead of the Windows creds I provided.

After 2 weeks of not being able to connect, and later adding the IP to the Hosts file, it now connects. I also noticed that if I logged on to the laptop using Microsoft account creds, I can't connect to SQL, but if I log on to laptop with Windows cred, SSMS can connect without a problem.

I have the same issue connecting to the TFS server, I have to log out of Visual Studio (that automatically logs in with Microsoft Account creds) and then connect to TFS in a backwards way.

Again, I'm pretty sure the issue is that somehow Windows 11 is automatically using Microsoft creds to access resources on other servers and that is why it can't connect.

I have the IDENTICAL setup on my Windows 10 laptop, same SSMS version, same VS version, accessing the same SQL server and same TFS server - and it works like a charm! I never log in to Windows 10 laptop using Microsoft Account creds.

I stopped using the new laptop because of all the issues, and just logged in today again. Right now, I can connect both to SQL and TFS server. I hope it lasts.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.