Using Kerberos with SQL Server

Kerberos is a widely accepted network authentication protocol that is used to provide a highly secure method to authenticate users.  Reliance is placed upon a trusted third party called the Key Distribution Center (KDC) to facilitate the generation and secure distribution of authentication tokens and symmetric session keys.  In a Windows environment, the operation of the KDC is assumed by the Domain Controller (DC) using the Active Directory (AD).  Furthermore, all Windows domain users are effectively Kerberos principals and are capable of engaging in Kerberos authentication.

Among the key benefits of Kerberos authentication that make it popular are:

  • Mutual authentication (the client can validate the identity of the server principal)

  • Secure authentication tickets (only encrypted tickets are used and passwords are never included in the ticket)

  • Integrated authentication (single sign-on, i.e., once the user is logged on, s/he does not need to log on again to access any service that supports Kerberos authentication)

Kerberos with SQL Server

SQL Server 2005 (and 2000) supports Kerberos indirectly through the Windows SSPI interface when using Windows integrated authentication (as opposed to SQL authentication).  However, Kerberos will only be used under certain circumstances as SQL Server allows SSPI to negotiate the authentication protocol to use; if Kerberos cannot be used, then Windows will fall back NTLM authentication.  Kerberos authentication is far more desirable than NTLM from a security (and, to a lesser degree, performance) point of view and I think it’s important to understand how to ensure Kerberos is used for remote connections when possible.  If you’re using integrated auth, you need to make sure that the follow are done:

  1. Both the client and server machines must be part of the same Windows domain, or else trusted domains.

  2. The server’s Service Principal Name (SPN) must be registered with the Active Directory (I’ll explain this in more detail below)

  3. The client must connect to the server using TCP/IP.  Assuming that the server has TCP/IP enabled, this can be accomplished by either placing TCP/IP at the top of the client’s protocol order or else prefixing the connection string with “tcp:”

Registering an SPN


The SPN is essentially a mapping between a principal name and the Windows account that started the server instance service.  This is needed because the client will use the server’s hostname and the TCP/IP port to which it connects to compose an SPN.  If the SPN mapping has not been performed, then the Windows security layer will be unable to determine the account associated with the SPN and Kerberos authentication will not be used.  In an attempt to facilitate this, the SQL Server 2005 instance will automatically try to register the SPN with the AD at startup if TCP/IP is enabled.  The problem is, however, that only a domain administrator or a Local System account has the authority to register an SPN.  Therefore, in the majority of cases where the service is started under a normal account, SQL Server will be unable to register the SPN for the instance.  This will not prevent the instance from starting but you will see the following entry in the error log:


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


The alternative is to have a domain admin manually register the SPN for the instance.   The format for an SPN is MSSQLSvc/FQDN:tcpport, where FQDN is the fully qualified domain name of the server and tcpport is the TCP/IP port number.  To register the SPN, the administrator will need to use the SetSPN.exe tool which is available from the Windows server resource kit (for Windows 2K3, you can find it here,;en-us;892777).  An example of the command is:


           setspn -A MSSQLSvc/ accountname


If an SPN already exists, then it must be deleted before it can be re-registered.  This is accomplished by a domain admin using the setspn -D command.


To verify that Kerberos authentication is being used, you may query the sys.dm_exec_connections DMV and look under the auth_scheme column, e.g.


select auth_scheme from sys.dm_exec_connections where session_id=@@spid


If Kerberos is being used, then it will display “KERBEROS”.


I should also mention that if the instance automatically registered an SPN at startup, then it will unregister it when the instance is stopped.


Potential Problems


If instance is configured to listen to a different port, then the SPN will need to be deleted and recreated using the new port number.  The problem is worse if the server is configured to use dynamic IP addresses as, potentially, a new SPN must be configured every time the server is started.  I recommend that a static IP address be used so that the SPN need only be registered once.  In addition, using a static IP address provides the additional benefit that the client can specify the TCP/IP directly in the connection string.  Doing this will prevent the need to rely upon SQL Browser to determine the port number (SQL Browser communication uses an unauthenticated UDP channel).


A couple of other considerations that are documented in the BOL are (i) the Dedicated Admin Connection (DAC) only uses NTLM so no SPN is registered for the DAC connection, and (ii) if the instance is configured to listen to multiple IP addresses, then the server will only attempt to automatically register the SPN using the first port that it identifies.

