Article ID: 829868 - View products that this article applies to.
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
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.
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 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:
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.
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:
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.
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.
Article ID: 829868 - Last Review: November 30, 2010 - Revision: 3.0