How to connect to SQL Server from another computer?

Luisa picture Luisa · Mar 21, 2010 · Viewed 210.2k times · Source

I want to connect from home using SQL Server 2005 to another PC.

I had a look on the msd...but before connecting it says I should connect to another computer using the computer management and it didn't work out....I can only connect to computers from my workgroup?

Thanks, Luisa

Answer

dyslexicanaboko picture dyslexicanaboko · Jul 20, 2011

Disclamer

This is just some additional information that might help anyone. I want to make it abundantly clear that what I am describing here is possibly:

  • A. not 100% correct and
  • B. not safe in terms of network security.

I am not a DBA, but every time I find myself setting up a SQL Server (Express or Full) for testing or what not I run into the connectivity issue. The solution I am describing is more for the person who is just trying to get their job done - consult someone who is knowledgeable in this field when setting up a production server.

For SQL Server 2008 R2 this is what I end up doing:

  1. Make sure everything is squared away like in this tutorial which is the same tutorial posted above as a solution by "Dani" as the selected answer to this question.
  2. Check and/or set, your firewall settings for the computer that is hosting the SQL Server. If you are using a Windows Server 2008 R2 then use the Server Manager, go to Configuration and then look at "Windows Firewall with Advanced Security". If you are using Windows 7 then go to Control Panel and search for "Firewall" click on "Allow a program through Windows Firewall".
    • Create an inbound rule for port TCP 1433 - allow the connection
    • Create an outbound rule for port TCP 1433 - allow the connection
  3. When you are finished with the firewall settings you are going to want to check one more thing. Open up the "SQL Server Configuration Manager" locate: SQL Server Network Configuration - Protocols for SQLEXPRESS (or equivalent) - TCP/IP
    • Double click on TCP/IP
    • Click on the IP Addresses tab
    • Under IP1 set the TCP Port to 1433 if it hasn't been already
    • Under IP All set the TCP Port to 1433 if it hasn't been already
  4. Restart SQL Server and SQL Browser (do both just to be on the safe side)

Usually after I do what I mentioned above I don't have a problem anymore. Here is a screenshot of what to look for - for that last step:

Port 1433 is the default port used by SQL Server but for some reason doesn't show up in the configuration by default.

Again, if someone with more information about this topic sees a red flag please correct me.