Named pipes error when connecting to remote Express Edition server

4
votes

I am trying to connect to MS SQL Server 2008 R2 Express Edition through a Remote computer. I am getting this error :

A network-related or instance-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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

On running SELECT CONVERT(char(20), SERVERPROPERTY('InstanceName')) instancenameting

I get NULL as the instance name. I have started SQL Browser service, checked TCP/IP are enabled.

What else can be done?

2
sql-serversql-server-2008
If you can login into it as localhost what do you get for Select @@Servername? - Eric J. Price
it is showing a different name something like IP-0A6E3175 - user2129794
You haven't mentioned how you are connecting to the server: are you using SSMS, another application or your own code? If it's your code, what does the connection string look like? The error mentions named pipes, not TCP/IP, so something may be misconfigured. There is a nice overview of troubleshooting this error here. - Pondlife
Then IP-0A6E3175 is the name of your SQL Instance and that is what you want to use as the server in your connection string. - Eric J. Price
I am connecting using C#, through my own code. connection string looks like : Data Source=<<server name>>;Initial Catalog=<<database name>>;Persist Security Info = True;User ID=<<username>>;password=<<password>> - user2129794

2 Answers

2
votes

Use 

Data Source=IP-0A6E3175;Network=DBMSSOCN;...

Or

Data Source=<<ip address>>;Network=DBMSSOCN;...

You'll also need to make sure that TCP/IP is enabled on the host using SQL Server Configuration Manager, that you aren't blocked by a firewall, etc.

And just for kicks, also please try:

Data Source=<<ip address>>\SQLEXPRESS;Network=DBMSSOCN;...
1
votes

In addition to Aaron's suggestion to ensure your connection string uses tcp, run SQL Server Configuration manager and check the following:

  • do you have more than one instance?
  • tcp/ip enabled?
  • if the instance you are targeting is not the default instance, it will not be on port 1433 but rather some dynamic port. The Microsoft libraries figure out which port using SQL Server Browser on port 1434. Your firewall needs to allow that also (UDP).
  • if your Express instance is the only instance, you can change to use port 1433. Then you don't need SQL Server Browser.