Troubleshooting Duplicate or Missing SPNs for a ConfigMgr 2007 SQL Database

Troubleshooting Duplicate or Missing SPNs for a ConfigMgr 2007 SQL Database

  • Comments 2
  • Likes

Toolbox3This 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.

Background

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.

Symptoms

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:

image

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.

image

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”.

image

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]

image

Another place to look is SMSDBMON.log file on your SCCM server. You will see errors such as:

*** [28000][18456][Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.  $$<SMS_DATABASE_NOTIFICATION_MONITOR>
*** [28000][18456][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.

Discovery

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?

To Find which account SQL is 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 StartAll ProgramsMicrosoft SQL Server 20xxConfiguration 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, select Properties.

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.

image

5. If the “Built-in account” option is selected, then typically “Local System” is selected and an SPN may already be registered.

To Find what Port SQL is Running Under:

1. Logon to the SQL server and open StartAll ProgramsMicrosoft SQL Server 20xxConfiguration Tools and select SQL Server Configuration Manager.

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.

image

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.

image

Important Note:

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.

Verification

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.

How to Check SPNs Using SetSPN.exe:

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>

image

If using a domain user account for SQL, you can use the following command to check SPNs against the server.

setspn –l domain\useraccount

image

How to Check SPNs Using ADSIEdit.msc:

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.

image

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…”

image

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 the Edit button to review the list of Service Principal Names registered against the account.

image

Resolution:

The following SPNs need to be registered for Configuration Manager to function:

MSSQLSvc/servername:port

MSSQLSvc/servername.domain.com:port

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.

Example: MSSQLSvc/SQLServer.domain.com:1433

Scenarios:

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.

To configure the domain user account to update its own SQL SPN:

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.

image

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.

image

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.

 

Troubleshooting:

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

 

Conclusion:

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

The App-V Team blog: http://blogs.technet.com/appv/
The WSUS Support Team blog: http://blogs.technet.com/sus/
The SCMDM Support Team blog: http://blogs.technet.com/mdm/
The ConfigMgr Support Team blog: http://blogs.technet.com/configurationmgr/
The SCOM Support Team blog: http://blogs.technet.com/operationsmgr/
The SCVMM Team blog: http://blogs.technet.com/scvmm/
The MED-V Team blog: http://blogs.technet.com/medv/
The DPM Team blog: http://blogs.technet.com/dpm/
The OOB Support Team blog: http://blogs.technet.com/oob/
The Opalis Team blog: http://blogs.technet.com/opalis

clip_image001 clip_image002

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • after hours of trying and testing, it turns out that you manually have to TYPE the line with setspn, do NOT copy & paste, this will not work

  • Its been my experience (and I've done twenty or more of these types of installs), that I need to do more than just those particular SPNs. I need to add two more (FQDN + Netbios name) omitting the ports.

    MSSQLSvc/servername:port

    MSSQLSvc/servername.domain.com:port

    MSSQLSvc/servername

    MSSQLSvc/servername.domain.com

    I've tried using just the first two entries, and 9 times out of 10, I'll continue to get the SMSProv error. Add the last two SPN entries, and I'm good to go.