April 17, 2007 at 3:06 pm
April 27, 2007 at 6:10 pm
I have been struggling with the same exact problem.
I spent 3 days on it and was able to get as far as using the SQL Server Certificate's public key inside my C# app to encrypt via RSA. The encryption worked but once the records were written to the database, DecryptByCert was unable to decrypt them with it's private key.
The same records could be decrypted if encrypted on the SQL Server.
I used the System.Security.Cryptography namespace in the .NET Framework to generate the RSA. It has a Certificates namespace which allows for easy importing and hashing.
The only workaround I could come up with was to encrypt at the client, send to host, decrypt on host, write to SQL and re-encrypt using a SQL Server key. Seems like a waste of CPU to encrypt the data twice, but I posted my issues on MSDN and nobody seems to care.
If you're interested, here's a copy of my MSDN post:
My app is supposed to run in an occasionally disconnected environment with
SqlCE to cache data which is syncronized at intervals.
I thought it would be wise to use a certificate encryption scheme to allow
the cleint to encrypt sensitive data with the central servers public key
derived from a .cer certificate file.
The client-side code looks something like this:
byte[] bytes = [My Un-Encrypted Value];
byte[] ebytes;
cert2 = new X509Certificate2("C:\\MyCertificate.cer");
using (RSACryptoServiceProvider provider = new RSACryptoServiceProvider())
{
X509KeyStorageFlags.DefaultKeySet);
provider.FromXmlString(cert2.PublicKey.Key.ToXmlString(false));
ebytes = provider.Encrypt(bytes, true);
}
Then the data is written into a varbinary(max) column on the central server
with the following code:
SqlConnection cn = new SqlConnection([connectionstring]);
cn.Open();
SqlCommand cmd = cn.CreateCommand();
SqlParameter parm = new SqlParameter("@Parameter", the);
cmd.Parameters.Add(parm);
cmd.CommandText = "insert into test values(@Parameter)";
object ip = cmd.ExecuteScalar();
When I check the Central DB, the cypher is getting inserted into the table.
However, when I try to view the data:
select convert(varchar(10), decryptbycert(Cert_Id(TheCert),
[FieldToEncrypt])) from [Table]
I consistently get NULL.
The code used to back up the certificate to a file is as follows:
BACKUP CERTIFICATE [CertificateName] TO FILE = 'c:\[folder]\[file].cer'
The certificate was generated with makecert.exe using the following command:
C:\"Program Files"\"Microsoft Visual Studio
8"\Common7\Tools\Bin\makecert.exe -sv [name].pvk -n "CN=[Name]" -pe -a sha1
-e 01/01/2020 -r -sky exchange [Name].cer
The certificate was created / imported into SQL Server 2005 with the
following statement:
CREATE CERTIFICATE [Name]
FROM FILE = 'c:\[Folder]\[File].cer'
WITH PRIVATE KEY (FILE = 'c:\[Folder]\[File].pvk',
DECRYPTION BY PASSWORD = '[Password');
Strangely, when I try :
update
set [column] = encryptbycert(cert_id('[CertName]'),
convert(varbinary(max), [SomeValue]))
and then execute the following:
select convert(varchar(10), decryptbycert(Cert_Id(TheCert),
[FieldToEncrypt])) from [Table]
The data decrypts fine. The issue is only when I try to encrypt using the
System.Security.Cryptography API.
For some reason the public key is generating a different algorithm on .NET
than in SQL Server.
This has taken me the better part of a day to struggle through and I'm not
able to narrow it down.
Some other things I've tried:
System.Security.Cryptography.X509Certificates.X509Certificate2 cert2 = new
X509Certificate2("C:\\[Name].cer");
byte[] bytes = [Data-To-Encrypt];
ContentInfo contentInfo = new ContentInfo(bytes);
EnvelopedCms envelopedMessage = new EnvelopedCms(contentInfo);
CmsRecipient recipient = new
CmsRecipient(SubjectIdentifierType.IssuerAndSerialNumber, cert2);
envelopedMessage.Encrypt(recipient);
byte[] encryptedBytes = envelopedMessage.Encode();
...Insert encryptedBytes to db sing ADO.net...
Furthermore, the same erratic behavior is happening with Asymmetric key
encryption. I tried manually instantiating an Asym key using
SqlServer.Management.SMO with the following code:
Microsoft.SqlServer.Management.Smo.Certificate cert = new Certificate();
Microsoft.SqlServer.Management.Common.ServerConnection cn = new
Microsoft.SqlServer.Management.Common.ServerConnection("[Server]",
"[UserID]", "[Password]");
Server svr = new Server(cn);
Database db = new Database(svr, "[DataBaseNme]");
Microsoft.SqlServer.Management.Smo.AsymmetricKey ak = new
AsymmetricKey(db, "[AsymKeyName]");
ak.Refresh();
byte[] key = ak.PublicKey;
Followed by:
System.Security.Cryptography.RSACryptoServiceProvider provider = new
RSACryptoServiceProvider();
System.Security.Cryptography.RSAParameters p = new
RSAParameters();
p.Modulus = key;
byte[] Exponent = {1,0,1};
p.Exponent = Exponent;
provider.ImportParameters(p);
byte[] bytes =
System.Text.ASCIIEncoding.ASCII.GetBytes("[String-Data-T-Encrypt]");
...Inserty bytes into table in database using ADO.net.
Whenever I try to decrypt the data local (on the server), the result is
always null. However, the server can decrypt it's own encryption.
I'm trying to figure out where the public key is failing.
Any help would be greatly, and sincerely appreciated.
Thanks!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply