Connecting to SQL Express Remotely

By Josh Wright on August 2010 in tips

 

Setting up remote connections for SQL Express is brutal. Especially for a dope like me.

But I think i've got it down. Here are my quick notes and links to people who explain it better. The problem (for me) is that everyone explains parts of the process, but nobody puts it all together.

WARNING: I'M JUST LEARNING, SO IF YOU'RE WORKING WITH SENSITIVE DATA OR A SENSITIVE SERVER THEN GET SOMEONE ELSE'S ADVICE

Helpful Links:

How to configure SQL Server 2005 to allow remote connections

Configure Express to accept remote connections

SQL Express: Remote Connetions

1) Install SQL Express

Download SQL Express 2005 for free from Microsoft. I chose the one with advanced services (SQL Server 2005 Express Edition with Advanced Services SP3) because hey, who doesn't want advanced services?

Make sure you enable "SQL Server and Windows Authentication Mode" and setup an "sa" password. If you forgot, connect to the db from the management studio, open properties, and check out the "Security" tab.

2) Fix the firewall

Open the firewall settings to the "Exceptions" tab. Click "Add Program..."

The path needs to be something like "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe".

Your instance name (MSSQL.1) might be different. Your "Program Files" might have a rockin (x86) at the end of it.

3) Listen on the Correct Port

Open up the "SQL Server Configuration Manager". It's in Program Files > Microsoft SQL Server 2005 > Config Tools > SQL Server Configuration Manager

Expand "SQL Server 2005 Network Config" and click "Protocols for SQLEXPRESS". Enable TCP/IP (if it was disabled). Then right click it and open its properties.

In the "IP Addresses" tab, look at the "IPAll" section. Blank out "TCP Dynamic Ports"... "0" is not cool.

Make up a port number for "TCP Port", but remember it... you'll use it later.

When you click "OK" they'll tell you to restart SQL. Do it or die. It's under the "Services" section of this configuration manager.

4) Try it Out

From another computer, connect through SQL Server Management Studio. Your "Server name" should look something like:

77.277.07.76\SQLEXPRESS,3333

...where 77.277.07.76 is your server name or ip and 3333 is that port name you imaginized earlier.

5) Troubleshoot

If it doesn't work, you're SOL. Just kidding, I would start by opening my firewall and trying it... turn it back on real quick though 'cause you're super exposed. If it worked, then at least you know your firewall is the only problem.

Good luck.