Kerberos和NTLM - SQL Server连接的那点事

当我们使用Windows Authentication去连接SQL Server的时候，SQL Server可能会使用Kerberos或者是NTLM来进行认证，有时间就会因为认证失败的缘故造成各种登录错误（login failed）。解决这些问题往往令人很迷惑。今天我们就来做一个简单的介绍。

 1. Kerberos VS NTLM

NTLM认证：Challenge – Response 模式

在使用NTLM协议时，客户端发送用户名到服务器端；服务器生成一个challenge并发送给客户端；客户端使用用户的密码来加密这个challenge，然后发送response到服务器端。如果该账号是一个本机账号，那么服务器使用Security Account Manager来验证用户；如果账号是一个域账号，那么服务器把这个response请求转送到域控制器(DC)上来让域控制器调用组安全策略来做用户认证，然后服务器就可以构建一个安全令牌并建立一个session。

Kerberos认证：Trust-Third_party架构

Kerberos认证提供一种服务器和客户端相互认证的机制。Kerberos包含了三个关键组件：Key Distribution Center (KDC)，客户端用户，和一个运行所需服务的服务器。KDC是域控制器的一部分，它执行两个任务：认证服务(AS)和票据许可服务(TGS)。当客户端用户登录到网络上时，它会向用户所在域的AS去请求一个“票据请求票据”（TGT）。然后，当客户端想要访问网络上的某个资源的话，它就出示以下东西：TGT，认证码，Server Principal Name（SPN）；有了这些东西，客户端就可以从服务所在的域中的TGS获得session票据。使用这个session票据，客户端就可以和网络上的服务进行交流，该服务会验证“认证码”然后创建一个访问令牌给客户端用户，接下来客户端就可以登录上该服务了。

 2. Kerberos和NTLM认证的要求

Kerberos的要求：

1) 客户端和服务器端一定要加入域，如果客户端和服务器端在不同的域的话，这两个域一定要被配置被互相信任。

2) 注册SPN。SPN是服务器上所运行服务的唯一标示。每个使用Kerberos的服务都需要一个SPN，这样客户沪端才可以辨认这个服务。SPN是注册在AD上的机器账户或者是域用户账户下的。比如，如果SQL Server运行在Local System或Network Service账户下，那么SPN就需要注册在机器账户下。如果SQL Server运行在域用户下，则SPN就需要注册在该域用户下。

这个顺便提一下。当一个服务运行在Local System或Network Service下的时候，我们称该服务运行在机器帐户下。因为，从AD的角度来看，每台机器的Local System或Network Service都代表了这条机器本身，而机器本身也是一种用户，叫做机器帐户。

SPN

一个SQL Server的SPN由以下元素组成:

· 服务类型：它标示了服务的泛用类。对于SQL Server而言，它永远是MSSQLSvc。

· 主机：它有两种形式。一个是运行SQL Server的计算机的fully qualified domain name (FQDN)。还有一种就是SQL Server的计算机的netbios名字，俗称短名。

· 端口号/实例名：服务所监听的计算机端口号。对于SQL Server而言，如果SQL运行在默认端口（1433）上，则端口号可以省略。

从SQL Server 2008开始，Kerberos可以支持TCP, Named Pipes和Shared Memory三种协议。因此对于SQL Server 2008我们也可以使用SQL Server的实例名来替代端口号（仅就命名实例而言）。

一些例子是：

MSSQLSvc/myserver.corp.mycomany.com:1433

MSSQLSvc/myserver:1433

MSSQLSvc/myserver.corp.mycomany.com

MSSQLSvc/myserver:

MSSQLSvc/myserver.corp.mycomany.com:instancename

MSSQLSvc/myserver:instancename

要注意的是，我们推荐为FQDN和netbios都分别注册SPN。这是因为有的客户端程序可能会使用短名的SPN来请求session ticket，但有的程序会使用FQDN的SPN来请求session ticket，有时候我们很难预见到应用程序到底想要哪种SPN。保险起见我们建议，为FQDN和netbios都注册上SPN。

NTLM

NTLM需要用户的密码来构建Challenge-Response，这样客户端才可以在不需要把密码通过网络发送到服务器端的情况来证明自己。所以说，如果你的客户端应用程序运行在机器账户下（比如local system，network service等），会发生什么呢J。

要测试NTLM是否正常。可以使用以下命令:

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

NTLM Fallback

