The SQL Server Administrator's Console

helping SQL Server data centers to build, monitor and maintain high performance data solutions and high functioning data teams
Console     SQLClue     Articles & Scripts Index     Feedback      

Encrypted Storage of Sensitive DBA Values

Data is one of the most important and critical assets of organization. DBAs are usually responsible for the organizations primary data stores. One of the challenges DBAs face when managing and properly securing primary data stores is in finding a way to manage the dauntingly long list of passwords and pass phrases needed to properly back-up and restore primary data stores.

Over the last couple of major SQL Server releases the ability to better secure data has matured significantly. Starting with SQL Server 2005 encryption and decryption of stored data in database columns has been available. With SQL Server 2008 the more flexible and almost as secure Transparent Data Encryption (TDE) is added to the arsenal. It is a welcome capability, It does not come without some costs of administrators time to manage and aquire necessary skills.      

Both SQL Server data encryption and TDE rely upon the SQL Server encryption hierarchies. SQL Server Books Online includes a diagram that describes the encryption hierarchy quite well. (Note that beginning with the November 2009 Books Online refresh the diagram is update, actually expanded to two diagrams  that graphically depict the encryption hierarchy.)

The top node of all SQL Server encryption hierarchies is the Windows Operation Systems Data Protection API (DPAPI). When a SQL Server instance is installed, the DPAPI is always used to generate a Service Master Key - . Best practice is to always backup this Service Master Key immediately after SQL Server 2005 or SQL Server 2008 is installed and store that backup in a secure offsite location. There is some reason to doubth that this prqactice is being carried out in all data centers. If the server fails or an attempt is made to move encrypted data to new hardware and there is no backup of the service master key, all data that relies upon an encryption hierarchy created by that SQL Server instance is at risk of becoming unusable. When you backup the Service Master Key you must provide a password used to protect the backup.

Before any SQL Server encryption can be used at least one more level in the hierarchy must be created. The Database Master Key is created by the DBA. A password must be provided. This password is used to encrypt the key in the database. Best practice is to backup the Database Master Key and store the backup in a secure offsite location. If the server fails, the database becomes suspect or an attempt is made to move encrypted data to new hardware and you do not have a backup of the service master key, all data that relies upon an encryption hierarchy created by that SQL Server instance may be unusable. When you backup the Database Master Key you must provide a password used to protect the backup.

Once a Database Master Key is created the encryption hierarchy can branch in a few directions:

  • A Database Encryption Key can be created and TDE enabled. Creating a TDE Database Encryption Key requires a reference to a certificate in the master database. This Certificate will be created using one of more passwords provided by the DBA. As with the Service Master Key, the Database Master Key and all other certificates loaded to a SQL Server, the Certificate should be backed up and stored in a secure offsite location. One or more passwords are required when backing up a Certificate.  

  • A highly secure Asymmetric Key encryption may be implement on selected data columns. The Asymmetric Key - a two part or public key/private key encryption - is the slowest encryption yet the more secure. Asymmetric keys can be encrypted by a password or by the Database Master Key or both.

  • Symmetric Key based encryption may be implemented on selected data columns. Multiple passwords can be used to secure a Symmetric key. In fact one or more Asymmetric Key encrypted by a password can be used to encrypt the Symmetric Key. There can be a variable number of passwords to support in a Symmetric Key scenario.  

This is by no means an exhaustive listing of the combinations and permutations of storage encryption methods in SQL Server. It is intended only to help make the point that a DBA may need to manage many passwords when encryption is used.

 

Some tools are needed to be able to store all these passwords, especially when strong passwords are used.

 

The rest of this article will lay out a easy to implement yet highly secure SQL Server storage encryption based method for storing passwords for the DBA that must use and remember many passwords. To be sure there are a number of good password vault software applications available for download on the Internet. Many are free. One main advantage of the alternative presented here is that - apart from being very easy for a DBA ti implement - it provides hands on experience and sample code to the DBA that must support storage encryption within SQL Server. It represents the “eat your own dog food” approach.

 

The discussion will be based on a SQLCMD mode script that is available here. There are at least two good reasons to use a SQLCMD script. First, it allows passwords to be fed to script without the need to store the passwords in the script as clear text at any time. Secondly the SQLCMD replacement variable token helps to illustrate the named-pair method that is used to securely store multiple passwords in the database. The SQLCMD variable name for each sensitive value will be store unencrypted in the data row along with the encrypted sensitive value as a key to each encrypted value. That way the valued are secure and the way to recall the value when it is needed does not have to be recalled from the DBA’s already jammed memory bank.

 

:setvar DATABASE_NAME "test"

