------------------------------------------------------------------------------------------- -- Some notes on Windows - Kerberos "Service Principal Name" (SPN) and related problems. -- -- Version : 5.0 -- Date : 21 November 2010 -- Compiled By: Albert van der Sel -- Applies to : Win2Kx Server systems, Kerberos, and Applications like IIS and SQL Server. -- Remark: This very simple note touches on concepts as SPN, Delegation and related subjects. ------------------------------------------------------------------------------------------- Contents: 1. What is an SPN 2. Possible fallback of Kerberos to NTLM 3. SPN for Services using a Domain Account or using Local System 4. Using the SetSPN utility 5. A few notes on Delegation ==================== 1. What is an SPN: ==================== In some modern types of "authentication", like Kerberos, the socalled "Service Principal Name" or SPN, is important. Services (or daemons) in the network, will be installed on certain Servers. The services will probably use Domain Accounts (instead of local accounts), to logon to Active Directory, using Kerberos. The SPN is "tied" (or "registered" or "mapped") to the "account object" that the service instance uses to log on, or identify. It is a "property" and value of an account object. If Active Directory (AD) is used (which is often true ofcourse), then the SPN is written in AD and belongs to an account object (host account, user account). Before the Kerberos authentication service can use an SPN to authenticate a service, the SPN must be registered on the account object that the service instance uses to log on. A given SPN can be registered on only one account. Actually, you may view a SPN as just a "value", specifying the "service", and the "machine", and "Domain qualifier", on which that "service" is running. But, at the same time, the SPN is mapped (like a sort of "pointer") to that specific account that the service is using. Service Principal Names (SPNs) are unique identifiers for services running on servers. 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. Without properly set SPNs, Kerberos authentication is not possible. If an SPN has not been correctly set and a client attempts to obtain a service ticket, a common result is a KDC_ERR_C_PRINCIPAL_UNKNOWN or a KDC_ERR_S_PRINCIPAL_UNKNOWN error. Furthermore, there are many other errors for which the cause might be a missing or an incorrectly set SPN. When a client wants to connect to a service, it locates an instance of the service, and composes an SPN for that instance. Then it contacts the KDC (Kerberos) for obtaining a ticket, while passing the SPN. Kerberos will use the SPN to locate the account of the specific service, and will construct a ticket, using it's specific sucurity mechanisms. So, suppose we want the client to obtain a ticket and authenticate with "mssqlserver" on "starboss" on port 1433. we let the client ask for a ticket. The client then has constructed a string and passes that to Kerberos. This string, the SPN, would then be: "mssqlserver/stargate.antapex.nl:1433". The general format of an SPN is "serviceclass/full_dns_name accountname" Now, Active Directory should have a "mapping" from this name to the account that "mssqlsrv" is using. Then, Kerberos is able to deploy it's specific security mechanism to grant the ticket to the client. So, in using Kerberos, the SPN is quite critical for authentication. If it's not correct, the client will not authenticate and connect using kerberos. However, a "fallback" on NTLM could be possible. 1. Most often, services will run under Domain accounts. In this case, an SPN is mapped to that user account in AD. 2. But running as "Local System" on the local machine, is possible too. In this case an SPN is mapped to that computeraccount in AD. So, as a consequence of point 2, if you want to view the spn's which are registered for a certain computer (like "server1), you might use the following "setspn" command: setspn -l server1 which shows you a listing of spn's registered for that machine. See section 4 for more on the SetSPN utility. ========================================== 2. Possible fallback of Kerberos to NTLM: ========================================== This section illustrates the situation that if Kerberos authentication fails (for some reason), that authentication may fallback to NTLM (NT4 style) authentication. There are a number of "traditional" ways for a "traditional" client to access SQL Server, like using: - shared memory (only for use on the local System) - named pipes (over TCP IP) - TCP IP and sockets Just for the sake of illustrating the fallback from Kerberos to NTLM, we are going to use "endpoints" in the discussion. I only use that, because it gives a nice illustration of matters. SQL Server 2005 provides for a rather special way, or interface, to access the database objects, that is, by using a "HTTP Endpoint". Newer types of applications are Web services. SOA applications, or also called "Web services", often use SOAP requests over HTTP. The HTTP Endpoint defined in SQL Server, responds to HTTP or HTTPS requests. An HTTP Endpoint is an interface to the database via HTTP for SOAP. The obvious way you want to use such a HTTP Endpoint, is to expose your stored procedures and functions to SOAP requests from those "Web services" types of applications. For what about the "authentication" of the client and Server, the following might be interresting. In SQL Server 2005, we might just create an "endpoint", for example, in order for external webservices to execute stored procedures. Such an endpoint might look like this: CREATE ENDPOINT SQLEP_test STATE = STARTED AS HTTP ( PATH = '/EP_test', AUTHENTICATION = (INTEGRATED), PORTS = (CLEAR), SITE = 'server1' ) FOR SOAP ( WEBMETHOD 'CustomerList' (NAME='TEST.dbo.stp_ShowCustomers'), WEBMETHOD 'InventoryList' (NAME='TEST.dbo.stp_ShowInventory'), BATCHES = DISABLED, WSDL = DEFAULT, DATABASE = 'TEST', NAMESPACE = 'http://test/showitems' ) For the AUTHENTICATION clause, in general we can choose from: AUTHENTICATION =( { BASIC | DIGEST | INTEGRATED | NTLM | KERBEROS } ), or AUTHENTICATION = { WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] So what does that all mean: - By setting AUTHENTICATION=(KERBEROS), Only Kerberos is used as the SOLE means of authentication. If in some way Kerberos is not supported by the client, or for example the SPN is misconfigured, authentication will fail. - When AUTHENTICATION=(WINDOWS), HTTP authentication for the endpoint can offer the following options as part of the authentication challenge: NEGOTIATE, KERBEROS, and NTLM. When the NEGOTIATE option is present, the client and server first will try to establish Kerberos-based authentication. But, if for example Kerberos is not supported by the client, or in someway negotiation is not possible, authentication will fall back to NTLM, which is the old-style NT4 type of authentication. Example: -------- Suppose you have configured an application, that is using SQL Server 2005. Suppose further that it's all installed on the same one Server, namely "Server1". Now, if you logon locally to "Server1", it all works fine. If you go to a client machine, and try to use the application from the client, it does not work and you get login dialogboxes, which fail all the time. Integrated auth fails when trying to connect remotely because the SPN is registered to local system and sqlserver is running under a different account. To work around this issue you can do one of the following - register a SPN for the sqlserver service account as shown below - enable only NTLM as an auth option on the endpoint as opposed to INTEGRATED To register the SPN one would do the following: If an instance of SQL Server is running as a domain user (MyDomain\MySQLAccount) on a computer that is named MySQLHost, the following commands can be used to set the appropriate SPNs: setspn –A http/MySQLHost MyDomain\MySQLAccount setspn –A http/MySqlHost.Mydomain.Mycorp.com MyDomain\MySQLAccount Note that one account can have multiple SPNs (one for each service or host name), but an SPN can be registered under only one account. Having the same SPN registered on multiple accounts causes Kerberos authentication to fail. Note that here we have used "http" as the "service class", that is, the type of service Windows Authentication is the preferred method for users to authenticate to SQL Server. Clients that use Windows Authentication are authenticated by either using NTLM or Kerberos. In an Active Directory environment, Kerberos authentication is always attempted first. But, Kerberos authentication is not available for SQL Server 2005 clients using named pipes. Starting from SQL Server 2008, kerberos is supported on TCPIP and named pipes. ================================================================== 3. SPN for Services using a Domain Account or using Local System: ================================================================== If you install a Service, after specifying the Domain user credentials the service will run under, the installer will then create an SPN in AD that's mapped to that specific service account. It will take the form as: servicetype/hostname.domain:port Domain\Account or, in Win2K8, this can be used too: servicetype/hostname.domain Account Instead of that a certain installer creates an SPN, we can do it as well, using the "setspn" utility. So: - If a service uses a Domain account, a SPN will be created that's mapped or tied to that user account. - Only DOMAIN ADMIN privileges can create or change SPN registration by using the SetSPN.exe tool. - If a SQL Server instance is running as the local system account, only members of the SQL Server sysadmin fixed server role can change SPN registrations by using the SetSPN.exe tool. - If the service account is Local System, the SPN is added in the Active Directory account of the Server that hosts the "service". Possible related problem: ------------------------- If you run the SQL Server service under the LocalSystem account, the SPN is automatically registered and Kerberos interacts successfully with the computer that is running SQL Server. However, if you run the SQL Server service under a domain account, the attempt to create the SPN might fail in most cases because the domain account does not have the right to set their own SPNs. When the SPN creation is not successful, this means that no SPN is set up for the computer that is running SQL Server. It would mean that Kerberos cannot be used, and SQL Server "integrated security" will fallback to NTLM (in most cases). But if you would use a domain admin account as the SQL Server service account, the SPN is successfully created, oviously because the domain administrator has permissions to create the SPN in AD. If no SPN exists, Kerberos will not be used. But as a Domain Admin, you can always create an SPN in AD. Please see section 4 for information on how to do that. Another solution may be found in the following: We are going to add permissions to the SQL Server service account, so that it is allowed to create the SPN. Method 1: Use "adsiedit" (from the "support tools") and find the Service Account. Then open the security tab. That is: From "Start->run" or from "cmd prompt": enter adsiedit.msc to launch the ADSIEdit MMC console. Expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= AccountName, and then select Properties. Select the Security tab. Select Advanced. At the Permission entries, click SELF, and then click Edit. click to select the check boxes for the following permissions: -Read servicePrincipalName -Write servicePrincipalNam The existence of the SPN can be checked with the prompt command (from the "support tools"): setspn -L ServiceAccount Checking if you use Kerberos authentication: -------------------------------------------- For Windows/integrated based logon's to SQL Server (contrary to SQL Server login's), you might want to know if authentication took place through NTLM or Kerberos. To verify that Kerberos authentication is being used, you may query the sys.dm_exec_connections DMV and take a look at the auth_scheme column: select auth_scheme from sys.dm_exec_connections where session_id=@@spid If Kerberos is used, it will output “KERBEROS”. ==================================================== 4. Using the SetSPN utility: ==================================================== This commandline tool can be used to add, change or delete an SPN for a service. Normally, it is not necessary to modify SPNs. An installer of a service, will create an SPN if the service uses a Domain account (mapping to that account), or if the Local System is used, it will be mapped to the computer account in AD. But sometimes, if a Server has it's name changed, or the user account of the service is changed, or DNS is changed, the SPN might not be in sync with the new information. To correct this situation, you might want to use the "setspn" utility. It can be found in the Windows Server 2003/2008 "support tools", which you can download from Microsoft. Setspn uses a few switches: For Win2K3 and Win2K8: -l: retreive a listing of spn, set for an account or host. -a: set an spn in AD -d: delete an spn -r: reset an spn (For Win2K8, we also have some additional "edit modes"). Here are a few examples: -- Obtain listings: setspn -L server1 REM see all spn's for this machine setspn -L ANTAPEX\SQL_Service_Acct REM see the spn for this user account setspn -L SQL_Service_Acct REM see the spn for this user account -- Set an SPN: setspn –A MSSQLSvc/.:1433 setspn -A MSSQLSvc/starboss.antapex.org:1433 ANTAPEX\SqlSrvAccount If an SPN already exists, it must be deleted before it can be reregistered. You do this by using the setspn command together with the -D switch. -- Delete an SPN: setspn -d http/starboss.antapex.org starboss Note: in a complex and large AD infrastructure, replication of AD objects and values might take quite some time to complete. ==================================================== 5. A few notes on Delegation: ==================================================== Scenario: --------- Suppose a user on MachineA, performs the following statement. Note that the import command involves a file on a share on MachineB. BULK INSERT TableA FROM '\\MACHINEB\Temp\file.csv' WITH ( FIRSTROW = 2 ); The user gets an error with respect to permissions: no access to \\MACHINEB\Temp\file.csv, Operating system error code 5 (Access is denied)' Explanation: ------------ If the user would have been logged on to SQL Server using a SQL Server account (and thus not using Windows authentication), SQL Server would have used the credentials of the SQL Server service account to access the file. When using Windows authentication, and using the Kerberos authentication service, SQL Server would have accessed the file *using the credentials of the user* (!). But it would have ONLY done that, if the domain account that SQL was running under, has the "Trust this user for delegation to any service (Kerberos only)", checked in AD. Otherwise. an ANONYMOUS LOGON logon is tried. Further information: -------------------- Delegation is the act of allowing a service to impersonate a user account or computer account in order to access resources throughout the network. When a service is trusted for delegation, that service can use the user's "identity" to use other network services. Just as we have seen in the above example, the SQL Server service impersonates the user in order to access the file. The account that the service is delegating for must not have the property "Account is sensitive and cannot be delegated" set. In the case of Windows Server 2003 functional level 2, there is no Delegation tab in the AD properties of the account, if there is no Service Principal Name (SPN) registered for the account. But, if a Domain account is used for a service account, the SPN should be in place, so then the tab will be visible. SSPI: ----- In some webpages that deal with SPN's, Kerberos, or Delegation, you might also encounter the term "SSPI". The Security Support Provider Interface (SSPI) is an layer or API used by Windows to access a number of authentication SSP implementations. Among possible other implementations, at least the following SSP's are available: - NTLM. Provides NTLM challenge/response - Kerberos. The preferred method for mutual client-server domain authentication - Negotiate. Selects Kerberos and if not available, NTLM protocol. - Digest SSP. Provides challenge/response based HTTP and SASL authentication between Windows and non-Windows systems. In Windows networks, Kerberos, NTLM and Negotiate are the most prominent SSP's.