How to connect to sql server database via LAN

Question

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

Answer 1

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'

Answer 2

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.