Windows使用一种被称为negotiate (SPNEGO)的算法来协商应该使用哪种认证方式。当连接SQL Server时，客户端驱动解析SQL Server的DNS名字然后组成它是以想要的SPN，讲这个SPN交给SPNEGO，让SPNEGO到KDC上去查找是否有合适的SPN，并以此来决定使用Keberos或NTLM。从Windows Server 2003开始，Kerberos都是默认的认证方式。但是当Kerberos失败的时候，可能会去尝试使用NTLM，这就被称为fallback。为什么要说“可能”呢？

因为当网络上有没有SPN注册时候，这个时候Fallback才会发生。当网络上有SPN注册，但是如果注册SPN的账号不是SQL Server服务账号（也就是说AD中SPN确实存在，但是注册在了错误的账号下），这个时候，无论是否存在一个被注册在正确的SQL Server服务账号下的SPN，Kerberos都会失败，而这种失败是不会fallback到NTLM上的。也就是说这种情况下，无论NTLM是不是可以用，认证都会直接失败。

3. SQL Server什么时候用NTLM什么时候用Kerberos ？

当客户端使用Windows认证来连接SQL Server时（例如，连接字符串中有integrated security或者trusted connection字样的话）。

SQL Server 2000/2005

1) 当有正确的SPN被注册情况下，使用TCP协议来做远程连接时会使用Kerberos。

2) 在Windows XP的系统上，如果有正确的SPN注册，使用TCP协议来做本地连接时也会用Kerberos。

3) 在Windows 2003/2008上，做本地连接时会使用NTLM。

4) 使用named pipes连接时，会使用NTLM。

5) 当没有找到SPN时，TCP连接会使用NTLM。

6) 当域中存在错误的SPN时，认证失败。

SQL Server 2008/2008 R2

1) 当SPN被映射到正确的域或者内建机器账号时 (Local System, Network Service)，本地连接会使用NTLM，而远程连接会使用Kerberos。

2) 当没有找到注册在正确的域或内建机器账号下的SPN时，连接会使用NTLM。

3) 当域中存在错误的SPN时，认证失败。

检查是否有正确的SPN存在

我们首先要下载setspn.exe工具。

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

然后在命令行中输入：

Setspn –L <Account>

Account可以是一个域用户也可以使一个机器名

a. 如果你的SQL Server是使用Local System或者Network Service启动的话，<Acount>处就填写SQL Server所在机器的机器名。Local System或者Network Service

b. 如果SQL Server是使用域用户启动的话，那么<Acount>处就填写该域用户名即可。

输出结果可以告诉你是否有正确的（注册在正确帐户下的）SPN存在。

添加SPN

如果SPN不存在，那么我们可以使用setspn.exe来添加SPN。要注意，如果你当前登录的账号不是域管理员但却是本机管理员的话，默认情况下你是有为本账号注册SPN的权限的（但是你不能为其他账号注册SPN）。如果你无法使用SQL Server服务账号来登录Windows，或者你的账号不是本机管理员，你就需要联系你的域管理员来运行以下命令来注册SPN。

Setspn –A MSSQLSvc/myserver.corp.mycomany.com:instancename <Account>

此处<Account>和setspn –L的用法是一样。

需要提的是，SQL Server在启动的时候，会自动尝试到AD中注册它的SPN。当服务停止的时候，SQL Server会尝试注销该SPN。但是默认情况下，域的安全策略只赋予了机器账号以注册SPN的权限。也就是说，只有当SQL Server在用Local System帐户启动时，它才会有足够的权限来成功注册和注销SPN。如果SQL Server是用域帐户启动的话（比如说，SQL群集一定要使用域帐户才能启动），则默认情况下我们不得不手工的运行Setspn –A命令来为SQL Server注册SPN（当然域管理也可以开放注册SPN的权限给那个SQL Server用的域账号，但是这种情况并不多见）。如果自动注册和注销SPN失败的话，在SQL Server的ERRORLOG中会有一段警告信息。

这样的自动注册机制带来了一个隐藏的问题。就是域中可能存在SPN但是你从未意识到。假设一开始SQL Server的启动帐户是local system帐户，那么在你没意识到的情况下，SQL Server的SPN就被注册到了域中的机器帐户下。这个时候Kerberos会运行的很正常，给你的感觉是你不需要做额外配置Kerberos也能运行。但是如果以后SQL Server的启动帐户被改为域用户，你会发现Kerberos突然不能正常工作了。而且此时即使你手动为该域用户注册了一个SPN，Kerberos也无法成功。那是因为在域中存在了一个错误的SPN，就是之前注册在机器帐户下的那个SPN。此时我们一定要删除那个错误的SPN。

删除SPN

删除SPN的权限要求和添加SPN是一样的。命令行是：

