February 2, 2018 at 10:56 am
ephilipse - Monday, December 21, 2015 3:48 AMAt 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.
Actually, the fact that AD is (typically) outside the control of the DBA is one reason why I love domain group authentication.
For example, in a large organization with 1,000 employees, the DBA doesn't probably doesn't know (perhaps doesn't even care to know) that Joe Bloke is the new guy in accounting. The DBA probably doesn't want to be pestered with a change request every time an employee is added or leaves either, and there is nothing wrong with that attitude, because there are plenty of other things a DBA should be doing besides deploying scripts on behalf of HR.
Instead, with AD group authentication and authorization, the decision about what staff should be members of [MYCORP\ACCTPayroll] can be left up to executive management and HR (with the implementation details handled by the AD network administrator). Really, it makes sense for the AD administrator to own and maintain the single version of truth regarding what employee should be assigned to which departmental roles. That's his job.
The DBA still has total control of SQL Server authorization at the group level, for example granting database access and permissions to the [MYCORP\ACCTPayroll] group. If needed, the DBA can always use the following to list what AD accounts are members of an AD group.
EXEC xp_logininfo 'MYCORP\ACCTPayroll','members';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 2, 2018 at 11:11 am
We typically implement mixed authentication, having an 'SA' account that is never used except in an unusual situation, like when there is an issue with the domain controller or the database has to be recovered. However, we have a process that creates and periodically rotates a random password.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 2, 2018 at 4:54 pm
peter.row - Friday, February 2, 2018 1:48 AMAll 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.
This technique is for controlling access to a database, not so much controlling what is done with that access. There is a difference particularly as very few externally facing applications will use individual user access levels enforced at the database level, usually in the application layer. Therefore in the above scenario what is being protected is the application's connection to the database, what the application does with this is up to the application.
As a general security management principle it is best to never assign direct access to a resource and to instead use security groups to manage access to the resource. While this does reduce immediate visibility of users it is a much more manageable scenario. If you're every worked anywhere near ops you'll have frequently come across the, understandably sensible, user access request "give the new starter the same access as the leaver", or anything similar to this. If you assign direct access to resources you will never be able to fulfill this request because in order to work out what access the original user had you would have to query every single network object or node you can find (and this is AD, not NetWare). For similar management reasons, if users may gain access to a resource, such as an MS-SQL database, and later lose access to this resource, something that happens routeinely in larger organisations with internal team, project or departmental changes, then it's often the case that they will gain or lose access to multiple resources at the same time. If these are security group controlled then it is a relatively simple matter to switch access and most sane security topologies have a concept of permissions groups and groups of permission groups :). In other words, you assign access to the resources themselves using security groups and in turn group these security groups together to make management of them as a whole easier. For example if a single application requires access to two databases and one file share then this is a good way to both manage this and to record clearly in the configuration that this is the case: AppGroup contains Database1, Database2 and FileShareA permissions. It is, of course, possible to assign AppGroup access to each of these but this loses visibility of what is actually being secured and while using groups like this adds some boilerplate for non-tin-pot setups it is invaluable.
While web.config connection strings may be encrypted, for a few technical security reasons it is generally considered to be more secure if the user that the connecting process is executing as uses its credentials to access the database and does not use a separately managed, possibly non-standard strength, MS-SQL server login that may or may not be transiently encrypted in the configuration file. There's nothing stopping you encrypting an integrated-auth connection string of course if you really feel like hiding things but "security through obscurity" only slows miscreants down, it doesn't add much genuine security. Using a separately managed, and therefore outside of normal password management systems, username and login in the database server itself adds a likely point of security weakness and it is this that is one of the key factors against using MS-SQL server logins compared to AD secured logins.
...hopefully this makes some sense 🙂
February 2, 2018 at 5:00 pm
Forgot to add... one of the main reasons for using MS-SQL server logins is when the MS-SQL server itself is separated from the application using a highly controlled firewall. If you punch holes through a firewall to allow the ports required for windows authentication to work you may as well not bother with the firewall and pretty much all exploits (ab)use these connections. Instead if you allow just port 1433 (default) through the firewall then you have no choice but to use MS-SQL server logins, therefore while the MS-SQL server itself is somewhat more secure, the connection configuration is less so - using a very strong MS-SQL server password and encrypting the configuration file connection settings are your only real methods to protect the database - not withstanding only assigning the exact rights required to the MS-SQL server login of course, I see far too many application developers that try and insist that their application requires owner access to the database.
February 5, 2018 at 2:25 am
n.ryan - Friday, February 2, 2018 4:54 PMpeter.row - Friday, February 2, 2018 1:48 AMAll 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.
This technique is for controlling access to a database, not so much controlling what is done with that access. There is a difference particularly as very few externally facing applications will use individual user access levels enforced at the database level, usually in the application layer. Therefore in the above scenario what is being protected is the application's connection to the database, what the application does with this is up to the application.
As a general security management principle it is best to never assign direct access to a resource and to instead use security groups to manage access to the resource. While this does reduce immediate visibility of users it is a much more manageable scenario. If you're every worked anywhere near ops you'll have frequently come across the, understandably sensible, user access request "give the new starter the same access as the leaver", or anything similar to this. If you assign direct access to resources you will never be able to fulfill this request because in order to work out what access the original user had you would have to query every single network object or node you can find (and this is AD, not NetWare). For similar management reasons, if users may gain access to a resource, such as an MS-SQL database, and later lose access to this resource, something that happens routeinely in larger organisations with internal team, project or departmental changes, then it's often the case that they will gain or lose access to multiple resources at the same time. If these are security group controlled then it is a relatively simple matter to switch access and most sane security topologies have a concept of permissions groups and groups of permission groups :). In other words, you assign access to the resources themselves using security groups and in turn group these security groups together to make management of them as a whole easier. For example if a single application requires access to two databases and one file share then this is a good way to both manage this and to record clearly in the configuration that this is the case: AppGroup contains Database1, Database2 and FileShareA permissions. It is, of course, possible to assign AppGroup access to each of these but this loses visibility of what is actually being secured and while using groups like this adds some boilerplate for non-tin-pot setups it is invaluable.
While web.config connection strings may be encrypted, for a few technical security reasons it is generally considered to be more secure if the user that the connecting process is executing as uses its credentials to access the database and does not use a separately managed, possibly non-standard strength, MS-SQL server login that may or may not be transiently encrypted in the configuration file. There's nothing stopping you encrypting an integrated-auth connection string of course if you really feel like hiding things but "security through obscurity" only slows miscreants down, it doesn't add much genuine security. Using a separately managed, and therefore outside of normal password management systems, username and login in the database server itself adds a likely point of security weakness and it is this that is one of the key factors against using MS-SQL server logins compared to AD secured logins.
...hopefully this makes some sense 🙂
It doesn't make any more sense, because you've dodged the question. In the common scenario I raised AD logins cannot be used because it's hosted - the server is not on your domain. Therefore simply saying SQL logins will be removed from the installation is not a solution for security.
In my case the application implements groups so it easy to give a new person the same permissions as a leaver.
I'm not saying SQL logins should be used all the time and never AD logins but the article and many comments seem to be implying SQL Server should not have SQL logins at all and ignore the common DB backed web application scenario.
February 6, 2018 at 6:49 am
The biggest danger with AD groups is control of the groups. In some organizations new users are created as as a copy of an existing account. Sometimes a lower level employee who needs read only access to data will be created as a copy from her manager's account with all his group membership. 6 months later the SOX auditors come by and want to know why he/she has access to modify sensitive data and who authorized it. With SQL logins you can set up a trigger to keep records. With AD the only way i've found was to import the entries from the security log on the DC's and keep them for months. And you have to be quick because on busy DC's the log may be overwritten every few hours.
February 27, 2018 at 6:56 am
alen teplitsky - Tuesday, February 6, 2018 6:49 AMThe biggest danger with AD groups is control of the groups. In some organizations new users are created as as a copy of an existing account. Sometimes a lower level employee who needs read only access to data will be created as a copy from her manager's account with all his group membership. 6 months later the SOX auditors come by and want to know why he/she has access to modify sensitive data and who authorized it. With SQL logins you can set up a trigger to keep records. With AD the only way i've found was to import the entries from the security log on the DC's and keep them for months. And you have to be quick because on busy DC's the log may be overwritten every few hours.
I have a PowerShell script that iterates across all servers (I'm more or less manage 100+) and leverages xp_logininfo to output the membership of each domain group into a table (a Type 2 SCD actually) for which I can run a report to see who is a member of a privileged group and what new members have been added or dropped from group membership.
For example:EXEC xp_logininfo 'MYCORP\ACCTPayroll','members';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 1, 2018 at 8:26 pm
alen teplitsky - Tuesday, February 6, 2018 6:49 AMThe biggest danger with AD groups is control of the groups. In some organizations new users are created as as a copy of an existing account. Sometimes a lower level employee who needs read only access to data will be created as a copy from her manager's account with all his group membership. 6 months later the SOX auditors come by and want to know why he/she has access to modify sensitive data and who authorized it. With SQL logins you can set up a trigger to keep records. With AD the only way i've found was to import the entries from the security log on the DC's and keep them for months. And you have to be quick because on busy DC's the log may be overwritten every few hours.
SQL Authentication Via AD Groups Part II: Who has what access?
http://www.sqlservercentral.com/articles/Active+Directory/135710/
For the accumulation of historical access records, I would suggest that you consider a third party log aggregation tool like Splunk.
Thanks
John
Viewing 8 posts - 46 through 52 (of 52 total)
You must be logged in to reply to this topic. Login to reply