[스크랩] SQL Server connectivity, Kerberos authentication and SQL Server SPN (Service Principal Name for SQL Server)

 

http://mssqlwiki.com/2013/12/09/sql-server-connectivity-kerberos-authentication-and-sql-server-spn-service-principal-name-for-sql-server/

 

SQL Server connectivity, Kerberos authentication and SQL Server SPN (SQL Server Service Principal Name )

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 0×80090311 while establishing a connection with integrated security; the connection has been closed
SSPI handshake failed with error code 0×80090304 while establishing a connection with integrated security; the connection has been closed

Note: For the last two errors error code translates to

Error -2146893039 (0×80090311): No authority could be contacted for authentication
Error -2146893052 (0×80090304): 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)

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

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.

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.

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

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)

}

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.

}

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,