SQL 2000 DBA Toolkit Part 1

Question

SQL 2000 DBA Toolkit Part 1

Viewing 15 posts - 16 through 30 (of 72 total)

You must be logged in to reply to this topic. Login to reply

Mike C SSC-Insane Points: 23224 More actions · Answer 1

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.

Richard Busse SSC Rookie Points: 40 More actions · Answer 2

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

Scott-364712 SSC Rookie Points: 26 More actions · Answer 3

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)

Mike C SSC-Insane Points: 23224 More actions · Answer 4

Yes, the encryption is tied to the local machine via the CryptoAPI. This is by design.

Scott-364712 SSC Rookie Points: 26 More actions · Answer 5

Is there any way around this? I need a way to implement public/private key encryption.

Mike C SSC-Insane Points: 23224 More actions · Answer 6

This package doesn't have any asymmetric encryption routines. All of the encryption algorithms implemented are symmetric encryption algorithms. (See Windows Help file for more info). If I were going to implement my own asymmetric encryption XP's, I'd probably start by checking out the asymmetric algorithms available via the CryptoAPI (documentation on MSDN).

BTW, asymmetric encryption is normally recommended for encrypting small pieces of data like symmetric encryption keys that secure your data, because it is orders of magnitude slower than symmetric encryption.

NB-373550 Newbie Points: 5 More actions · Answer 7

NB-373550

Newbie

Points: 5

October 10, 2006 at 2:16 pm

#665086

Excellent toolset, very useful, thanks

Mike C SSC-Insane Points: 23224 More actions · Answer 8

Thanks.

There was a question about how to access the functions from a database other than the master database, but I didn't have a chance to respond previously. Just use 3-part naming in your non-master database:

SET Enc_Fake_CC = master.dbo.fn_encrypt_3des(CAST(Fake_CC AS VARBINARY(16)),

'Local Key 1',

NULL)

Thanks.

nicu paraschiv SSC Enthusiast Points: 139 More actions · Answer 9

Hi Mike, I have a question please:

If the keys are created using the user's credentials at that time, what would happened if the user's password gets changed later on. Will I still be able to decrypt previously encrypted information?

Can you please clarify?

Thank you in advance; it's a very useful tool.

Nick

Mike C SSC-Insane Points: 23224 More actions · Answer 10

I'm not using the user's password to secure the keys - I use other identifying data about a user that do not change as often, so a password change will not affect a user's ability to use a key. Dropping and re-creating a user, changing a user's login ID, changing the domain name, etc., will affect a user's ability to use a password tied to their login credentials, however. But those are far less common scenarios (I think, anyway...)

Thanks

nicu paraschiv SSC Enthusiast Points: 139 More actions · Answer 11

Hi Mike, I have a question for you please:

If we use a password to create the master key, instead of a user's login credentials, I will be unable to decrypt the master key on a different machine, using the same password.

What happens if we have a server crash and we need to reinstall the whole machine from scratch? After restoring the database back up, we will still be able to decrypt the data?

Thank you in advance,

Nick

Mike C SSC-Insane Points: 23224 More actions · Answer 12

You need to export the key container and re-import on the target machine. Here are some links with more information:

http://search.msdn.microsoft.com/search/default.aspx?query=export+key+container&siteid=0&tab=0

http://msdn2.microsoft.com/en-us/library/aa380252.aspx

http://msdn2.microsoft.com/en-us/library/ms867088.aspx

http://msdn2.microsoft.com/en-us/library/ms867080.aspx

nicu paraschiv SSC Enthusiast Points: 139 More actions · Answer 13

Hi Mike, it’s a lot of information in there, but I'll have to read it

Thank you for your help.

Mike, I noticed that in a previous version of the toolkit you also provided the source code for the dlls.

Can you provide the source for this version as well, for the AES algorithm?

Thank you, Nick

Mike C SSC-Insane Points: 23224 More actions · Answer 14

I've sent the source code to Steve to post when he has an opportunity.

nicu paraschiv SSC Enthusiast Points: 139 More actions · Answer 15

Hi Mike, I am still trying to figure out how to import/export the keys on a different machine; I am not very familiar with the terms and the CryptoAPI thing.

The whole issue comes from the fact that if you loose the machine, even if you re-install and restore the database, the encrypted information will be useless because the decryption is not working anymore, so there has to be a way of exporting the ...key, I guess??

If anybody on this forum has already figured out how to do this, please let me know, I would really appreciate it.

Thank you, Nick