:setvar SERVICEMASTERKEY_ENCRYPTIONPASSWORD "password1_#*&Gt&D*"

:setvar SERVICEMASTERKEY_BACKUPPASSWORD "password2_)(NND(*#GB#@DG"

:setvar DATABASEMASTERKEY_ENCRYPTIONPASSWORD "password_M#D(D&GH@"

:setvar DATABASEMASTERKEY_BACKUPPASSWORD "password4_NDjdo#07^&"

:setvar VALUE_ENCRYPTION_KEY_ALGORITHM "DES"

:setvar DATABASE_ENCRYPTION_KEY_ALGORITHM "AES_128"

 A multi-layered security cloak is used to protect the sensitive values from unintended or malicious use. Some of the layers are inherent to the encryption hierarchy, others augment but are not directly related to the encryption hierarchy.

  • A Database Master Key is created in the master database.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$(MASTERMASTERKEY_ENCRYPTIONPASSWORD)';

  • This Database Master Key is used to create a server certificate for the TDE Database Encryption Key of the sensitive value database.

    • CREATE CERTIFICATE ServerCert WITH SUBJECT = 'TDECertificate';

  • A stand alone database is created.

DATABASE [$(DATABASE_NAME)]

  • Database chaining is explicitly disabled to prevent unintended access via compiled database objects and assemblies loaded to other databases on the server. (not part of encryption hierarchy)

ALTER DATABASE [$(DATABASE_NAME)] SET DB_CHAINING OFF

  • The TRUSTWORTHY option is explicitly disabled in this database to prevent Code Access Security (CAS) unsafe assemblies from being loaded without detection. (not part of encryption hierarchy)

DATABASE [$(DATABASE_NAME)] SET TRUSTWORTHY OFF

  • The database is placed in Restricted Use mode. No logins are mapped to users in the database. Only logins included in the sysadmin Fixed Server Role can access the database. (not part of encryption hierarchy)

ALTER DATABASE [$(DATABASE_NAME)] SET RESTRICTED_USER

  • The new database is encrypted using TDE. This serves to protect the database files from being copied or a backup used to move the database to an unauthorized SQL Server instance where a dictionary attach might be perpetrated or cryptographic tools maliciously applied to access the sensitive values.

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = $(DATABASE_ENCRYPTION_KEY_ALGORITHM)

ENCRYPTION BY SERVER CERTIFICATE ServerCert

 

ALTER DATABASE $(DATABASE_NAME)

SET ENCRYPTION ON

  • A Database Master Key is created in the new user database

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$(DATABASEMASTERKEY_ENCRYPTIONPASSWORD)';

    • A Symmetric Key encrypted by the Database Master Key is used to encrypt/un-encrypt the data column containing the sensitive values. No password is required when the Certificate is encrypted by the Master Key.

OPEN MASTER KEY DECRYPTION BY PASSWORD = '$(MASTERMASTERKEY_ENCRYPTIONPASSWORD)';

 

CREATE CERTIFICATE [DBACertificate]

WITH SUBJECT = 'Encryption of sensitive DBA values';

 

CLOSE MASTER KEY

  • The Service Master Key is backed up to a default location
     

    DECLARE @Service_MasterKey_Backup_Statement NVARCHAR(300)

    SET @Service_MasterKey_Backup_Statement =

    'BACKUP SERVICE MASTER KEY TO FILE =

    ''Service_MasterKey_'

    + CAST(DATEPART(YEAR,CURRENT_TIMESTAMP) AS [VARCHAR] (4))

    + '_'

    + CAST(DATEPART(MONTH,CURRENT_TIMESTAMP) AS [VARCHAR] (2))

    + '_'

    + CAST(DATEPART(DAY,CURRENT_TIMESTAMP) AS [VARCHAR] (2))

    + '_'

    + CAST(DATEPART(HOUR,CURRENT_TIMESTAMP) AS [VARCHAR] (2))

    + '_'

    + CAST(DATEPART(MINUTE,CURRENT_TIMESTAMP) AS [VARCHAR] (2))

    + '_'

    + CAST(DATEPART SECOND,CURRENT_TIMESTAMP) AS [VARCHAR] (2))

    + ''' '

    + 'ENCRYPTION BY PASSWORD = ''$(SERVICEMASTERKEY_BACKUPPASSWORD)'';'

    EXEC sp_executesql @Service_MasterKey_Backup_Statement;

    • The master Database Master Key and the newly created database Database Master Key are automatically backed up to a default location when created.

DECLARE @DBA_MasterKey_Backup_Statement NVARCHAR(300)

SET @DBA_MasterKey_Backup_Statement =

'BACKUP MASTER KEY TO FILE = ''DBA_Database_MasterKey_'

+ CAST(DATEPART(YEAR,CURRENT_TIMESTAMP) AS [VARCHAR] (4))

+ '_'

+ CAST(DATEPART(MONTH,CURRENT_TIMESTAMP) AS [VARCHAR] (2))

+ '_'

+ CAST(DATEPART(DAY,CURRENT_TIMESTAMP) AS [VARCHAR] (2))

+ '_'

+ CAST(DATEPART(HOUR,CURRENT_TIMESTAMP) AS [VARCHAR] (2))

+ '_'

+ CAST(DATEPART(MINUTE,CURRENT_TIMESTAMP) AS [VARCHAR] (2))

+ '_'

+ CAST(DATEPART(SECOND,CURRENT_TIMESTAMP) AS [VARCHAR] (2))

+ ''' '

