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?
Best Answer
[ODBC] DRIVER=SQL Server Native Client 10.0 UID=drazen LANGUAGE=your native DATABASE=name_db WSID=name server APP=Microsoft Office 2010 Trusted_Connection=Yes SERVER=(local) Description=describe conn