Internet access to your SQL Server?

· by · in ,

Occasionally I see questions on dba.stackexchange.com where it appears the SQL Server is exposed directly to the Internet, allowing remote connections. This is very bad. In this post, I provide some reasons why.

First, it might be helpful to understand why someone might want to connect to their SQL Server via the Internet. Consider any of the following likely scenarios:

  • There is a client application or website hosted in a different data-center that needs access to the data stored in the SQL Server.
  • You need to provide geographical redundancy between several SQL Servers located in different countries that are only connected via the Internet.
  • You’re doing development on a hosted SQL Server and don’t understand the implications of having SQL Server exposed.

The typical way you might expose your SQ Server to the Internet when the server is behind a router or firewall is to open an incoming TCP port at the router or firewall directed at the SQL Server on port 1433. If you do this, you will very quickly start seeing incoming connection attempts from computers on the Internet. These attempts will try to login, typically via the “sa” account, continuously trying passwords.

You can mitigate continuous password attempts by having SQL Server automatically lock-out accounts that have experienced more than “x” number of failed logins. However, in the case of an Internet-connected SQL Server, this essentially means the “sa” account will be continually locked out, making it useless. At that point, you might be tempted to disable the “lock after x failed logins” policy so that you can login.

Depending on the available bandwidth, your SQL Server may be inundated with attempted logins and other network traffic from the Internet, causing denial-of-service or poor performance.

If you need to connect to your SQL Server over the Internet, use one of the following solutions:

  • Setup a VPN connection between the SQL Server and the clients that need to access it via the Internet. This is the safest and preferred method.
  • If you have static IP addressing for the client computers, configure the router or firewall to only accept incoming connections to the SQL Server port from those specific IP addresses.