SQL Server Blog

9 MIN READ

Understanding Kerberos and NTLM authentication in SQL Server Connections

Microsoft

Mar 23, 2019

First published on MSDN on Dec 02, 2006

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 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 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 "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 h ttp://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?