SQL Server 2008R2 cannot be connected from Windows Server 2000 through Windows Authentication

Question

I setup a SQL Server 2008R2 64bit. This SQL Server can be connected from Windows 2003 client through Windows authentication, but CANNOT be connected from Windows 2000 client through Windows authentication, even it's using exactly the same domain user account. The error message is "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication".

By the way, both Windows 2000 and Windows 2003 clients can get connection through SQL Server authentication. And all clients are in the SAME DOMAIN with this SQL Server.

I'm so wondering why only Windows 2000 failed to login. I tried to install the latest version of MDAC (2.8 SP1), but still failed.

Anyone please help me! Thanks!

Answer 1

Hello nonno,

Have you checked the clock of that server that can't connect? If it differs more then 5 minute from the SQL Server time, then the server is treaten as untrusted.

---

Olaf Helper

Blog Xing

Answer 2

Hello Olaf,

The clock of the client machines and the database server are very close, nearly in sync.

I just checked the SQL Error Log, found another related error message "SSPI handshake failed with error code 0x80090302, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.  [CLIENT: 160.101.219.2].".

Any idea?

Answer 3

My colleague found the solution. It's related to the local security policy setting "LAN Manager Authentication Level" of the Win2000 client. It was set to "Send LM & NTLM responses" and failed to connect to SQL2008. He set it to "Send NTLMv2 response only" then it works!

Btw, anybody can tell me what exactly it is? Or can I set it on the SQL2008 server, so that it can accept "Send LM & NTLM responses"?

Answer 4

Hi nonno,

Please refer to:

http://technet.microsoft.com/en-us/library/cc738867(v=ws.10).aspx

Send LM & NTLM responses: Clients use LM and NTLM authentication and never use NTLMv2 session security; domain controllers accept LM, NTLM, and NTLMv2 authentication.

Send NTLMv2 response only: Clients use NTLMv2 authentication only and use NTLMv2 session security if the server supports it; domain controllers accept LM, NTLM, and NTLMv2 authentication.

It seems SQL Server windows authentication use NTLMv2 session security. For more information about LAN Manager Authentication Level, I would like suggest you to post this issue to windows server forum. http://social.technet.microsoft.com/Forums/en-US/category/windowsserver

---

Iric Wen
TechNet Community Support

Answer 5

I fixed it by adding the Service Principal Name (SPN) of the SQL Server to the domain.

setspn -A MSSQLSvc/DC5EPRIDSQL01.corp.ha.org.hk:43100 corp\epridsqladmin
setspn -A MSSQLSvc/DC5EPRIDSQL01.corp.ha.org.hk:MID_HO_MP1 corp\epridsqladmin