Use of Kerberos Authentication in SQL Server 2005
Kerberos is a network authentication protocol which can be used only with TCP/IP protocol. So if the client connects to the sql server with the Named Pipe Alias Kerberos is not used. Every service that will use Kerberos authentication needs to have an SPN set for it so that clients can identify the service on the network. If an SPN is not set for a service, then clients will have no way of locating that service. Without properly setting the SPNs, Kerberos authentication is not possible.
For more information on SPNs, please refer to the below article,
https://msdn.microsoft.com/en-us/library/ms677949.aspx
To use Kerberos authentication, you must make sure that all the following conditions are true:
• |
Both the server and the client computers must be members of the same Windows domain or members of trusted domains. |
• |
The server's service principal name (SPN) must be registered in the Active Directory directory service. |
• |
The instance of SQL Server 2005 must enable the TCP/IP protocol. |
• |
The client must connect to the instance of SQL Server 2005 by using the TCP/IP protocol. For example, you can put the TCP/IP protocol at the top of the client's protocol order. Or you can add the prefix "tcp:" in the connection string to specify that the connection will use the TCP/IP protocol. |
What is SPN?
Service Principal Name (SPN) is the identity of a service which is started with the particular Domain Account running in that domain. SPNs are registered at the Domain Controller (Active Directory).
To register the SPN at the AD we need to provide the following information
1) The service
2) The Fully Qualified Domain Name (FQDN) of the system on which the service is installed
3) The TCP/IP port no. on which the service is listening. (Since we can have multiple services of same type running on the same system but on the different host for e.g sql server)
4) The startup account with which the service is started
SPNs are used by the clients who are connecting to that sql server service using windows integrated authentication mechanism
When SQL Server is running under the local system account or under a domain administrator account, the instance will automatically register the SPN in the following format when the instance starts:
MSSQLSvc/<FQDN>:<tcpport>
C:\Program Files\Resource Kit>setspn -L pariks-2003
Registered ServicePrincipalNames for CN= pariks -2003,OU=Workstations,OU=Machines,DC=testdc,DC=corporate,DC=sampledomain,DC=com:
MSSQLSvc/ pariks -2003.testdc.corporate.sampledomain.com:1393
SMTPSVC/ pariks -2003
SMTPSVC/ pariks -2003.testdc.corporate.sampledomain.com
HOST/ pariks -2003
HOST/ pariks -2003.testdc.corporate.sampledomain.com
You must manually register the SPN for the instance under a domain administrator account to use Kerberos authentication. To register the SPN, you can use the SetSPN.exe tool that is included with the Microsoft Windows 2000 Server Resource Kit. This tool is also included with the Microsoft Windows Server 2003 Support Tools. The Windows Server 2003 Support Tools are included in Microsoft Windows Server 2003 Service Pack 1 (SP1).
You can use a command that is similar to the following to register an SPN for an instance:
SetSPN –A MSSQLSvc/<ComputerName>.<DomainName>:1433 <AccountName>
If an SPN already exists, you must delete the SPN before you can reregister it. You may have to do this if the account mapping has changed. To deleted an existing SPN, you can use the SetSPN.exe tool together with the -D switch.
After you connected to an instance of SQL Server 2005, run the following Transact-SQL statement in SQL Server Management Studio:
select auth_scheme from sys.dm_exec_connections where session_id=@@spid
If SQL Server is using Kerberos authentication, a character string that is listed as "KERBEROS" appears in the auth_scheme column in the result window
In order to verify that Kerberos is setup and working fine we can use KerbTray utility. Kerbtray utility can be downloaded from
How to use KerbTray utility
1) Install the KerbTray utility from the above link and install it in C:\Program Files\Resource Kit
2) On Installing it a GREEN icon appears on the Task Bar on the right hand bottom of the screen
3) Right Click on the icon and click purge tickets to purge all the tickets received by that machine
4) Wait until the green icon turns yellow. When the icon turns yellow fire the following command from the cmd prompt
C:\Program Files\Resource Kit>net session /d
The command completed successfully.
5) Now we can request the ticket from the AD using the following command from the cmd prompt
C:\Program Files\Resource Kit>set L (Press Enter)
lib=C:\Program Files\SQLXML 4.0\bin\
LOGONSERVER=\\comp-dc-01
C:\Program Files\Resource Kit>net use \\ comp-dc-01\IPC$
Local name
Remote name \\ comp-dc-01\IPC$
Resource type IPC
Status OK
# Opens 0
# Connections 1
The command completed successfully.
**comp-dc-01 is the DC for the domain.
After firing the above command wait for some time and you will see that yellow icon of the KerbTray turning green which indicates that it has started received Tickets which can also be confirmed by right clicking on the icon and click on LIST TICKETS
https://support.microsoft.com/kb/909801/ (How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005)
**To enable Kerberos logging in the Windows Xp, 2003, Vista as well as Windows 2000 (Reboot required after setting the following registry key), we need to set the following registry key
1. Start Registry Editor.
2. Add the following registry value:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters
Registry Value: LogLevel
Value Type: REG_DWORD
Value Data: 0x1
If the Parameters subkey does not exist, create it.
3. Quit Registry Editor. The setting will become effective immediately on Windows Vista, on Windows Server 2003, and on Windows XP. For Windows 2000, you must restart the computer.
** IMPORTANT : This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:
256986 Description of the Microsoft Windows Registry
Also please contact the Product Support Services for more help regarding the specific issue you have.
Parikshit Savjani
SE, Microsoft SQL Server
Comments
Anonymous
June 07, 2009
CONTRADICTION IN TEXT: When SQL Server is running under the local system account or under a domain administrator account, the instance will automatically register the SPN. and a few lines later... You must manually register the SPN for the instance under a domain administrator account to use Kerberos authentication.Anonymous
June 09, 2009
Hi Frederico, In the second part, I meant, in order to manually register SPN's for the instance you need to use Domain Administrator because only Domain Admins have rights to register SPN's Hope this clarifies Parikshit