Restrict SQL access

Last Post 03 Jan 2012 08:54 AM by russellb. 15 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Chris Metzger
New Member
New Member

--
22 Dec 2011 06:31 AM
OK - so I'm attempting to restrict access to SQL Server on a per instance basis based on source hostname.  The environment:

VMware vCloud
Windows Server 2008 R2 Enterprise 64bit
SQL Server 2008 R2 Enterprise 64bit
Multiple layers of NAT'ing (which means I can't use source IP most likely although I'll try it if possible)

So the routing and firewalling in the vCloud UI leaves a lot to be desired and I am unable to control access that way.  Each SQL Server has multiple instances (2 Primaries and 1 Mirror that serves both with each Primary serving as Witness for the other).  I have multiple App/IIS Servers that each connect to their own assigned SQL instance.  The goal is to restrict access per instance so that only its assigned App Server can connect to it (thereby eliminating the possibility of cross connection and someone doing something stupid by making changes in the wrong instance/database(s)).

Someone on another forum suggested firewalling/routing however that is not possible on a virtual platform where those pieces don't function the way they are supposed to so I have to find another option.  I could use some sort of blacklist or whitelist in SQL but that appears to not be ideal and requires a lot of T-SQL so it would be difficult for me to do (I'm not a DBA - I'm an Engineer who is a DBA by accident although I will be taking the training and certification next year).  Someone else suggested IPSec however that presents its own issues when you try and use Windows Firewall on SQL Servers (and is really unnecessary plus WF just gets in the way half of the time so I avoid it like Bubonic plague).

Is there someone out there who has done this previously or someone who may have a workable option?  If it's possible I want to do this to prevent mistakes by Support personnel but if not then I'll have to make sure they all understand that if they screw up it's on their head and I'll be screaming it from the mountain top that they are an idiot (which may be a deterrent unto itself).  Any help is VERY much appreciated!
russellb
New Member
New Member

--
22 Dec 2011 11:14 AM
First of all, if using sql authentication, the passwords should be different on each instance. That solves the problem right there. I'd use the same usernames though, in case you need to failover from one instance to another. If that will never happen, then usernames should be different too.

If you're using Windows Authentication, just don't allow certain accounts access -- unless you're already using all the same delegated accounts. In which case, have a look at logon triggers. There is some overhead associated with these, but they're easy to create. Just be sure not to lock yourself out with a logon trigger. Even if you do, you could login via DAC and remove/modify the trigger.
Chris Metzger
New Member
New Member

--
22 Dec 2011 11:36 AM
Yeah can't do that. Our software uses SQL accounts to connect to the database (as the intermediary for users basically - so users never touch the databases directly). So the login is the same across all instances (so is the SA pw because I can't manage 50+ different SA passwords). So with that said login triggers aren't going to solve the problem. Right now only Tech folks and Domain Admins have access to the "physical" SQL Servers - everyone else uses SSMS from each App Server to connect to the databases. So what I'm attempting to do is more for prevention of stupidity vs actual security necessity.
russellb
New Member
New Member

--
22 Dec 2011 01:00 PM
Why won't login triggers work?

Anyway, what you SHOULD do, in my opinion, is manage the 50+ passwords.
russellb
New Member
New Member

--
22 Dec 2011 01:01 PM
Also, if users are accessing the SQL Servers via SSMS then they should be using Windows Authentication. Leave SQL Authentication for applications. And that, only where necessary.
Chris Metzger
New Member
New Member

--
22 Dec 2011 01:15 PM
First, login triggers won't work because all of the instances are silo'd to one or two SQL Servers but provide services to 57 App Servers. Well all of the same people still need access to all of the instances - so why bother with login triggers since it's not going to do what I want which is allow them to only connect to one instance of SQL per App Server? I just want them to have to move to the respective/correct App Server to get to the associated SQL instance/databases.

Second, if I wasn't busy as the platform's architect, engineer, and administrator I would make every sa password different - however SQL Server isn't my only concern. Not to mention then I have to give that SA password out anyways so I have, yet again, defeated the purpose of setting each one different. And yes they are using SQL auth because using windows auth means I then have to closely manage SQL security and for every one of 57 instances (and more as the system continues to grow with new clients) I have to be sure to add their security groups and allow them SA-level access anyway (which then still defeats the purpose). But that still doesn't answer my original question which was how do I restrict access so that only ONE app server can access any ONE specific SQL instance (ie. forcing a one to one relationship so even if they try to access a SQL instance through SSMS that doesn't belong to the App Server they are on they will not be allowed and will be forced to move to the associated App Server - hence then my AD auditing will capture the login and record it so if someone screws up a database or an app server I'll know exactly who it was and when). I wasn't asking a security question and I have no interest in hearing about how to manage my security - what I need to know is how to control access exclusive of security because the access is to be controlled by originating source (ie. even if I could trap traffic at the firewall for port 1433 and then forward it I would need layer 7 routing in there to read the source hostname and even then I would only be able to forward the packet on to the SQL Server and not a specific instance).

