4.0.0 HP PolyServe Software for Microsoft SQL Server Administration Guide (T5392-96056, March 2010)
• Host: the fully qualified domain name for the computer running the SQL Server service.
• Port: the TCP port that the SQL Server service is listening on.
An example of a valid SPN for SQL Server is:
MSSQLSvc/vqar13s11.ad1.polyserve.com:50004
When connecting to SQL Server via Kerberos, the client SQL driver uses the Winsock API
(gethostbyname and gethostbyaddr) to resolve the SQL Server fully qualified name to form an SPN
for the target SQL Server. Whether the SPN is valid depends entirely on DNS name/address resolution.
If the client-formed SPN is invalid, the SSPI interface retries by looking up an SPN in Active Directory.
If an SPN does not exist in AD for the SQL Server, Kerberos authentication is not used and the logon
switches to an NTLM authentication.
A valid SPN for SQL Server is assigned to two types of containers. When the SQL Server service
account is a domain administrator or the local system account, an SPN for SQL Server is automatically
registered and assigned to the “hostname” container when SQL Server starts up. If the SQL Server
service account is not a domain administrator or the local system account, the SPN for the SQL Server
container is the service account. The SETSPN utility can be used to register a SQL Server SPN for the
service account. (The utility is available as part of Windows 2003 Support Tools on the Install media
or Windows 2000 Resource Kit Tool referenced below.)
For example:
SETSPN.EXE -A MSSQLSvc/vqar13s11.ad1.polyserve.com:50004 SQLsvc
NOTE:
You must be a domain administrator to run the setspn command.
Because Kerberos authentication will try to use the first SQL Server SPN that it finds in Active Directory,
it is important to have only one SPN for each SQL Server service and to assign the correct container
to each SPN. To gather a list of the current SPNs for SQL Server in Active Directory, use the attached
querySpn VBScript. For example:
C:\>cscript querySpn.vbs MSSQLSvc/* | find "vqar13s11"
-- MSSQLSvc/vqar13s11.ad1.polyserve.com:50004
To delete an existing SPN, run setspn with -D. For example:
C:\>setspn -D MSSQLSvc/vqar13s11.ad1.polyserve.com:50004 vqar13s11
Unregistering ServicePrincipalNames for CN=Administrator,CN=Users,DC=pdxad1,
DC=polyserve,DC=com
MSSQLSvc/vqar13s11.pdxad1.polyserve.com:50004
Updated object
References
The following Microsoft documents provide additional information:
• Windows 2000 Resource Kit Tool: Setspn.exe
http://www.microsoft.com/downloads/details.aspx?FamilyID=5fd831fd-ab77-46a3-9cfe-
ff01d29e5c46& DisplayLang=en
• How to: Enable Kerberos Authentication on a SQL Server Failover Cluster
http://msdn.microsoft.com/en-us/library/ms189585.aspx
• How to use Kerberos Authentication in SQL Server
http://support.microsoft.com/kb/319723
HP PolyServe Software for Microsoft SQL Server administration guide 117