Il-Sung Lee
Program Manager, SQL Server Protocols

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

Comments (48)

  1. al says:

    Is the select statement "to verify that Kerberos authentication is being used" for SQL 2005? If so, is there an equivilant for SQL 2000?



  2. Unfortunately, querying resulting SSPI auth package of SPNEGO, whether it is NLTM or Kerberos, is not available in SQL 2000. This is new feature added in SQL 2005.

  3. Tim Toennies says:

    Will this also work when using the NT AuthorityNetwork Service account to control the SQL Server instances?  

  4. The “Cannot generate SSPI context” issue is described by in general….

  5. Bill Bruce says:

    After manually registering the spn for the sql server service account we still are receiving this error. The results of the above query are still displaying NTLM. Is there a work around to this problem?

  6. Hi, Bill

       1)Did you successfully register the SPN through Setspn.exe?

       2)you still receive which error?0x2098? If so, that means you are using invalid account to register SPN, check your DC to verify this account.

       3)Did you enable TCP/IP protocol and restart server?

       4)which service account your sql server is running under? It should be admin account such as domain admin account or localsystem account.

       5)Which OS you came across this problem, XP?

    In a word, try setspn.exe( here is a useful link about setspn,, see if you can register SPN? if it report error 0x2098, it means the account is invalid, contact your domain administrator to check the account in DC.



  7. Adarro says:

    I think I may have a firewall issue, but if someone could look at the following and shed some light or toss a tip I would be highly appreciative.

    I have a server ServerA which resolves internally to and externally to Somename.Outside.Com

    I am using a Domain Account and verified the SPN’s are correct via setspn -L and can log in using a trusted connection via any of the following internally:,1433




    Each of the above returns ‘Kerberos’ when checking the sys.dm_exec_connections system view.

    However, from outside, Somename.Outside.Com (and all variations) returns ‘Cannot Generate SSPI Context’.  ping -a resolves correctly and connecting to / direct IP via SQL authentication works fine. Also connects fine (as assumed) when using a VPN.

    I’ve done Matt’s ( delete SPN trick successfully, checked every setting under the KB articles and ‘Cannot Generate SPPI Context’ blog and the outside clients are members of the same domain.

    Is there an additional port (besides 1433) that is required when using SSPI?

    Server: SQL 2005 Server Standard Edition SP1

    Windows 2003 SP1

    Clients: Windows XP SP2 w/ SQL 2005 Express SP1

  8. Hi,Adarro

      First, double check "ping -a" returns the correct fully qualified domain name like somename.Outside.Com? Can you connect by using "sqlcmd /S somename /E" ? Are the client and server in the same domain, right? what is the client FQDN?

      Secondly, please download setspn.exe and klist.exe from SQL Server 2003 Resource Kit. And do "setspn -L <servermachinename>" whether you saw something like "MSSQLSvc/<server FQDN>:1433" ? If there is, could you contact you domain administrator to delete this SPN or use "setspn -D <SPN> machinename ", then try:

      (1) change your sql server service to run under localsystem account.

      (2) restart the service.

      (3) test the connection and let me know what you get.

      If there is no SPN, then go to your client box, do "klist -purge" and retry connection, let me know what you get.

    Good Luck!


  9. P says:

    We’re having an issue with linked servers/Kerberos and SPN’s.

    SPN is registered. Everything works ok.

    However it seems like every day or every other day the linked server breaks reverting back to NTLM. What gives? Checked SPN -l…service is there.

    Kerberos debugging on….no noticeable errors.

    Able to receive tgt and service tickets.

    Checked everything under the sun. Any suggestions as to why it works one day and then breaks?

  10. BR says:

    I’d suggest taking a network trace from the middle tier server for request from both client and the backend server.

    Also, check the settings according to the article "Troubleshooting Kerberos Delegation"

  11. SQL Server 2005: “Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’”.

    SQL Server 2000: “Login failed…

  12. Oli says:

    We just had a similar issue to hte one described above. In the end we resolved it by drop/creating the linked servers on both sides.

  13. Take a look at Nan’s recent blog about the "Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’” error message with DQ.



  14. Gabe Friedmann says:

    In my implementation, even after registering the SPN connections from SQL Server management Studio on the local box using TCP/IP always used NTLM.  Any connections from other boxes in the domain used Kerberos.

  15. Kostas Giagias says:

    In my company we use 2 SQL servers 2000 and the link works fine…

    Both servers are mixed mode. Everyone but my NT Login…

    When I’m executing something that has to read data from the other server : I get the Error Login Failed for user null.

    My Nt login has rights on both servers. I’ve tried  in Server Client Network Util to use names pipes / tcp/ip nothing worked.

    We checked with Network Admin settings of my account to another that works and we have no differences!!!

    I’m Lost !!!


  16. Il-Sung Lee says:

    The Windows Security layer always falls back to NTLM when connections are made locally.  This appears to be the design of SSPI when NEGOTIATE is used so what you are seeing is expected.

  17. Il-Sung Lee says:

    When you get a NULL user error, then it may mean one of several things including:

    1 – Your impersonation failed

    2 – You’ve impersonated the client’s credentials but you are making the outbound connection using NTLM

    3 – Your application is run under Local System and NTLM is used.

    With regard to 2 and 3, make sure that the outbound connection is made using TCP/IP and that the correct SPN is registered.  Also make sure that you don’t have any DNS problems; specifically, your server’s FQDN should resolve correctly.  If it doesn’t then the client may compose the wrong SPN string and cause the connection to NTLM.  

    With respect to 3, Windows does not permit applications run under Local System to make outbound connections using NTLM as Local System — NT ANONYMOUS credentials are used instead.

    I hope this helps, but if it doesn’t, then I suggest that you post your problem to the SQL Server Data Access Forum:


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

  19. Bimal Fernando says:

    I can connect to my SQL server using KERBEROS from any server on the network (Win 2000, Win 2003)

    But when i connect from my Win XP SP2 PC i always connected as NTLM?

    Any idea why? Any local policy to check? anything else?

    My Setspn return the correct results.

  20. ruchi says:

    where should SetSPN command executed, Active Directory machine or sql server machin

  21. Matt Neerincx (MSFT) says:

    It does not matter where you execute the SetSPN command, the command updates data in Active Directory which is centrally located in your domain.

  22. Mikhail Zuskov says:

    I wounder if i can use Kerberos without Active Directory?

    We’ve got samba domain controller.

    I need to install WSUS server and i want to use our dedicated SQL 2005 server, but it now runs only with NTLM, because we don’t have AD.

  23. shinashaju says:

    i have a error in the installation process like "logon account cannot be validated for the sql service.please verify user name and password".what is this

  24. isd83 says:

    I can verify that the sql server is using Kerberos authentication by running the select statement: select auth_scheme from sys.dm_exec_connections where session_id=@@spid

    However, I can not see the spn name when I run Setspn.exe -L "service-account-name". I am still getting:Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’

  25. Не первый раз встречаюсь с тем, что настройка делегирования для связанных серверов (так в русском BOL

  26. Nik says:

    Is only the SQL service needed as SPN? If not, how is the syntax for SQLAgent service, which seems to need Kerberos in CU3+ updates or is sharing this:

    setspn -A MSSQLSvc/FQDN:1433 accountname

  27. underkj says:

    Is this error related to this discussion?? Thanks!

    SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security

  28. M.srinivasan says:


    i have to link postgre sql from sql server 2005 express. (sp_addlinkedserver)

    i tried. but its not working

    but it is possible in msaccess 97….

    pls help me..



  29. sarah li says:

    We’ve configured this sometime ago. The problem we have is we get inconsistent results when connecting using the same account.

    Client’s OS: Windows XP 2002 SP2

    SQL Server: SQL 2005 SP2

    Result 1 –

    If I logon to the client PC with my Windows domain account, and then register SQL Server Management Studio directly with Windows Authentication, the result is ‘NTLM’.

    Result 2 –

    If I logon to the client PC with my Windows account, and then register SQL Server Management Studio with Windows Authentication again but using Run As my Windows domain account, the result is ‘KERBEROS’.  

    As a result of this, we get different results here and there depending on how the user is connecting and how the connection string is set up in the application and how the application is deployed.

    It looks like the KERBEROS is configured right for SQL Server. What’s wrong here? We need it to be KERBEROS consistently.

    Thank you in advance.


  30. Ben says:

    We have the same SSPI issue and asked the system admin to add SPN to AD, which he did successfully. But we still get "Cannot generate SSPI context" error message.

    Do we need to restart SQL Server service?



  31. Vlad says:

    Hi, is there anything else should be done if client is a JDBC application?



  32. dpblogs says:


    Please refer to the documentation of the JDBC driver you are using.  If you are using the Microsoft SQL Server 2005 JDBC Driver, Kerberos support is currently only supported if the JDBC application is running on a Windows machine.  Please look at the "integratedSecurity" connection property at for additional information.



  33. MikeSmith says:

    I am getting error 0x20b5/8373 "Failed to assign SPN on account…"

    Does anyone have suggestions for solving this issue?



  34. Brad says:

    I am getting the SSPI error only when users are connected to the Network via Cisco VPN.  Would the fix mentioned above fix my problem?

  35. Chaitanya says:

    To verify that Kerberos authentication is being used, you may query the sys.dm_exec_connections DMV and look under the auth_scheme column, e.g.

    select auth_scheme from sys.dm_exec_connections where session_id=@@spid

    The above statement works in SQL 2005. What is the way to know the auth scheme in SQL 2000 for connections.

  36. Matt Neerincx (MSFT) says:

    Not using a system table or DMV.

    There are several ways you can determine what is used.  The easiest is to enable security auditing for successful logons on the server side, this will create an event log for each logon and this event log entry will tell you whether or not Kerberos was used.

    This article tells you how to turn it on:

  37. RaOsolage says:


    I’m using SQL Server 2008 for MOSS 2007. We’re using Kerberos. Is there any reason why I shouldn’t disable all protocols on the server side other than TCP/IP? There is no SQL Server internal requirements for the Shared Memory or Named Pipes protocols to be enabled are there?

  38. Jeff says:

    We’ve successfully set up Kerberos on our SQL Servers by manually setting the SPN’s for each.  All of our SQL Servers are linked using window authentication.  This all seems to be working correctly, however, we have a old VB6 app that runs under a domain account that executes a distributed query and we are getting an anonymous login error on the hop to the second sql server.  This worked previously when the linked servers used a sql login to connect.  Also, when running the query from the same machine the app runs on with the same credentials (from query analyzer) the query runs without any problems.  Does anyone have any ideas?  

    Also, with manually registered SPN’s… do they persist if the DC is rebooted?  Is there any point at which manually registered SPN’s need to be re-registered.


  39. Mike Nikolic says:

    Hi Il-Sung Lee, thanks for such extreme good article!



  40. Chris Conner says:

    Client Tier is Management Studio or Query Analyzer

    Middle Tier x64 2003 SP1 – SQL 2000 SP4

    Destination is x86 2003 SP1 – SQL 2000 SP4

    SPN is set for Domain Service Account on middle tier & destination, TCP/IP is the only protocol enabled, linked server does not work on double-hop. However, if I remote to the middle tier and use linked server it works (obviously) and while that "ticket" is still live, I am able to successfully double-hop from the Client.  What am I missing???

  41. Kiran says:

    I am having double hop issue connecting linked server. So i registered it using the statement

    setspn MSSQLSvc/ domainname<account under which sql service runs>

    when i execute–C:Program FilesSupport Tools>setspn -l domainname<account under which sql service runs>

    Registered ServicePrincipalNames for CN=ESM Nagbot 2000,OU=UserAccounts,DC=domainname,DC=corp,DC=<x>,DC=com:


    The Query results NTLM still

    select auth_scheme from sys.dm_exec_connections where session_id=@@spid

    I am still getting the error. We use SQL 2005. Can some one suggest.

  42. Arturo says:

    Kiran, try with.

    setspn MSSQLSvc.3/ domainname<account under which sql service runs>

    Instead of:

    setspn MSSQLSvc/ domainname<account under which sql service runs>

    For SQL 2005 you need to use MSSQLSvc.3, to work properly.

  43. Raj says:

    I see the below error my SQL Logs after I restart cluster service.

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

  44. says:

    I have a SPN registered to two accounts: the server SQL 2000 is installed on and a domain account

    The current Server replaced an older server that had the same name.  From what I am told, the old server was removed from Active Directory and the new one was brought into AD with the same name.  The old server used a Domain account (the same one used to register the SPN) to start the SQL Server service.  The new server was configured to use the Local System account to start the service.  

    I noticed a Event ID 11 pointing to the SPN.  Which SPN can I remove?  I am taking an educated guess that I can remove the one registered under the Domain account used for the SQL Service

  45. Senthilkumar says:

    server reboot need or not? if we add/remove SPN.


    Senthilkumar S