Understanding Kerberos and NTLM authentication in SQL Server Connections

  • Article

In this post, I focus on how NTLM and Kerberos are applied when connecting to SQL Server 2005 and try to explain the design behavor behind several common issues that customers frequently hit.

On this page:

Kerberos VS NTLM.

Requirements for Kerberos and NTLM in SQL Connections.

When are Kerberos and NTLM are applied when connecting to SQL Server 2005.

Common issues and workaround.

Troubleshooting Tips checklist.

 

I. Kerberos VS NTLM

NTLM Authentication: Challenge- Response mechanism.

In the NTLM protocol, the client sends the user name to the server; the server generates and sends a challenge to the client; the client encrypts that challenge using the user’s password; and the client sends a response to the server.If it is a local user account, server validate user's response by looking into the Security Account Manager; if domain user account, server forward the response to domain controller for validating and retrive group policy of the user account, then construct an access token and establish a session for the use.

Kerberos authentication: Trust-Third-Party Scheme.

Kerberos authentication provides a mechanism for mutual authentication between a client and a server on an open network.The three heads of Kerberos comprise the Key Distribution Center (KDC), the client user and the server with the desired service to access. The KDC is installed as part of the domain controller and performs two service functions: the Authentication Service (AS) and the Ticket-Granting Service (TGS). When the client user log on to the network, it request a Ticket Grant Ticket(TGT) from the AS in the user's domain; then when client want to access the network resources, it presents the TGT, an authenticator and Server Principal Name(SPN) of the target server, contact the TGS in the service account domain to retrive a session ticket for future communication w/ the network service, once the target server validate the authenticator, it create an access token for the client user.

II. Requirements for Kerberos and NTLM authentication

Kerberos, several aspects needed:

1) Client and Server must join a domain, and the trusted third party exists; if client and server are in different domain, these two domains must be configured as two-way trust.

2) Registered SPN. Service Principal Name(SPNs) are unique identifiers for services running on servers. Each service  that will use Kerberos authentication needs to have an SPN set for it so that clients can identify the service on the network. It is registered in Active Directory under either a computer account or a user account.

   Service Principal Name

   An SPN for SQL Server is composed of the following elements:    

   • ServiceClass: This identifies the general class of service. This is always MSSQLSvc for SQL Server. 
   • Host: This is the fully qualified domain name DNS of the computer that is running SQL Server. 
   • Port: This is the port number that the service is listening on.  

     eg:  MSSQLSvc/myserver.corp.mycomany.com:1433

NTLM

NTLM requires user's password to formulate a challenge-response and the client are able to prove its identities without sending the password to server. Thus you can tell if your client running under System Context w/o credential, what might happen?

NTLM fallback

NT LAN Manager is the authentication protocol used in Windows NT and in Windows 2000 work group environments. Windows Server 2003, Windows XP, and Windows 2000 use an algorithm called Negotiate (SPNEGO) to negotiate which authentication protocol is used. Although the Kerberos protocol is the default, if the default fails, Negotiate will try NTLM.

III. When are Kerbers and NTLM applied when connect to SQL Server 2005.

Under condition that you are using Integrated Security or trusted connection which use windows authentication.

1) Kerberos is used when making remote connection over TCP/IP if SPN presents.

2) Kerberos is used when making local tcp connection on XP if SPN presents.

3) NTLM is used when making local connection on WIN 2K3.

4) NTLM is used over NP connection.

5) NTLM is used over TCP connection if not found SPN.

To undersand these scenarios, first you need to know hwo to verify your SQL Server SPN exists:

download the SetSpn.exe from https://www.microsoft.com/downloads/details.aspx?FamilyID=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&DisplayLang=en

At a command prompt, type:

setspn -L <Account>

Account could be either <machinename> or <domainusername>

a. If your SQL Server running under LocalSystem or NetworkService account, you should be able to

see SPN by:

setspn -L <hostserver that your sql installed>

b. If your SQL Server running under a domain user account, you should be able to see SPN by:

setspn -L <mydomain><username>

c.If the domain user is non-admin, you can ask your domain administrator to register the SPN under

your account if you must use Kerberos authentication.

