Accessing SQL Server on Windows Server 2008 R2 from remote client

sql-server windows-server-2008-r2
Nick Foote picture Nick Foote · Apr 19, 2011 · Viewed 32.4k times · Source

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

AJ Dhaliwal picture AJ Dhaliwal · Aug 28, 2012

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.

Related questions

SQL Server login works in a connection string, but fails in DSN

I have a web site and I'm trying to setup a DSN instead of using a connection string because it seems more secure. The following connection string works fine in my site: "Server=servername; database=mydb; user id= web.account; …

Read More
How to create a SQL Server login for a service account on a non-English Windows?

Given a Windows Server 2008 R2 system, where the service accounts use a non-English display language, with SQL Server 2008, the following Transact-SQL statement fails: CREATE LOGIN [NT AUTHORITY\NETWORK SERVICE] FROM WINDOWS ... with the following error: Windows NT user or group …

Read More
Error 17886 - The server will drop the connection

We are running a website on a vps server with sql server 2008 x64 r2. We are being bombarded with 17886 errors - namely: The server will drop the connection, because the client driver has sent multiple requests while the session is …

Read More