Security with Auditability.

IF you are a DBA in any of the following:

  • A publicly-traded company
  • A company that has to comply with HIPAA or PCI
  • A company with DBA that hate disorder

…then you need to have an audit-friendly method of securing your SQL assets.  In general, a few rules make this easier on DBA and on management.

1. Get rid of SQL Server authentication!

The sooner the better.  If you do not have a sa login, it is really hard for a third party to login as sa.  And even if you have disabled sa  in favor of another sysadmin-powered login with a name like “janitor_readonly,” if that login is hacked then your data is in the open.

On the other hand, in the modern era, there is little to no need for SQL Server authentication at all.  With SQL 2008 and newer, the DBA actually has to opt in to using SQL Server authentication.  Your database servers should exclusively use integrated security.

Why?  First of all, every login is mapped to the AD user that was running the program that logged in.  Server applications have their own logins, and the clients are logged in with the end-user’s login.

2. Groups and Roles

The first thing you might think is something like, “I have 10,000 people logging into application X!”  I’ll be spending every waking minute maintaining SQL logins and permissions to the databases they need.

Nope.  Create Active Directory groups.  Assign them logins and database users with the minimum permissions that are needed. Examples:

  • DOMAINAccounting_RO – Read only to Accounting
  • DOMAINAccounting_RW – Read/Write to Accounting
  • DOMAINAccounting_Payroll – Read/Write Payroll Data
  • DOMAINSQL Admins – DBA with sysadmin privileges

Usually each database has a limited set of privilege levels.  in my shop, we have a limited set of groups and we use T-SQL to generate DSADD commands to add the group accounts to Active Directory, and pass these on to the sysadmins to be added (no DBA should be a Domain Admin – would you trust any DBA with that much power?  😀 ).

Each domain user is added to one or the other domain groups (or multiple groups; the permissions accumulate unless an explicit DENY is issued).

Use database roles to dereference objects further.  You then GRANT permissions to the database role, and then assign the database user for the AD Group.

Then when the desktop/sysadmins provision a user, they are added to the groups using ADUC (Active Directory Users and Computers), and the database users and permissions are defined once at the outset, and only need minor review as DDL changes are made.

User DOMAINCFO would be added to DOMAINAccounting_RW and DOMAINAccountingPayroll.  Then he or she would have the needed permissions at the outset.

3. Auditing Made Easy

If you enable successful logins to be saved to the database log, you will see that you will see the actual AD user that was logged in, along with the machine from which the login was made.  This information is also shown in sp_who2 and elsewhere in good monitoring tools.

Auditing tools now have access as well.  Now, if someone went in and ran a query like this:

update EmployeeSalary set SalaryRate = SalaryRate * 2 where EmployeeType = 'Janitor';

…any good auditing tool would capture the user ID and workstation name / IP of the culprit.  If someone did the same query with the sa login, you would have a lot less information on the culprit than would be advisable.

In short, it’s high time that SQL Server Authentication join the relics of Information Technology in the museum, right next to the IBM System/370-158.

Leave a Reply

Your email address will not be published. Required fields are marked *