the target principal name is incorrect. cannot generate sspi context. Windows Authentication SQL Server 2017

Question

Hi All,

I am stuck in a strange issue post installation of SQL Server 2017 Standard. 

I installed a Core licensed SQL Server 2017 Standard Edition on a Server with Mixed Mode Authentication. Set up was executed successfully.

While I am connected to my network through VPN Connection. I am able to connect with Server through SQL Authentication However with Windows Authentication it does not connects and fails out with message as  "The target principal name is incorrect. Cannot generate SSPI Context."

However when i tried to connect through LAN with Windows Authentication it let me connect without any issues.

Can you please help me in resolving the connectivity issue over VPN for Windows Authentication.

Below are the error details

===================================

The target principal name is incorrect.  Cannot generate SSPI context. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

------------------------------
Server Name: 000.000.00.00
Error Number: 0
Severity: 11
State: 0
Procedure: GenClientContext


------------------------------
Program Location:

   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)

.

.

.

.

Thank You. 

---

Best Regards, Sunil Sharma

Answer 1

Hi Sunilsharma,

>The target principal name is incorrect.  Cannot generate SSPI context. (.Net SqlClient Data Provider)

This is a typical Kerberos authentication failure. There are various reasons for this error. The most common one is the SPN problem. Many cases of this error have been found due to abnormal KDC operation or abnormal TGS service. You can reference: how-to-troubleshoot-the-cannot-generate-sspi-context-error-message

You can also try workaround as next:
If you use your domain account to log in, you should grant the account rights of Read and Write SPN, and then restart the server with this account.
1.Make sure TCP/IP Protocols are enabled and configured correctly;
2.Close the firewall;
3.In your DC, run->”adsiedit.msc”
4.Assume that the start account is YX\Administrator, Administrator->Properties->Security->Advanced->Permissions->Add->Select a principle->Input “SELF”->OK  (as next screenshot shows)

5.Choose “Read serverPrincipalName” and “Write serverPrincipalName” (as next screenshot shows)

6.Use this account restart your server and browser;





Note:
You need to note that when solving Kerberos-related problems, you may encounter this situation: clearly all the conditions required for Kerberos are configured.
OK, but you still get Kerberos errors or NTLM errors when you test the connection. At this time, you may wish to try the following two tricks:

(1) There may be multiple DCs in a domain environment, and the series of changes you make during the investigation will only affect one of the DCs. maybe
You can use another DC to connect the client to SQL Server, but this DC has not been synchronized to the series of changes you made before.
At this time, you do not need to wait for automatic synchronization between DCs to occur, you can run the following statement to force synchronization between DCs:
Repadmin / syncall

(2) Credential Cache may also be a problem. Credential Cache is used by Kerberos to cache authentication information on this machine. It mainly contains TGT and Session tickets. Since Credential Cache has a life cycle (usually 10 hours) on the machine, if the client has received incorrect authentication information and cached it, it will use this information to access SQL Server until the cache expires. So you will always get errors. The solution is to clear the Credential Cache by any of the following three methods.
1) Use the klist.exe purge command
2) Use kerbtray tool
3) Restart the entire machine

Best Regards.

yuxi

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact [e-mail]

Answer 2

What yuxi666 mentioned is really good!

What i can add… check if the SPN for SQL has been added to the SQL Service account.

1. On your SQL Server, open SQL Server Configuration Manager

2. Look at the Log On As column for the SQL Server service.

3. Open a command prompt and type the following command:

setspn -l [Log on account]

For example, if the log on account is Domain\svc-sql the command line would be setspn -l Domain\svc-sql

If the account is NT Authority\Local System, the account is the computer account.  Then the command line would be setspn -l [SQL_Computername]

The SQL SPN should look like this: MSSQLSvc/[SQLServerName]:Port

If the SPN is not configured, that should explain why you receive the error Cannot generate SSPI context

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-ver15

hth

---

This posting is provided AS IS without warranty of any kind

Answer 3

Hi Sunilsharma,

Is the reply helpful?

Best Regards.

yuxi

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact [e-mail]

Answer 4

Hi Sunilsharma,

Is the reply helpful?

Best Regards.

yuxi

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact [e-mail]

Answer 5

Hi Sunil,

you also use below workaround.

1.Add windows credentials in windows credential manager on machine you use to access  sql server.

2. add server name in windows hosts file.

then try to connect sql server.

---

regards Ashok

Answer 6

Dear Yuxi,

Apologies for the late reply and Thank you for such detailed and informative response.

I will try these steps and update you with my results.

For your information We don't have  any dedicated service account using which Default Instance SQL Service is running.

The account name is NT Service\MSSQLSERVER which was set up by installation wizard. 

Tried to search account with setspn however it didn't fetch any result.

Not sure if  the account name is correct. Am i missing anything here. Do I need to register it?

Thank You.

---

Best Regards, Sunil Sharma

Answer 7

Hi Sunilsharma,

The account should be machineaccount or domain user account.Change NT Service\MSSQLSERVER to your machine name for your case.

Use Local System or Network Service to restart your SQL service and SQL Browser if you don't have one domain account.If you use these two accounts to restart your SQL service and the SPN will be registered under the machine account.

You can check as next:

1. check whether there is the SPN under my machine account, because my machine in in the YX domain, so my machine account is YX\VM2, if your machine is not in any domain, you can change the code as 

setspn -l MachineName

2.If there is no SPN under the machine account, you need to register one SPN by mannual,code as

SETSPN -A MSSQLSvc/VM2.yx.com:SQLVM2 YX\VM2    (change YX\VM2 and VM2 to your machine name on your side)

3.check again

More information: register-a-service-principal-name-for-kerberos-connections

Best Regards.

yuxi

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact [e-mail]

Answer 8

Thank You Yuxi.

I have tried following steps.

1. klist purge command.

2. Changed the Log On Account as Network Service for SQL Service.

3. Restarted the server.

4. Checked that domain\machinename SPN is also registered with following command

SETSPN -l "DOMAIN\MACHINENAME" It produced same details as you mentioned in the above image.

I believe that i don't need to register the SPN again. Can you please help me SQLVM2 in your command. 

Note: I am not able to connect to SQL with windows authentication only over VPN, However i could connect with windows authentication in LAN.

Thank You.

---

Best Regards, Sunil Sharma

Answer 9

Hi Sunilsharma,

This may be one network issue.

Could you please upload the error log here?

the path is:

Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n 

Please check whether this is helpful:

trusted-connection-to-sql-fails-after-connecting-to-a-vpn, connect-to-sql-via-windows-authentication-over-vpn

Best Regards.

yuxi

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact [e-mail]

Answer 10

Hi Yuxi.

Thanks a lot for the post and such detailed responses.

Below link has helped me to connect to SQL Server over VPN Connection with windows authentication.  

connect to sql via windows authentication over vpn.

Again thanks a lot for helping me out for the issue. Appreciate It.

Thank You.

---

Best Regards, Sunil Sharma

Answer 11

Hi Sunilsharma,

Glad to hear that the issue is solved.

Thanks for your reply.

Best Regards.

yuxi

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact [e-mail]