How to connect to SQL Server from another computer?

by

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.

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. Insert an image in a column of a table that already has 5 columns
  3. How to avoid the “divide by zero” error in SQL?
  4. SET versus SELECT when assigning variables?
  5. Querying data by joining two tables in two database on different servers
  6. sql query to return differences between two tables
  7. Real life example, when to use OUTER / CROSS APPLY in SQL
  8. How to print VARCHAR(MAX) using Print Statement?
  9. Convert Xml to Table SQL Server
  10. Concatenate values based on ID
  11. Splitting delimited values in a SQL column into multiple rows
  12. How to find current transaction level?
  13. EXISTS vs JOIN and use of EXISTS clause
  14. Restrict an SQL Server connection to a specific IP address
  15. Custom aggregate function (concat) in SQL Server
  16. Average of multiple columns
  17. SQL . The SP or function should calculate the next date for friday
  18. Cumulative Total in MS Sql server [duplicate]
  19. How do I find the data directory for a SQL Server instance?
  20. Simulating group_concat MySQL function in Microsoft SQL Server 2005?
  21. How can I do an UPDATE statement with JOIN in SQL Server?
  22. The multi-part identifier could not be bound
  23. Fastest way to update 120 Million records
  24. What’s the best to check if item exist or not: Select Count(ID)OR Exist(…)?
  25. SQL Server 2008 – IF NOT EXISTS INSERT ELSE UPDATE
  26. Query runs slow with date expression, but fast with string literal
  27. Should I use SQL_Variant data type?
  28. Count records for every month in a year
  29. Backup SQL Schema Only?
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment