By default, remote connections are disabled for all versions of SQL Server 2005, including SQL Server 2005 Express. If you try to connect from a different machine, you’ll get an error message like this:
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 default settings SQL server does not allow remote connection.
To resolve it, you need to enable remote connections in the SQL Server Surface Area Configuration Tool (under Start->Programs->Microsoft SQL Server 2005->Configuration Tools) and then restart the SQL Server Express service. See this full walkthrough is on MSDN on how to do so.
If you then get errors like “Error Locating Service/Instance Specified” you have two options: using the SQL Server Browser, or using TCP/IP & specifying the port when you connect.
SQL Server Browser
The SQL Server Browser service tells interested parties the SQL Server instances available on the machine. It’s a mild security risk (especially if you expose it over the internet), but it lets you easily connect to SQL Server instances, especially if your database server has multiple instances running. You’ll need to ensure that the SQL Server Browser Service is running on the database server, and that the database server’s firewall isn’t blocking the SQL Server Express service or the SQL Server Browser service. See this MSDN article on how to do so.
TCP/IP Ports
This method is more secure but a little more work. You don’t have to run the SQL Server Browser service, and you get to pick (and manage traffic on) the IP and port that each SQL Server instance listens on. Worth it for a public server, IMO. If you’re using TCP/IP and don’t want to run SQL Server Browser for security reasons, or if you have multiple SQL Server instances on your server, you’ll need to check the TCP/IP ports that your SQL Server Database Engines are listening on, and ensure each running instance has its own port.
Open up SQL Server Configuration Manager (under Start->Programs->Microsoft SQL Server 2005->Configuration Tools), expand SQL Server Network Configuration, click Protocols for SQLEXPRESS, double-click TCP/IP. Note whether “Listen All” is Yes or No. Click the IP Addresses tab. If “Listen All” was Yes, then you can set the ports in the “IPAll” section below. If “Listen All” was set to No, then ensure that there’s a port specified for the listed IP addresses, or disable (set Enabled to “No”) any public IPs you don’t want to expose SQL Server on.
Then open up Windows Firewall on the database server (or whatever your firewall program is) and allow TCP traffic in for the port you specified. Be sure to indicate who you want to access this port, too (e.g. the internet, your local network, a specific subnet, or just the local machine).
Then you should be able to connect to the server using “<ip address>,<port>” (e.g. “192.168.0.10,8000”) from SQL Server Management Studio.
