I went into this in my post about SQL Server remote connections, but basically, for each SQL Server instance running on your database server, you can enable remote access, control which IPs and ports SQL Server listens on, and which IPs are allows to access which port.

Use Windows Security when possible

Connecting via a Windows account is more secure than connecting with a SQL Server login and password. In the past those values were sent in plaintext (eek), although now SQL Server 2005 offers lockouts, password complexity enforcements, and password expiration for SQL Server logins, so they’re not as unsecure as they used to be. Also if you’re using the SQL Native Client to connect, your login packet is encrypted, so the password isn’t in plaintext anymore.

If you need to use SQL Server logins, don’t use the sa account — log in with a different account with limited permissions. And use strong passwords. If it’s available over the internet, you should use an SSL certificate to encrypt connections, too (see Encrypting Connections to SQL Server in Books Online).

Allowing Remote Access

SQL Server doesn’t allow remote access by default. So if you want other machines to access SQL Server, open up SQL Server Surface Area Configuration (in Programs->Microsoft SQL Server 2005->Configuration Tools) and click Surface Area Configuration for Services and Connections. On the left side, navigate to the Remote Connections node under your SQL Server instance’s Database Engine node, and ensure Local and Remote connections is selected with Using TCP/IP only. Then click OK.

Configuring IPs

You do so via opening up SQL Server Configuration Manager, opening the Network Configuration node and clicking the Protocols section. You’ll see TCP/IP on the right.

Double click TCP/IP. You’ll see a dialog like below with some settings on the Protocol tab:

By default, Listen All is set to Yes, which means SQL Server is listening on every IP the server has. If the server has a public IP, or if you don’t want it listening on certain IPs, set Listen All to No. Now switch to the IP Addresses tab.

You’ll see entries for all the machine’s bound IPs — in the above example 192.168.0.10 is the internal IP, 77.89.121.42 is a public IP, and 127.0.0.1 is the local loopback IP (note those aren’t my real IPs, they’re just for show).  Active means the IP address is a working IP network-wise — changing the value via the dropdown doesn’t do anything. If Listen All is set to No, you can set specific ports for various IPs, or tell SQL Server to not listen on them by setting Enabled to No. Notice how I’ve used the port 2000 and disabled access on the public IP.

FYI, Dynamic Ports is a setting where SQL Server finds an available port at runtime. I don’t recommend Dynamic Ports because a) you have to run the SQL Server Browser service (a security risk) in order to inform clients which port is being used, and b) it’s hard to protect SQL Server with a firewall because you never know which port is going to be used. So you’re stuck opening up a bunch of ports, which is bad.

Anyhow, if Listen All was set to Yes on the Protocol tab, then you can’t apply settings for individual IPs — you instead apply settings for all IPs in the IPAll section at the bottom.

Configuring the Firewall

Now you can create a firewall rule allowing certain IPs access to your machine over certain ports. If you’re using Windows Firewall, you can do so by first opening Windows Firewall from the Control Panel and clicking the Exceptions tab. You’ll see a list of current exceptions.

Click Add Port. Give your rule a name (e.g. “SQL Server Rule”) and specify the port you set in SQL Configuration Manager.

Now we want to specify who can access our server over this port. By default, everyone can (even folks over the internet), which IMO isn’t super secure. So click Change Scope. You can choose My Network to allow all computers on your network to access your server, but if you want a more narrow range (good if you have a large network but only a few machine should be able to access your machine), you can enter the specific IPs and/or masks in the Custom List section.

Now click OK, and click OK again, and your new firewall rule is applied.

Finishing Up

Now restart your SQL Server service, and you’re ready to connect. You can also feel better that you’ve protected your SQL Server instance.