Understanding Kerberos and NTLM authentication in SQL Server Connections

Understanding Kerberos and NTLM authentication in SQL Server Connections

Rate This
  • Comments 25

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 http://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 Authority\ANONYMOUS' 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 http://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.

http://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 "domain\machinename$" in SQL Server.

[5] "Login failed for user 'NT Authority\NetworkService'"

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: http://blogs.msdn.com/sql_protocols/archive/2005/10/15/481297.aspx

http://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 http://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

http://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: http://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: http://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:

http://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

Leave a Comment
  • Please add 6 and 4 and type the answer here:
  • Post
  • This is not a blog post.

    This is documentation.

  • Incorrect DNS can lead to various network connectivity issues. In this post, I explain how it affects

  • 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

  • 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

  • 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?

  • 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

  • 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.

  • 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 Authority\ANONYMOUS' LOGON" error message.

    Is it possible to use NTLM and Named Pipes and use impersonated users all the way to the database?

  • 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.

  • 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,

  • 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

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

  • Hi,

    I have a stand-alone(not connected to any domain) 64 bit machine with Windows Server 2008 Enterprise SP1.

    On this machine I have SQL Server 2008 enterprise edition version 10.0.1600.22  64-bit default instance.  It is running under 'LocalSystem' account. The authetication is 'mixed' mode.

    when the SQL server service starts, it gives the following message, which is fine.

    "The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. 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."

    Then when I try to login through SQL server management studio (from the same machine) using windows authentication, I get the following error:

    "Login failed for user 'mymachinename\Administrator'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <named pipe>]"

    I have only 'named pipe' enabled as a protocol. I have used "net view \\server" command to verify that NTLM is working fine.

    What could be the problem here? is NTLM fallback not working?

    Any help or pointers will be greatly appreciated.

    Thanks

  • 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

  • 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

Page 1 of 2 (25 items) 12