"Not associated with a trusted SQL Server connection" error when a Systems Management Server 2003 or Configuration Manager 2007 site in Advanced Security mode tries to connect to SQL Server

Article translations Article translations
Article ID: 829868 - View products that this article applies to.
Expand all | Collapse all

SYMPTOMS

When a Microsoft Systems Management Server 2003 (SMS 2003) site or a System Center Configuration Manager 2007 (ConfigMgr 2007) site runs in Advanced Security mode you may receive the following SQL server connection error message:

Not associated with a trusted SQL Server connection

CAUSE

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.

RESOLUTION

To resolve this issue, you must manually create fully qualified domain name (FQDN) and NetBIOS SPN entries. To do this 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:
  5. MSSQLSvc/NetBIOS SPN entry:
    MSSQLSvc/sqlhost:1433

MORE INFORMATION

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.

Note The default instance of SQL Server uses TCP port 1433 for network communications. When you use a named instance, the port number is dynamically assigned. Configuration Manager does not support manually changing or defining the port number for either the default instance or named instances of SQL Server.

http://technet.microsoft.com/en-us/library/bb632618.aspx

REFERENCES

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.

Properties

Article ID: 829868 - Last Review: November 30, 2010 - Revision: 3.0
APPLIES TO
  • Microsoft Systems Management Server 2003
  • Microsoft System Center Configuration Manager 2007
  • Microsoft System Center Configuration Manager 2007 Service Pack 1
  • Microsoft System Center Configuration Manager 2007 R2
  • Microsoft System Center Configuration Manager 2007 Service Pack 2
Keywords: 
kbprb KB829868

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com