setspn -A <mydomain><username>

d. If your sql server is running under a local machine admin account, you can either ask your

domain administrator or run setspn under your domain credential to add the SPN.

Summary, SQL Server would automatically register SPN during start up if:

a. Your sql server running under LocalSystem/Network Service/Domain admin user account.

b. TCP/IP protocol is enabled.

Otherwise, you need to manually register SPN if forcing Kerberos authentication.

Normally, if you are making TCP connection, SQL driver on the client tries to resolve the fully qulified DNS name of the server that is running SQL, and then format the SQL specific SPN, present it to SPNEGO, later SPNEGO would choose NTLM/Kerberos depends on whether it can validate the SPN in KDC, the behavior is different from OS to OS, in most case, if SPN was not found, Kerberos authentication failed, it fallback to NTLM, but there is exception like in above case 2), if Kerberos authentication failed, it would not fallback. If you are making NP connection, SQL driver generate blank SPN and force NTLM authentication.

IV. Common issues and Workaround.

[1] "Login Failed for user 'NT AuthorityANONYMOUS' LOGON"

In this scenario, client make tcp connection, and it is most likely running under LocalSystem account, and there is no SPN registered for SQL instance, hence, NTLM is used, however, LocalSystem account inherits from System Context instead of a true user-based context, thus, failed as 'ANONYMOUS LOGON'. See https://support.microsoft.com/kb/132679.

The workaround here is

a. ask your domain administrator to manually register SPN if your SQL Server running under a domain user account.

b. use NP connection.

c. change your sql server to run under either localsystem account or networkservice account.

Here, a is recommended.

[2] "Login Failed for user ' ', the user is not associated with a trusted SQL Server connection".

In this scenario, client may make tcp connetion, plus, running under local admin or non-admin machine account, no matter SPN is registered or not, the client credential is obviously not recognized by SQL Server.

The workaround here is:

Create the same account as the one on the client machine with same password on the target SQL Server machine, and grant appropriate permission to the account.

Let's explain in more detail:

When you create the same NT account (let's call it usr1) on both
workstations, you essentially connect and impersonate the local account of
the connecting station. I.e when you connect from station1 to station2,
you're being authenticated via the station2's account. So, if you set the
startup account for SQL Server (let's assume it's running on station2) to be
station2's usr1, when you connect to SQL from station1 with station1's usr1
login, SQL will authenticate you as station2's usr1.

Now, within SQL, you can definitely access station1's resources. Though, how
much access will depend on station1's usr1 permission.

So far, SQL only deal with an user who is part of the sysadmin role within
SQL Server. To allow other users (non-sysamdin) access to network resources,
you will have to set the proxy account. Take a look at the article for
additional info.

https://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

[3] "Could not open a connection to SQL Server[1326]"

The same root cause as [2], just is making np connection.

[4] "Login failed for user '<domain><machinename>$' "

In this scenario, you client probably running under LocalSystem account or NetworkService account, so, just need to grant login to the account "domainmachinename$" in SQL Server.

[5] "Login failed for user 'NT AuthorityNetworkService'"

This is a typical authorization failed case, and it probably when client running ASP.NET application and use ASPNET account or network service account.

workaround, see  http://support.microsoft.com/kb/316989/

[6] Can not generate SSPI Context.

This is typical Kerberos authentication failure, there are various situations that can trigger this error. see blog: https://blogs.msdn.com/sql_protocols/archive/2005/10/15/481297.aspx

https://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx

The major reason is due to the Credential Cache( is used by Kerberos to store authentication information, namely the TGT and session ticked is cached so that can be used during their lifetime.)

The most general workaround is: clean up credential cache by using "klist.exe -purge" or kerbtray.exe or just reboot machine.

See more detail about various cause and solution in https://support.microsoft.com/kb/811889.

Differenciate Authentication failed and Authorization failed.

When you saw error " Login failed for user ' ' ...." or " Login failed for user '(null)' " or " ANONMOUS LOGON", these are authentication failure.

When you saw error like " Login failed for user '<username>' ", these are authorization failure, which is related to your SQL server security settings.

The final part gives troubleshootin tips checklist for authentication fail which is the focus of this blog.

V. Troubleshooting Tips checklist

[1] Verify computer settings

https://technet2.microsoft.com/WindowsServer/en/library/e1c3f70d-f8b3-4642-93c6-61421fd1292e1033.mspx?mfr=true

[2] Verify DNS name resolution

The key factor that makes Kerberos authentication successful is the valid DNS functionality on the network.

ping <remoteserver> , ipaddress should return

ping -a <ipaddress> , FQDN should return

nslookup, type the ipaddress, should get FQDN, or type FQDN should return ipaddress.

[3] Verify NTLM works.

try command:

"net view \server", or "net view \ipaddress".

[4] Verify SPN set

See which account SQL Server is running under, if SQL Server fails to register SPN, there is errorinfo in ERRORLOG, but you should doublecheck whether expected SPN was manually registered by other people.

[5] Clean up your client credential cache and retry see whether the problem persists.

[6] Then go to part III, to see your scenario falls into which case listed, and analyze whether the problem is included in the Common issues part IV, and applied the solution. Again, be careful to differenciate authentication error and authorization error. If you face authorization error, recommend post your question to the security forum: https://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1

[7] Make sure your SQL Server Protocol setting is correct for NTLM and Kerberos before go to step [8].

a. You are using windows authentication.

b. You already grant proper permission to the windows account,

c. Your server has SPN registered or not as you expected, also the port in SPN is the one that sql server is listening.

d. If making remote connection, you enabled "File and Printer Sharing" in the firewall on your remote server.

e. TCP/IP or NP is enabled.

f. Your client connection string specify the correct target server name and sql instance name.

[8] If you find it is pure Kerberos or NTLM issue, you need to check system log and  security log or even do netmon to gather Kerberos or NTLM error code for further debugging. 

See "Diagnose Tool" secion in this: https://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx#E2HAC

Summary:

If you face problem that did not list out in this post, please provide following info w/ your problem:

1) Which account your client is running under?

