Error 401 or "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" when clients try to connect to a SMS 2003 Management Point

Verify permissions

1. At the management point server, click Start, click Run, type cmd, and then click OK. If your SMS site is running under the Standard security mode, go to step 4. If your SMS site is running in the Advanced security mode, go to step 2.
2. If your SMS site is running Advanced security, start a new Command Prompt window that is running under the local system account. To do this, type the following at a command prompt, and then press ENTER:
   ATFutureTime /interactive cmd
   At the time that you specify, a new Command Prompt window opens that is running under Svchost.exe.
   
   Note FutureTime can be any time that is later than the current time, in 24-hour form.
3. At a command prompt, type the following, and then press ENTER:
   osql -S SQLServer -d SMSdbname -E
   Note SQLServer is the name of the server that is running SQL Server, and SMSdbname is the name of the SQL database for your SMS site.
   
   If this command succeeds, your management point has the correct permissions to the SQL database. The command is successful if a 1> prompt is returned. Type exit, and then press ENTER to return to the command prompt. If you receive the following error message, go to step 4:
   Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
4. If you receive the "Login failed" error message that is described in step 3, repeat the command, but use the Fully Qualified Domain Name (FQDN). For example, type:
   osql -S SQLServer.europe.corp.microsoft.com -d SMSdbname -E
   If the command does not succeed, view the DNS settings for the domain where the management point computer is located.
5. If the command still fails check to see whether the MSSQLServer Service is using a user account to log on with, change the service to use the Local System account on the Log On tab in the service properties and run the commands again. If you must run the service by using a user account, make sure that the user account is added to the Domain Administrator group. You will also have to follow the steps in the following article in the Microsoft Knowledge Base:
   829868 Systems Management Server 2003 Advanced Security site with Remote SQL does not connect to SQL Server
    

Additional troubleshooting

1. The appropriate Service Principal Name (SPN) attributes may not be generated for the account that started the SQL services. To resolve this issue, you must manually create the 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. You must use Domain Administrator credentials. Determine if the SQL services run as a domain account or as the local computer account. To use the SetSPN utility to manually create the appropriate SPNs, follow these steps:
   
   When the SQL service is started with a user account
   • To create the FQDN SPN at a Command Prompt window, type the following command:
     setspn -A MSSQLSvc/SqlHostname.mydomain.com:1433 SqlServiceAccount
   • To create the NetBIOS SPN at the command window, type the following command:
     setspn -A MSSQLSvc/SqlHostname:1433 SqlServiceAccount
   When the SQL service is started under the Local System account
   
   If SQL Server runs under the local system account, you do not have to manually configure an SPN for SQL Server. The SPN is created automatically when SQL Server starts and is removed when the SQL service is stopped. If SQL Server runs under a domain user account, you must manually configure an SPN.

   SPN removal

   A duplicate SPN for the computer that is running SQL Server may cause connectivity problems. The following procedure will remove any existing SPNs from SQL Server and add a new SPN:
   1. Stop the MSSQL service.
   2. Run the SETSPN -L computer name command to list all SPNs. The output typically resembles the following:

      MSSQLSvc/MySQLServer dasaccount1 
      MSSQLSvc/MySQLServer:1433 
      MSSQLSvc/MySQLServer.MyDomain.com dasaccount1 MSSQLSvc/MySQLServer.MyDomain.com:1433 
       

      For more information, click the following article number to view the article in the Microsoft Knowledge Base:
       
      319723 How to use Kerberos authentication in SQL Server
       
      
      If the MSSQL service runs under the Local System account, the SPN is removed when the MSSQL service is stopped. Therefore, if an SPN is listed in the output of the SETSPN -L command, it is a duplicate SPN. If the MSSQL service runs under a domain user account, the SPN is still listed when you run the SETSPN -L command. Therefore, you cannot determine whether the SPN is a duplicate. In both cases, to make sure that the SPN is not a duplicate, remove and replace the SPN as described in steps 1c through 1i.
       
   3. If any entries that contain MSSQLSvc are listed, use the SETSPN -D command to remove those entries. For example, type the following:
      SETSPN –D MSSQLSvc/MySQLServer DAS account
      SETSPN -D MSSQLSvc/MySQLServer.MyDomain.com <DAS account>
   4. Run the SETSPN -L computer name command again to make sure that no listings for MSSQLSvc appear.
   5. If the MSSQL service runs under the Local System account, make sure that the SQL Server startup account is the Local System account. You can configure this setting on the Security tab in the Properties dialog box of the server in SQL Server Enterprise Manager.
   6. If the MSSQL service runs under a domain user account, register the SPN by using the SETSPN -A command as mentioned earlier.
   7. Restart the MSSQL service.
   8. After the replication updates the servicePrincipalName attribute on any domain accounts, verify that the SPNs are registered correctly. To do this, run the following command:
      SETSPN -L computer name
   9. If the SPNs are registered correctly, test the SMS connection by using the methods in the "Verify permissions" section.
2. On each primary site, make sure that the SMS_SitetoSQLConnection security group contains the computer accounts or SMS service accounts for all the child servers that report to the primary site. Typically, these accounts are added to the SMS_SitetoSQLConnection security group when a site is installed. If the Setup program cannot add the account, the following site status message is logged in the SMS Administrator console:
   4908 - Site Component Manager could not add machine account "%1" to the SQL Access Group "%2"on the SQL Server machine "%3".
3. The Kerberos ticket cache may have to be reset. Use the Kerbtray tool from the Windows 2000 Server Resource Kit to clear the existing Kerberos ticket cache. For more information about how to use the Kerbtray tool, click the following article number to view the article in the Microsoft Knowledge Base:
    
   232179 Kerberos administration in Windows 2000
    
4. Make sure that the DNS server for the domain is listed first in the TCP/IP properties of the management point server.
5. The FQDN for the target domain must be listed at the top of the suffix search list on the management point server. To change the suffix search list, follow these steps:
   1. Click Start, click Run, type ncpa.cpl, and then click OK.
   2. Right-click the connection that you want to change, and then click Properties.
   3. In the Connection Name Properties dialog box, select Internet Protocol (TCP/IP) under This connection uses the following items, and then click Properties.
   4. On the General tab, click Advanced, and then click the DNS tab.
   5. Click Append these DNS suffixes (in order), click the target domain, and then move the target domain to the top of the list by clicking the scroll arrow.
   6. Click OK two times, and then click Close.
   
   =====
   
   NOTE: This issue has only been observed with Systems Management Server 2003 and does not affect System Center Configuration Manager 2007.