Hi @nigelpost ,
This section shows you the steps to help ensure that your computer does not experience any SSPI problems.
There are several situations of my distance here. Please check the full content in this article.
Verify the domain Verify that the domain to which you log on can communicate with the domain to which the computer that is running SQL Server belongs. There must also be correct name resolution in the domain.
-
You must make sure that you can successfully log on to Windows by using the same domain account and password as the startup account of the SQL Server service. For example, the SSPI error may occur in one of the following situations:
-
The domain account is locked out.
-
The password of the account was changed. However, you never restart the SQL Server service after the password was changed.
-
If your logon domain differs from the domain of the computer that is running SQL Server, check the trust relationship between the domains.
-
Check whether the domain that the server belongs to and the domain account that you use to connect are in the same forest. This is required for SSPI to work.
-
Use the Account is Trusted for Delegation option in Active Directory Users and Computers when you start SQL Server.
Note The 'Account is Trusted for Delegation' right is required only when you are delegating credentials from the target SQL server to a remote SQL server such as in a double hop scenario like distributed queries (linked server queries) that use Windows authentication.
-
Use the Manipulate Service Principal Names for Accounts (SetSPN.exe) utility in the Windows 2000 Resource Kit. Windows 2000 domain administrator accounts or Windows Server 2003 domain administrator accounts can use the utility to control the SPN that is assigned to a service and an account. For SQL Server, there must be one and only one SPN. The SPN must be assigned to the appropriate container, the current SQL Server service account in most cases and the computer account when SQL Server starts with the local system account. If you start SQL Server while logged on with the LocalSystem account, the SPN is automatically set up. However, if you use a domain account to start SQL Server, or when you change the account that is used to start SQL Server, you must run SetSPN.exe to remove expired SPNs, and then you must add a valid SPN. For more information, see the "Security Account Delegation" topic in SQL Server 2000 Books Online. To do this, go to the following Microsoft website:
http://msdn2.microsoft.com/library/aa905162(SQL.80).aspx For more information about Windows 2000 Resource Kits, go to the following Microsoft website:
http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/default.mspx?mfr=true
-
Verify that name resolution is occurring correctly. Name resolution methods may include DNS, WINS, Hosts files, and Lmhosts files. For more information about name resolution problems and troubleshooting, click the following article number to view the article in the Microsoft Knowledge Base:
169790 How to troubleshoot basic TCP/IP problems
-
For more information about how to troubleshoot accessibility and firewall issues with Active Directory, click the following article numbers to view the articles in the Microsoft Knowledge Base:
291382 Frequently asked questions about Windows 2000 DNS and Windows Server 2003 DNS
224196 Restricting Active Directory replication traffic and client RPC traffic to a specific port
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
