3

I'm trying to connect to a SQL Server 2008 R2 Express database using a Linq DataContext. The database engine is running on another machine in the local network. We are using SQL authentication, for the time being. SQL Server is set up to allow remote connections.

My connection string looks like this, and works fine. I haven't specified the instance, but it's the only one, so I guess it is chosen by default:

Data Source=192.168.1.50;Initial Catalog=DbName;User ID=SomeUser;Password=SecretPassword;

I've installed the same engine locally too, and this connection string works too:

Data Source=localhost;Initial Catalog=DbName;User ID=SomeUser;Password=SecretPassword;

So does this one, connecting locally:

Data Source=.\SQLEXPRESS;Initial Catalog=DbName;User ID=SomeUser;Password=SecretPassword;

So what if there are multiple instances of the engine running on a remote computer, and I want to specifically refer to an instance?

Data Source=192.168.1.50\SQLEXPRESS;Initial Catalog=DbName;User ID=SomeUser;Password=SecretPassword;

The above connection string yields

Error connecting to database: A network-related or instance-specific error occur red while establishing a connection to SQL Server. The server was not found or w as not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, er ror: 26 - Error Locating Server/Instance Specified)

Why doesn't this work? I can't find any examples of a connection string connecting to a particular instance on a remote database engine.

epalm
  • 4,283
  • 4
  • 43
  • 65
  • try looking at how to configure SQL Server via NamePipes or ping that IP address like this `ping -a 192.168.1.50` and use the name that's returned as the Data Source also I just thought of something.. you need to have the default port number of the remote database server as well in the connection string – MethodMan Jun 17 '13 at 17:58

2 Answers2

1

Try this connection string for remote connection:

Data Source=192.168.1.50\SQLEXPRESS,1433;Network Library=DBMSSOCN;Initial Catalog=DbName;User ID=SomeUser;Password=SecretPassword;

Port 1433 should be opened if firewall is used. And also try to enable remote connection to DbName in sql server management studio.

Firdavs Kurbonov
  • 1,252
  • 4
  • 16
  • 42
0

Even with an IP address, you must specify the instance name.

Aka,

Data Source=192.168.1.50\SQLEXPRESS

is your best candidate there.

  1. Firewalls. You may have to open the PORT NUMBER. But more importantly, you may have to open up the program name.

Note this article:

http://technet.microsoft.com/en-us/library/ms175043.aspx

Note this area in the article:

In the Program dialog box, select This program path. Click Browse, and navigate to the instance of SQL Server that you want to access through the firewall, and then click Open. By default, SQL Server is at C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe. Click Next.

ALSO:

I like to use this program to "ping" my machine and port. Emphasis on the "and the port".

http://www.microsoft.com/en-us/download/details.aspx?id=24009

I keep that little tool around for doing basic debugging with connection issues.

granadaCoder
  • 26,328
  • 10
  • 113
  • 146