Column encrypted in Database, but decrypted in clear text in Cube

  • Article

In one of my last projects I have had the following challenge: The customer wanted me
to encrypt a sensitive column in the database, better say in a dimension table.
But, the column must show up in the cube when browsing, and it has to be
clearly readable there. So the customer wanted to prevent that someone can
select the data in clear text from the table, or steal the backup of the
database and is able to read the data. But someone who has the right to browse
the Cube must read the column (the column is used as the name column in the
dimension).

OK, tough goal. But why?

To encrypt a column in the database (be aware: this has nothing to do with
transparent data encryption, TDE, this here is about encryption of a single
column in a table) you create the column as varbinary(128). Then you need a
key, symmetric or asymmetric, to encrypt and decrypt. Or a certificate.

So, what can one use here?

Encrypt by Key:

  • Symmetric or asymmetric
  • Key will be generated in the database
  • You need an additional master key in the database, which protects this key
  • You open the key, and encrypt
  • You open the key, and decrypt
  • There are different algorithms (DES, AES etc…)

Encrypt by certificate:

  • Create a certificate
  • Encryption using certificates is based on asymmetric algorithms

So, OK, first stop here: the requirement was, that the dimension table can hold some
100 000 rows. But asymmetric encryption is not recommended for lots of rows.
So, encrypt by certificate only works with asymmetric algorithms, so this is
not an option.

So we need to go for Encryption by key. But here our next problem comes up: We must
open the key first to use it.

We should reflect on how the data from a SQL Server database is read from SSAS. It
can be done directly from the table, or, the recommended way, using a view. But
in both ways it is not possible to use an ‘open key’ in the SQL code….

One possibility is to use a view, and inside the view use a stored procedure with
open query. In the procedure you can run any code you want. I tried that, and
it worked…. But, should I really go for this?

Just think a little bit more about it. And, there is a solution. The key must open
itself when needed, and really, there is a possibility to do that. All you need
is a combination of symmetric and asymmetric keys, a SQL Server key and some
permissions for the right account….

Let’s do it using demo code…

First we need a demo DB:

use master

go

CREATE DATABASE Crypt

go

use Crypt

GO

Then we need a database master key:

-- Database Master Key.... we need one….

if not exists (select
* from sys.symmetric_keys where symmetric_key_id = 101)

create master key encryption by password =
'Password123'

Do not forget the password that you set here!!! If you need to restore the database
later on a different instance of SQL Server, you need that, otherwise the
encrypted data stays encrypted forever….

To protect (encrypt) this key, we connect it to the SQL instance the DB is running
on. This prevents people from opening the master key when stealing a backup,
and we need it for the automatic opening later. The Service Master Key opens
automatically when the instance starts running and decrypts the database master
key. But: the database master key is encrypted by the service master key, so
the DB is bound to the SQL instance for that to work:

ALTER MASTER KEY ADD
ENCRYPTION BY SERVICE MASTER KEY

go

Now we create an asymmetric key. This will not be used for the column encryption
(asymmetric is bad for a lot of data, you remember….), it is used to protect a
symmetric key we create afterwards:

IF NOT EXISTS (select
* from sys.asymmetric_keys where name = 'STORE_CRYPT_ASYM')

BEGIN

      Create asymmetric key STORE_CRYPT_ASYM

            with algorithm = RSA_2048;

END

Use any algorithm here which is available, I went for the RSA_2048.

Now we create the symmetric key, which will be encrypted by the asymmetric key we just
created. With this trick, we do not need to encrypt the symmetric key with a
password, which must later be used for an ‘open key’…..

IF NOT EXISTS (select
* from sys.symmetric_keys where name = 'STORE_CRYPT')

BEGIN

create symmetric key STORE_CRYPT with
algorithm = DES

           encryption by asymmetric key STORE_CRYPT_ASYM

END

Here we use the DES on the symmetric keys, it is just my favor, use any that is
supported there, look at books online.

Now we create a table:

CREATE TABLE Demo

(

      ID int identity(1,1) not null PRIMARY KEY,

      CryptColumn varbinary(128) not null,

      InsertDateTime datetime not null

)

GO

For the inserts into the table, we need to open the symmetric key first…

open symmetric key
STORE_CRYPT decryption by Asymmetric key STORE_CRYPT_ASYM

This works because we are sysadmin on the SQL instance. It also works as a dbo in
the database. For others to do that, you need the right permissions set…. More
later to come here on that topic.

We do an insert into the Table…

insert into dbo.Demo

(CryptColumn, InsertDateTime)

select EncryptByKey(Key_GUID('STORE_CRYPT'), 'Dies ist ein Test'), GETDATE()

Close the key afterwards

close symmetric key STORE_CRYPT

We do a select on the table…

 

All fine, not readable with a normal select.

Now we create a role in the database

if not exists (select * from sys.sysusers where name = 'DeCryptColumns' and issqlrole = 1)

      create role DeCryptColumns

go

We give this role the two rights necessary to use the Keys…

grant view definition on symmetric key::STORE_CRYPT to [DeCryptColumns];

go

grant control on asymmetric key::STORE_CRYPT_ASYM to [DeCryptColumns]

go

A select on the table with the decrypt column code inside… and, just remember…. We did
not open the key as we did for the insert!

select ID,

CONVERT(VARCHAR,
DecryptByKeyAutoAsymKey(AsymKey_ID('STORE_CRYPT_ASYM'), NULL, CryptColumn)) AS CryptColumn,
InsertDateTime FROM dbo.Demo

 

This happens when you have the right to use the keys, they open by itself and
decrypt.

If you don’t have the rights, your just get NULL instead of the clear text in the
CryptColumn data column.

So, all you need to do, to have this in clear text in the SSAS cube is…..

  • Use a view to select the data for the cube and include the DecryptByKeyAutoAsymKey
    for the column to be decrypted
  • Create the account used for processing the cube as login in the SQL Server instance
    (you must do so in any case, otherwise you have no chance to read the data)
  • Map the login additionally to the DeCryptColumns role in the database. I suppose
    you gave the account already the db_datareader role to be able to read data in
    the database to fill the cube
  • Process the cube, and the column is in clear text when browsing the cube

The data is readable in the cube, but encrypted in the table. So someone without rights
to the keys only sees NULL, and someone who steals the database backup also
sees encrypted data, or NULL.