다음을 통해 공유


SQL Server Remote Blob Storage (RBS) Credential Store Symmetric Key Rotation

The SQL Server team would like to advise RBS admins on security procedures for rotating the credential store symmetric key. If a provider requires the setup and use of a secret stored within the credential store (see related article), RBS uses this symmetric key to encrypt any provider secrets which a client may request to gain authorization to the provider’s blob store. Currently there are a couple of security challenges: 1) RBS 2014 and prior versions use a credential store which holds secrets encrypted using the TRIPLE_DES symmetric key algorithm which is outdated and not as strong as it should be and 2) There is no recommended way to rotate this symmetric key. It is highly recommended that if you are currently using TRIPLE_DES then you should follow steps in this article to strengthen your key. One may also want to rotate this key if there are security policies in place to periodically rotate keys or if the policy requires certain encryption strength properties (e.g., algorithm or key length) with which the current symmetric key does not adhere.

Note that RBS2016 will no longer use a credential store symmetric key that uses the TRIPLE_DES option. It will instead use AES_128 which addresses the first issue in the paragraph above. It's also worth noting that SQL Server 2016 will not even allow you to create a TRIPLE_DES symmetric key when using compatibility mode 130.

One can determine the RBS credential store symmetric key properties by issuing the following T-SQL query on the RBS database:

 

SELECT * FROM sys.symmetric_keys WHERE name = 'mssqlrbs_encryption_skey';

  

If the output from the above statement shows that TRIPLE_DES is still used, then it is advised to rotate this key.

To rotate the RBS credential store symmetric key, one may use the attached script on the RBS database. Do backup your database prior to key rotation. At the script’s conclusion, it has some verification steps.

If security policies require different key properties (e.g., algorithm or key length) from the ones provided, then the script may be used as a template for modification at one’s own risk. One would need to simply change the key properties in two places: 1) the creation of the temporary key 2) the creation of the permanent key.

The RBS credential store symmetric key rotation script is as follows:

  

IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'sp_rotate_rbs_symmetric_credential_key')

BEGIN

       DROP PROC sp_rotate_rbs_symmetric_credential_key;

END

 

/* This stored procedure will replace the currently used RBS credential store

symmetric key with one of your choosing. One may want to do this if there is

a security policy requiring periodic key rotation or if there are specific

algorithm requirements, for example. In this stored procedure, a symmetric

key using AES_256 will replace the current one. As a result of the symmetric

key replacement, secrets need to be re-encrypted with the new key. This stored

procedure will also re-encrypt the secrets. The database should be backed up

prior to key rotation. */

CREATE PROC sp_rotate_rbs_symmetric_credential_key

AS

BEGIN

BEGIN TRANSACTION;

BEGIN TRY

CLOSE ALL SYMMETRIC KEYS;

 

/* Prove that all secrets can be re-encrypted, by creating a

temporary key (#mssqlrbs_encryption_skey) and create a

temp table (#myTable) to hold the re-encrypted secrets. 

Check to see if all re-encryption worked before moving on.*/

CREATE TABLE #myTable(sql_user_sid VARBINARY(85) NOT NULL,

    blob_store_id SMALLINT NOT NULL,

    credential_name NVARCHAR(256) COLLATE Latin1_General_BIN2 NOT NULL,

    old_secret VARBINARY(MAX), -- holds secrets while existing symmetric key is deleted

    credential_secret VARBINARY(MAX)); -- holds secrets with the new permanent symmetric key

 

/* Create a new temporary symmetric key with which the credential store secrets

can be re-encrypted. These will be used once the existing symmetric key is deleted.*/

CREATE SYMMETRIC KEY #mssqlrbs_encryption_skey 

    WITH ALGORITHM = AES_256 ENCRYPTION BY

    CERTIFICATE [cert_mssqlrbs_encryption];

 

OPEN SYMMETRIC KEY #mssqlrbs_encryption_skey 

    DECRYPTION BY CERTIFICATE [cert_mssqlrbs_encryption];

 

INSERT INTO #myTable

    SELECT cred_store.sql_user_sid, cred_store.blob_store_id, cred_store.credential_name,

    encryptbykey(

        key_guid('#mssqlrbs_encryption_skey'),

        decryptbykeyautocert(cert_id('cert_mssqlrbs_encryption'),

            NULL, cred_store.credential_secret)

        ),

    NULL

    FROM [mssqlrbs_resources].[rbs_internal_blob_store_credentials] AS cred_store;

 