Setspn –D MSSQLSvc/myserver.corp.mycomany.com:instancename <Account>

如何发现存在错误的SPN

前面提到，如果存在错误的SPN的话，那么或导致认证直接失败而不会fallback到NTLM。那么如何发现有错误的SPN存在呢？有两种方法：

1) 使用工具ldifde

Ldifde可以根据你的需求，把域环境中所有的SPN都输出出来。以下是一个例子。

ldifde -f C:\SPNs.txt -t 3268 -d dc= mycompany,dc=com -l serviceprincipalname -r (serviceprincipalname=MSSQLsvc*) -p subtree

使用以上命令，可以把mycompany.com域中所有的以MSSQLSvc开始的SPN都导出到C:\SPNs.txt文件中去。这样我们就可以打开SPNs.txt去查看是否有错误的SPN存在了。

2) Setspn –X

从Windows Server 2008开始，setspn有了新的选项-X。使用-X选项就可以查找域中是否存在重复的SPN。所谓重复的SPN值得是注册在不同账户下的相同SPN。那么则重复的SPN中至少有一个是错误的（也可能都是错误的）。

如何确认客户端使用Kerberos连接到了SQL Server

最简单的办法，是当连接建立后去查询SQL Server中的一个DMV：sys.dm_exec_connections。该DMV有一个列叫auth_scheme。该列会显示对应的连接所使用到底是NTLM还是Kerberos。

除了DMV之外，我们当然还可以通过在客户端和服务器端中间抓取网络包，然后分析网络包来判断使用的认证方式是哪一个。

此外，微软客户支持有一个内部工具叫SSPIClient，使用该工具去连接SQL Server, 它的log不但可以获知使用了什么协议来连接SQL Server，还能告诉为什么Kerberos失败了（如果失败的话）。这样就可以采用有效的手段来解决阻碍Kerberos运行的问题。

4. 什么时候我们需要Kerberos

既然NTLM和Kerberos都可以作为认证的方式，那为什么我们需要花大工夫来配置SPN使得Kerberos可以工作呢？为什么Windows默认先尝试Kerberos，只有当Kerberos失败时才会尝试NTLM呢?

我们推荐使用Kerberos来作为SQL Server的连接协议，至少是可以获得三种好处。

1) 安全性上的优势。NTLM在面对一些安全攻击的时候还是相对脆弱，比如它不能很好的防范中间人（man-in-the-middle）的攻击。一些使用Kerberos的关键优势，可以从这里找到。

https://msdn.microsoft.com/en-us/library/cc280744.aspx

2) 有一些应用程序，包括微软的SCCM，SCOM等，是要求运行在Local System帐户下的。Local System账号会使得NTLM无法工作。这个时候我们就需要把Kerberos配置通才能使得应用程序可以成功登陆SQL Server。

3) 在所谓的double-hop的应用场景下，我们需要使用委托的功能把客户端的身份信息传送到最后端的SQL Server。此时只有Kerberos才能完成“委托”的重任。否则在最后端的SQL Server就会收到一个登陆失败的提示。Double-hop和委托又是一个很大的概念，他们的配置方式在本文中就不详细展开了。有兴趣的话，可以去参阅以下文章及其扩展连接，内容非常详细。

https://www.microsoft.com/download/en/details.aspx?id=4754

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

5. 常见问题

[1] "Login Failed for user 'NT Authority\ANONYMOUS' LOGON"

这个时候客户端很可能是运行在Local System帐户下的，而且SQL Server没有注册SPN。于是，NTLM就被使用了。而由于Local System账号继承自System Context而不是一个真实的user context，于是就被当成ANONYMOUS LOGON。

解决方法就是在SQL Server的服务帐户下手工注册SPN

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

或者 "Login Failed for user ' （ (null)', the user is not associated with a trusted SQL Server connection".

这种情况下，很明显就是客户端用户没能被SQL Server识别出。

对于这个问题，解决方案要分两种情形。

1) 如果客户端程序是运行在一个本机用户（非域用户）或者是一个非本机管理员权限的机器帐户（非local system）下，那么无论SQL Server是否有注册SPN，都会得到这个错误。那么解决方案就是:

在服务器端创建一个和客户端用户“同用户名用密码”的本机账号，然后在SQL Server中赋予相应的登录权限。这就是所谓pass through的方式。此时你实际上是在使用SQL Server那台计算机的同名帐户来访问SQL Server和相关的其他资源。因此SQL Server机器上该帐户的权限设置决定了客户端的操作权限。

