By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

SQL 2005 Encryption questions

P: n/a
Hello,
I have been researching the use of symmetic and asymmetic encryption in
SQL 2005 and I am pretty excited to give it a try. Through examples, I
can encrypt the data, but I cant figure out what to do next...

What I want:
1. our social security field to be encrypted so that only the person(s)
that need it can decrypt it.
2. prevent DBA's from decrypting the data themselfs
3. Simple way to encrypt the data on the table (maybe a trigger?)

I thought I would use asymmetric keys, this way I can embed the public
key into my data warehouse process to encrypt the data.
I thought I would prompt the user for the private key when the report
runs, that way I wont store the key on the server.
This would be a place to start.
Someone in the office said that we can store the keys in Active
directory, so maybe I could make this seemless to the user running the
report?
I've found a lot of great articles that got me started, but I am
needing the next step
Any Ideas would be apprecitated!
TIA
Rob

links to articles I have found handy:

http://www.databasejournal.com/featu...nt.php/3483931
http://www.devx.com/dbzone/Article/29232/0/page/3
http://www.sqlservercentral.com/colu...intversion.asp

Apr 26 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
rcamarda (rc******@cablespeed.com) writes:
What I want:
1. our social security field to be encrypted so that only the person(s)
that need it can decrypt it.
2. prevent DBA's from decrypting the data themselfs
3. Simple way to encrypt the data on the table (maybe a trigger?)
Nothing is simple when it comes to encryption. For starters, I hope that
you are perfectly aware of that if you encrypt the SSN, you cannot
use it to find a person, at least not effeciently?

I thought I would use asymmetric keys, this way I can embed the public
key into my data warehouse process to encrypt the data.
I thought I would prompt the user for the private key when the report
runs, that way I wont store the key on the server.


Cryptography is not my best game, but I thought you needed the private
key to encrypt something?

And I as I understand the topic for CREATE ASYMMETRIC KEY, you always
create or load a key-pair into the database.

I think what you should prompt the user for is the password to the
key. And the user will have to specify the password to encrypt the
data as well. At least, that is my understanding of it. But as I said,
I don't know cryptography too well.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 26 '06 #2

P: n/a
Like PGP, I would encrypt with the public key and the private key the
owner would use to decrypt the information.
Example, I want to send you secure information. You would create a Key
pair and give me the public key. I encypt the data with your public key
and send it to you. the only way to be able to read the message is to
decrypt it with your private key.

Apr 26 '06 #3

P: n/a
Update:
I can get this to work on static data:

CREATE ASYMMETRIC KEY Student_aKey
WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'Th1ssuck5'

DECLARE @encryptedstuff NVARCHAR(250)
set @encryptedstuff = EncryptByAsymKey(AsymKey_ID('Student_aKey'),
N'That''s all folks')
SELECT @encryptedstuff
SELECT CAST( DecryptByAsymKey(AsymKey_ID('Student_aKey'),
@encryptedstuff,N'Th1ssuck5' ) AS NVARCHAR)
SELECT CAST( DecryptByAsymKey(AsymKey_ID('Student_aKey'),
EncryptByAsymKey(AsymKey_ID('Student_aKey'), N'That''s all
folks'),N'Th1ssuck5' ) AS NVARCHAR)
However when I use these tecniques on my table, I can not decrypt the
data. So I thought I would emulate the encryption, decryption with the
following:
select
cast(DecryptByAsymKey(AsymKey_ID('Student_aKey'), estudent_ssn,
N'Th1ssuck5') as nvarchar(100))
from (
SELECT
cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), 'supercalifragi') as
nvarchar(100)) AS eSTUDENT_SSN
) as xx

which does not work.
While I was composing this email, I thought I would put a N in front of
my literal:
select
cast(DecryptByAsymKey(AsymKey_ID('Student_aKey'), estudent_ssn,
N'Th1ssuck5') as nvarchar(100))
from (
SELECT
cast(EncryptByAsymKey(AsymKey_ID('Student_aKey'), N'supercalifragi') as
nvarchar(100)) AS eSTUDENT_SSN
) as xx
which worked.
What is the N in front of the literal 'supercalifragi' do? is it a
shortcut for cast(xxx as nvarchar())??

Apr 26 '06 #4

P: n/a
rcamarda (rc******@cablespeed.com) writes:
What is the N in front of the literal 'supercalifragi' do? is it a
shortcut for cast(xxx as nvarchar())??


Sort of. N marks the literal as a Unicode literal.

The idea is not unique to SQL. I C++ you use L for the same purpose.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 27 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.