7

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...

Karl Arsenault
  • 147
  • 1
  • 1
  • 9
  • 1
    You are stating you have enabled Mixed mode authentication, but the exception message clearly states otherwise. Has there been a reboot since changing the authentication mode? See this post: http://stackoverflow.com/questions/8441158/an-attempt-to-login-using-sql-authentication-failed – Jacco Feb 13 '13 at 21:50

3 Answers3

9

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.

David Aleu
  • 3,922
  • 3
  • 27
  • 48
  • It returns 0. If I understand correctly this means my server is in Mixed authentication mode as I expect/need it to be? – Karl Arsenault Feb 13 '13 at 22:24
  • Good, SQL Server is definitely mixed authentication... is the password short? you could uncheck the enforce password policy in the database properties although you said you could connect remotely and if that policy was on and the password was short you shouldn't be able to connect remotely. – David Aleu Feb 13 '13 at 22:36
  • Another option would be don't specify the port in your connection: 169.254.204.232 instead of 169.254.204.232,1433 – David Aleu Feb 13 '13 at 22:36
  • I'm assuming you have non-trusted connections allowed (again, as you can log in remotely) but you can check from Studio Management, right click on your server instance > properties > security – David Aleu Feb 13 '13 at 22:39
  • Thanks for the help guys. I solved this problem. Please take a look at my update in my original post (I can't post an answer to my own question for at least 5 more hours) – Karl Arsenault Feb 13 '13 at 22:42
6

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 :)

Adarsh
  • 141
  • 2
  • 2
2

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...

Karl Arsenault
  • 147
  • 1
  • 1
  • 9