WebGuru’s Blog about Digital Life

Challenge: By default, the SQL Server Express 2005 was not installed for remote access. Which means networking protocols are disabled by default in SQL Server Express. Thus, if you simply installed Express and chose all the defaults like I did, SQL Server Express will only be able to have connections originating on the local machine where SQL Server is installed.

That is why I got the following error message when I tried to connect to one SQL Server Express instance installed on another server from my desktop’s SQL Server Management Studio Express.

An error has occurred 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

And this is the screen shot I have.

Solutions:

here goes the configuration which can make your SQL Server Express 2005 accept remote access. Every steps below will have to be done on the server with your SQL server installed, and make sure you log onto the server with some kinds of administrator permission.

1. STEP 1: Enabling remote access for TCP/IP or Named Pipe.
   First we must tell SQL Server Express to listen on TCP/IP or named pipe:
   1-1),  Click Start –> All programs –> Microsoft SQL Server 2005 –> Configuration Tools –> SQL Server Surface Area Configuration.
   
    
   1-2) Click Surface Area Configuration for Services and Connections then select Remote Connections, choose either enable TCP or Named Pipe or both.
   
2. Step 2 (Optional) Make sure the TCP/IP enabled 
   Launch the SQL Server Configuration Manager and Click on the "Protocols for SQLEXPRESS" node, then Right click on "TCP/IP" in the list of Protocols and choose, "Enable". This step is optional since normally this TCP/IP has been enabled by default.
   
3. Step 3 (Optional) Make sure the SQL Server browser is started. By default, this will be started automatically. You can check from your Windows Services section.
4. Step 4 Please make your firewall does not block any SQL server ports.
5. Final Step: Reboot your server.

Now, you should be OK to remotely access your SQL Server Express 2005 from your desktop(you can use the free utility SQL Server Management Studio Express to test).