This message is not an error message. This text is only a warning that SQL Server cannot register a service principal name (SPN). This indicates that the security mechanism that will be used is Microsoft Windows NT Challenge\Response (NTLM) authentication instead of Kerberos authentication.
These messages should only be considered a problem if your SQL Server installation requires Kerberos authentication or the network security settings prevent fallback to NTLM negotiation. Otherwise, these messages can be ignored safely.
Warning If you use the Active Directory Service Interfaces (ADSI) Edit snap-in, the LDP utility, or any other LDAP version 3 clients, and you incorrectly modify the attributes of Active Directory objects, you can cause serious problems. These problems may require that you reinstall Microsoft Windows 2000 Server, Microsoft Windows Server 2003, Microsoft Exchange 2000 Server, Microsoft Exchange Server 2003, or both Windows and Exchange. Microsoft cannot guarantee that problems caused by incorrectly modifying Active Directory object attributes can be solved. Modify these attributes at your own risk.
To enable the SQL Server service account to establish SPNs correctly on startup, follow these steps:
- Click Start, click Run, type Adsiedit.msc, and then click OK.
- In the ADSI Edit window, expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN=AccountName, and then click Properties.
- DomainName represents the name of the domain.
- RootDomainName is a placeholder for the name of the root domain.
- AccountName represents the account that you specify to start the SQL Server service.
- If you have specified Local System to start the SQL Server service, AccountName represents the account that you use to log on to Microsoft Windows.
- If you have specified a domain user account for the SQL Server service, AccountName represents the domain user account.
- In the CN=AccountName Properties dialog box, click the Security tab.
- On the Security tab, click Advanced.
- In the Advanced Security Settings dialog box, make sure that the SELF user is listed under Permission entries. If the SELF user is not listed, click Add, and then add the SELF user.
- Under Permission entries, click SELF, and then click Edit.
- In the Permission Entry dialog box, click the Properties tab.
- On the Properties tab, click This object only in the Apply onto list, and then make sure that the following permissions are selected under Permissions:
- Read servicePrincipalName
- Write servicePrincipalName
- Click OK three times, and then close the ADSI Edit window.
When you perform this workaround, you eliminate SPN issues for new installations or installations that have had the TCP/IP port or domain name modified.
Important We recommend that you do not grant WriteServicePrincipalName right to the SQL service account when the following conditions are true:
- There are multiple domain controllers.
- SQL Server is clustered.
Assume that you have the following:
- A SQL virtual instance named Sqlcluster with two nodes: Node A and Node B.
- Node A is authenticated by domain controller A and Node B is authenticated by domain controller B.
The following may occur:
- The Sqlcluster instance is active on Node A and registered the SQL SPN in domain controller A during start up..
- The Sqlcluster instance fails over to Node B when Node A is shutdown normally.
- The Sqlcluster instance deregistered its SPN from domain controller A during the shutdown process on Node A.
- The SPN is removed from domain controller A but the change has not yet been replicated to domain controller B.
- When starting up on Node B, the Sqlcluster instance tries to register the SQL SPN with domain controller B. Since, the SPN still exists Node B does not register the SPN.
- After some time, domain controller A replicates the deletion of the SPN (from step 3) to domain controller B as part of Active Directory replication. The end result is that no valid SPN exists for the SQL instance in the domain and hence you see connection issues to the Sqlcluster instance.
Note This issue is fixed in SQL Server 2012.
Artikelnummer: 303411 – Letzte Überarbeitung: 07.04.2013 – Revision: 1