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:
我有一个Visual Studio C ++项目(非托管C ++),我尝试连接到LAN中另一台机器上的SQL Server 2008实例。我使用TCP / IP。我的连接字符串是:
"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.
重要的事实:我能够使用SSMS远程成功连接用户ID为myDBUser和密码myPassword的实例 - 使用SQL身份验证模式(并在连接选项中指定TCP / IP)!此外,登录后我可以成功导航数据库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.
另请注意,当我的实例是本地的并且我使用的是Windows身份验证时,相同的代码已成功连接。换句话说,自从整个事情最后工作以来发生了变化的是我将服务器移动到另一台机器,现在使用SQL身份验证,因此更改了我的连接字符串 - 否则代码根本没有改变。
Here is the error message I get in my SQL Server 2008 instance's Server Logs:
以下是我在SQL Server 2008实例的服务器日志中收到的错误消息:
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.
用户登录失败“。原因:尝试使用SQL身份验证登录失败。服务器仅配置为Windows身份验证。错误:18456,严重性:14,状态: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.
请注意,在该错误消息中引用的用户为空,即使在我的连接字符串中我指定了非空白用户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...
更新:我似乎解决了这个问题。你准备好了解这个错误信息是多么愚蠢和完全不相关?在连接字符串中,我只是将“用户ID”更改为“uid”,将“密码”更改为“pwd”,现在它可以正常工作。我现在在SQL Server日志中看到“已成功连接”...
3 个解决方案
#1
8
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.
尝试运行SELECT SERVERPROPERTY('IsIntegratedSecurityOnly');如果返回1是Windows身份验证,如果0混合。如果它返回1肯定是Windows身份验证,并且必须有其他错误。
#2
5
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.
右键单击对象资源管理器上的servername - >属性 - >安全性 - >将服务器身份验证更改为SQL服务器和Windows身份验证模式 - >单击确定。
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.
然后断开SQL并重新启动系统。然后在sql身份验证模式下使用更改的密码登录SQL Server 2008。
Thanks :)
#3
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...
答案:在连接字符串中,我只是将“用户ID”更改为“uid”,将“密码”更改为“pwd”,现在它可以正常工作。我现在在SQL Server日志中看到“已成功连接”...
#1
8
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.
尝试运行SELECT SERVERPROPERTY('IsIntegratedSecurityOnly');如果返回1是Windows身份验证,如果0混合。如果它返回1肯定是Windows身份验证,并且必须有其他错误。
#2
5
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.
右键单击对象资源管理器上的servername - >属性 - >安全性 - >将服务器身份验证更改为SQL服务器和Windows身份验证模式 - >单击确定。
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.
然后断开SQL并重新启动系统。然后在sql身份验证模式下使用更改的密码登录SQL Server 2008。
Thanks :)
#3
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...
答案:在连接字符串中,我只是将“用户ID”更改为“uid”,将“密码”更改为“pwd”,现在它可以正常工作。我现在在SQL Server日志中看到“已成功连接”...