In one of my last projects I have had the following challenge: The customer wanted meto 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 beclearly readable there. So the customer wanted to prevent that someone canselect the data in clear text from the table, or steal the backup of thedatabase and is able to read the data. But someone who has the right to browsethe Cube must read the column (the column is used as the name column in thedimension).
OK, tough goal. But why?
To encrypt a column in the database (be aware: this has nothing to do withtransparent data encryption, TDE, this here is about encryption of a singlecolumn in a table) you create the column as varbinary(128). Then you need akey, symmetric or asymmetric, to encrypt and decrypt. Or a certificate.
So, what can one use here?
Encrypt by Key:
Encrypt by certificate:
So, OK, first stop here: the requirement was, that the dimension table can hold some100 000 rows. But asymmetric encryption is not recommended for lots of rows.So, encrypt by certificate only works with asymmetric algorithms, so this isnot an option.
So we need to go for Encryption by key. But here our next problem comes up: We mustopen the key first to use it.
We should reflect on how the data from a SQL Server database is read from SSAS. Itcan be done directly from the table, or, the recommended way, using a view. Butin 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 withopen query. In the procedure you can run any code you want. I tried that, andit worked…. But, should I really go for this?
Just think a little bit more about it. And, there is a solution. The key must openitself when needed, and really, there is a possibility to do that. All you needis a combination of symmetric and asymmetric keys, a SQL Server key and somepermissions for the right account….
Let’s do it using demo code…
First we need a demo DB:
CREATE DATABASE Crypt
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 databaselater on a different instance of SQL Server, you need that, otherwise theencrypted data stays encrypted forever….
To protect (encrypt) this key, we connect it to the SQL instance the DB is runningon. 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 opensautomatically when the instance starts running and decrypts the database masterkey. But: the database master key is encrypted by the service master key, sothe DB is bound to the SQL instance for that to work:
ALTER MASTER KEY ADDENCRYPTION BY SERVICE MASTER KEY
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 asymmetric key we create afterwards:
IF NOT EXISTS (select* from sys.asymmetric_keys where name = 'STORE_CRYPT_ASYM')
Create asymmetric key STORE_CRYPT_ASYM
with algorithm = RSA_2048;
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 justcreated. With this trick, we do not need to encrypt the symmetric key with apassword, which must later be used for an ‘open key’…..
IF NOT EXISTS (select* from sys.symmetric_keys where name = 'STORE_CRYPT')
create symmetric key STORE_CRYPT withalgorithm = DES
encryption by asymmetric key STORE_CRYPT_ASYM
Here we use the DES on the symmetric keys, it is just my favor, use any that issupported 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
For the inserts into the table, we need to open the symmetric key first…
open symmetric keySTORE_CRYPT decryption by Asymmetric key STORE_CRYPT_ASYM
This works because we are sysadmin on the SQL instance. It also works as a dbo inthe database. For others to do that, you need the right permissions set…. Morelater to come here on that topic.
We do an insert into the Table…
insert into dbo.Demo
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
We give this role the two rights necessary to use the Keys…
grant view definition on symmetric key::STORE_CRYPT to [DeCryptColumns];
grant control on asymmetric key::STORE_CRYPT_ASYM to [DeCryptColumns]
A select on the table with the decrypt column code inside… and, just remember…. We didnot open the key as we did for the insert!
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 anddecrypt.
If you don’t have the rights, your just get NULL instead of the clear text in theCryptColumn data column.
So, all you need to do, to have this in clear text in the SSAS cube is…..
The data is readable in the cube, but encrypted in the table. So someone without rightsto the keys only sees NULL, and someone who steals the database backup alsosees encrypted data, or NULL.