SQL 2000 DBA Toolkit Part 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.

  • 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?






  • 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:



    Server B:




    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,





    select @encText

    EXEC master.dbo.xp_decrypt_aes


     @plaintext OUTPUT,





    select convert(varchar,@plaintext)

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

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

  • 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.

  • Excellent toolset, very useful, thanks

  • 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',



  • 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.


  • 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...) 


  • 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,


  • 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

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

  • 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

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

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