I didn't - but isn't that why I am encrypting the data - so that if somebodu does get a login all hell see is gibirsh.

If the user does not get a login in the first place - then what's the problem , why even encrypt?

Ok - sorry again but I am having trouble running the functions from a non master database - I keep on getting

Could not find stored procedure 'dbo.xp_encrypt_aes'. The statement has been terminated.

Do you have any advice thanks.

Extended stored procedures live in the master database.  The script to install the functions should be installing them to the master database as well.  Did the install script you downloaded not install the functions to the master database?  If you are accessing the functions from another database, the format is master.dbo.function_name.

I guess I'm not understanding your other question.  Column level encryption prevents someone from being able to see the raw data in the tables.  It's part of an overall security system that includes physical security, communication security, etc.  If someone were to get an admin login, for instance, they will be able to do pretty much anything they want to do on your server.

Also regarding logging in - I am in a funny situation where I have users (i.e. other developers) logging in to the db with full rights (it would be very hard to lock down access to this one table  etc...) - and I don't want them to be able to see the data in a specific field.

I guess for my situation the best approach would be for the app layer (which they do not have access to) to send the data preencrypted to the database. is that correct?

Take a look at the documentation   If you specify NULL as password2 for the Master Key, it ties data encrypted with it to the account under which it's encrypted.  So you can tie decryption of data to the same account that it was encrypted with by not supplying the second password.  That means that not only would a maluser need a login to decrypt your data, they need the same login that was used to encrypt the data.

This is more secure, and can help prevent other users from getting into your encrypted data.  The downside is that only the account that encrypted the data can decrypt the same data.

Nice work!  You should get some kind of award for this.  It is very cool.

Can I see the code behind the following DLL's? I'm concerned I may need it for my internal documentation in case anyone asks how it works:

• xp_decrypt_aes.dll
• xp_encrypt_aes.dll

Also, do you know if it is possible to create extended stored procedure DLL's with Visual Basic .NET?  Or is C# required?

Thanks!

Rick

MCP,MCAD,MCSD

After installing the extended procs, etc I ran into an issue passing encrypted data from one db server to another.  I wrote some simple SQL to show this.  If I run the below SQL on server A and then server B I get diferent values for the encrypted data.  So when I encrypt data on Server A and then attempt to decrypt on Server B, I get Null.

As you can see, I bypassed the key tables and am passing the master and local keys directly.  Is there something else that the encryption code is using that would make encryption values unique for each server?

Server A:

0x424E750E96151DB65F6C6750A4ABA91A
test

Server B:

0x19E7D34C42A2271F34BB69CFC2529173
test

DECLARE @encText VARBINARY(8000)
DECLARE @plainText VARBINARY(8000)
declare @tmp varbinary(100)

set @tmp = convert(varbinary,'test')

EXEC master.dbo.xp_encrypt_aes @tmp,
 @encText OUTPUT,
 'pw12345',
 0x2BFD1CC6C094293DD6FD053809B68E3517BA255101BE5AA55DE3EBC501E7BFD1DBB59FE722263CB4699830871627570F1E73DD5FAA23A5BEC642218C109A524F,
 0xF2909C4FE472D92D1221747CAFE59D8BA078CEDC8AECB3BC3D83BBE7F595C585115D9CCEAC263C4B5884B5958EB82CBC813114E8A3192172F18D8540B268E00AB2A70BA9573708BD2419A26E2AA78159,
 256
select @encText

EXEC master.dbo.xp_decrypt_aes
 @encText,
 @plaintext OUTPUT,
 'pw12345',
 0x2BFD1CC6C094293DD6FD053809B68E3517BA255101BE5AA55DE3EBC501E7BFD1DBB59FE722263CB4699830871627570F1E73DD5FAA23A5BEC642218C109A524F,
 0xF2909C4FE472D92D1221747CAFE59D8BA078CEDC8AECB3BC3D83BBE7F595C585115D9CCEAC263C4B5884B5958EB82CBC813114E8A3192172F18D8540B268E00AB2A70BA9573708BD2419A26E2AA78159,
 256
select convert(varchar,@plaintext)