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.

Bulahbulawayo

Answer 1

Combined with tips from the other answers and help from our hosting provider we managed to sort this out. It was mostly an issue of those ports being blocked by our provider.

Bulahbulawayo

Answer 2

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.

Messiah

I tried a lot of solutions, this one "Scroll down to IPAll. Set TCP port to 1433" fixed my problem – Amado

Answer 3

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)

Stradivari

Hi, thanks. Checked TCP/IP is enabled for all the instances, it is. What do you mean about the "tcp:<ip_address>"? Currently the connection string is as follows in next comment ... as I've said this works fine from in the same physical network but not from different remote physical networks. Where do I put this tcp: ? – Bulahbulawayo

jdbc:sqlserver://<ip_address>:<fixed_port>;instanceName=<instance_name>;databaseName=<database_name>; – Bulahbulawayo

Additionally, when I open a windows remote desktop to the Windows Server 2008 R2 machine and start SQL Server Management Studio on that, to connect to the database it uses Windows authentication, is this going to be an issue? – Bulahbulawayo

I'm sorry @Nick, I didn't notice we can't even PING the new Windows Server 2008 R2 machine. I thought that is a problem with SQL Server only. In this case you should check network devices and configuration, but I'm not an administartor and can't help :) – Stradivari

Answer 4

Make sure you unblocked 1433 port which is used for SQL Server. If your instance is named, you need to set up port for this instance in configuration manager and then unblock this port in firewall.

Butterfingers

Hi, thanks for the advice. I checked Windows Firewall. It was turned OFF so I turned it ON. This disabled the connectivity from our CentOS App box so I followed your tips, added rules allowing various SQL Server ports and applying a fixed port to our named instance of SQL Server. Doing this re-enabled connectivity from our CentOS app box, so you tips did help once Windows Firewall was turned on. However we still cannot connect from our remote machines, with or without the VPN open. All SQL Server port firewall rules are set to Domain+Private+Public. – Bulahbulawayo

If you can not connect then it is a network problem. Check your router configuration and maybe if you can check traffic by running netstat from command line. – Butterfingers

Answer 5

I understand that this is an old post but one other thing to check is to make sure that you have enabled a particular IP Address and Port on the IP Address Tab of SQL Server Network Configuration, this is in addition to the other posts. See this LINK for further information

Converter

Answer 6

If there is still a problem after Ip configuration and other things.

please check datetime of your client pc.

it should be same as server

[i have solved recently with this technique]

there is a problem in sql server if client machine is not synced with server time client do not connect to server.

Aleece