Accessing SQL Server on Windows Server 2008 R2 from remote client

Question

We are currently shifting from using PostgreSQL installed on remote CentOS boxes to using MS SQL Server 2008 R2 installed on remote Windows Server 2008 R2 boxes.

Our web application that uses this database is installed and running on another CentOS box where it will remain.

All of these boxes are provided by a 3rd party hosting provider and are all on the same domain network.

Our development is therefore remote from the server/database domain as our development machines are remote. In the past opening a VPN was enough to allow our development instances of our web app running locally on our PCs to connect remotely to the PostgreSQL database running on the CentOS box.

However now we are having trouble doing the same with the Windows Server 2008 R2 box. Even with the VPN open any attempt to connect (or even ping) the remote server machine times out.

Our code to get a connection to the database instance on the new database server is correct as when we upload that code onto the CentOS application box and run it, it connects fine, as the code is now running on a box in the same physical domain as the Windows Server 2008 R2 box. However we need to make a connection from remote development machines.

Does this make sense? Are there some windows firewall settings we need to change to allow remote connections? As I said, we can't even PING the new Windows Server 2008 R2 machine from remote boxes.

Answer 1

Here is a summary of what worked for me to get remote connections working. I am not an expert in this so some of my steps may not be necessary. I took some of the steps from other answers to this question.

• Open SQL Server Configuration Manager
• Select SQL Server Network Configuration
• Chose your instance of SQL Server
• Make sure that TCP/IP protocol is enabled
• Right click TCP/IP protocol
• Select properties
• Click IP addresses tab
• Scroll down to IP4. The IP address of the server should be here. Set active to yes and enabled to yes. Set TCP port to 1433 (don't know if this is necessary. Can some expert comment)
• Scroll down to IPAll. Set TCP port to 1433
• Make an inbound firewall rule for port 1433
• open sql server management studio, right click server instance, properties->connections-> allow remote connections. Security-> SQL Server and Windows Authentication mode
• restart sql server service
• restart sql server browser

like another answer says you must also use the correct connection string in your client.

Answer 2

Do the following:

1. Open SQL Server Configuration Manager
2. Select SQL Server Network Configuration
3. Chose your instance of a SQL Server
4. Make sure that TCP/IP protocol is enabled
5. Restart server if it was off

Consider the following things:

• Authentication mode (Windows /SQL Server / mixed)
  • Windows users permissions
• Server name in the connection string
  • <machine_name>\<instance_name> (mutual physical network)
  • tcp:<ip_address>\<instance_name> (different physical networks)