2

I wish to allow remote access to an SQL Server database from a Microsoft Access database to allow my client to build reports as and when they wish.

I can do this without any issue by opening the relevant port in the Windows Firewall on the server. This however seems a very insecure way of allowing access and my server is alarmingly being targeted by someone (or more than one person) trying to guess the ‘sa’ password.

While this password has been set very strongly according to published guidelines, I would like to prevent this from happening.

What are the best practices surrounding allowing access to the server in this way?

Many thanks, Anthony

Ant Swift
  • 133
  • 6

3 Answers3

4

You should not open a service directly to the public internet like this if you can possibly help it.

I suggest using some form of VPN which would provide proper security (fully encrypted traffic and so on), will only allow those with a key for the VPN to even see that the SQL server exists, and may even speed things up by adding compression to the mix. It doesn't need to cost anything either: OpenVPN is free (and OSS), stable and reliable - we use it all the time for similar things.

If you don't want a full VPN then install a SSH server on your machine and let your client connect through that by using its tunnelling feature to forward connections to port 1433. There are a number of ports of the full OpenSSH set for Windows, so again this solution is F+OSS.

If your client really can't cope with the extra hassle of a VPN or SSH client, then there isn't much you can do. If they have a fixed IP address you can remove login attempts from other sources by limiting the firewall' "accept port 1433 connections" rule to that address. If they have a dynamic address but from a fixed range (i.e. they always connect from the same ISP) then you can at least limit the rule's scope to that range of addresses.

If your client is reluctant to use a VPN or SSH tunnelling, you can "sell" them the solution by extolling the virtues of decently encrypted traffic (that direct connection will be sending data in plain text), compressed traffic (this could be significant if they run reports that output many rows), and in the case of something like OpenVPN a more reliable connection (OpenVPN is more resilient to a drop-and-reconnect or other network blip than a direct connection will be). Having SQL server in any for directly addressable by the public network is generally considered a bad idea.

David Spillett
  • 22,534
  • 42
  • 66
  • Could you please elaborate on SSH tunnels in [my question here](https://serverfault.com/questions/950356/secure-remote-access-to-sql-server-in-a-multi-tenant-environment-using-tls-tunne)? – Louis Somers Jan 23 '19 at 11:37
0

Don't use SA or Rename the SA account.

DaniSQL
  • 1,097
  • 7
  • 12
  • Very fair comment, although sa isn't actually used for by anything/one to access the server, more for any recovery that must take place. I will rename it but it that wouldnt stop login requests using that username and my log files will still grow. – Ant Swift Jul 13 '10 at 21:49
0

It seems like SQL Reporting Services would be perfect here. I could be totally off base, but it seems like it'd be handy to expose reporting to your client (on a separate server, if possible) without putting your DB server in the line of fire.

Kara Marfia
  • 7,892
  • 5
  • 32
  • 56
  • Reporting services is currently in use however the client would like to author their own reports. Allowing them access to the Report Builder involves relaxing yet more permissions which I’m reluctant to do. – Ant Swift Jul 14 '10 at 10:03
  • Sounds like a reasonable reluctance! – Kara Marfia Jul 14 '10 at 12:06
  • Consider a port other than 1433 for SQL Server. That, like the sa login that is found with Mixed Mode security is well known to those wanting to hack. Be sure that the port has not been designated for use by another protocol. – jl. Jul 14 '10 at 13:07