使用Excel VBA中的ADO通过网络连接到SQL Server

I need to connect to a SQL Server DB running on my laptop from another computer on a network. I am doing this both from a C# app as well as in a VBA macro. Both run perfectly on my laptop and the C# app runs perfectly over the network. However I cannot connect using VBA over the network. This is my connection string:

我需要从网络上的另一台计算机连接到运行在我笔记本上的SQL Server DB。我在c#应用程序和VBA宏中都在这样做。两者都能在我的笔记本电脑上完美运行，c#应用也能在网络上完美运行。但是，我不能通过网络使用VBA进行连接。这是我的连接字符串:

ConnectionString = "Driver={SQL Server};Server=MY-LAPTOP; DAtabase=SAFEXlive; UID = MyUsername; PWD=MyPassword"

ConnectionString = "司机= { SQL Server };服务器=我的笔记本电脑;数据库= SAFEXlive;,上的用户名都UID = dangillmorPWD =我的密码”

Aside from the 'Driver={SQL Server}' this is the same as the connection string I am using in the C# app which works.

除了'Driver={SQL Server}'之外，这与我在c#应用程序中使用的连接字符串相同。

I am then using the following code (With a reference to Microsoft ActiveX Data Objects 6.0 Library in VBE) to open the connection:

然后，我使用以下代码(参考VBE中的Microsoft ActiveX Data Objects 6.0库)打开连接:

Dim oConnection As Connection
Set oConnection = New Connection
oConnection.ConnectionString = strConnectionString
oConnection.Open

This works correctly if I run it on my laptop but if I run it on another computer on the network I get the error: "Run-time error '-2147217843 (80040e4d) [Microsoft][ODBC Server Driver][SQL Server]Login failed for user..." and the user it specifies it the windows log in for the computer.

如果我在我的笔记本电脑上运行它，它可以正常工作，但是如果我在网络上的另一台电脑上运行它，我就会得到一个错误:“运行时错误”-2147217843 (80040e4d) [Microsoft][ODBC Server Driver][SQL Server] [SQL Server]Login failed for user…

Are there some security settings I need to set in code or in excel? Or am I constructing the connection string incorrectly? What am I doing wrong?

在代码或excel中是否需要设置一些安全设置?还是我构造连接字符串不正确?我做错了什么?

2 个解决方案

#1

Solved. The answer is rather infuriating. The problem is in fact with the connection string, with the UID. Believe it or not changing ...UID= MyUsername;... to ..UID=MyUsername;..., i.e. removing the space character, was all it took! Thanks for suggestions though.

解决了。答案相当令人愤怒。实际上，问题在于连接字符串和UID。信不信由你……,上的用户名都UID = dangillmor……. .;上的用户名都UID = dangillmor……，即去掉空格字符，这就是全部的代价!谢谢你的建议。

#2

Try this Connection string,

试试这个连接字符串,

ConnectionString = "Provider=SQLOLEDB;Data Source=MY-LAPTOP;Initial Catalog=SAFEXlive;User ID=MyUsername;Password=MyPassword"

Is this an AD Domain login? Make sure you have appended the domain to the username e.g, domain\user . I suggest using integrated security instead of this.

这是一个广告域登录吗?确保已将域附加到用户名e。g,域\用户。我建议使用综合安全性，而不是这个。

#1

#2