RBS Security Model
RBS stores blob data in external blob stores which often use a differing security method from SQL Server. This blog post details the RBS security model for administrators and provider developers.
Master Key Requirement
As part of the RBS installation a database master key is required to be set. This is used for RBS credential encryption and the module signing / schema separation of the RBS internal tables and external views / stored procedures. Detailed information on the specifics of this are below. The database administrator should back up the master key by using BACKUP MASTER KEY and store the backup in a secure, off-site location.
Provider Security
There are 4 ways of integrating blob store provider security with RBS and SQL Server. Use of each is dependent on the type and support included in each provider.
1. The blob store supports Windows integrated security.
2. Application specifies credentials to access blob store, while opening an RBS Connection. Credentials are managed by the application.
3. A mapping of SQL Principals to Blob Store Principals is maintained by the administrator using RBS auxiliary tables. RBS manages this mapping and automatically flows credentials to the blob store after performing SQL security check.
4. The blob store supports the SQL security model. This allows treating security for RBS blobs in a very similar way to traditional varbinary(max) blobs. Such a level of integration is possible with providers that use SQL Server, such as the RBS FILESTREAM provider.
Windows Integrated Security
Some blob stores such as the sample File Server provider support windows integrated security and ACL blobs based on windows user identity. For such blob stores, RBS doesn’t have to do anything specifically to provide security to blobs. The blob store can be secured independently from RBS, using the mechanisms provided by the blob store. RBS client library guarantees that the provider is called in the same user context that the application uses to call into RBS client library.
When an RBS call is made to access a blob, it can be accessed on the blob store if the windows user in whose context the call was made has permissions to access the blob in the blob store. Such a call can be made after the blob ID, blob reference or blob locator has been retrieved from the database, which are secured using database security and RBS roles. The granularity of the permissions (read/write blob, create pool etc.) is specific to the blob store and is configured by the administrator on the blob store independent of RBS.
Credentials Managed by Application
RBS APIs provide a very flexible way of passing config options at various places. The application can provide credentials to access the blob store by setting CommandOptions on a RemoteBlob or RemoteBlobCollectionManager object before performing an operation. These credentials are not cached and need to be passed whenever needed to connect to the blob store.
This is a very simple approach, but requires the application to store and manage credentials, which adds complexity at the application layer. A better approach is for the credentials to be managed by the administrator, which the following two options provide.
Mapping SQL Principals to Blob Store Principals
A mapping from SQL Principals to Blob Store Principals can be setup and the blob store credentials can be stored in the database. This allows the credentials to be managed by the administrator. The administrator has the responsibility of making sure that the access rights to the user tables are reflected in this mapping to maintain consistency.
The credential management is exposed through a series of stored procedures:
-- Adds a credential to the credential table.
create procedure [mssqlrbs].[rbs_sp_set_blob_store_credential] (
@sql_user_sid varbinary(85),
@blob_store_id smallint,
@credential_name nvarchar(256),
@credential_secret varbinary(max))
-- Retrieve all the decrypted credentials owned by the current user.
create procedure [mssqlrbs].[rbs_sp_get_blob_store_credentials] ()
-- Retrieves a list of all the credentials in the table. Does not decrypt any secrets.
create procedure [mssqlrbs].[rbs_sp_get_all_blob_store_credentials] (
@sql_user_sid varbinary(85))
-- Removes a credential from the credential table.
create procedure [mssqlrbs].[rbs_sp_delete_blob_store_credential] (
@sql_user_sid varbinary(85),
@blob_store_id smallint,
@credential_name nvarchar(256))
For each SQL user that has access to RBS blobs, there will be one or more rows in the credential store: one per blob store (represented by the blob_store_id) the user has access to. This row stores the credentials to use to connect to the blob store. There will be one row per user per provider. To create a default credential the sql_user_sid can be specified as 0x. This credential will be used by any user that does not have a specific user credential added to the credential store.
Blob Store Credential Name
The credential name is an unencrypted credential identifier used by the store library to distinguish a credential without requiring the decryption of the credential secret. This may be a username or other non-protected information.
Blob Store Credential Secret
The credential secret is a byte array protected using SQL Server symmetric encryption. The exact internal format of the binary fragment is decided by the provider, e.g. it may contain a password, profile name / secret combination or any other important information that should be protected. The provider will need to provide a tool or instructions to generate the Credential Secret fragment that the administrator can then insert into this table through a stored procedure.
SQL User SID Representation
A SQL user is represented by the SQL user SID (which can be retrieved using SUSER_SID function). SID is chosen because it is guaranteed to be unique for every user and cannot be repeated unlike user IDs and login names. This offers better protection in case a user is deleted and another is added with the same name.
Steps to Access a Blob
With this scheme, the sequence of steps for accessing the blob store is:
1. Application calls RBS client library to initialize a new RemoteBlob or RemoteBlobCollectionManager object. It passes in a SqlConnection object.
Based on this SqlConnection’s (DataSource, Database, User), a RemoteBlobSession object is associated with this RemoteBlob or RemoteBlobCollectionManager object.
2. RBS client library uses the provided SqlConnection and calls a stored procedure to get Provider Credentials – rbs_sp_get_blob_store_credentials.
3. Stored procedure finds the user associated with the current connection using SUSER_SID, performs a lookup on the rbs_blob_store_credentials table and retrieves all the credentials corresponding to this user.
4. RBS Client library stores the Provider Credentials in the RemoteBlobSession object.
5. Application calls RBS client library to perform an operation that communicates with the blob store (e.g. blob store / fetch).
6. RBS calls provider library, passing in any matching cached BlobStoreCredentials[] along with other configuration options.
7. Using the given Blob Store Credential, the blob is accessed on the store. Provider library is responsible for managing connections to the blob store. It may maintain a pool of connections if needed.
Credential Encryption
Provider credentials need to be protected using encryption. Two things can be encrypted: column in which the credentials are stored and channel over which the credentials are transmitted.
Column Encryption
The Credential column is encrypted using symmetric encryption available on SQL Server, and requires a master key to be present on the database at the time RBS is enabled. A single symmetric key is created during RBS installation and used to encrypt / decrypt all the credential secrets in the table.
Channel Encryption
RBS opens a new encrypted SqlConnection in order to retrieve credentials based on the original connection string with the encrypted flag set, and optionally setting the TrustServerCertificate flag if the option has been specified during setup.
In-Memory Encryption
When credentials are cached on the client, they are kept in encrypted form so that a memory dump does not contain the credentials in clear text. This is done using the ProtectedMemory class which internally calls the DPAPI OS encryption APIs.
Blob Store supports SQL Security Model
A tightly integrated solution allows using SQL Principals to secure access to the blob store directly. This can be done if the provider uses SQL server, e.g. the SQL RBS FILESTREAM provider. When using a provider that is not located on the same SQL Server as the RBS metadata store, for example the RBS FILESTREAM provider in the remote configuration this may not be possible.
Module Signing and Schema
The RBS metadata is split between two schemas, mssqlrbs and mssqlrbs_resources. Externally facing modules such as user and admin stored procedures, views and functions are located in the main mssqlrbs schema. Internal tables and stored procedures are located in the mssqlrbs_resources schema.
A master key in the database is required to create a signing certificate and certificate account, along with the symmetric key used to encrypt the credential data. The modules in the mssqlrbs schema are signed with the certificate, and the certificate account is granted various permissions to the tables in the mssqlrbs_resources schema. When a user executes a module in the mssqlrbs schema that has been signed with the RBS certificate, during the execution of the module further privileges are granted to access mssqlrbs_resources that have been explicitly granted to the certificate user. This allows the admin to control user access using the db_rbs_reader, db_rbs_writer and db_rbs_maintainer SQL roles without requiring in depth knowledge of RBS internals.