3.6.0 MxDB for SQL Server Installation and Administration Guide (5697-7088, December 2007)

Chapter 7: Troubleshooting 98

• 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