- The best practice recommendation is that we use Windows authentication vs. SQL Server authentication.
- In an enterprise environment, we typically want to use Windows groups over Windows users.
- This means the DBAs only have to worry about assigning permissions to the groups.
And for the most part this is fine. AD admins (or security admins, if you have a separate group) are responsible for security already. It makes sense to compartmentalize the overall management of security groups this way, allowing them to parcel out permissions based on the organization's needs. There's one big issue that comes to mind: they also manage the Windows group for the DBAs.
They should. However, the issue is that a person with such permissions could add himself/herself to the security group, make use of the elevated rights, and then remove himself/herself from the security group. As a DBA, there isn't anything I can do to detect this. And as a DBA, my options for detecting the active use of the elevated rights isn't exactly straight-forward. To be able to detect this scenario I'm going to need help. I'm going to need help from the AD admins.
Group membership changes will record an appropriate security event in the security event log on a DC. That is, if the DCs are configured right. Those events could be forwarded to a collection server and they can also be picked up by some log management device. However, none of these are within the control of the DBA. This is an example where IT needs to cross teams to protect the SQL Servers. There is some work on the DBA side and there is some work on the AD side. If you have a security team that monitors logs, there's some work on their side, too. Quite simply, crossing teams is a must.
How is your relationship with the other infrastructure teams? Are you all working together towards the same goals with respect to security?