SQL Server 2008 error 18456 state 58, Login failed for user "

Question

I have a Visual Studio C++ project (unmanaged C++) in which I try to connect to a SQL Server 2008 instance on another machine in the LAN. I use TCP/IP. My connection string is:

"DRIVER={SQL Server Native Client 10.0};Server=tcp:169.254.204.232,1433;Network Library=DBMSSOCN;Initial Catalog=myDB;User ID=myDBUser;Password=myPassword;"

Important fact: I am able to successfully connect remotely to that instance with user id myDBUser and password myPassword using SSMS -- using SQL Authentication mode (and specifying TCP/IP in the connection options)! Also, once logged in I can successfully navigate the database myDB.

So yes, I have enabled Mixed mode authentication on my server.

Also note that the same code was successfully connecting when my instance was local and I was using Windows Authentication. In other words, what changed since this whole thing last worked is that I moved my server to another machine, am now using SQL Authentication, and therefore changed my connection string -- the code has otherwise not changed at all.

Here is the error message I get in my SQL Server 2008 instance's Server Logs:

Login failed for user ". Reason: An attempt to login using SQL Authentication failed. Server is configured for Windows Authentication only. Error: 18456, Severity: 14, State: 58.

Notice that the user being quoted in that error message is blank, even though in my connection string I specify a non-blank user ID.

Other connection strings I tried that give the same result:

"DRIVER={SQL Server Native Client 10.0};Server=MACHINE2;Database=myDB;User ID=myDBUser;Password=myPassword;" (where MACHINE2 is the windows name of the machine hosting the sql server instance.)

I do not specify an instance name in the above connection string because my instance is installed as the default instance, not a named instance.

Any ideas on how to solve this?

UPDATE: I solved this problem it seems. Are you ready to find out how silly and totally unrelated that error message was? In the connection string, I just changed "User ID" to "uid" and "Password" to "pwd", and now it works. I now see "Connected successfully" in my SQL Server logs...

Answer 1

Try running SELECT SERVERPROPERTY('IsIntegratedSecurityOnly'); if it returns 1 is Windows Authentication if 0 Mixed. If it returns 1 is definitely Windows Authentication and there must be something else wrong.

Answer 2

I think I solved this problem by doing this...

right click on servername on object explorer -> Properties -> Security -> Changed server authentication to SQL server and Windows authentication mode -> click OK.

after that open server on object explorer -> Expand security -> Expand Login -> right click on your login -> properties -> type new password -> confirm password -> OK.

then disconnect your SQL and restart your system. then login SQL server 2008 with changed password in sql authentication mode.

Thanks :)

Answer 3

The answer: In the connection string, I just changed "User ID" to "uid" and "Password" to "pwd", and now it works. I now see "Connected successfully" in my SQL Server logs...