Maybe this was the wrong place to ask a question like this.
Chris Metzger
New Member
New Member

--
22 Dec 2011 01:44 PM
I must stand corrected. Apparently there is a way to setup a logon trigger to restrict access by hostname (I finally found an article on it although the info is for blocking by app name and hostname not exactly restricting access to only one hostname source). So I need a DBA to explain how this works as I am obviously not a DBA so defer to someone who knows better.
russellb
New Member
New Member

--
22 Dec 2011 08:39 PM

Just check the hostname and then disallow the login. Do you need a code example? I'd be happy to provide it.

If all of your apps and users are logging in as sa, you have a giant problem. I guarentee they don't need it. You need to do a security audit as soon as possible.

I hear ya that you don't think you have time. Maybe time to bring in a short-term consultant to straighten some of these things out for you. At least to make recommendations.

57 instances is enough to require a full-time DBA. I can only begin to guess at all the other issues that are going unaddressed at this point.

EDIT: Oops, two SQL instances, 57 app servers.  My bad.


EDIT AGAIN: Wait, this means you only need to manage TWO sets of passwords...

Chris Metzger
New Member
New Member

--
22 Dec 2011 09:51 PM
Nope - 2 SQL Servers + 1 Mirror = 114 SQL named instances (57 App Servers each with an assigned named instance with each instance having a mirror instance) so no I do not have time to manage all of those passwords. I would have to spend all of my time managing security for SQL if I set different SA pw's and also required they use Windows auth with group security assigned SA-level rights. It's too much and they aren't going to hire a DBA - it's been a stretch to get them to pay for the training for me (CISSP, SQL, and such).

With that said yes I would WELCOME some example code that would help me plug in some info and test it with the two systems currently in test mode on the platform - then I could implement it as we go-live. Anything helpful is appreciated. Thanks.
russellb
New Member
New Member

--
23 Dec 2011 10:30 AM
CREATE TRIGGER denyLoginFromHost
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF host_name() <> 'Allowed Host Here' 
  BEGIN
ROLLBACK;
END
END;
russellb
New Member
New Member

--
23 Dec 2011 10:34 AM
Be Careful. One mistake and it's easy to lock yourself out of the server.
russellb
New Member
New Member

--
23 Dec 2011 10:57 AM
Why does each app server connect to its own instance?
Chris Metzger
New Member
New Member

--
26 Dec 2011 04:20 AM
Each App Server is isolated for HIPAA reasons - we could install the app to the same server for multiple instances (and multiple websites) but then that becomes a non-standard install. Then, database naming convention is the same across the app - so if you have 50something instances of the app you will have 50something instances of the same database name by default (which, again, could be changed but then it becomes a non-standard install and a PITA to update later). So the easiest method, and most secure, is to ensure each instance is isolated from the others - that way external access is specifically per-instance and data cross-contamination is no longer possible. The final issue there is that if you don't control what SQL instance can be reached from what App Server then Support personnel could make a mistake and change data in the wrong database when assisting a customer with an issue (something that is not unheard of).

So would this work (allowing access from App Server 123456AS01 and the SQL Server where the instance is housed)?:

CREATE TRIGGER denyLoginFromHost
FOR LOGON
AS
BEGIN
IF host_name()<>'123456AS01, SQL01'
BEGIN
ROLLBACK;
END
END;

And where should this be executed? Against the Master for each instance or against each database within the instance? I assume Master but you know what happens when you assume...

THANKS for the help!
russellb
New Member
New Member

--
26 Dec 2011 09:20 AM
Master.

CREATE TRIGGER denyLoginFromHost
FOR LOGON
AS
BEGIN
IF host_name() not in ('123456AS01', 'SQL01')
BEGIN
ROLLBACK;
END
END;
Chris Metzger
New Member
New Member

--
02 Jan 2012 06:46 AM
I will test this week. Thanks!!!
russellb
New Member
New Member

--
03 Jan 2012 08:54 AM
You're Welcome. Glad to help.
You are not authorized to post a reply.

Acceptable Use Policy