+ 'ENCRYPTION BY PASSWORD = ''$(DATABASEMASTERKEY_BACKUPPASSWORD)'';'

EXEC sp_executesql @DBA_MasterKey_Backup_Statement;

Once these actions are complete the encryption hierarchy is in place and the database is secured from all access by users that are not members of the sysadmin Fixed Server Role.

Additional Considerations

Some measures that may improve the security of the sensitive values storage are not used. Give these measures adequate consideration when implementing secure storage of sensitive DBA values:

  • An Asymmetric Key is more secure than a Symmetric Key. In keeping with the dogfooding principal, it is likely that the performance requirements of line of business storage encryption solutions will dictate the Symmetric Key approach. Since that is what will probably be used most by the application, it is used here to keep the discussion germane to the understanding of application layer encryption. It is also easier to understand the fundamentals of using the encryption hierarchy with a Symmetric Key based example and then using that knowledge to implement an Asymmetric Key based solution than it is to first look at an Asymmetric Key based example and then implement a Symmetric Key line of business solution. The building blocks move more naturally from a Symmetric Key example to an Asymmetric Key application solution than the other way around.   

  • A password could also be applied to the Symmetric key and required each time the stored procedures are called. The requirement that there is a value not stored anywhere but in the mind of the DBA is not conducive to good business continuity best practices. If the DBA leave or forgets this one word all stored sensitive data is lost. If it is written down, the entire catalog of sensitive words is greater risk.

  • The Symmetric Key could have VIEW permission Revoked. The Certificate in the new database could have CONTROL permission revoked. These would be measure to prevent inadvertent access, for example if users were allowed to access the database for purposes other than the encrypted sensitive word container in the future or the RESTRICTED USER database setting was inadvertently disabled.

  • It is left to the DBA to devise and implement a suitable plan for moving backups of Master Keys and Certificates to a secure offsite location. This is strongly recommended.

  • Forward recovery practices for recovery of Master Keys and certificates from backups beyond the scope of this article. It is recommended that recovery scenarios are documented and tested by the DBA. The SQLClue Data Center Runbook is an excellent place to store these documents and test findings.

 

The Sensitive Value Container

To complete the sensitive values encrypted storage container, a table to store the sensitive values and stored procedures to add, remove and select values are created.

The table holds only the named-value pair

      CREATE TABLE [dbo].[DBAValue]

            ( [Name] [NVARCHAR](128)

            , [Value] [VARBINARY] (256)

            , CONSTRAINT pk_DBAValue__Name

              PRIMARY KEY ([Value])); 

 

The stored procedures are also brief. They all rely upon the implicit execute permissions available to sysadmin Fixed Server Role members. The Add and Get procedures both must use the certificate created previously and used to create the Symmetric Key in the new database to open the Symmetric Key again for each add or read operation. Since no password was used to create the Key, Opening it now is transparent to the DBA.

OPEN SYMMETRIC KEY DBAKey

DECRYPTION BY CERTIFICATE DBACertificate;

IF NOT EXISTS ( SELECT *

                             FROM [dbo].[DBAValue]

                             WHERE Name = @Name)

     INSERT [dbo].[DBAValue] ([Name], [Value])

     SELECT @Name, EncryptByKey(Key_GUID('DBAKey'), @Value);

The procedure that deletes rows needs no special treatment to deal with the encrypted column. It neve needs to decryot the data to delete a row.

Any user without VIEW permissions on the KEY or CONTROL permissions on the Certificate would not be able to read or write to the table. Recall that the database is in Restricted User Node and has no users. What that means is that if a malicious user had been able to gain access to these stored procedures they would be already be running with privileges elevated to ‘sa’ level. The SQL Server would already be fully compromised. And there would be nothing that could be done to stop them from making any change desired to the SQL Server configuration.

 

 

This page was last modified on Sunday, January 17, 2010 03:18:49 PM