SQL Server login works in a connection string, but fails in DSN
I have a web site and I'm trying to setup a DSN instead of using a connection string because it seems more secure.
The following connection string works fine in my site:
"Server=servername; database=mydb; user id= web.account; password=PassW0rd!; Integrated Security=SSPI"
However, when I try to set up a DSN (using the 64-bit odbc admin) I get the following error:
Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'web.account'
Keep in mind, I'm using the same account and password I use in the connection string (which works). It's weird too because the error occurs right after I click 'Next' on the "How should SQL Server verify the authenticity of the login ID?" page.
It seems like the odbc admin does not even try to connect to the server to verify the credentials. What can I check to prove this / How can I fix this?
Things I've tried:
- I have checked the server authentication mode, as proposed in other solutions, and that is set to "SQL Server and Windows Authentication mode", as it should be.
- I have tried creating both a User and System DSN - both get the same error, this seems like further evidence that it's not trying to connect to the server to check the credentials.
- I have tried the same DSN creation on multiple servers, all have the same problem. Is it possible the SQL Server is refusing these types of connections?
UPDATE 1: The DBA just told me that the web.account is actually a Windows account, not an SQL account. I guess that's why the odbc isn't working, because it doesn't try to connect as a different windows user, only as the current windows user. So now the question is, how do I set up a DSN with a different windows account? Or, is that even possible?
Answer
This post has a response that says DSNs setup with NT authentication are specifically for the entire system, not a specific account, so a runas command could be used from cmd, like so:
runas /netonly /user:domain\web.account "C:\Windows\SysWOW64\odbcad.exe"
It will then ask you for the web.account password. Then you can create a DSN, and this should work.
UPDATE: I had to add the web.account user to my Administration Group using this fine how-to. Then I had to run cmd as my elevated account. Then ran the runas command I showed above. Then, I was finally able to create a non-native DSN, which allowed me to connect to the Data Server as a different Windows user.
Still waiting for DBA to check the logs to say this actually worked or not, but I feel pretty good about it. Will update after confirmation.