SQL Server connectivity, Kerberos authentication and SQL Server SPN (Service Principal Name for SQL Server)
Posted by Karthick P.K on December 9, 2013
Most of you would already be aware of Kerberos authentication in SQL Server (http://technet.microsoft.com/en-us/library/cc280744%28v=sql.105%29.aspx) It is mandate for delegation and highly secured method for client server authentication.
Connection failures caused by Kerberos authentication issues drives majority of questions in MSDN and other SQL Server forums. Some of the common errors you would get when Kerberos authentication fails include.
{
Cannot generate SSPI context
login failed for user NT Authority Anonymous
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. (Microsoft SQL Server, Error: 18456)
Login failed for user ‘(null)’
Login failed for user ”
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
Linked server connections failing
SSPI handshake failed with error code 0x80090311 while establishing a connection with integrated security; the connection has been closed
SSPI handshake failed with error code 0x80090304 while establishing a connection with integrated security; the connection has been closed
Note: For the last two errors error code translates to
Error -2146893039 (0x80090311): No authority could be contacted for authentication
Error -2146893052 (0x80090304): The Local Security Authority cannot be contacted
So it is pretty much clear that if you get last two errors then it means secure session could not be established with you domain controller. So you can use nltest /SC_QUERY:YourDomainName to check the domain connection status.
You will also see below event from netlogon session in system event log when your SQL Server connection fails with last two errors in the above list
Log Name: System
Source: NETLOGON
Event ID: 5719
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: client.Contoso.com
Description: This computer was not able to set up a secure session with a domain controller in domain CONTOSO due to the following:
There are currently no logon servers available to service the logon request.
This may lead to authentication problems. Make sure that this computer is connected to the network. If the problem persists, please contact your domain administrator.
}
Before we jump into troubleshooting Connection failures caused by Kerberos authentication let see how to force SQL Server to use Named pipes protocol when you get above errors and workaround the problem till you fix the Kerberos authentication with TCP/IP. To force SQL Server to use NP protocol you can use any one of the below methods.
1. Prefix the SQL Server instance name with np: Ex: If your server name is Mssqlwiki\Instance1 , modify the connection string to np: Mssqlwiki\Instance1
2. Change the order of client protocols and bring Named pipes before the TCP/IP protocol (SQL Server configuration manager -> SQL Server native client configuration -> Client protocols -> Order – >Bring Named pipes above TCP/IP)
![clip_image002[4]](https://mssqlwiki.files.wordpress.com/2013/12/clip_image0024.jpg "clip_image002[4]")
Note: You have to do the change both in 32-Bit and 64-Bit SQL Server native client configuration in your client systems.
3. Create a named pipe Alias
When you get Kerberos authentications errors or if you notice SQL Server is failing back to NTLM authentication you can follow below steps to troubleshoot Kerberos failures.
1. How to check If SQL Server is suing Kerberos authentication?
SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid
![clip_image004[4]](https://mssqlwiki.files.wordpress.com/2013/12/clip_image0044.jpg "clip_image004[4]"){kind=small}
For the Kerberos authentication to work in SQL Server, SPN (Service principal name) has to be registered for SQL Server service. SPN is automatically registered by SQL Server using the startup account of SQL Server when SQL Server starts and deregistered when SQL Server is stopped. Kerberos authentication would fail when the SPN is not registered (or) when there is duplicate SPN’s registered in Active directory (or) client system is not able to get the Kerberos ticket (or) DNS is not configured properly.
2. How to Check if SPN’s are successfully registered in the active directory?
When SPN’s is registered in active directory during the startup of SQL Server by startup account of SQL Server, a message similar to one below is logged in SQL Server error log.
2013-12-05 22:21:47.030 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/node2.mssqlwiki.com ] for the SQL Server service.
2013-12-05 22:21:47.030 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/node2.mssqlwiki.com:1433 ] for the SQL Server service.
![clip_image006[4]](https://mssqlwiki.files.wordpress.com/2013/12/clip_image0064.jpg "clip_image006[4]"){kind=small}
When SQL Server could not register SPN’s during the startup below error message is logged in SQL Server error log?
Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/node2.mssqlwiki.com ] for the SQL Server service. Windows return code: 0xffffffff, state: 53. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/node2.mssqlwiki.com:1433 ] for the SQL Server service. Windows return code: 0xffffffff, state: 53. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
![clip_image008[4]](https://mssqlwiki.files.wordpress.com/2013/12/clip_image0084.jpg "clip_image008[4]"){kind=small}
3. I see SQL Server could not register SPN error message in SQL Server errorlog. How do I make SQL Server register SPN’s automatically?
If your Domain controller is windows2008R2 or lower grant Read servicePrincipalName and Write servicePrincipalName privilege for startup account of SQL Server using ADSIEDIT.msc tool
Launch the ADSI Edit -> Domain -> DC=DCNAME,DC=com -> CN=Users -> CN=SQLServer_ServiceAccount -> Properties -> security tab-> advanced ->Add self -> Edit ->in permissions ->Click properties -> grant ->Read servicePrincipalName and -> Write servicePrincipalName
If your domain controller is Windows2012 grant Validate write to service principal name for startup account of SQL Server using Active directory user and computers snap in
![clip_image010[4]](https://mssqlwiki.files.wordpress.com/2013/12/clip_image0104.jpg "clip_image010[4]")
4. From SQL Server error log I see SPN’s are registered successfully but still Kerberos authentication is failing. What is next?
Check if there are duplicate SPN’s registered in Ad using the LDIFDE tool. Below query will fetch all the SQL Server SPN’s from active directory and print in c:\temp\spnlist.txt.
Ldifde -f c:\temp\spnlist.txt -s YourDomainName -t 3268 -d "" -r "(serviceprincipalname= MSSQLSvc/*)"
Search for duplicate SPN in the output file (spnlist.txt). In our case SPN name is MSSQLSvc/node2.mssqlwiki.com:1433 .So if there are more than one entry in the output file for MSSQLSvc/node2.mssqlwiki.com:1433 then there is a duplicate SPN’s which has to be deleted.
5. How do I identify which SPN is duplicate?
In the output of the LDIFDE you will find the SAM accountName which registered the SPN, just above the ServicePrincipalName (Refer the sample below). If the SAM account is not the startup account of SQL Server then it as duplicate SPN.
{
sAMAccountName: NODE2$
sAMAccountType: 805306369
dNSHostName: NODE2.mssqlwiki.com
servicePrincipalName: MSSQLSvc/node2.mssqlwiki.com
servicePrincipalName: MSSQLSvc/node2.mssqlwiki.com:1433
}
6. There is a duplicate SPN in active directory how do I delete?
Use the setspn tool
Syntax: Setspn -D "MSSQLSvc/FQDN:port" "SAMAccount name which has duplicate SPN "
Setspn -D " MSSQLSvc/node2.mssqlwiki.com:1433" "DOMAIN\Accountname"
7. SPN’s are registered properly, there is no duplicate SPN but still the Kerberos authentication is not working ?
Run the KLIST exe from the client and check if it is able to get the ticket
Example:
Klist get MSSQLSvc/node2.mssqlwiki.com:1433
If the client is able to get the ticket then you should see a output similar to one below
{
c:\Windows\System32>Klist get MSSQLSvc/node2.mssqlwiki.com:1433
Current LogonId is 0:0x2de9f6
A ticket to MSSQLSvc/node2.mssqlwiki.com:1433 has been retrieved successfully.
Cached Tickets: (10)
![clip_image012[4]](https://mssqlwiki.files.wordpress.com/2013/12/clip_image0124.jpg "clip_image012[4]")
}
If the client is unable to get the ticket then you should see an error similar to one below.
{
c:\Windows\System32>Klist get MSSQLSvc/node2.mssqlwiki.com:1433
Current LogonId is 0:0x2de9f6
Error calling API LsaCallAuthenticationPackage (GetTicket substatus): 0x6fb
klist failed with 0xc000018b/-1073741429: The SAM database on the Windows Server
does not have a computer account for this workstation trust relationship.
![clip_image014[4]](https://mssqlwiki.files.wordpress.com/2013/12/clip_image0144.jpg "clip_image014[4]")
}
If the client is unable to get the ticket check if it not able to retrieve the ticket only the ticket for SQL Server (or) not able to get any tickets. You can use below commands
Klist get Host/FQDN of DC where SQLServer is installed
Klist get Host/FQDN of SQLServer Machine name
If all the tickets are failing then most probably the issue should be with DNS/Network setting, you can troubleshoot further based on the error you receive from klist or collect Netmon traces to troubleshoot further.
8. If the client is able to get the ticket and still Kerberos authentication fails?
Ping the SQL Server name and IP address (with –a ) and identify if it is able to resolved to fully qualified name DNS name, If it is not able to resolve to FQDN of SQL Server then fix the DNS settings
9. How to Collect Netmon traces and identify Kerberos authentication failure?
Wait for my next blog
If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group
Thank you,
Karthick P.K |My Facebook Page |My Site| Blog space| Twitter
Disclaimer:
The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights
MSSQLWIKI 
Velmani said
As always , you will rock with your posts… fantastic one…
Gopalakrishnan Arthanarisamy said
Excellent Karthick.
Ramraj said
Nice post…
Vimal said
Really superb
Ramu said
Excellent article 🙂
추숙 said
Nice post…^___^
Kushagra said
Just a small question is this possible to that my sessionid’s auth_schme is–> Kerberos for net_transport(TCPID) where as in SQL Server error log the message should be coming like this:–
The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x202b, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. | SQLServerScribbles.COM said
[…] Above error occurs when the kerberos authentication fails in SQL Server you can follow the simple steps below to fix the Kerberos authentication failures. More detailed troubleshooting steps for Kerberos authentication failure is documented in https://mssqlwiki.com/2013/12/09/sql-server-connectivity-kerberos-authentication-and-sql-server-spn-s… […]
Top SQL Server blogs from MSSQLWIKI « MSSQLWIKI said
[…] SQL Server connectivity, Kerberos authentication and SQL Server SPN (Service Principal Name for SQL&… […]
Asif said
If sql server database engine and agent are running with two different service account, do we need to follow any thing special while manually registering the SPN, means read service principle name and write service principle name permission should be given to only sql server database engine service account or to both(sql server database engine and agent service account)?
www.cad23d.pl said
Using ingenious technology and innovation the Romans made an Empire that withstood the test of time.
The simple truth is, people join MLM opportunities as
a consequence of who introduced them. If you’re still with a loss, you’ll be able to contact the buyer care team either by email, live chat,
or phone during standard west coast business hours.
http://www.navitas.com.pl said
In exactly the same sense, business people would be
wise to present their workers something to unite under. She invites you to visit her site where she is going to share a proven method to start an online business.
Donnie Jonston may be the author of this short article about
how you can make money on Ebay Donnie has years of work experience
as a writer as well as working with drop shippers in a very variety of entrepreneurial ventures.
http://www.itad-bb.pl/ said
Bulks from the advertisers are primarily private property owners,
letting managers and property agents. You can run your
home based business perfectly should you become cordial on the customers.
Donnie Jonston could be the author of this short article about the way
to make cash on Ebay Donnie has a lot of work experience like a writer
plus working with drop shippers inside a variety of entrepreneurial ventures.
assassinsarms.pl said
Often we hear experts on television that report a particular stock is likely to soar and now is
the time to acquire. She invites that you visit her site where she is going to share a proven method to start an business online.
ll have the practical guidance you’ll need on how to find a concierge business”.
http://www.behapeks.com.pl said
In exactly the same sense, business owners would be wise to offer their workers something
to unite under. Each auction could be conducted which has a
different set of terms including bid increments, variety of auction rounds and expense reimbursement for the
stalking horse. I have witnessed my share of scams, and have the truth is
done a great job avoiding being taken for any sucker and I’m
here to tell you, Ameriplan just isn’t a scam.
http://help.hiretheworld.com/ said
The dedicated team of AVG professionals is accessible here all
round the hands of time, whom you are able to reach by calling around the AVG tech support number.
An attention grabbing attractive website is essential in the technologically advanced and highly competitive market of current age, for the success of business.
I have seen my share of scams, and have the truth is done a good job avoiding being taken for a sucker and I’m here to share with you, Ameriplan just isn’t a scam.
dialoguemaps.informatik.uni-hamburg.de said
The dedicated team of AVG professionals can be acquired here all
round the hands of time, whom you are able to reach by calling for
the AVG tech support number. The the fact is, people join MLM
opportunities as a result of who introduced them.
The business degree raises one’s social standing: in short, it opens to suit your needs doors that would have otherwise remained closed for your
requirements.
http://hexi.pl said
The Home Business Success Academy is a coaching establishment that assists enterprisers and home business operators how to grow
their constitutions employing target marketing and
assorted processes to produce a business. This is the major reason that has using a
health insurance policy in place is vital for
your lifestyle. In most cases building and starting a business means taking many
risks, which is exactly why many people don’t go into business.
Fix A Service Principal Name Spn Could Errors - Windows XP, Vista & Windows 7, 8 said
[…] SQL Server connectivity, Kerberos authentication and SQL … – SQL Server connectivity, Kerberos authentication and SQL Server SPN (SQL Server Service Principal Name ) Most of you would already be aware of Kerberos …… […]
imgur said
Drupal is one of these effective software packages that helps people and businesses publish content
on their websites. The web design on a web hosting
providers website is a quick and easy indication of the quality of the provider.
Japanese Gardens – Although the gardens are technically in Fort Worth, Texas, it is only
a short drive.
How To Fix A Service Principal Name Spn Emails Errors - Windows Vista, Windows 7 & 8 said
[…] SQL Server connectivity, Kerberos authentication and SQL … – SQL Server connectivity, Kerberos authentication and SQL Server SPN (SQL Server Service Principal Name ) Most of you would already be aware of Kerberos …… […]
Faisal said
the error code that I’m getting in my error log is 0x8009030c
it is intermittent and can last for several minutes and then clients are able to connect. While this issue happens, I’m able to connect remotely to my standalone SQL 2008 R2. I’m trying to figure out how to track this issue. doesn’t look like an SPN issue but i did verify and the SPN exists. Server has not been restarted recently (so SPNs shouldn’t be automatically getting dropped and recreated)
2014-11-07 00:00:32.830 spid23s This instance of SQL Server has been using a process ID of 1528 since 10/19/2014 7:06:49 PM (local) 10/19/2014 11:06:49 PM (UTC). This is an informational message only; no user action is required.
(today being 11/07/2014)
any thoughts on this one? here’s the full error if that helps.
2014-11-07 00:11:19.740 Logon Error: 17806, Severity: 20, State: 14.
2014-11-07 00:11:19.740 Logon SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. [CLIENT: xxx.xx.xx.xx].
2014-11-07 00:11:19.740 Logon Error: 18452, Severity: 14, State: 1.
2014-11-07 00:11:19.740 Logon Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: xxxx.xx.xx.xxx]
both the client and servers are in the SAME domain, so i know it’s NOT a trust issue. so these messages seem misleading to me.. there’s no other error message in Errorlog right before or after these and there’s no additional info that i can find in the event logs either.
How To Fix A Service Principal Name Spn If I Could Errors - Windows Vista, Windows 7 & 8 said
[…] SQL Server connectivity, Kerberos authentication and … – SQL Server connectivity, Kerberos authentication and SQL Server SPN (SQL Server Service Principal Name ) Most of you would already be aware of Kerberos …… […]
Roland said
Hi,
I’m running a two-node SQL AlwaysOn cluster SRVSQL01 and SRVSQL02 with a few sql server 2012 instances. Clients are connecting via virtual instance name e.g SRVSQLBLA. SQL Server Service is running under a domain service account.
1) SQL Server itself just tries to register an SPN for the FQDN\Instance and FQDN\PortSQL but no virtual instance names. Server log shows Error: 0x2098 at startup, allthough I set the permissions “read service principle name” and “write service principle name” on the computer account of the cluster nodes for the service account as well as “write public information” on the service account itself.
I can’t get it to work, SPNs are not getting registered while starting up the sql server instance. With a group membership of Domain Adminstrators or Administrators for the service account registering the SPN at instance startup succeeds.
2) With manually registered SPNs kerberos is still not working when connecting using the virtual instance name. It’s still using NTLM although I verified the SPNs using setspn -L …
I registered the SPNs for the FQDN as well as the Netbios name of the virtual instance, for both with the port and instance name.
Any suggestions?
Kind Regards,
Roland
Tommy said
“ How to Collect Netmon traces and identify Kerberos authentication failure?
Wait for my next blog
”May I know if you have posted this blog:-)
Mat said
Great post with more information on the subject. Very much appreciate this!
www.chaseclocks.com/ said
get the best click here available
sqlfrndz said
Here is the Dead easy way to fix.. It does the same thing as described here but with Nice , easy Interface..called Microsoft® Kerberos Configuration Manager for SQL Server®
https://www.microsoft.com/en-us/download/details.aspx?id=39046
Venkat said
Great post. This has really helped me.
SSPI handshake failed with error code 0x8009030c
We have done the OS upgrade and started getting this issue. Thanks a lot Karthik
Kathaleen said
Should your website needs much more traffic, it’s likely
you have already looked over other advertising options.
However, there are incredibly other marketing avenues that you can try so that you can improve
your traffic, however, seo is one of the very guidelines on how to generate massive amounts of traffic which you have never seen before.
Scott said
WOW did this save my tail! I have been working on a difficult customer recreation where we have to create a trusted dual domain SQL setup but for whatever reason no matter what we did the SPN wouldn’t work and we confinued to default to NTLM. Made the ADSIEDIT changes in step 3 and POOF now kerberos is the default protocol on our queries and we can move forward. I spent countless hours troubleshooting the kerberos piece with our remote folks and they were adament about the configuration being off. Turns out that step 3 fixed us up and we are working! Thanks a bunch… what took me 14 hours of work on monday to set the environment up and troubleshoot, was resolved in less than an hour with your doc… 🙂
How To Enable Kerberos Authentication Sql 2008 | People Life said
[…] SQL Server connectivity, Kerberos authentication and SQL … – SQL Server connectivity, Kerberos authentication and SQL Server SPN (SQL Server Service Principal Name ) Most of you would already be aware of Kerberos authentication … […]
How To Enable Kerberos On Sql Server 2008 | People Life said
[…] SQL Server connectivity, Kerberos authentication and SQL … – SQL Server connectivity, Kerberos authentication and SQL Server SPN (SQL Server Service Principal Name ) Most of you would already be aware of Kerberos … […]
» Dedicated Server Holding Windows 2008 Sql. said
[…] SQL Server connectivity, Kerberos … – … – SQL Server connectivity, Kerberos authentication and SQL Server SPN (SQL Server Service Principal Name ) Most of you would already be aware of Kerberos … […]
SQL Server – Resolve – Cannot generate SSPI context | SQL Jana said
[…] SQL Server connectivity, Kerberos authentication and SQL Server SPN (Service Principal Name for SQL … […]
André Krämer said
Great Post!
Thank you. I had an issue connecting to a sql server instance from a PC that was not domain joined using stored domain credentials in the windows credential manager. From time to time I’ve got the message “login from untrusted domain” and the sql connection failed. Step 3 + giving public write access to the “SELF” account finally solved my issue.
Sharice Ghera said
SQL Server connectivity, Kerberos authentication and SQL Server SPN (Service Principal Name for SQL Server)
[…]Most people won’t ever be able to make hundreds of thousands in Network Marketing.[…]
Kalpa Supplier said
[…] Kalpa Pharmaceuticals Kalpa Pharmaceuticals Kalpa Pharmaceuticals Kalpa Pharmaceuticals Kalpa Pharmaceuticals Kalpa Pharmaceuticals Kalpa Pharmaceuticals Kalpa Pharmaceuticals Kalpa Pharmaceuticals Kalpa […]
q said
The Giants peuvent avoir également perdu le demi défensif du-Prince Amukamara pour la saison, la sortie du jeu au deuxième trimestre avec une blessure au biceps et de ne pas revenir à la programmation après la mi-temps.
login failed for user nt authorityanonymous logon management studio - portalall said
[…] SQL Server connectivity, Kerberos authentication and SQL … […]