SQL 2000 DBA Toolkit Part 1

Question

SQL 2000 DBA Toolkit Part 1

Viewing 15 posts - 31 through 45 (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

Hi Nick,

I don't have much time to write a key container import/export program right now, but you might be able to use aspnet_regiis to export and import the key containers (see http://msdn2.microsoft.com/en-US/library/2w117ede.aspx). In .NET code you can use the System.Security.Cryptography namespace to import and export the appropriate information. Here's a sample in C# to steer you in the right direction (I did not thoroughly test this - this is just some starter code):

static void Export()

{

CspParameters cp = new CspParameters();

cp.KeyContainerName = "SCRYPTO";

RSACryptoServiceProvider rsa = new RSACryptoServiceProvider(cp);

RSAParameters p = rsa.ExportParameters(true);

XmlSerializer x = new XmlSerializer(typeof(RSAParameters));

MemoryStream ms = new MemoryStream();

x.Serialize(ms, p);

ms.Flush();

StreamWriter sw = new StreamWriter(@"c:\scrypto.params", false, Encoding.UTF8);

sw.Write(System.Text.Encoding.ASCII.GetString(ms.ToArray()));

sw.Close();

ms.Close();

}

static void Import()

{

CspParameters cp = new CspParameters();

cp.KeyContainerName = "SCRYPTO";

RSACryptoServiceProvider rsap = new RSACryptoServiceProvider(cp);

RSAParameters p = new RSAParameters();

XmlSerializer x = new XmlSerializer(typeof(RSAParameters));

Stream reader = new FileStream(@"c:\scrypto.params", FileMode.Open);

p = (RSAParameters)x.Deserialize(reader);

reader.Close();

rsap.ImportParameters(p);

}

To finish up, you'll want to back up the encrypted passwords used to encrypt keys and data in your database. Per the documentation, these are stored in the Local_Key_Vault and Master_Key_Vault tables in the master database.

I pulled out the source code and zipped it up, and I'll send it to you personally if you send me your email address.

Thanks

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

Hi Mike, thank you so much.

I was able to get the source code from Steve in the meantime.

I will test the import/export code for the key container, I'll keep you posted.

Thanks again,

Nick

Anukul SSChasing Mays Points: 624 More actions · Answer 3

Hi Mike!

I am getting an error message while installing the DBA ToolKit for SQL 2000. Below is what I receive on executing the Install.sql script. Let me what I can do to correct this.

------------------------------------------------------------------------****Starting Installation

****----------------------------------------------------------------

****Installing Key Management Extended Procedures

**** Master Key

**** Local Key

****Creating Key Management Stored Procedures

**** Master Key

**** Local Key

****Installing Cryptographic Extended Procedures

**** ROT-13

**** Triple DES (3DES)

**** AES/Rijndael

**** Twofish

**** DES

**** Blowfish

****Installing Regular Expressions

**** Search

**** Split

**** Match

**** Replace

****Installing Hash Function

**** SHA(2)

****Installing Conversion Routines

**** Base64

****Installing File Routines

**** DirScan

****Installing String/Phonetic Routines

**** ProperCase

**** Jaro-Winkler Edit Distance

**** Levenshtein Edit Distance

**** Double Metaphone

**** NYSIIS

**** Celko Improved Soundex

**** Daitch-Mokotoff Soundex

****Installing Calendar Functions

**** Modulo

**** Day of Week Occurrence

**** Installing Easter Calculator

**** Holiday Calculator

****Installing Master Key Vault

Server: Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column 'Key', table 'master.dbo.Master_Key_Vault'; column does not allow nulls. INSERT fails.

ODBC: Msg 0, Level 16, State 1

Cannot load the DLL C:\Program Files\Microsoft SQL Server\MSSQL\Binn\xp_create_master_key.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).

Step 1. Searching for Master Key Vault in database.

Master Key Vault already exists in this database. Skipping vault creation step.

Step 2. Creating Master Key.

The statement has been terminated.

Problem adding master key Master Key 1. Stopping now.

Step 3. Finished.

****Installing Local Key Vault

Step 1. Searching for Master Key Vault.

Master Key Vault located in database.

******* Could not locate Master Key 'Master Key 1'.

****----------------------------------------------------------------

****Install Complete

------------------------------------------------------------------------

Thanks!

Anukul

[font="Verdana"]
-------------------------------------
Anukul
My Blog
My Twitter Profile
My Stumbles[/url]
[/font]

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

Hi Anukul,

This particular issue is noted in the documentation. Usually it means that SQL Server could not load one of the supporting DLL's: MSVCR71.DLL or OPENDS60.DLL. The most likely reason is that one of them is not in the system path. Copy these two DLL files to the MSSQL\BINN directory and it should resolve the issue. There are more suggestions in the documentation.

Thanks

Anukul SSChasing Mays Points: 624 More actions · Answer 5