2) 如果客户端应用程序是运行在一个域用户下的话，那么该错误就说明Kerberos的验证失败了，这往往是由于没有SPN或者SPN不正确造成的。这个时候你可以选择一下两个方案中的一个:

a. 建立正确的SPN，使得Kerberos能成功。

b. 干脆让客户端用NTLM来连接SQL Server。你可能需要去检查AD，如果有发现错误的SPN就删掉。

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

和错误[2]一样的问题，只不过[2]是发生在TCP连接上的，[3]一般是发生在Named Pipe连接上的。

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

这种情况下，你的客户端程序是使用Local system或者Network service运行的，最简单的解决办法就是在SQL Server的login中添加一个"domain\machinename$"账号。Machinename就是客户端的机器名。

[5] Cannot generate SSPI Context.

这是一个典型的Kerberos认证失败。造成这种错误的原因多种多样，最常见的还是SPN的问题，也发现过很多由于KDC工作不正常，或者TGS服务异常导致该错误的案例。我建议大家看一下这篇KB，写的比较全面：

https://support.microsoft.com/?id=811889

这里有两篇博客供参考：

https://blogs.msdn.com/b/sql_protocols/archive/2005/10/15/481297.aspx

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

明明都配置好了，为什么Kerberos就是不工作呢?

要提醒大家的是，在解决Kerberos相关问题的时候，我们可能会遇到这种情况：明明kerberos所需的所有条件都配置好了，但是测试连接时仍旧一直得到Kerberos错误或者是NTLM错误。这个时候大家不妨试一下如下两招:

1) 在一个域环境中可能存在多个DC，我们在troubleshooting的时候做的一系列改变只会作用在其中某一台DC上。有可能客户端连接SQL Server时使用的是另外一台DC，而此时这台DC还没有同步到你之前所做的那一系列改变。这个时候，我们不需要等DC间的自动同步发生，可以运行以下语句来强制DC间作同步:

Repadmin /syncall

2) Credential Cache也可能会是问题。Credential Cache是Kerberos用来在本机上缓存认证信息的。他主要包含TGT和Session票据。由于Credential Cache在本机上有一段的生存周期（通常是10小时），因此如果之前客户端得到过不正确的认证信息并缓存起来的话，在缓存过期前就会一直使用这个信息来访问SQL Server，于是就一直会得到错误。解决方法是以下3个办法中的任何一个来清除Credential Cache。

a. 用klist.exe –purge命令

b. 用kerbtray工具

c. 重启整个机器。

认证和授权的区别

认证(authentication)的错误，包括 Login failed for user ‘’, Login failed for user ‘(null)’,或者ANONYMOUS LOGON。我们常见的18452错误就是认证错误。

当你看到Login failed for user <username>时，这其实是一个授权(authorization)错误。此时，SQL Server已经识别出了要求登录用户，但是由于SQL Server内部的安全相关的设置，登录操作没有成功。我们常见的18456错误就属于这类错误。

Ø Error: 18456, Severity: 14, State: XX.

我们可以通过State:XX来判断到底是什么原因导致的授权失败。但是，由于担心恶意的客户端可能通过state来猜测失败原因并采取相应手段来攻击服务器，State是不能返回客户端的。它会被写入SQL的ERROELOG文件。

失败state对应的原因：

1 通用错误

2 远程登录的登录在服务器中不存在

3，4 解密内存中加密的密码失败

5 SQL登录不存在

6 连接的SQL登录不符合登录类型列表

7 登录功能关闭

8 密码不符合

9密码不可用（登录时正在修改密码）

10 账户策略验证失败

如果以下密码策略检查评估返回假：

（1）登录成功

（2）密码已过期

（3）用户需要更改密码

然后我们设置登录状态，并返回一个失败state10

11 windows验证没有被授权可以连接

12 SQL验证没有被授权可以连接

13 服务器暂停，禁止所有登陆

14 登录不能用于此连接的接口类型

15 连接字符串中指定的数据库无效

16个默认数据库是无效的

17默认语言为无效

18这种登录类型密码不能改变

19无法解密加密的内存中的新密码

• Anonymous
  October 08, 2011
  写错字啦1) 客户端和服务器端一定要加入域，如果客户端和服务器端在不同的域的话，这两个域一定要被配置沪入域，如果客户端和服务器端在不同的域的话，这两个域一定要被配置被互相信任。
• Anonymous
  October 25, 2011
  写得相当不错。可以顺带提一下SQL Cluster上的Kerberos认证。另外，“如何发现存在错误的SPN”这一段还可以加一种情况：SPN注册之后SQL服务密码被修改过，这种情况下会看到格式正确的SPN，但是抓SSPIClient log可以看到问题。