By: Matteo Lorini | Comments (7) | Related: > Security
Problem
In many organizations there is a need to segment what DBAs can do in certain environments. By default most DBAs are made sysadmins, but this gives them full control over the SQL Server instance. To meet certain regulatory guidelines this is not allowed, because DBAs have access to sensitive data. By default there is not a way to segment out certain DBA tasks vs. viewing user data, so in this tip we look at a Separation of Duty Framework that was developed to allow you to achieve this.
Solution
Separation of duty, as a security principle, has as its primary objective the prevention of fraud and errors. In the face of internal and external regulatory requirements like PCI and SAS 70, organizations are realizing that the security and privileges of the DBA must be minimized for certain types of data that is categorized as sensitive.
SQL Server provides a rich set of security features that allow customers to satisfy their security goals. Everything from encrypting sensitive data to recording all activity (Audit) within a particular database is easily attainable with the features introduced in 2008. However, one topic that many struggle with is in the area of Separation of Duties or Role Separation. The subject frequently comes up when facing regulatory compliance, usually in the form of "How do I prevent my administrators from seeing my data?" While SQL Server does not provide a packaged solution to facilitate Separation of Duties, it does provide functionality that allows for Separation of Duties to be achieved.
The goal of this tip is to share with the community, Lara Rubbelke's idea on how we can achieve Separation of Duty within SQL Server.
At the 2010 PASS Summit, Lara Rubbelke (Data Platform Technology Specialist for Microsoft) presented a SQL Server Separation of Duty Framework that can be used to achieve Separation of Duty. The basic idea consists of delegating certain DBA tasks that require sysadmin rights to a Junior DBA or a non-DBA without granting him/her sysadmin rights.
The Separation of Duty framework can be downloaded for free from http://sqlserversod.codeplex.com/releases/view/55355, and it comes with the following pre-defined procedures:
For example; sp_who2 requires the VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL Server. If you do not have this permission, the user sees only their current session. However, using the Separation of Duty Framework a regular user is able to run a command similar to sp_who2 to see all sessions like a sysadmin, without having to grant the VIEW SERVER STATE permission directly. This methodology therefore allows organizations to create a set of standard procedures that allows a non-sysadmin to run, but still preventing the viewing of sensitive data.
Before installing the Separation of Duty Framework, please read the documentation that comes with it.
When installing, the installation script will ask for the following values:
| ServerName | The name of the instance where the SQL Server Separation of Duties Framework will be installed. |
| DatabaseName | The name of the database which will be used to manage the signed procedures. This may be an existing database or a new database. It is highly recommended not to use an application database, and to use a database that is specifically designed for DBA management. |
| KeyBackupFileDestination | The directory where the keys will be backed up during the installation. |
| MasterDMKPassword | The Master database Database Master Key password. This will only be used if a Database Master Key does not yet exist in the Master database. If a Database Master Key exists in the Master Database, this password is disregarded. |
| PKBackupPassword | The password used to encrypt the private key when backed up. |
| UserDMKPassword | The password for the Database Master Key that will be created in the database specified in DatabaseName. If a Database Master Key exists in this database, the password is disregarded. |
Using PowerShell, run ".\InstallScript" to run the installation.
Once the installation is complete, the script will create the following procedures in the database that you specified in the installation process. In my case, I chose DBASoD as the database name.
To test, I created a SQL user named DBA1 with the following permissions. (Note: DBA1 is not a sysadmin)
Let's connect to SQL Server using the newly created user DB1 and run: sp_who2 (I only get my session information)
Now, let's run: dba.sysproc_sp_who2 (I get back all information this account would see if it were a sysadmin)
As you can see this is pretty straightforward to setup and hopefully it gets you closer to your goal of separating duties in your SQL Server environment.
Next Steps
- This was just one example of the available items in this download. As you can see from the screen shots above there are several commands that can be run using this technique without having to grant the sysadmin fixed server role.
- Download and install the Separation of Duty Framework from http://sqlserversod.codeplex.com/releases/view/55355
- Learn more about PCI and SAS 70 http://itknowledgeexchange.techtarget.com/compliance-governance/sas-70-pci-dss-2009-regulatory-compliance-checklist/
- Review these additional security tips
About the author
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
