11

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.

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

I hope i asked the question correctly

Improve this question
3
  • As the message states attaching from network share is not supported. What is the edition of SQL Server you're trying to connect to? Commented Oct 2, 2013 at 20:33
  • 1
    Do you have SQL Server installed on the Server? You don't want to attach to the MDF file over the lan, you want to connect to the remote server. Commented Oct 2, 2013 at 21:06
  • Sql server 2008 and its also installed on the remote server system Commented Oct 3, 2013 at 4:37

2 Answers 2

Reset to default
8

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

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

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'
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

6

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

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

Check this for more details

http://blogs.msdn.com/b/varund/archive/2010/09/02/create-a-sql-server-database-on-a-network-shared-drive.aspx

Option 2 Connect to remote SQL Server instance from local computer

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.

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

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

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.

Improve this answer

3 Comments

I apologize for the late response. I have been trying many options. I have also tried to connect using IP. This is what i currently learned from connectionstrings.com. I have tried to connect to the remote computer using this string = 'Server=tcp:SERVER,1433;Database=TBSDB.mdf;Integrated Security=True;Network Library=dbmssocn;Connect Timeout=30;User Instance=True' Then system tcp connection enabled and firewall disabled. The computer name is SERVER where TBSDB.mdf is the database file located in the App directory of the C:\ of the remote computer.
But i get the error meassage: A network-related or instandce-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known)
I have been waiting for replies since 5 days. Please help me.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.