如何通过LAN连接到SQL Server数据库

want to connect to a database on another PC connected via LAN. I am able to use the sql server db with string like C:\Users... but i cannot connect using string like (\\Server\c\user...) I tried to move the db file to My Documents, still i get this error.

想要连接到通过LAN连接的另一台PC上的数据库。我能够使用sql server db与字符串如C:\ Users ...但我无法使用字符串连接(\\ Server \ c \ user ...)我试图将db文件移动到我的文档,仍然我收到这个错误。

I get the following error message: An attempt to attach an auto-names database for file (\\SERVER\Users\Jeswills\Documents\TBSDB.mdf) failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share

我收到以下错误消息:尝试附加文件的自动名称数据库(\\ SERVER \ Users \ Jeswills \ Documents \ TBSDB.mdf)失败。存在具有相同名称的数据库,或者无法打开指定的文件,或者它位于UNC共享上

I hope i asked the question correctly

我希望我正确地提出这个问题

2 个解决方案

#1

Not sure what specifically you’re trying to do here but I guess it’s one of these two.

不知道你在这里要做什么具体,但我想这是这两个中的一个。

Option 1 Attach database stored on remote shared drive to a local SQL Server

选项1将存储在远程共享驱动器上的数据库附加到本地SQL Server

Note that this is only possible starting in SQL Server 2008 R2. If you’re running SQL Server 2008 this is not an option.

请注意,这只能从SQL Server 2008 R2开始。如果您正在运行SQL Server 2008,则这不是一个选项。

Check this for more details

Option 2 Connect to remote SQL Server instance from local computer

选项2从本地计算机连接到远程SQL Server实例

If that database is already attached to SQL Server instance that runs on the same machine then it’s much better to just connect to that instance from SSMS than trying to attach database from remote storage.

如果该数据库已经连接到在同一台机器上运行的SQL Server实例,那么从SSMS连接到该实例比尝试从远程存储连接数据库要好得多。

To do this you need to enable TCP/IP protocol in SQL Server Configuration Manager. It’s under SQL Server Network configuration node. Make sure you enable TCP/IP and also set enable the IP address for listening (this is under TCP/IP properties).

为此,您需要在SQL Server配置管理器中启用TCP / IP协议。它位于SQL Server网络配置节点下。确保启用TCP / IP并设置启用侦听的IP地址(这是在TCP / IP属性下)。

Apart from this you’ll want to enable remote connections on your remote instance. This is done from SSMS -> instance properties -> Connection tab

除此之外,您还需要在远程实例上启用远程连接。这是通过SSMS - >实例属性 - >连接选项卡完成的

When this is done you should be able to connect to remote instance from local SSMS by typing in IPaddress/instance name. For example 192.168.0.125/{instance_name} or only IP address if this is default instance.

完成此操作后,您应该能够通过键入IPaddress / instance name从本地SSMS连接到远程实例。例如192.168.0.125/{instance_name}或仅IP地址(如果这是默认实例)。

#2

As database does not support the '\SERVER\c...' parameters, i had to attach the database, after enabling TCP/IP and SQL Browser, i had to create a login through security and add it to the attached database file because authentication must be SQL not windows. And i also gave read/write privileges to the account. Then on the child system, i confirmed connection to the account through SSMS with the login connecting to SERVER (which is the remote computer's name).

由于数据库不支持'\ _SERVER \ c ...'参数,我必须附加数据库,在启用TCP / IP和SQL浏览器之后,我必须通过安全性创建登录并将其添加到附加的数据库文件,因为身份验证必须是SQL而不是Windows。我还为帐户提供了读/写权限。然后在子系统上,我确认通过SSMS连接到帐户,登录连接到SERVER(这是远程计算机的名称)。

Note: you must be able to ping the remote systems and SQL Server Express R2 installed. I tried with SQL Server Express but did not get a head way. www.connectionstrings.com/sql-server-2008 for more connection string

注意:您必须能够ping远程系统和安装的SQL Server Express R2。我尝试过使用SQL Server Express,但没有采取行动。 www.connectionstrings.com/sql-server-2008获取更多连接字符串

Then i used this connection string to connect remotely, making integrated security and user instance = false unlike if i were connecting locally.

然后我使用这个连接字符串远程连接,使集成安全性和用户实例=假,不像我在本地连接。

 Data Source=SERVER\SQLEXPRESS,1433;Database=DATABASEFILE.MDF;Integrated Security=False;Network Library=dbmssocn;Connect Timeout=30;User Instance=False;user='USERNAME';password='PASSWORD'

#1