Hi Mike!

I have been successfull in installing the ToolKit on SQL Server2000. Thanks for your help.

I observed that all the functions are installed in Master database, so all queries have to be executed in master taking the DB.dbo.tablename route. Can these functions also be installed in the respective database in which encryption/decryption has to be done?

That way, sql operations will become a lot easier and faster.

Please suggest.

Thanks,

Anukul

[font="Verdana"]
-------------------------------------
Anukul
My Blog
My Twitter Profile
My Stumbles[/url]
[/font]

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

The functions can be modified to run from a different database from master; however, I don't know that you will get much of a performance improvement because the key_vault tables and the extended procedures are in the master database. Because the functions call extended stored procedures, you cannot get away from accessing the master database.

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

FYI to Everyone:

edtited: Contact the webmaster for the source.

Compilation of some partsrequires Boost Library source (in particular the Regular Expression extended procedures require Boost). Boost source is available for free download at http://www.boost.org/.

If you do download and compile the source, I highly recommend downloading the latest Platform SDKfrom http://www.microsoft.com/downloads/details.aspx?FamilyId=A55B6B43-E24F-4EA3-A93E-40C0EC4F68E5&displaylang=en.

I've also been asked several times about the license for this toolkit. Here it is:

DBA Toolkit License

The DBA Toolkit is composed of my original source code, and/or other source code that the original authors have placed in the public domain. Credit has been given throughout the source code to those original authors, and their original licensing agreements have been maintained in the source code where appropriate.

You are free to usethis source code and binarieshowever you want, modify it however you like, anduse itfor any purpose you like, personal or commercial. All I ask is two things:

1. If you redistribute the source code, modified or unmodified, give me a shout out in the comments or something

2. If you do something really cool and make big and/or useful improvements, consider sharing it with the rest of the world (this is not mandatory, but it is the nice thing to do). I'm particularly interested in the improvements people make to the source, and always appreciate the feedback.

Obviously the Boost Library is covered by Boost's own licensing agreement which you will need to observe if you decide to use the portions of code that interface with it (the Regular Expression functionality). And Microsoft has their own licensing agreements you might need to review when before distributing programs compiled using the Platform SDK.

Anukul SSChasing Mays Points: 624 More actions · Answer 8

Mike:

Thanks for the response!

So, can the whole set of procedures, extended procedures along with Key Vault tables be installed in one particular database in which this has to be used, instead of Master database?

Also, if thats possible, then these can be installed in each database where encryption is required (i know that may not be advisable). But still can that too be done?

Let me know.

Thanks,

Anukul

[font="Verdana"]
-------------------------------------
Anukul
My Blog
My Twitter Profile
My Stumbles[/url]
[/font]

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

Extended Stored Procedures, by definition, can only be installed in the master database. The other functions, procedures, and tables can be modified to work from another database, but they must still have access to the extended procedures in the master database.

Anukul SSChasing Mays Points: 624 More actions · Answer 10

Mike:

I have created 2 of the other functions, in a specific database, in which I have to use the encryption. I modified these functions to lookup the master database for the extended functions and the Vault Tables.

All seem to work fine now. Do you think there may be any kind of problems later?

-Anukul

[font="Verdana"]
-------------------------------------
Anukul
My Blog
My Twitter Profile
My Stumbles[/url]
[/font]

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

As long as you reference the XP's correctly in the master database, I don't see a problem. If you look through the Stored Procedure and UDF code, you'll be able to see all the references to objects in the master database.

Anukul SSChasing Mays Points: 624 More actions · Answer 12

Thanks so much Mike !

Cheers,

Anukul

[font="Verdana"]
-------------------------------------
Anukul
My Blog
My Twitter Profile
My Stumbles[/url]
[/font]

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

Mike, if I use a password to create the master key/local key, then I have to use the same password everytime to encrypt/decrypt data. Question is, how do I change the password? I can decrypt the data, create a new master/local key with a new password and then encrypt the data again, but is there any other way for me to change the password?

Thank you, Nick

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

Hi Nick,

That's the method that has to be used. I didn't create an automated method of decrypting/re-encrypting data (like in SQL 2005) for this version; particularly because I didn't want to get into the complexity of trying to keep track of which encryption key was used on which columns. To do something like that would require storing the ID of the encryption key with the data (like in SQL 2005), and I didn't jump into that with this version - so it has to be done manually; although you could create a stored procedure to help automate the process.

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

Hi Mike, I just wanted to make sure there was no other easier way. For updating the data, I can use something like encryt(decrypt(data,password1),password2) in one single update query (of course, after I have created the second master/local key)

Also, as an alternative I could use the user's credentials instead of the password from the beginning.

Considering we might need to change the password from time to time, and there is some large amount of data that needs to be encrypted, which method would you recommend?

Thank you, Nick