[sql] How to connect to SQL Server from another computer?

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

I'll edit my previous answer based on further info supplied. You can clearely ping the remote computer as you can use terminal services.

I've a feeling that port 1433 is being blocked by a firewall, hence your trouble. See TCP Ports Needed for Communication to SQL Server Through a Firewall by Microsoft.

Try using this application to ping your servers ip address and port 1433.

tcping your.server.ip.address 1433

And see if you get a "Port is open" response from tcping.

Ok, next to try is to check SQL Server. RDP onto the SQL Server computer. Start SSMS. Connect to the database. In object explorer (usually docked on the left) right click on the server and click properties.

alt text http://www.hicrest.net/server_prop_menu.jpg

Goto the Connections settings and make sure "Allow remote connections to this server" is ticket.

alt text http://www.hicrest.net/server_properties.jpg

all of above answers would help you but you have to add three ports in the firewall of PC on which SQL Server is installed.

1. Add new TCP Local port in Windows firewall at port no. 1434

2. Add new program for SQL Server and select sql server.exe Path: C:\ProgramFiles\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe

3. Add new program for SQL Browser and select sqlbrowser.exe Path: C:\ProgramFiles\Microsoft SQL Server\90\Shared\sqlbrowser.exe

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:

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

Here is the link ... You must read it.