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