December 21, 2015 at 8:44 am
vivkkrishnan20.05 (12/21/2015)
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.
Hi. That is sad. I came into a shop where security was horrible (not my present shop). The sa password was known by everyone in the IT department. It was the same for all servers.
The day I changed them all to long passwords from grc.com/pass there was anger and shouting and accusations that people could not get jobs done. Oddly enough everyone got their jobs done with the logins they were given. 😀
Thanks
John.
December 21, 2015 at 8:48 am
cpagani@coop.co.za (12/21/2015)
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?
Hi.
If you have your groups set up improperly, and you have lax security set up, and the user is savvy enough to know how to use that SQL query tool (e.g., Management Studio) and to know SQL enough to examine what tables are out there and how to get to them, yes.
However this approach would also allow any auditing tool to capture that unauthorized access and report it to management.
Again, the AD groups reveal, not conceal, the people accessing the database.
Thanks
John.
December 21, 2015 at 8:48 am
We disable SA on all DB servers but still generate a random 32 character SA password that is recorded nowhere just to be safe. Except of course for the Great Plains DB server because it's crap and requires SA way to go MS!
December 21, 2015 at 4:34 pm
-
December 21, 2015 at 4:38 pm
SQLBlimp (12/21/2015)
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.
Off course it is rudimentary to have some form of trust to system engineers, but it is entirely different to hand-over access control to them. It would be out of place. If you cannot control access to the database than you cannot be responsible for that part either. A possible work around to this is using local groups instead of AD groups.
We have a similar process here whereby users are added to AD groups automatically, but I am still a vital part of the final approval process. If that would not have been the case than I would not be able to be responsible for it. I can't even see whom is part of that group. Therefor I have a report that shows all users that have accessed the database over a certain period of time. This report is checked with the AD groups-access, by an audit department.
I do agree that SQL logins are horrible for security, there is no real secure way of dealing with it. But then AD suffers from similar flaws. The main issue is that sql logins are used often by applications that apparently are installed locally or passwords are not stored encrypted. I do feel that there are still lots of work arounds to that like using 'execute as' priviledges and loginless users. So, if feasible limiting the possibilities a login has as much as possible.
December 21, 2015 at 5:28 pm
ephilipse (12/21/2015)
Off course it is rudimentary to have some form of trust to system engineers, but it is entirely different to hand-over access control to them. It would be out of place. If you cannot control access to the database than you cannot be responsible for that part either. A possible work around to this is using local groups instead of AD groups.
We have a similar process here whereby users are added to AD groups automatically, but I am still a vital part of the final approval process. If that would not have been the case than I would not be able to be responsible for it. I can't even see whom is part of that group. Therefor I have a report that shows all users that have accessed the database over a certain period of time. This report is checked with the AD groups-access, by an audit department.
I do agree that SQL logins are horrible for security, there is no real secure way of dealing with it. But then AD suffers from similar flaws. The main issue is that sql logins are used often by applications that apparently are installed locally or passwords are not stored encrypted. I do feel that there are still lots of work arounds to that like using 'execute as' priviledges and loginless users. So, if feasible limiting the possibilities a login has as much as possible.
I understand your position, and I can see the usefulness of DBA controlling who has access to what databases. That said, a good audit tool is vital for any enterprise that has any SOX, HIPAA or PCI exposure.
I also am a believer that DBA -- even though it is by far the most critical position in IT -- is not the best party to determine who accesses what, at least at an individual level. The true owners of the data are the business owners and not the DBA. The business owners should make determinations about who accesses what, and DBA should determine how to safely grant that access, and also when push-back is appropriate (e.g., the payroll analyst does not need to have dbo access). In a co-operative environment, this should arise.
That also said, every DBA must have read rights to all of the groups attached to the databases, no matter how deep they go. One can also design queries to give a listing of group membership through SQL Server. I don't even think that you need to have read rights to the groups, I think that SQL Server somehow has that already.
That might be a good follow-up article - query group logins to see who has what access through SQL Server. Let me see if I can put together some queries that yield the result. Your point is very well taken.
Thanks
John.
December 22, 2015 at 4:46 am
How do you get assign a default schema to an AD group then?
In 08R2, if a user logs in, it will create a schema, a user and assign schema to that user.
December 22, 2015 at 5:33 pm
Let me see if I have this straight...
It is safer to have each individual authenticate via AD (totally agree) and then be authorized via database access groups?
So instead of an application controlling authorization internally (e.g. admin vs. power user vs. data entry vs. global read-only vs. limited read-only) and using a single SQL logon with a secure, changeable, encrypted password, I have AD groups that grant the needed access at a fine-grained level to emulate the application authorization? Or do I just have a single AD group that all users belong to with enough capabilities for every role and hope the users don't access the database with a different tool?
Not sure this works well for all cases, although I heartily agree that AD is much simpler for managing authentication, just not convinced for authorization in all situations.
December 23, 2015 at 2:04 am
Great article.
Using Windows Auth ONLY is something I have been pushing for in my environment and I have succeeded with two new applications this year. A new version of one of our apps is also coming on with Windows Auth ONLY.
I just think it is a matter of company policy. Definitely the management has to e done across teams and groups have to be planned properly. This is something that once a company has decided to do can be done gradually as applications are retired and upgraded.
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
December 23, 2015 at 7:03 am
Ronzo,
Yes and no. You're right that the application should control the fine grained access. That doesn't require a sql login. One pattern I use is to set up a group for RWE (read write execute), then create a service account for each web server/app server and add to that group. That group is a login/user. After that, its all on the app team to gate access accordingly. Auditing there is harder, because it's usually all done by the service account, but auditors get that, and expect the app to do additional logging, either internally or by passing identity info to each proc call.
There are plenty of times when I need to grant users direct access to data, at various levels. For those I create groups to add as logins/users, then add (or have added) the users that need that particular access. I typically want firewall rules in place so that it takes a combination of me granting access AND them having the right IP/subnet to get to the data.
On top of all of that I always grant permissions to a role, never to a user/group. Often that means I have one role with one group/user and I'm ok with that. It allows me to easily understand what I've granted, lets me easily add a "temp" group or user for troubleshooting, and makes it very clear to the auditor what we're doing - the AD group names aren't always useful.
To me the only valid use cases for sql logins is when you're forced into it by an app (and that's a warning sign right there) and when you don't have a domain (and don't want to use local accounts). It shouldn't be used for any new app.
December 23, 2015 at 7:28 am
Worth mentioning again that many of us can't create windows groups, or manage their contents, or make sure they work across domains.
January 21, 2016 at 5:32 am
sean 93151 (12/21/2015)
We disable SA on all DB servers but still generate a random 32 character SA password that is recorded nowhere just to be safe. Except of course for the Great Plains DB server because it's crap and requires SA way to go MS!
That's the kicker isn't it? You recommend a way to further improve company security requesting not SQL Auth and renaming sa on all prod boxes and the IT Manager says "No can do, that will stop the application(s) from working." :crazy: (Two examples of this: Aderant & Mattersphere).
qh
February 18, 2016 at 7:18 am
We are looking at implementing this at my job. We do have full control over the AD groupings because we are a small enough company.
What I'm worried about is SSRS reports that users are subscribed to. In order to do that, you have to store credentials in the RDL, so the bypasses the groupings, because that stored user (execution account?) would need access to all the stored procs run by the reports.
Now, we can group the reports into folders by department, and limit access to even seeing the reports - would that be enough? Can you have the Report Server permissions automatically based on AD permissions?
February 18, 2016 at 9:43 am
Using this methodology, I'm still not clear on how you're supposed to limit a user's access to the underlying data, where they can just use an alternative, ODBC-compliant tool, (like MSAccess) to view and change their data. That's been our concern with using AD accounts, that user's can run around our applications and get to their data with INS/UPD/DEL access.
--=Chuck
February 18, 2016 at 9:59 am
chuck.forbes (2/18/2016)
Using this methodology, I'm still not clear on how you're supposed to limit a user's access to the underlying data, where they can just use an alternative, ODBC-compliant tool, (like MSAccess) to view and change their data. That's been our concern with using AD accounts, that user's can run around our applications and get to their data with INS/UPD/DEL access.--=Chuck
Hi -
1. Do you give your end users the name(s) of your production SQL Servers and databases?
2. Do you have the ability to grant the application's service account the RW(X) access and limit the individual users to read-only access?
3. Do you have an auditing tool in place?
4. If the user has a SQL login that has RW(X) access, how is that different except that any changes made through a SQL login cannot be reliably traced to the end users?
5. If the user is sophisticated enough to locate the server name and database name and to use Access to build SQL to change things, wouldn't he or she be sophisticated enough to hunt down a connection string in your app's config file and thereby gain access to the SQL credentials?
Not meaning to challenge you; just trying to get a fuller picture of the situation to see if I can offer meaningful help.
Thanks
John.
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply