December 20, 2015 at 9:57 pm
Comments posted to this topic are about the item Liberating the DBA from SQL Authentication with AD Groups
December 21, 2015 at 3:37 am
Well, this is something that I have mentioned several times that sharing a 'sa' login is absolutely dangerous, to just not be taken seriously. So, I have left trying.
December 21, 2015 at 3:48 am
At itself an ok article.
However, in many situations the AD is beyond the control of the DBA, since it is part of system engineering, this would also hand-over authorisation and authentication responsibilities to systems engineering, you may not want to do that. It is not a matter of trust, but being responsible for the authentication and authorisation parts, should in this case not be defined by system engineering, if you no-access to AD then you can't be responsible for authorisations. And this opens a whole new world of possible issues of for example disgruntled ex-systems engineers.
But if you do have full access to the servers controlling the Database, perhaps one could use local groups instead. However, in such cases it can occur that trusted authorisation does not work in SSMS. Requiring that a user is added with a windows login in the security pane of ssms, instead of the group.
So despite efforts, we are frequently condemned to authorisation directly into SQL server security, rather than a group be it a local or AD group.
December 21, 2015 at 5:19 am
Also, disable sa.
December 21, 2015 at 5:21 am
Do we have statistical (rather than anecdotal) evidence that this is safer?
Is there open security research on the topic?
Do you know where to look for it?
412-977-3526 call/text
December 21, 2015 at 5:56 am
I don't agree with this logic. All you need is a clever user who can download some SQL query tool and he/she can go and browse away?
December 21, 2015 at 6:20 am
In my experience, moving people in and out of AD groups is often done without DBA knowledge. It isn't difficult for the DBA to see who is in a group, or nested groups, but systems doesn't just hand over the tools for this.
When roles change and I ask systems to move someone in or out of a group, it may not happen for months. Or they add somebody to a group with read-write access without my knowledge. Exclusive use of AD groups is nice in a perfect world.
Since access to production is limited, I prefer to create individual domain logins to sql as needed, with custom permissions. For QA/DEV I typically do use AD groups and sometimes, due to testing needs, make specific individuals sysadmins.
December 21, 2015 at 6:37 am
t
December 21, 2015 at 6:43 am
duplicate post, no way to delete apparently.
December 21, 2015 at 7:34 am
John, lots of good stuff, but maybe a bit of a mixed message too based on the title. I'm all for removing sql authentication, but in practice it has rarely been a realistic option. It's not on because I'm lazy (or the DBA before me), but because something is using a sql login and I can't get the business (or the 3rd party vendor) to invest the time to change it. Is it a lot of time? It shouldn't be, but it its more than just changing the connection string. It requires testing, may impact reports and packages, etc, etc. It's a tough sell to fix something that is working vs working on something that is perceived as adding value.
I like standardizing groups and delegating group membership. I've commonly used patterns for "-r, -rw, and -dbo'. Getting the right pattern takes some understanding of the environment. For example, I have an instance with several hundred db's that are the same schema and all used by one app, we use the same group in each - its a db level group, sorta! In other places it would be a different group per db to really partition access. Having a script to generate whatever pattern of names is smart, good stuff!
Adding to that, I typically request that only app/report servers and jump boxes have direct access to SQL (firewall rules). That way, AD credentials or not, it limits the ability of users to run queries from hell. In practice some users will get that access, even on production servers, but it doesn't have to be open for everyone to try.
From a compliance perspective removing (or even reducing) sql authentication is a win, as is having AD groups that clearly indicate the business use and access, the latter being important when it's time to recertify everyones access (quarterly or yearly).
I saw an earlier post about the validity of AD groups being "more secure". It's a fair question. I think AD is more secure when managed service accounts are used - no one has the password. After that, from an administrative perspective I see AD as being better and I think that is the selling point to use, though to be fair everyone auditor has been trained to think "sql logins" bad and so removing them does make life easier.
Thanks for the article.
December 21, 2015 at 7:41 am
I happen to use AD groups for security for all database users. However, I cannot eliminate SQL Logins because we have too many vendor applications that require the use of SQL authentication. And while I like your idea of creating RO, RW and RWX AD groups for each database, that can quickly become unwieldy. I have over 1000 databases across all of my systems and the idea of managing 3000+ AD groups does not excite me. Instead, I created SQL groups based on types of users (Developers, Business users, etc) and/or application groups. It is not necessarily as flexible has having RO/RW/RWX for each database, it does reduce the maintenance headache.
Gordon Pollokoff
Wile E. is my reality, Bugs Bunny is my goal - Chuck Jones
Walking on water and developing software from a specification are easy if both are frozen. - E. Berard
Doing more things faster is no substitute for doing the right things. - S. R. Covey
Any sufficiently advanced bug is indistinguishable from a feature.- R. Kulawiec
December 21, 2015 at 8:24 am
The last I knew you couldn't Audit individual actions in SQL if you used AD groups. We have resorted to a PowerShell script which reads the membership of the groups and assigns individual AD accounts with the access required or remove old accounts. If SQL can now audit at the user level even if they are authorized by group that would be great!
December 21, 2015 at 8:31 am
sean 93151 (12/21/2015)
The last I knew you couldn't Audit individual actions in SQL if you used AD groups. We have resorted to a PowerShell script which reads the membership of the groups and assigns individual AD accounts with the access required or remove old accounts. If SQL can now audit at the user level even if they are authorized by group that would be great!
Hi. I tell you from firsthand experience that AD users with permissions coming through a group will certainly show up as themselves and not as the group through which they gained access. I've not seen a group showing as a user at any time, SQL 2000 through 2014.
Thanks
John.
December 21, 2015 at 8:36 am
Totally confusing to me. Glad I'm not the one dealing with it.
December 21, 2015 at 8:41 am
ephilipse (12/21/2015)
At itself an ok article.However, in many situations the AD is beyond the control of the DBA, since it is part of system engineering, this would also hand-over authorisation and authentication responsibilities to systems engineering, you may not want to do that. It is not a matter of trust, but being responsible for the authentication and authorisation parts, should in this case not be defined by system engineering, if you no-access to AD then you can't be responsible for authorisations. And this opens a whole new world of possible issues of for example disgruntled ex-systems engineers.
But if you do have full access to the servers controlling the Database, perhaps one could use local groups instead. However, in such cases it can occur that trusted authorisation does not work in SSMS. Requiring that a user is added with a windows login in the security pane of ssms, instead of the group.
So despite efforts, we are frequently condemned to authorisation directly into SQL server security, rather than a group be it a local or AD group.
Hi. It is true that most DBA are not Windows admins. I'm not a domain admin or an account operator in my shop. Nor do I want that. However, unless you think that your sysadmins are bad guys, doesn't there need to be a rudimentary level of trust there?
Moreover, one can tailor the approach to needs. If an application has tight security, then you can tie the roles more closely to the access.
There is a reason that auditors hate SQL logins. Once the password to a login is compromised, then anyone can sign on with that SQL login and see or even change data and do so in a virtually untraceable manner. The AD group eliminates the untraceable part of the equation unless someone compromises an AD account. And even then, if you have the most basic auditing, you will have a workstation name from where the offending login is used, which is more than a SQL login provides.
Thanks
John.
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply