mysql中的rbs_远程 Blob 存储 (RBS) (SQL Server) - SQL Server | Microsoft Docs

远程 Blob 存储区 (RBS) (SQL Server)Remote Blob Store (RBS) (SQL Server)

11/03/2016

本文内容

适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)

SQL ServerSQL Server 远程 BLOB 存储区 (RBS) 是一个可选的附加组件，它允许数据库管理员在商用存储解决方案中存储二进制大型对象，而不是直接存储在主数据库服务器上。Remote BLOB Store (RBS) is an optional add-on component that lets database administrators store binary large objects in commodity storage solutions instead of directly on the main database server.

RBS 包括在 SQL ServerSQL Server 安装介质上，但 SQL ServerSQL Server 安装程序不安装它。RBS is included on the SQL ServerSQL Server installation media, but is not installed by the SQL ServerSQL Server Setup program. 在安装媒体上搜索 RBS 以找到安装文件。Search for RBS.msi on the installation media to locate the setup file.

如果没有 SQL ServerSQL Server 安装媒体，可以在以下位置之一下载 RBS：If you do not have SQL ServerSQL Server installation media, you can download RBS at one of the following locations:

为什么选择 RBS？Why RBS?

优化的数据库存储和性能Optimized database storage and performance

在数据库中存储 BLOB 可能会占用大量文件空间和昂贵的服务器资源。Storing BLOBs in the database can consume large amounts of file space and expensive server resources. RBS 可以将 BLOB 传输到你选择的专用存储解决方案，并在数据库中存储对 BLOB 的引用。RBS transfers the BLOBs to a dedicated storage solution you choose and stores references to the BLOBs in the database. 这样可以释放服务器存储空间用于结构化数据，释放服务器资源用于进行数据库操作。This frees server storage for structured data, and frees server resources for database operations.

高效 BLOB 管理Efficient BLOB management

有好几项 RBS 功能都支持存储的 BLOB 管理：Several RBS features support stored BLOBs management:

BLOB 是通过 ACID(原子性、一致性、隔离性和持久性)事务管理的。BLOBS are managed with ACID (atomic, consistent, isolatable, durable) transactions.

BLOB 组织成集合。BLOBs are organized into collections.

包括垃圾收集、一致性检查和其他维护功能。Garbage collection, consistency checking, and other maintenance functions are included.

标准化 APIStandardized API

RBS 定义一组 API，可为应用程序提供用于访问和修改任何 BLOB 存储的标准化编程模型。RBS defines a set of APIs that provide a standardized programming model for applications to access and modify any BLOB store. 每个 BLOB 存储区都可以指定自己的提供程序库(该库嵌入到 RBS 客户端库)，并指定存储和访问 BLOB 的方式。Each BLOB store can specify its own provider library which plugs into the RBS client library and specifies how BLOBs are stored and accessed.

许多第三方存储解决方案供应商都开发了符合这些标准 API、在不同存储平台上支持 BLOB 存储的 RBS 提供程序。A number of third-party storage solution vendors have developed RBS providers that conform to these standard APIs and support BLOB storage on various storage platforms.

RBS 要求RBS Requirements

对于存储 BLOB 元数据的主数据库服务器，RBS 要求安装有 SQL ServerSQL Server Enterprise。RBS requires SQL ServerSQL Server Enterprise for the main database server in which the BLOB metadata is stored. 但是，如果使用提供的 FILESTREAM 提供程序，则可以在 SQL ServerSQL Server Standard 上存储 BLOB 本身。However, if you use the supplied FILESTREAM provider, you can store the BLOBs themselves on SQL ServerSQL Server Standard. 若要连接到 SQL ServerSQL Server，RBS 要求对 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 至少使用 ODBC 驱动程序版本 11，对 SQL Server 2016 (13.x)SQL Server 2016 (13.x)至少使用 ODBC 驱动程序版本 13。To connect to SQL ServerSQL Server, RBS requires at least ODBC driver version 11 for SQL Server 2014 (12.x)SQL Server 2014 (12.x) and ODBC Driver version 13 for SQL Server 2016 (13.x)SQL Server 2016 (13.x).

RBS 包括 FILESTREAM 提供程序，因此，您可以使用 RBS 在 SQL ServerSQL Server的实例上存储 BLOB。RBS includes a FILESTREAM provider that lets you use RBS to store BLOBs on an instance of SQL ServerSQL Server. 如果要使用 RBS 在其他存储解决方案中存储 BLOB，则必须使用为该存储解决方案开发的第三方 RBS 提供程序，或者使用 RBS API 开发自定义 RBS 提供程序。If you want use RBS to store BLOBs in a different storage solution, you have to use a third party RBS provider developed for that storage solution, or develop a custom RBS provider using the RBS API. Codeplex上以学习资源的形式提供了在 NTFS 文件系统中存储 BLOB 的示例提供程序。A sample provider that stores BLOBs in the NTFS file system is available as a learning resource on Codeplex.

RBS 安全性RBS Security

SQL 远程 Blob 存储团队博客是有关此功能的一个非常好的信息源。The SQL Remote Blob Storage Team Blog is a good source of information about this feature. RBS 安全模型一文中介绍了 RBS 安全模型。The RBS security model is described in the post at RBS Security Model.