IF( EXISTS(SELECT * FROM #myTable WHERE old_secret IS NULL))

BEGIN

    PRINT 'Abort. Failed to read some values';

    SELECT * FROM #myTable;

    ROLLBACK;

END;

ELSE

BEGIN

/* Re-encryption worked, so go ahead and drop the existing RBS credential store

symmetric key and replace it with a new symmetric key.*/

DROP SYMMETRIC KEY [mssqlrbs_encryption_skey];

 

CREATE SYMMETRIC KEY [mssqlrbs_encryption_skey]

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE [cert_mssqlrbs_encryption];

 

OPEN SYMMETRIC KEY [mssqlrbs_encryption_skey]

DECRYPTION BY CERTIFICATE [cert_mssqlrbs_encryption];

 

/*Re-encrypt using the new permanent symmetric key. 

Verify if encryption provided a result*/

UPDATE #myTable

SET [credential_secret] =

    encryptbykey(key_guid('mssqlrbs_encryption_skey'), decryptbykey(old_secret))

 

IF( EXISTS(SELECT * FROM #myTable WHERE credential_secret IS NULL))

BEGIN

    PRINT 'Aborted. Failed to re-encrypt some values'

    SELECT * FROM #myTable

    ROLLBACK

END

ELSE

BEGIN

 

/* Replace the actual RBS credential store secrets with the newly

encrypted secrets stored in the temp table #myTable.*/

SET NOCOUNT ON;

DECLARE @sql_user_sid varbinary(85);

DECLARE @blob_store_id smallint;

DECLARE @credential_name varchar(256);

DECLARE @credential_secret varbinary(256);

DECLARE curSecretValue CURSOR

    FOR SELECT sql_user_sid, blob_store_id, credential_name, credential_secret

FROM #myTable ORDER BY sql_user_sid, blob_store_id, credential_name;

 

OPEN curSecretValue;

FETCH NEXT FROM curSecretValue

    INTO @sql_user_sid, @blob_store_id, @credential_name, @credential_secret

WHILE @@FETCH_STATUS = 0

BEGIN

    UPDATE [mssqlrbs_resources].[rbs_internal_blob_store_credentials]

        SET [credential_secret] = @credential_secret

        FROM [mssqlrbs_resources].[rbs_internal_blob_store_credentials]

        WHERE sql_user_sid = @sql_user_sid AND blob_store_id = @blob_store_id AND

            credential_name = @credential_name

FETCH NEXT FROM curSecretValue

    INTO @sql_user_sid, @blob_store_id, @credential_name, @credential_secret

END

CLOSE curSecretValue

DEALLOCATE curSecretValue

 

DROP TABLE #myTable;

CLOSE ALL SYMMETRIC KEYS;

DROP SYMMETRIC KEY #mssqlrbs_encryption_skey;

 

/* Verify that you can decrypt all encrypted credential store entries using the certificate.*/

IF( EXISTS(SELECT * FROM [mssqlrbs_resources].[rbs_internal_blob_store_credentials]

WHERE decryptbykeyautocert(cert_id('cert_mssqlrbs_encryption'),

    NULL, credential_secret) IS NULL))

BEGIN

    print 'Aborted. Failed to verify key rotation'

    ROLLBACK;

END;

ELSE

    COMMIT;

END;

END;

END TRY

BEGIN CATCH

     PRINT 'Exception caught: ' + cast(ERROR_NUMBER() as nvarchar) + ' ' + ERROR_MESSAGE();

     ROLLBACK

END CATCH

END;

GO

 

/* Rotate the RBS credential store symmetric key, and notice that the secrets

remain the same despite the key rotation. */

SELECT *, decryptbykeyautocert(cert_id('cert_mssqlrbs_encryption'), NULL, credential_secret)

FROM [mssqlrbs_resources].[rbs_internal_blob_store_credentials];

 

EXEC sp_rotate_rbs_symmetric_credential_key;

 

SELECT *, decryptbykeyautocert(cert_id('cert_mssqlrbs_encryption'), NULL, credential_secret)

FROM [mssqlrbs_resources].[rbs_internal_blob_store_credentials];

 

/* See that the RBS credential store symmetric key properties reflect the new changes*/

SELECT * FROM sys.symmetric_keys WHERE name = 'mssqlrbs_encryption_skey';

 

Related Articles: https://blogs.msdn.com/b/sqlrbs/archive/2010/08/05/rbs-security-model.aspx

RBS 2016 Documentation: https://msdn.microsoft.com/en-us/library/gg638709.aspx