Systems Management Server 2003 Advanced Security Site with Remote SQL Does Not Connect to SQL Server

When a Microsoft Systems Management Server (SMS) 2003 site runs in Advanced Security mode and has a Remote SQL Server, you may receive the following SQL server connection error message:

This issue may occur if the following conditions are true:

• The appropriate Service Principal Name (SPN) attributes are not generated for the service account that started the SQL services.
  
  -and-
• The SQL service account is not in the Domain Administrators group.

To resolve this issue, you must manually create fully qualified domain name (FQDN) and NetBIOS SPN entries. To do so, you can use the SetSPN utility from the Windows 2000 Server resource Kit. You must run the SetSPN utility on a computer that resides in the SQL server's domain and with an account that has Domain Administrator credentials.

To use the SetSPN utility from the Windows 2000 Server Resource Kit to manually create the appropriate SPNs, follow these steps:

1. Click Start, click Run, and then in the Open box, type:
   cmd
2. Click OK.
3. Create the SPNs:
   • To create the FQDN SPN when the SQL service is started with a domain user account, at the command line, type:
     setspn -A MSSQLSvc/sqlhost.mydomain.com:1433 sqlserviceaccount
   • To create the NetBIOS SPN when the SQL service is started with a domain user account, at the command line, type:
     setspn -A MSSQLSvc/sqlhost:1433 sqlserviceaccount
   • To create the FQDN SPN when the SQL service is started with the SQL server’s System account, at the command line, type:
     setspn -A MSSQLSvc/sqlhost.mydomain.com:1433 sqlhost
   • To create the NetBIOS SPN when the SQL service is started with the SQL Server’s System account, at the command line, type:
     setspn -A MSSQLSvc/sqlhost:1433 sqlhost

Note Do not add the domain prefix to the service account name because the SetSPN utility does not support that action. The SetSPN utility is located on the Windows 2000 Resource Kit CD-ROM.

You can use the ADSI Edit tool to view the SPNs for an account. The ADSI Edit tool is located in the Windows Support Tools folder on the Windows 2000 Server CD and the Windows Server 2003 CD. To set up Support Tools use the Setup.exe program in the Support\Tools\Setup folder. After you install Support Tools, follow these steps:

Warning If you use the ADSI Edit snap-in, the LDP utility, or any other LDAP version 3 client, and you incorrectly modify the attributes of Active Directory objects, you can cause serious problems. These problems may require you to 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 that occur if you incorrectly modify Active Directory object attributes can be solved. Modify these attributes at your own risk.

1. Click Start, point to Programs, point to Windows 2000 Support Tools, click Tools, and then click ADSI Edit.
2. Expand Domain NC, and then expand CN=Users.
3. Right-click the account that starts the SQL services, and then click Properties.
4. In the Properties dialog box, click ServicePrincipalName in the Select a property to view box, and verify that entries that are similar to the following entries exist:
   
   FQDN SPN entry:

   MSSQLSvc/sqlhost.mydomain.com:1433

   NetBIOS SPN entry:

   MSSQLSvc/sqlhost:1433

When the SQL service runs under a domain user account that is a member of the Domain Administrators group, the SQL service automatically creates the SQL SPN that contains the FQDN of the SQL server. The SQL service does not create the SQL SPN that contains the SQL server’s NetBIOS name. If the SQL service account is not in the Domain Administrators group, you must manually create the FQDN and the NetBIOS SQL SPNs.

The problem most frequently occurs in the following cases:

• The SQL server and the site system computer are in different domains, the SQL Server service starts with a user account or the SQL server’s local system account, and the site system computer tries to connect to the SQL server.
• The SQL service runs under a user account, and the site system computer tries to use the site server's computer account to connect to the SQL server.

Note These scenarios work only if both the NetBIOS and the FQDN SPN entries exist.

For more information about how to use the SetSPN utility to register SPNs, see the Security Account "Delegation" topic in the SQL Books online. For more information about the SetSPN utility, see the Windows 2000 Server Resource Kit.