How to connect to SQL Server from another computer?

sql sql-server-2005 sql-server-2008 sql-server-ce
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.

Related questions

How to avoid the "divide by zero" error in SQL?

I have this error message: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. What is the best way to write SQL code so that I will never see this error message again? I could do either of the following: …

Read More
sql query to return differences between two tables

I am trying to compare two tables, SQL Server, to verify some data. I want to return all the rows from both tables where data is either in one or the other. In essence, I want to show all the …

Read More
Simple DateTime sql query

How do I query DateTime database field within a certain range? I am using SQL SERVER 2005 Error code below SELECT * FROM TABLENAME WHERE DateTime >= 12/04/2011 12:00:00 AM AND DateTime <= 25/05/2011 3:53:04 AM Note that I need to get rows within a certain …

Read More