6
I'm trying to connect to a SQL Server 2008 database from a 3rd-party application through an ODBC connection. I am having success when the ODBC is set up with Windows Authentication but not when it's set up with SQL Server Authentication.
My general process is
- Create 64-bit System ODBC DSN ("C:\Windows\SysWOW64\odbcad32.exe")
- When I create the DSN I test the connection at the end and it all looks good. ANd I can verify this in SQL Server using
Login succeeded for user 'myuser'. Connection made using SQL Server authentication. [CLIENT: 10.85.43.91]
- I then go to my application, reference that DSN and try to login. I get an error saying
Unable to connect to general Datasource. Connect String: DSN=<DSN Name> Error: login failed for user ''. I then go back to the SQL error log and see
Login failed for user 'myuser'. Reason: Password did not match that for the login provided. [CLIENT: 10.85.43.91]
and
Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 10.85.43.91]
I am able to connect to SQL through SMSS with that username and run queries.
I'm also able to verify that Authentication mode is MIXED. from the log lines at server startup. Same verification via Object Explorer > Server Properties > Security > SQL Server and Windows Authentication Mode.
We started the server last night, too just to make sure all these settings were true.
This happens while trying to connect from at least 4 computers.
What gives? What am I missing? Why can I not connect to SQL with SQL Authentication via ODBC?
Brad
Posted 2013-06-26T15:20:42.257
Reputation: 486
How are you passing the credentials to the ODBC driver? Also are you sure your program is using the 64 bit driver? – Scott Chamberlain – 2013-06-26T15:35:43.933
Also I think you may get better answers on Stack Overflow, I voted to move. – Scott Chamberlain – 2013-06-26T15:37:31.113
I give the SQL Server user name and password to the DSN when I set it up through Windows, then in the application I just elect to use that one. I am sure the program is using the 64 bit driver. The program has the option to create DSNs from within it too (it opens up the Windows tool) and it makes 64-bit DSNs. Plus if I make a 32-bit system DSN it doesn't show up on the list of available connections. – Brad – 2013-06-26T15:39:45.300
@ScottChamberlain I wrote this all up on SO then thought it would be better here :) I guess I was wrong. – Brad – 2013-06-26T15:40:19.643
Do you have the ability to name specific application you are using? – Ramhound – 2013-06-26T16:33:18.997
3
Questions about database administration are off-topic here, but can be asked on Database Administrators Stack Exchange.
– bwDraco – 2013-06-27T16:21:53.520@DragonLord I think I'm going to remove this. The answer was extremely localized. It was solved but it was only by being on the phone with the vendor (kind of their error) – Brad – 2013-06-27T18:02:30.620
That's okay. Just make sure you don't delete too many of your questions; having a disproportionate number of downvoted or deleted questions may result in a question ban.
– bwDraco – 2013-06-27T19:08:04.970@DragonLord I guess I can answer it, too. It was a good lesson learned to always run everything As Administrator. – Brad – 2013-06-27T19:11:04.663
Then go ahead and answer your own question; there's nothing wrong with doing that. Do keep in mind, though, that you must wait until 48 hours after the time you posted your question before you can accept your own answer (that's about 20 hours from now). – bwDraco – 2013-06-27T19:14:29.457