Configuring SQL Server Express 2005 for Remote Access when SQL Server does not allow remote connections

Errors connecting to SQL Server Express 2005

Are you getting errors connecting to SQL Server Express 2005 via remote client software and yet have no problem connecting to it on the local machine?

Some of the errors you might be seeing are:

• sql server does not allow remote connections

• SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

• An error has occured while establishing a connection to the server. When connecting to SQL Server 2005,this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.(provider:Named Pipes Provider,error:40-Could not open connection to SQL Server))

• Server does not exist or access denied

If yes, then read on, because SQL Server Express 2005 is not automatically configured for remote access during installation. It can be enabled however, so the problems you are having are more of a "feature" than a bug.

Why this page? Well, we too ran into this problem - just as you have now. In recognition of all of the kind help we received from the Internet newsgroups we thought it might be useful to collect the information here so as to provide assistance to others in the same position. If you have any suggestions for updates to this page please let us know.

Here's a quick summary of the actions you need to take. The first three actions have a separate page which provides more details and some screen shots of the procedure.

1. [Link] Enable the TCP/IP protocol using the Surface Area Configuration Utility
2. [Link] Make sure the TCP/IP protocol is enabled in the SQL Server Configuration Utility
3. [Link] Make sure the SQL Server browser is started. Note this step is optional. It is possible to set the SQL Server instance to use a fixed IP address - but this is non-standard for named instances. See sqlexpress's WebLog for details.
4. Make sure SQL Server and SQL Server Browser are exempted by the firewall on the server machine. This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in the windows firewall. Chris D. sent in a note which might help.
5. Note: In order to get things to work. You might need to completely reboot the server machine after making the changes. There have been reports (thanks P.C.) that starting and stopping the SQL Server and Browser software is not enough.

Client software for testing remote connectivity to SQL Server Express 2005

Need some software to test out remote connectivity to SQL Server Express 2005? Probably there are lots of ways. Here are four to try out:

• Download and install the SQL Server Management Studio Express Community Technical Preview. SSMSECTP (as it is affectionately known) is a freebie, upgraded and cut down version of the SQL Server 2000 management software. Works pretty well for simple management tasks, has a T-SQL window and, of course, nicely tests out remote connectivity.

  To install SSMSE you will need the following (note the 2.0 Beta version of the .NET framework is not acceptable. Uninstall it first if you have it.). It is probably best to install them in the order below

  Microsoft .NET Framework Version 2.0 Redistributable Package
  Microsoft Core XML Services (MSXML) 6.0
  Microsoft SQL Server Management Studio Express - Community Technology Preview (CTP) November 2005

  Or try the ADO bare bones Connection tip using .udl files sent in by Kieran H and Dan P.

  Or try the client side osql.exe utility with the following command line:

  osql -S SERVERNAME\SQLEXPRESS -U <username>
  

  To use OSQL, start up a Windows command session (Start:Run...:cmd) and type in the above command. On our test machine the OSQL binaries are located in the C:\Program Files\Microsoft SQL Server\90\Tools\Binn directory. If you do not have this directory somewhere it probably means you did not install the client side tools.

  So of course now the question is ""how to install the OSQL tool (and others) on the client" Well fortunately it is not too difficult as they are co-located in the same installation package as the SQL Server Express 2005 software. To install them just go back to the same installer you used to create the SQL Server Express 2005 instance on the remote system and run it on your client system. Instead of accepting the default install (which installs the server engine) choose to install only the client side utilities. This will create the above directory and install the tools without installing the database on your client machine.

  For further testing you can also dig around a bit and find out how to use the SQLCMD.exe utility.

  Or use the following snippet of C# code. Note: If you are using the Visual Studio 2005 environment you might want to read the page entitled Using Visual Studio 2005 to Connect to SQL Servers by Name

      private void button1_Click(object sender, 
          System.EventArgs e)
      {
          SqlDataReader rdr = null;
  
          // 1. Instantiate the connection
          // test by server name instance name
          // SqlConnection conn = new SqlConnection(
          //   "Data Source=MYSERVERNAME\\SQLEXPRESS; 
          //   Initial Catalog=Northwind; 
          //   User ID=sa; 
          //   Password=sa");    
          // test by ip address, port and instance name
          SqlConnection conn = new SqlConnection(
      	   "Data Source=123.123.123.123,1066;
      	   Network Library=DBMSSOCN;
      	   Initial Catalog=Northwind;
      	   User ID=sa;Password=sa;");
  
          // 2. Open the connection
          conn.Open();
           // 3. Pass the connection to a command object
          SqlCommand cmd = new SqlCommand(
      	    "select * from Customers", conn);
          // 4. Use the connection
          rdr = cmd.ExecuteReader();
          // print the CustomerID of each record
          while (rdr.Read())
          {
              Console.WriteLine(rdr[0]);
          }
          // close the reader
          if (rdr != null)
          {
              rdr.Close();
          // 5. Close the connection
          if (conn != null)
          {
              conn.Close();
          }
          MessageBox.Show("Done", 
      	   "Done", 
      	   MessageBoxButtons.OK, 
      	   MessageBoxIcon.Exclamation);
      }
  

Other resources