Securing Data from the DBA

Perhaps it is the increasing number of companies that outsource or offshore the DBA function, but I am regularly asked how to secure the data from the DBA who is in the sysadmin fixed server role. As I have previously discussed, you have a number of issues to consider when trying to secure your data from a sysadmin or db_owner. Some members of the community consider EncryptByPassphrase() to be the best way to provide this functionality. In my opinion, the better method is to architect a solution that protects an asymmetric key with a password and protects the symmetric key with this asymmetric key (hybrid approach). Even this approach is limited in what it can protect, however.

When assigned to the sysadmin fixed server role, the DBA can view, insert, update, and delete data from any table in any database in the instance. The DBA assigned to the sysadmin role can control all keys and certificates managed by SQL Server. In short, anyone assigned to the sysadmin role will inherit the rights to decrypt and encrypt any data in any database that relies on the SQL Server encryption key architecture. If you are using the SQL Server 2005 database encryption key hierarchy, you have few options for securing the data from the DBA. As discussed previously, you may decide to forgo the key hierarchy and protect the asymmetric key, certificate, or symmetric key with a password, but the sysadmin still has the authority to drop these keys, which puts your sensitive data at risk.

At first blush, EncryptByPassphrase() promises to provide a solution for this requirement: if the DBA does not have the passphrase, the DBA cannot decrypt the sensitive data. The DBA cannot drop the keys, because they do not exist in the database. You may experience key protection, but a thorough review of the way in which the data is accessed may uncover that you are not securing the passphrases from the DBA. For instance, when data is accessed through stored procedures, you may hard-code these passphrases directly in the stored procedure. The DBA has rights to view all stored procedure text and will have access to these values. As an alternative, you may input the passphrase as a parameter, as in the following example:

CREATE PROCEDURE GetPersonData
    @PassphraseEnteredByUser VARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON

    SELECT
        CONVERT(
            NVARCHAR(9),
            DecryptByPassphrase(
                @PassphraseEnteredByUser,
                SocialSecurityNumber)
        ) AS PersonData
    FROM PersonID
END
GO

Any junior DBA can set up a simple trace against the database to capture all activity, including stored procedures. The DBA will see the stored procedure call coupled with the input parameter containing the passphrase.

Some experienced stored procedure aficionados are aware of a little SQL Server 2000 "trick" to hide certain procedures from Profiler and Trace. When you pass a commented-out sp_password together with the execution of the stored procedure, SQL Server will not pass the statement to Profiler:

EXEC GetPersonData N'Passphrase'  --sp_password
GO

Profiler Output
-- 'sp_password' was found in the text of this event.
-- The text has been replaced with this comment for security reasons.

This "trick" was an unintended feature in SQL Server 2000 and does not produce the same results in SQL Server 2005. As of SQL Server 2005 Service Pack 1, SQL Server does not support flagging the execution of a procedure as private to block the statement text from Trace and Profiler.

If your motivation is to protect data from anyone in the sysadmin role, using the EncryptByPassphrase() feature will require that you generate your DML statements in the application and send the batch to SQL Server. In this example, your application may store the passphrases outside of SQL Server in a configuration file or directly in the application code. Of course, the sysadmin should not have access to the source code or file location containing the passphrases. When the statement is sent to SQL Server from the application, SQL Server Profiler will not reveal text related to encryption functions. When your application code passes the DecryptByPassphrase() function coupled with the proper passphrase, Profiler will replace the text of the event with "The text has been replaced with this comment for security reasons." Navigating toward an approach to embed DML SQL statements within the application code carries a number of concerns.

If you protect your asymmetric keys, certificates, or symmetric keys with a password, you will open the key directly in your application code and call a stored procedure that contains the DML statements in the same session. In the previous example, we may have a stored procedure as follows:

CREATE PROCEDURE GET_Person
AS
    SET NOCOUNT ON

    SELECT
        CONVERT(
            NVARCHAR(9),
            DecryptByKey(SocialSecurityNumber)
        ) AS Person
    FROM Personid
GO

The application code will open the symmetric key and subsequently execute the GET_Person procedure. This solution will minimize the code that is embedded in your application, while providing a level of data protection from the sysadmin and db_owner. The DBA cannot change a password associated with a key or certificate without passing in the old password, which protects your passwords from malicious activity. However, this solution is still at risk if a disgruntled DBA decides to drop all keys and certificates on the server. A good backup strategy is required to address this situation.

There is no easy solution to protecting your data from the DBA. Outsourcing and offshoring such a sensitive role will require analysis of your overall server security layer; SQL Server 2005 encryption should not be considered a single approach to solving this dilemma.