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 domainuser 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 [domainuser] from windows", then try -
osql -S -U [domainuser] -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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.