SQL 2000 DBA Toolkit Part 1
-
The advantage of using the user credentials is that you don't have to pass the plain text password "over-the-wire" to the server for every encryption/decryption. There's really no performance benefit to decrypting and re-encrypting. If you plan on doing this on a regular basis, I would recommend creating a stored procedure to perform the decryption/re-encryption for you so you get the same process every time. If you do it in a stored procedure, you might be able to get some performance benefit by doing the conversion in chunks; e.g. decrypt/re-encrypt 10,000 rows at a time instead of all at once; although I haven't tested that myself to be able to tell you for sure.
-
Hi Mike, I' m back to changing the password issue. I am thinking, since the password (second password) is used only to encrypt and decrypt the master key, then it should be possible to change the password without having to create a new set of master/localkeys, right?? In other words, you can use the password to decrypt the master key and then use a new password to encrypt the master key and put it back in the table master_key_vault. The local key doesn't have to change, in this scenario, right?
Please let me know what do you think so far.
Thank you, Nick
-
Yes, you should be able to decrypt and re-encrypt the master key with a new password without having to decrypt/re-encrypt everything else. That's the advantage of layering the keys, ANSI style
.As for the keys, if you change the master key, you'll need to decrypt and re-encrypt all the local keys it encrypts. If you change a local key, you'll have to decrypt and re-encrypt all the data it protects.
-
Hi Mike, so far so good.
I never worked with c++, I was trying to reproduce the code in vb using the windows api, I am basically using the same functions to acquire context, CryptDecrypt etc, I have also imported the key container on my machine where I run the code but....it's not working
After I updated the master key in the database, encrypted with the new password, I noticed that the hex representation of the key is longer then the previous one, all other master keys (I have like 4 of them) have the same length except the new one is longer... I know I must be doing something wrong, any suggestions will be appreciated..
Thank you, Nick
-
If you haven't yet, download the C++ source code and use it as a guide for the conversion. The code for the extended proc that creates the master key, and the XP that encrypts the local keys with the master keys are the ones you want to look at.
-
Has anyone gotten this to compile in VC++ 8 (Visual Studio 2005) or am I stuck with VC++ 7.1? If you got it compiled in VC++ 8, I'd love to hear how you went about it.
Thanks!
Rick
-
The link for the source code does not seem to be working now (http://www.sqlservercentral.com/products/mcoles/default.asp), would it be possible for you to supply a new link?
Nicklas Blanchard
-
The source code link is still not working. (http://www.sqlservercentral.com/products/mcoles/default.asp), would it be possible for you to supply a new link?
-
I have looked at this toolkit yesterday and it is really wonderful.
I have instelled it for testing on 2 windows 2000 boxes and it works great.
on third box while installing scripts i got this error
Step 1. Searching for Master Key Vault.
Master Key Vault located in database.
Step 2. Generating local key.
Msg 1, Level 1, State 1
An MS Crypto API cryptographic service handle could not be acquired.
Server: Msg 515, Level 16, State 2, Procedure Create_Local_Key, Line 116
Cannot insert the value NULL into column 'Key', table 'master.dbo.Local_Key_Vault'; column does not allow nulls. INSERT fails.
Local Key Vault located in this database.
Step 3. Checking for existence of local key in vault.
Step 4. Saving Local Key in Vault.
The statement has been terminated.
Step 5. Finished.
The only difference I have seen on this sql server is that we r using it as an instance
like name of sql server is dt-geo12\geo12
so the physical binn path becomes
c:\Program Files\Microsoft SQL Server\MSSQL$geo12\binn
Can anybody suggest anything?
Thanks
-
Hi Mike,
I've used your original SQL Encryption toolkit and now replacing it with the DBA Toolkit.
I appreciate what you have done for the SQL community by releasing this toolkit.
I've implemented your toolkit and have created a layer on top of it which consists of views and instead of triggers which auto encrypt/decrypt data being inserted/retrieved.
Client application is unaware of the encryption, which is quite useful when encryption is needed but client application should remain unchanged.
One question: moving a SCRYPTO keystore to a different machine.
I've tried your sample .NET code which runs ok but toolkit does not seem to decrypt same value on 2 different machines.
I'm trying to locate some Microsoft tools but not sure which one will do the trick. Can you offer any suggestions?
Thanks
Ed
-
Im having the same problem. Im trying to figure out how to transfer to a new machine.
Please help.
-
I've solved the issue of exporting/importing RSA key container SCRYPTO from one machine to another.
The issue was revolving around the machine vs. current user key containers.
Original C# code provided by Michael Cole was working but it was importing a key container at the current user level. All I had to do is to add one extra line while importing to indicate that we're working at the machine level.
"cp.Flags = CspProviderFlags.UseMachineKeyStore;"
Below is the modified and tested C# code:
using System;
using System.Collections.Generic;
using System.Text;
using System.Security.Cryptography;
using System.Xml.Serialization;
using System.IO;
namespace certMgr
{
class Program
{
static void Main(string[] args)
{
try
{
Console.WriteLine("This app is used to export or import SCRYPTO keystore used by the DBAToolkit.");
Console.Write("Please input E(xport) or I(mport): ");
string strResp = Console.ReadLine();
if (strResp == "E")
{
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();
}
else
{
if (strResp == "I")
{
CspParameters cp = new CspParameters();
cp.KeyContainerName = "SCRYPTO";
cp.Flags = CspProviderFlags.UseMachineKeyStore;
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);
}
else
Console.WriteLine("Error: incorrect parameter!");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
}
}
-
Hello,
Has anybody used this on clustered servers??. Also does the vault have to reside on the master db?
Thanks
-
hi Mike,
I have same problem, do i have to do any correction to make this work,
Thanks,
Leo
cdsingh (3/5/2008)
I have looked at this toolkit yesterday and it is really wonderful.I have instelled it for testing on 2 windows 2000 boxes and it works great.
on third box while installing scripts i got this error
Step 1. Searching for Master Key Vault.
Master Key Vault located in database.
Step 2. Generating local key.
Msg 1, Level 1, State 1
An MS Crypto API cryptographic service handle could not be acquired.
Server: Msg 515, Level 16, State 2, Procedure Create_Local_Key, Line 116
Cannot insert the value NULL into column 'Key', table 'master.dbo.Local_Key_Vault'; column does not allow nulls. INSERT fails.
Local Key Vault located in this database.
Step 3. Checking for existence of local key in vault.
Step 4. Saving Local Key in Vault.
The statement has been terminated.
Step 5. Finished.
The only difference I have seen on this sql server is that we r using it as an instance
like name of sql server is dt-geo12\geo12
so the physical binn path becomes
c:\Program Files\Microsoft SQL Server\MSSQL$geo12\binn
Can anybody suggest anything?
Thanks
-
:w00t:I am getting this error when importing the params file:
System.Security.Cryptography.CryptographicException: Access is denied.
at System.Security.Cryptography.CryptographicException.ThrowCryptogaphicException(Int32 hr)
at System.Security.Cryptography.Utils._GenerateKey(SafeProvHandle hProv, Int32 algid, CspProviderFlags flags, Int32 keySize, SafeKeyHandle& hKey)
at System.Security.Cryptography.Utils.GetKeyPairHelper(CspAlgorithmType keyType, CspParameters parameters, Boolean randomKeyContainer, Int32 dwKeySize, SafeProvHandle& safeProvHandle, SafeKeyHandle& safeKeyHandle)
at System.Security.Cryptography.RSACryptoServiceProvider.GetKeyPair()
at System.Security.Cryptography.RSACryptoServiceProvider..ctor(Int32 dwKeySize, CspParameters parameters, Boolean useDefaultKeySize)
at System.Security.Cryptography.RSACryptoServiceProvider..ctor(CspParameters parameters)
at DBACertManager.CertManager.Main(String[] args)
My machine.config has trust =FULL
Any ideas
Viewing 15 posts - 46 through 60 (of 72 total)
You must be logged in to reply to this topic. Login to reply