sql server 2022 windows authentication not working on peer to peer network

Question

I have two brand new latest version PCs with windows 11 installed and connected as a peer to peer network.

I have installed MS SQL 2022 (Developer) on one pc and SSMS on the other pc.

I can establish a connection via SA account and query the database as expected - so all firewall & ports are open and working. I can also share folders without it asking for credentials, so all networking is working.

However if i switch to Windows Auth in SQL2022 i get an SSPI error on the client - the SQL log does not register any attempt to login, suggesting the error maybe coming from the client before reaching the server.

ERROR: The target principal name is incorrect. Cannot generate SSPI context (Microsoft SQL Server)

I've always thought SSPI is typically related to active directory and a domain server, but this is a peer to peer network, so no AD or domain server. When SQL starts up, it notes in the log that it cannot create an SPN. I assume it's because it can't reach a domain server.

Each computer has the same user registered on both PCs using a MS Account style login with PIN. With SQL Server 2019, i could easily add a credential in the Cred Manager on the client to map my user account to appropriate account on the SQL pc.

example,

Credential: servername:firewallport

Name: peter or micorsoftaccount******@email.com or servername\peter (tried all of these)

Password: windows account password.

Using SQL 2019 the above credential worked no problems for last 3 years, also worked via a VPN to my office from home. However, I just setup a new test platform using SQL 2022 version and the Windows Auth won't work. ERROR: The target principal name is incorrect. Cannot generate SSPI context (Microsoft SQL Server)

I have tried to establish a connection using SSMS, Excel and VS2022 - all fail with the same error message. SQL Server config has named pipes and tcp enabled. Any ideas on where to look now?

Is there something i can add to the connection string to prevent SSPN context being required? have i missed something obvious. I have also tried starting SSMS as admin.

Answer 1

SQL Server support Windows Auth mainly in a AD (domain) enviroment. In a Workgroup (perr-to-peer) you can get it working, if the local Windows account exists on both machines with the same password.

Answer 2

Hi @Peter Halbert,

Check it out here to see if you miss any steps.

Also check this same thread to see if it is helpful.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Solved this problem after reading a lot of web pages and some very helpful articles. Thanks to everyone who commented on this page as it helped me to solve the problem.

The quick answer in short is; you must create a Local Account with the same credentials as your MS account.

Why? By default, when installing a new copy of Windows 11 the setup recommends that you use a Microsoft Account to login with. For most people, that's no big deal as you can simply create an account @ MS with an email like (******@hotmail.com, ******@outlook.com). When you create an account, your PW is saved to a secure MS site and not your pc. This improves security because you don't have to enter your password every time you log into Windows 11. Windows 11 setup also recommends that you create a pin, use facial rec or a fingerprint to further secure your account. Once all that's done, the setup creates a special account on the Windows 11 pc typically using the firstname of your email address (you can also edit it). But wait, here's the kicker. The standard user account/password is not fully initialised. So apps like SQL cannot authenticate. And this where you must convert the MS account to a Local Account, save your username/pw combination and then convert back to a MS account (going back to MS Account is optional, but without it you'll lose features, like roaming, syncing data and other key features).

How For those of you who want to try this, just follow this text guide (courtesy of Copilot AI)

• To switch from a Microsoft account to a local account, open Settings > Accounts > Your Info, click Sign in with a local account instead, confirm your Microsoft account password, enter a username and password for the local account, and sign out. You can then sign in with the local account credentials.> - To switch from a local account to a Microsoft account, open Settings > Accounts > Your Info, click Sign in with a Microsoft account instead, enter your Microsoft account email and password, and sign in. You can then use your Microsoft account to access various online services and sync your settings across devices.

That's it. Once you have created a Local Account (and optionally converted back again) on any PC that's needs to connect to an instance of SQL server on another PC (same workgroup), you're good to go. If you need to cross domains (ie, to another network domain) see this link for a helpful guide using Credential Manager.