Trying to give something back to the Community...
SQL Service Principle Names (SPNs), can cause all manner of grief with ConfigMgr if they're either not setup correctly or missing. The following post from The Configuration Manager Support Team Blog should set you on the straight and narrow if you're having SPN issues:
This blog post addresses a common problem customers face during the installation of System Center Configuration Manager 2007 in which the following error appears: “Setup failed to install SMS Provider.” When this error occurs, there are several possible causes, but the first thing I check is Service Principal Names or SPNs.
When setting up SQL Server for System Center Configuration Manager you can configure SQL services to use the local SYSTEM account or a domain user account. In either case, a Service Principal Name needs to be registered in Active Directory, but in the case where a domain user account is used for SQL Services, manual registration is typically required. It is important that the SPN be registered prior to installing SCCM, as the installation will fail.
This article will provide the steps required to register the SPN, but before covering that check out the symptoms of a SPN problem below.
One of the clearest indications that there is a problem with the SPN is during the SCCM installation.
For example, if the SQL SPN is not properly registered, and you choose to install the SMS Provider on the SCCM site server, the installation will fail during the installation of the SMS Provider. See the example below:
It is possible to bypass this error by choosing to install the SMS Provider on the SQL server which in an option during the SCCM installation, but it does not resolve the SPN problem after the fact.
For example, if you install the SMS Provider on the SQL server, and you have an SPN problem you may see the following errors:
In the SCCM console navigate to Site Database <Site Name> – Tools and right-click the ConfigMgr Service Manager and select Start ConfigMgr Service Manager.
If you receive the error “Error communicating with the specified ConfigMgr Site Server” there is definitely an issue.
Another symptom you will have is that multiple errors will show up in your SQL Server Logs.
On the SQL Server open Microsoft SQL Server Management Studio and connect to the instance in which you installed SCCM.
In the SQL console, navigate to Management – SQL Server Logs and right-click the Current log and select “View SQL Server Log”.
In the SQL server log you will see multiple errors similar to this:
03/06/2009 12:09:54,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 192.168.1.11]
Another place to look is SMSDBMON.log file on your SCCM server. You will see errors such as:
*** [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. $$<SMS_DATABASE_NOTIFICATION_MONITOR> *** [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. $$<SMS_DATABASE_NOTIFICATION_MONITOR
*** Failed to connect to the SQL Server. $$<SMS_DATABASE_NOTIFICATION_MONITOR> CTriggerManager::Init - unable to get SQL connection $$<SMS_DATABASE_NOTIFICATION_MONITOR>
NOTE: In cases where SQL reports NT AUTHORITY\ANONYMOUS LOGON failures, it is typically due to SPN or Kerberos issues.
To properly register a Service Principal Name for SQL you need two pieces of information:
· Which account is SQL running under?
· What port is SQL running under?
In the case where the SQL service is configured to run as SYSTEM, it is likely that you don’t have an SPN issue, but in some cases the SCCM administrator may not know what account SQL is running under.
To check what account SQL services are running under:
1. Logon to the SQL server and open Start – All Programs – Microsoft SQL Server 20xx –Configuration Tools and select SQL Server Configuration Manager.
2. In SQL Server Configuration Manager – Select the SQL Server Services node.
3. In the Details pane, right-click the SQL Server (Instance) name and in the context menu, selectProperties.
4. In the SQL Server (instance) Properties dialog, within the Log On tab, if “This account” is selected, then it is likely that a domain user account is listed. Note the domain user account listed in the Account Name field.
5. If the “Built-in account” option is selected, then typically “Local System” is selected and an SPN may already be registered.
2. In SQL Server Configuration Manager – Select the SQL Server Network Configuration node and select the sub-node Protocols for <SQLInstance>
3. In the details pane, select TCP/IP and select Properties.
4. At the TCP/IP Propertied dialog, select the IP Addresses tab and scroll down to the IPAll section. The value of TCP Port is the port number SQL is running under.
If SQL is configured to use the default instance, the port should be statically defined as port 1433.
If using a SQL named instance, the port should be listed as “TCP Dynamic Ports” and will change every time SQL is restarted. This can pose a problem when registering an SPN as the port will change.
How to you check whether an SPN is registered? There are two tools you can use to check and list Service Principal Name.
Setpn.exe or ADSIedit.
This utility is installed natively in Windows Server 2008, but if running Server 2003 you have to install the Server 2003 SP1 Support Tools. A link to the Server 2003 SP1 Support Tools download can be found within this page.
If using the Local SYSTEM account for for SQL, you can use the following command to check SPNs against the server.
setspn –l MSSQLSvc/<SQLSERVERNAME>
If using a domain user account for SQL, you can use the following command to check SPNs against the server.
setspn –l domain\useraccount
This utility not installed natively in Windows Server 2008. You can install it by opening Server Manager, select and then right-click Features and click Add Features. Select Remote Server Administration Tools – Role Administration Tools – Active Directory Domain Services Tools and check Active Directory Domain Controller Tools.
Complete the Add Features Wizard.
If running Server 2003, you will need to install but if running Server 2003 you have to install the Server 2003 SP1 Support Tools. A link to the Server 2003 SP1 Support Tools download can be found within this page.
To check SPNs, open Adsiedit.msc and right-click the ADSI Edit node and select “Connect to…”
At the Connection Settings dialog, click OK.
· If using the Local SYSTEM account, navigate and select the OU which contains the SQL computer account and in the details pane, right-click the computer account and select Properties.
· If using a domain user account for SQL, navigate and select the OU which contains the domain user account and in the details pane, right-click the user account and select Properties.
In the Properties dialog, scroll down to the Service Principal Name attribute. You can select theEdit button to review the list of Service Principal Names registered against the account.
The following SPNs need to be registered for Configuration Manager to function:
Where servername is the NETBIOS name of the SQL server, and servername.domain.com is the FQDN of the SQL Server. Port is the port number which SQL is using.
If using Local System for SQL Services, just check to make sure the SPN is registered. It is unlikely you will need to change anything because the computer account usually has enough permissions to update its own SPN.
If using a Domain User Account for SQL Services, and SQL is installed using the Default instance and the port is 1433 run the following SetSPN.exe command:
setspn.exe –A MSSQLSvc/servername:1433 domain\sqlserviceaccount
setspn.exe –A MSSQLSvc/servername.domain.com:1433 domain\sqlserviceaccount
If using a Domain User Account for SQL Services, and SQL is installed using a Named instance and the port is set as Dynamic, you can use ADSIEdit to grant the user account permissions to update its own SPN. This is recommended.
1. Open ADSIEdit.msc and navigate and select the OU which contains the domain user account.
2. In the Details pane, select the domain user account and select Properties.
3. At the user account Properties dialog, select the Security tab.
4. Select the Advanced button.
5. At the Advanced Security Settings for <user> dialog, select the SELF account and select Edit.
6. At the Permission Entry for <user> properties, select the Properties tab.
7. Scroll down and verify that Allow is checked next to Read servicePrincipalName and Write servicePrincipalName is selected.
8. Click OK, OK and then OK to save the settings.
9. Restart the SQL Services, and you should be able to check the SPN against the domain user account and it should be updated.
Once the Service Principal Name is set, you may have to wait for domain synchronization to occur.
Restart the server which Configuration Manager 2007 is going to be installed on to clear Kerberos tickets.
If the above errors still persist, and you are certain the SPN was registered successfully, you may have a duplicate record in AD. You can check for duplicates by running this command:
ldifde –f C:\SPNCheck.txt -t 3268 -d "" -l servicePrincipalName -r "(servicePrincipalName=MSSQLSvc/servername*)" -p subtree
The MSSQLSvc/servername* portion of the above command should be edited to include the server name of your SQL server.
The command will return all SPNs with the string MSSQLSvc/servername* and write the results to the text file: C:\SPNCheck.txt.
Here is an example of a duplicate SPN in the results in the SPNCheck.txt file:
dn: CN=Administrator,CN=Users,DC=gb,DC=net changetype: add servicePrincipalName: MSSQLSvc/servername.domain.com:1433
dn: CN=s-sqladmin,OU=Admin,DC=gb,DC=net changetype: add servicePrincipalName: MSSQLSvc/servername.domain.com:1433
As you can see, the same SPN is registered against the domain\Administrator and the s-sqladmin accounts.
This is a duplicate as the SPN registered against the Administrator account is no longer needed.
To delete the incorrect SPN run the command:
setspn.exe –D MSSQLSvc/servername.domain.com:1433 domain\administrator
I hope that this blog entry was helpful. If there is something missing, please comment.
Here are some links to Microsoft articles which may provide some background on the subject:
Systems Management Server 2003 Advanced Security Site with Remote SQL Does Not Connect to SQL Server
Security Account Delegation
Gerry Borger | Senior System Center Support Engineer
Read the complete post at http://wmug.co.uk/blogs/cliffs_blog/archive/2010/10/26/configmgr-2007-troubleshooting-duplicate-or-missing-spns-for-a-configmgr-2007-sql-database.aspx