SQL and Remote Connections

Excuse the simplicity of this post but we've all been tripped up by SQL Server / SQL Express not accepting remote connections or not allowing SQL Logins and have had to hunt through our scratty notes to remember what we did last time we installed it. Well here's the definitive guide to getting SQL to accept remote connections and getting it to accept the sa (or other SQL user) login.

1. Ensure TCP/IP is enabled in surface area configuration
2. Ensure "enabled" for TCP/IP in Network Configuration -> protocols in SQL Server Configuration manager.
3. Ensure sqlservr.exe exempt in firewall (if necessary)

That'll get it accepting remote connections. If you've installed with windows auth mode only though and you want to login with SQL users, you'll need to turn mixed mode on too:

1. Open Enterprise Manager and open the server properties
2. Set security to SQL Server and Windows auth mode
3. If necessary enable the sa user:
   • Open security -> logins and open properties for the sa user
   • Select the status tab from the left hand side
   • Set Login: Enabled
4. Restart SQL Server if you changed security at step 2

I know this was all very simple, but if nothing else I'll find it useful to refer back to when I try to connect and get a "connection refused" error and scratch my head for a bit.... [8-|]