Administer SQL 2008 R2 through the Internet

Question

Administer SQL 2008 R2 through the Internet

bendy1999

SSC Eights!

Points: 942
More actions
January 16, 2012 at 7:05 am

#251408

Hello all,
I'm currently facing the following challenge:
We have a dedicated windows Box running with a Hosting Company. From there, multiple Websites are served using IIS and SQL 2008 R2.
Multiple Persons can do Configuration tasks on the Websites that are served from that server. For that, they sometimes also need to access the Database using SSMS to modify Data contained in the Database. As you can imagine, the Admin RemoteDesktop Sessions are often blocked and people start stepping on each others feet.
I'm now looking for a way to allow those people to remotely connect to the Database Engine using their local SSMS client. I could open the respective ports on the SQL-Server and the firewall to the Internet and tell people to connect directly using the DNS-Name. From my earlier Days I remember that this is a bad idea and should not be done under any circumstances.
I now wonder if there is a different way to allow Users to "administer" the SQL-Server without blocking an Admin RDP-connection. Is there a way to accomplish this?
I'm currently playing around with SSTP-VPN to encrypt the connection, but didn't succeed in setting it up so far.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

Surely local installation of SSMS (client tools) configured to connect to your server at port 1433 (assuming this is opened and TCP/IP connections allowed remotely on server) would do the trick?

Or if running only SPs etc and doing no DBA work, a straightforward ODBC?

As far as I know neither of these methods require a RDP connection.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

bendy1999 SSC Eights! Points: 942 More actions · Answer 2

Hello Derek,

That's right and is already in place within our LAN-Environment. But in this case, the path to the remote server leads through the internet. I always thought that opening port 1433 to the internet is not recommended, although my knowledge is not the newest 🙂

That's why I'm looking for a way to secure the connection

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 3

Opening port 1433 would leave you susceptible to the Slammer virus if any of your sa passwords were short, clear or NULL. (I trust they're not!) I've not heard of any remote attacks through 1433, it's only SQL that listens out on that port and the front-end is pretty resilient to attack.

I'm not an expert in RDP unfortunately and this might be a question to be answered by someone else - but would other products such as VNC / VMRC / Dameware be more suitable, or is the bottleneck simply the number of allowed admin accounts? Is there an option to get this changed (maybe speak to your provider?)

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 4

One more thing, if you are concerned about security and protecting against data loss/theft have you thought about implementing transparent data encryption?

http://msdn.microsoft.com/en-us/library/bb934049.aspx

This would protect against wholesale data loss i.e. an .mdf goes missing, and normal precautions such as limiting user roles, login management etc. would protect against a lone ranger attack.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

bendy1999 SSC Eights! Points: 942 More actions · Answer 5

Slammer and the likes is was keeps me from exposing port 1433 directly to the internet.

RDP is by default limited to two concurrent sessions for admin purposes. VNC, TeamViewer and others connect to the console session so you have just one concurrent session available.

The other option would be to buy Terminal Server CAL's and bump up the sessions limit. What bothers me about this is that I have to spend a couple of 100 $ per CAL just to let people work directly on the server. Especially, if that cost could be avoided. Current practice is to just rent another server and set up new sites there. Not very cost effective and not fun to administer either.

My goal is to give everyone the possibility to work from their local machine and keep the admin sessions available for the admins.

TDE has also crossed my mind, but I'm afraid I won't get the twenty-odd grand to buy a license 🙁

Nakis I. SSC Enthusiast Points: 129 More actions · Answer 6

Nakis I.

SSC Enthusiast

Points: 129

June 16, 2012 at 3:57 pm

#1501986

Citrix?