自定义提供程序Custom providers

使用自定义提供程序在 SQL ServerSQL Server外部存储 BLOB 时，请确保使用适合于自定义提供程序所使用的存储介质的权限和加密选项保护存储的 BLOB。When you use a custom provider to store BLOBs outside of SQL ServerSQL Server, make sure that you protect the stored BLOBs with permissions and encryption options that are appropriate to the storage medium used by the custom provider.

凭据存储对称密钥Credential store symmetric key

如果提供程序需要设置和使用凭据存储中存储的密码，RBS 将使用对称密钥加密提供程序密码，客户端可能会使用该密码获取对提供程序 blob 存储的授权。If a provider requires the setup and use of a secret stored within the credential store, RBS uses a symmetric key to encrypt the provider secrets which a client may use to gain authorization to the provider's blob store.

RBS 2016 使用 AES_128 对称密钥。RBS 2016 uses an AES_128 symmetric key. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 不允许创建新的 TRIPLE_DES 密钥，但为实现向后兼容的情况除外。does not allow the creation of new TRIPLE_DES keys except for backwards compatibility reasons.

RBS 2014 及以前的版本使用凭据存储，其中包含使用已过时的 TRIPLE_DES 对称密钥算法加密的密码。RBS 2014 and prior versions use a credential store which holds secrets encrypted using the TRIPLE_DES symmetric key algorithm which is outdated. 如果当前正在使用 TRIPLE_DES，MicrosoftMicrosoft 建议你遵循本主题中的步骤将密钥轮换为更强的加密方法，以增强安全性。If you are currently using TRIPLE_DESMicrosoftMicrosoft recommends that you enhance your security by following the steps in this topic to rotate your key to a stronger encryption method.

你可以通过在 RBS 数据库中执行以下 Transact-SQLTransact-SQL 语句来确定 RBS 凭据存储对称密钥属性：You can determine the RBS credential store symmetric key properties by executing the following Transact-SQLTransact-SQL statement in the RBS database:

SELECT * FROM sys.symmetric_keys WHERE name = 'mssqlrbs_encryption_skey'; 如果该语句的输出显示仍在使用 TRIPLE_DES ，则应轮换此密钥。SELECT * FROM sys.symmetric_keys WHERE name = 'mssqlrbs_encryption_skey'; If the output from that statement shows that TRIPLE_DES is still used, then you should rotate this key.

轮换对称密钥Rotating the symmetric key

使用 RBS 时，应定期轮换凭据存储对称密钥。When using RBS, you should periodically rotate the credential store symmetric key. 这是一种常见的可满足组织安全策略的安全最佳实践。This is a common security best practice to meet organizational security policies. 轮换 RBS 凭据存储对称密钥的方法之一是使用 RBS 数据库中的 以下脚本 。One way to rotate the RBS credential store symmetric key, is to use the script below in the RBS database. 你也可以使用此脚本迁移到更强的加密强度属性，如算法或密钥长度。You can also use this script to migrate to stronger encryption strength properties, such as algorithm or key length. 在轮换密钥前，先备份数据库。Backup your database prior to key rotation. 脚本结束时，它有一些验证步骤。At the script's conclusion, it has some verification steps.

如果你的安全策略需要与所提供属性不同的密钥属性(例如，算法或密钥长度)，可以将该脚本用作模板。If your 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. 在以下两个位置修改密钥属性：1) 创建临时密钥的位置 2) 创建永久密钥的位置。Modify the key properties in two places: 1) the creation of the temporary key 2) the creation of the permanent key.

RBS 资源RBS resources

RBS 示例RBS samples

Codeplex 上提供的 RBS 示例演示如何开发 RBS 应用程序，如何开发和安装自定义 RBS 提供程序。The RBS samples available on Codeplex demonstrate how to develop an RBS application, and how to develop and install a custom RBS provider.

RBS 博客RBS blog

RBS 博客 提供可帮助你理解、部署和维护 RBS 的其他信息。The RBS blog provides additional information to help you understand, deploy, and maintain RBS.

密钥轮换脚本Key rotation script

此示例创建一个名为 sp_rotate_rbs_symmetric_credential_key 的存储过程，以将当前使用的 RBS 凭据存储对称密钥替换为This example creates a stored procedure named sp_rotate_rbs_symmetric_credential_key to replace the currently used RBS credential store symmetric key

你所选的密钥。with one of your choosing. 如果安全策略要求定期轮换密钥You may want to do this if there is a security policy requiring

或者有特定的算法要求，你可能想要这样做。periodic key rotation or if there are specific algorithm requirements.

在此存储过程中，使用 AES_256 的对称密钥将替换当前的密钥。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

现在，你可以使用 sp_rotate_rbs_symmetric_credential_key 存储过程轮换 RBS 凭据存储对称密钥，密码在密钥轮换前后保持不变。Now you can use the sp_rotate_rbs_symmetric_credential_key stored procedure to rotate the RBS credential store symmetric key, and the secrets remain the same before and after 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';

另请参阅See Also