2) Which account your SQL Server is running under?

3) Is SPN registered for your SQL Server?

4) Does your client and server join the domain? Are they in the same domain?

5) Which OS your client and server is on?

6) What is the error message?

7) What error info in your SQL Server ERRORLOG?

8) What is your connection string?

9) Local connection or remote?

10) Linked server involved?

11) Any Kerberos delegation involved? double-hop or single-hop?

If your scenario invovle linked server and kerberos delegation, please check blog:

https://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

MING LU

SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Comments

  • Anonymous
    December 11, 2006
    This is not a blog post. This is documentation.

  • Anonymous
    January 01, 2007
    The comment has been removed

  • Anonymous
    January 02, 2007
    I followed these instructions and couldn't work out why it worked then stopped working later.  I eventually discovered that it was due to a bug in SQL Server 2005 RTM + SS2005 SP1. The workaround of using Named Pipes for the connection between the linked servers works a treat and is much less hassle. http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

  • Anonymous
    February 05, 2007
    IF YOU WERE LOOKING FOR ONE HERE YOU GO..........The Axis StorPoint CD+ is a cd/dvd network storage server. This device is ideal for multiple users with workstations using different operating systems. It's a match made in heaven for IT departments, manufacturing, education and medical professionals. http://cm.ebay.com/cm/ck/1065-29296-2357-0?uid=121939807&site=0&ver=LCA080805&item=250080916580&lk=URL

  • Anonymous
    March 23, 2007
    I'm running into an issue where a Windows Service that I wrote and runs as Local System tries to query my SQL 2005 DB on boot using Windows Authentication(SQL Express is the only dependency I have set) and I get a login failed error and it doesn't do what it is supposed to, but if I manually stop and start the service once the computer is booted it works fine, what other services are dependent in order to make this authenticate right away?

  • Anonymous
    March 26, 2007
    You will definitely need Lsass.exe to be running before Windows authentication (remote or otherwise) can be used.  I am unsure if you will need IIS to be running or not. Hope this helps, John

  • Anonymous
    June 25, 2007
    Hi, Can the following configuration work: ASP.NET application works on domain machine, the application pool is running under domain account.Database is running on workgroup machine. I've configured local account with the same name and password (as domain account used by ASP.NET application). Still I get "Login failed for user '<account_name>'." message. Can this configuration work? I understand that I need NTLM since the database is not in domain. What additional configuration is required for NTLM protocol? If it can not work at all can you shortly explain what step in NTLM authentication does not allow this? regards, Sergey.

  • Anonymous
    November 26, 2007
    Hi, I have domain users who try to access a website from XP client computers that are not a member of the domain. Internet Explorer -> IIS6 -> SQL Server 2005 Is it possible to log in when double hop Kerberos is used? Currently it only works inside the domain. Outside it switches to NTLM and the user gets the "Login Failed for user 'NT AuthorityANONYMOUS' LOGON" error message. Is it possible to use NTLM and Named Pipes and use impersonated users all the way to the database?

  • Anonymous
    November 26, 2007
    Just want to add that all users, inside and outside of the domain, uses the same hostname to access the site and that SPN has been set up.

  • Anonymous
    February 11, 2008
    Can someone shed some light on the following error: NT Status: STATUS_LOGON_FAILURE (0xc000006d) Is this in anyway way related to a failed Kerberose authentication failure? We are seeing this from the client machine.  What would be the cause for this typically? We have been unable to resolve this error and have been unable to locate possible causes for this online. Thanks,

  • Anonymous
    October 14, 2008
    We had a similar issue and we worked with Microsoft and they determined that our DBA installed SQL Server not under the default SA account but under a different user.  We had to make a custom SPN because the one that SQL installed didn't work. http://www.teamofcoders.com

  • Anonymous
    December 02, 2008
    Error: 18452 Login failed for user ‘null‘ , … • “Null” or ‘’ means that client windows token is not trusted

  • Anonymous
    February 22, 2009
    The comment has been removed

  • Anonymous
    February 25, 2009
    Sam, NTLM is used here. The error message about SPN is normal. The errorlog should contain more details about the login failure, then you can refer this blog to find out more. http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx Thanks, Xinwei

  • Anonymous
    March 22, 2009
    Hi, I'm new to SQL Server and NTLM. The IT installed a device that capture all the trafic to the SQL Server. They told me to change the SQL Server Agent Job to use this device (from security reasons). When I changed the SQL Server Agent Job (using the alias property) it failed with the error: "Login failed for user ''. The user is not ... Even though that the SQL Server Agent Job and the SQL Server are on the same machine (I just route the network to use the new device). Thanks, Uri

  • Anonymous
    March 26, 2009
    I was asked yesterday about sharing my security links for SQL Server, so I thought I would post those

  • Anonymous
    July 20, 2009
    I am new to MS SQL and NTLM protocol. I want to know which version of the SQL server jdbc driver file supports NTLM authentication and what is the syntax for using it. Thanks for your help.

  • Anonymous
    July 20, 2009
    http://aspdotnetexpert.blogspot.com/2008/06/login-failed-for-user-sa-reason-not.html

  • Anonymous
    July 30, 2009
    The comment has been removed

  • Anonymous
    August 11, 2009
    I tried realize that in SQl Server 2000 down AD with function level Windowns 2000, and I don't get success result.

  • Anonymous
    December 09, 2009
    What do you mean by "NP Connection"?  It's mentioned a few times in the article, but it never defined.

  • Anonymous
    February 24, 2010
    Hi, Thanks for the share. We have some trusted domains under the same forest, one of the domain has 400 domain controllers, sometimes some of the DC is down and as a result users might not be able to be authenticated. Is it possible for SQL to authenticate specific domain through specific domain controller? Thank you very much.

  • Anonymous
    November 15, 2010
    NP connection www.teratrax.com/.../connecting_sql_server_express.html Connection protocols SQL Server 2005 supports a number of protocols for connecting client applications with the database server. These protocols are TCP, Named Pipes (NP), Shared Memory (SM), VIA, and HTTP. Only TCP, NP, and SM are supported in SQL Server Express.

  • Anonymous
    August 15, 2012
    Thank you for the post - it would have been even better if you had used a spell checker. :-

  • Anonymous
    July 23, 2013
    excellent blog..must say thanks for such knowledgeble post