SQL Server Encryption Questions

SQL Server Encryption Questions

Post by Bren » Thu, 02 Nov 2006 07:50:29

I have been messing around a little bit with SQL Server Certificates. I am a
little confused by some things i found

1) When doing the following:
DECLARE @encryptedstuff VARCHAR(900)
SELECT @encryptedstuff = EncryptByCert(Cert_ID('MyCert'),
SELECT @encryptedstuff
SELECT CAST(DecryptByCert(Cert_ID('MyCert'), @encryptedstuff) AS VARCHAR)

The doing the following 'Select @encrypted' produced different results with
the same data and same key. Each time i ran the above section it was
differrent. I even insert 60 records into a table but using the
declare @temp tinyint

set @temp = 0

while (@temp < 60)
insert into test
values(EncryptByCert(Cert_ID('MyCert'), 'duhhh!!!!!!!!!!!!'))
@temp = @temp + 1

Again the records were different. Why? I can't see how anything could ever
be descrypted if the data was different.

2) When i first started studying the principles i thought cert were not
certs in the traditional sense. You had to have a master key in your
database to create a certificate so i assumed the certificate was pretty
much a symmetric key that used the master key in your database. Is this not
correct? If it is which symmetric algorythm is it using? If not why is there
also a 'create symmetric key' statement where you actually specificy the
algorithym? If this is supposed work like a traditional CA (certificate
authority) then it should be asymmetric.

3) OK how does this really protect you that much? Yes it encrypts the data
so if an attacker tries to read the data directly then it will be just
garbage. While it would be hard for him to get the key since its also
encrypted i would think the only thing he would need is either the CertId,
Symmetric Key Name, or the Asymmetric Key Name to be able to read your data.
In this case it would seem knowing the name of the name of the key is just
as good as knowing the key itself. Am i missing something?


SQL Server Encryption Questions

Post by Laurentiu » Thu, 02 Nov 2006 08:49:21

1) Encryption is non-deterministic. Additional information is used to
randomize the result..

2) SQL Server certificates store real certificates, which are RSA
public+private keys. You don't need to have a master key in the database to
create a certificate, you can just encrypt the certificate using a password.

3) Knowing the name of a key object is not sufficient to allow you access to
that object. Look at Books Online to find out more about the permissions
required to encrypt/decrypt. If you protect keys using passwords, then in
addition to permissions you also need to know the password that protects the


Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.


SQL Server Encryption Questions

Post by Mike C » Thu, 02 Nov 2006 11:47:56