February 18, 2016 at 10:00 am
Indianrock (12/23/2015)
Worth mentioning again that many of us can't create windows groups, or manage their contents, or make sure they work across domains.
True, but with the supplied scripts and the suggested enhancements in this thread (thanks!), you can know who has what access and raise concerns.
Thanks
John.
February 18, 2016 at 10:30 am
I'm slightly confused as to how they would circumvent if we are using AD only. By disabling SQL logins, it forces the SQL server to use the AD group-member information.
If the groups are setup on the SQL server so that a member of the AD group "Department1" has permissions to execute existing stored procs, but not directly query data, and a member of the AD group "IT" has permissions to directly query data, create stored procs, and execute stored procs - then wouldn't a person with ONLY "Department1" AD membership NOT be able to do an INS/UPD on ANY table because they dont have permissions, regardless of what method they use to log in? Likewise, the member of the "IT" AD group, would be able to create, execute Sprocs, but not DROP tables, because it wasn't in their permissions?
Someone who is a member of BOTH AD groups would have the highest level permissions out of the two groups they belong to correct? What does the software package accessing the DB have to do with how SQL server handles authentication, if you force it to always use Windows Auth?
Not trying to be snarky - just genuinely confused as to how they would bypass the SQL server Auth check by using a different software package.
February 18, 2016 at 10:45 am
No offence taken, as I've been struggling with choosing the best approach for awhile now. If this problem can be solved AND we could use AD accounts, then I'm all for it:
1. Do you give your end users the name(s) of your production SQL Servers and databases?
No, but there are tools you can download which will list SQL Server instances on your network. I guess what I'm trying to say is that I can't guarantee that this information is non-discoverable.
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?
I'm not sure what you're asking here. Isn't this the crux of this discussion, using service accounts vs AD accounts (with similar accesses as the service accounts)?
3. Do you have an auditing tool in place?
We do not have an auditing tool, other than data change capture on the most sensitive tables. That doesn't mean that other tables can endure data changes outside of the application context, though. We just don't want to put data change capture on everything.
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?
I guess I would say here, that we've made an attempt to protect our service account's credentials so that can't happen (see next answer).
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?
I'm thinking that it's easier to find out instance names than it is to figure out the username/password for our service accounts, at least that's our goal. When the connection string is available in the config file, it's encrypted.
--=cf
February 18, 2016 at 10:46 am
Andrew,
We don't always control INS/UPD/DEL through stored procs. So these users will have those DML rights against tables (and we don't have the staff to change everything over to sp's just in order to use AD accounts, unfortunately).
February 18, 2016 at 12:55 pm
We actually are in the process of converting all of our raw excel queries into stored procs for that reason. If we disallow all but a certain AD group (our IT group) access to directly query the database, we can give everyone access to run Sprocs, which we have control over the content of - so we can make sure nobody puts in an UPD/DEL etc...
Only our ERP application, DBAs and high level IT AD group members would have access to the production server (transactional), and then we logship to a secondary server for reporting. Then we use AD logins to segment which AD groups (departments) have access to view the RDLs that apply to them. Those RDLs connect to a RDS data source using a read-only account with access to everything on the reporting server, but no access to production.
If I am thinking correctly, under that system, no AD user would have any access to the SQL servers unless specifically in an AD group that was granted permissions to do so. And since people can be part of more than one AD group, we can easily give IT read/write access to all the RDLs, and people only in the HR department only get read access to their HR folder on the SSRS virtual folder on the report server.
As long as we keep separate AD groups specifically for SQL logins, we can have group "HR", group "SQL-HR", and "SQL-HR-Admin" for access levels. Standard HR would be for network fileshare and sharepoint access, while the SQL ones pertain specifically to the HR SSRS reports, depending on sensitivity. Admin would be for things like harassment claims etc, while regular HR could be for just employee listing or something trivial.
Let me know if I'm missing something in my security plan.
February 18, 2016 at 1:07 pm
andrew.leef (2/18/2016)
We actually are in the process of converting all of our raw excel queries into stored procs for that reason. If we disallow all but a certain AD group (our IT group) access to directly query the database, we can give everyone access to run Sprocs, which we have control over the content of - so we can make sure nobody puts in an UPD/DEL etc...Only our ERP application, DBAs and high level IT AD group members would have access to the production server (transactional), and then we logship to a secondary server for reporting. Then we use AD logins to segment which AD groups (departments) have access to view the RDLs that apply to them. Those RDLs connect to a RDS data source using a read-only account with access to everything on the reporting server, but no access to production.
If I am thinking correctly, under that system, no AD user would have any access to the SQL servers unless specifically in an AD group that was granted permissions to do so. And since people can be part of more than one AD group, we can easily give IT read/write access to all the RDLs, and people only in the HR department only get read access to their HR folder on the SSRS virtual folder on the report server.
As long as we keep separate AD groups specifically for SQL logins, we can have group "HR", group "SQL-HR", and "SQL-HR-Admin" for access levels. Standard HR would be for network fileshare and sharepoint access, while the SQL ones pertain specifically to the HR SSRS reports, depending on sensitivity. Admin would be for things like harassment claims etc, while regular HR could be for just employee listing or something trivial.
Let me know if I'm missing something in my security plan.
As long as the underlying applications are not too brittle to allow this plan to work, it is quite sound!
Thanks
John.
February 18, 2016 at 1:31 pm
Well that's nice to hear!
I don't think that anything that connects to our SQL databases (ERP, WMS, CRM) is that rigid in requiring SQL Auth - I'm pretty sure everything can use Windows Auth - so hopefully we can implement it!
February 18, 2016 at 7:35 pm
It's taken us about 5 years, but we are 99% to an Active Directory Group solution. It has made life much easier for our DBA team and to pass yearly PCI audits! Our few boxes still using sql logins are SQL 2000 (I know, sad.) and 2005 boxes, that will be completely phased out by the end of this year.
February 2, 2018 at 1:48 am
All those that want to get rid of SQL authentication what would you do in this very common scenario:
You have a web application. Access to the database is done via stored procedures except for logins which are handled via direct table access of Entity Framework using Microsoft's Identity framework for application logins.
Therefore all users that access the website use a single database connection string with a single SQL login because the application handles authentication and authorization itself. The website is hosted, it is not part of your network and the people that could login are Joe Bloggs off the street, trust providers, as well as your own staff.
Are you going to give the 3rd party web application permissions to create windows accounts on a cloud AD? I'd argue that would be a lot worse, lots more points of failure and harder to control.
One of the articles main complains is connectionstrings - in web.config connectionstring section can be encrypted. It is decrypted with no intervention when the application requests it but a person opening the file on the server would not be able to read it, nor would they be able to read it if they copied the file off the server and tried locally because the encryption is machine specific. To decrypt it you'd need to have direct access to the server or remote access with admin permissions - if someone hacks that level of access you've got bigger problems than SQL logins.
February 2, 2018 at 3:19 am
We implemented AD Groups to manage access years ago and have a leavers process that ensures that people are removed from groups on the day of the departure / transfer. All sa accounts are both disabled and renamed. Sadly the major problem that we have to put with, is 3rd party vendors claiming that their application doesn't support windows authentication. "If you want to use our application then you must use SQL Authentication."
Until ALL vendors stop using SQL Authentication as their default Auth mechanism we'll be unable to get rid of this.
Regards
February 2, 2018 at 3:50 am
Jeff_C - Friday, February 2, 2018 3:19 AMWe implemented AD Groups to manage access years ago and have a leavers process that ensures that people are removed from groups on the day of the departure / transfer. All sa accounts are both disabled and renamed. Sadly the major problem that we have to put with, is 3rd party vendors claiming that their application doesn't support windows authentication. "If you want to use our application then you must use SQL Authentication."Until ALL vendors stop using SQL Authentication as their default Auth mechanism we'll be unable to get rid of this.
Regards
See my post that appears just above your original request. Please explain how you would handle this common scenario without using SQL login. Until DBAs explain how this scenario can be solved the stance of SQL authentication must be removed is laughable.
February 2, 2018 at 8:00 am
A year or so back, I convinced a customer with a database on my servers to go the route suggested in the article. The application in question has an MS Access front-end and a lot of users, so I had a huge list of logins (all domain logins.) Once they had our AD team create the group and add the users to it, I dropped the users from SQL and have never looked back. They can get users added and removed as needed, all without having to involve me.
As for getting rid of all SQL Logins, this was attempted once before (before I started,) and because of a quirk in how our IIS instances are configured (and are required to be configured,) it didn't work. So, we still have SQL Logins, but except for developers (who are required to use their AD login to work on the application / database) no one has the password for these accounts. Not a perfect solution, but it's the one I have to work with. Frankly, I'd love to be able to go to a straight AD login on my SQL Servers.
As for the SA account?
Renamed, long, complex password, then disabled. I don't think I've ever used the SA account to get into one of my servers, for any reason (and as the only DBA right now, no one else has, either.)
February 2, 2018 at 8:44 am
peter.row - Friday, February 2, 2018 3:50 AMJeff_C - Friday, February 2, 2018 3:19 AMSee my post that appears just above your original request. Please explain how you would handle this common scenario without using SQL login. Until DBAs explain how this scenario can be solved the stance of SQL authentication must be removed is laughable.
I think there are two very different scenarios both of which have valid considerations. Where an application is being used purely for the intranet, there is no excuse for not using windows / ldap / Kerberos authentication. It was that scenario to which I was referring.
Where the application is designed to be internet facing then the argument to use sql authentication makes more sense. Having said that, we do have the OAuth2 standard which provides independent authorisation maybe this can be leveraged.
February 2, 2018 at 9:20 am
In our situation, a global company that has undergone/undergoing several mergers and divestitures, we have dozens of internal domains where not all domains have two way trusts established, mostly due to legal reasons. Since we have applications running in one domain that need access to SQL clusters sitting in another non-trusted domain, the only recourse we have is SQL logins.
February 2, 2018 at 9:26 am
A few issues:
1. If sa is disabled and you need to recover the instance for some reason, that will complicate things at a high pressure moment.
2. As many have said, people will be added to groups without you knowing about it. Usually they will not delegate permissions to the group (like they could) that let you handle this. So it depends on what access the group has as to how much risk this entails. When it's hundreds or more users, a group will be easier. However I ran into a situation where another DBA had authorized a group temporarily for SysAdmin and initially it was a small group. Well many databases have now been added to that instance and that team merged with a few others and everyone got added to this group with 'sa'! I found this when looking at another security request they had. So quite a mess. So groups have their challenges for access.
3. When someone is removed from a group their access is NOT instantly disabled. This is simply not true. They could still have access for quite some time depending on the scenario.
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply