Topics from the Microsoft SQL Server Protocols team - Netlibs, TDS, SQL Browser, etc.
This post focus on a typical cause of sql login failure issue described as the title.
In real world, a customer log on to the machine as a domain\user or local machine accout, he can connec to SQL Server through SQL Server Management Studio. However, he fails to connect by using SQL Login, namely, in connection string specify User as the NT account, and the password.
He did: "Create login [domain\user] from windows", then try -
osql -S -U [domain\user] -P xx or osql -S -U[machineaccount] -P xx.
To explain the reason, first you need to understand two Authentication Modes in SQL Server 2005:
The –U and –P options are only for SQL Authentication. The SQL Client will not try to impersonate a windows user, instead it directly send the user/password to SQL Server and try to find a matching SQL login.
The solution is: You need to runas to impersonate the windows user and connecting using -E option in "osq.exe" or specify "Integrated Security=SSPI" or "Trusted_Connection = yes" in your connection string.
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights
These issue i have had with this error message is our application using the jtds open source driver which by default uses NTLM authentication. Our client used NTLMv2. We were able to put ';useNTLMv2\=true' into our connection string and no longer got this error.