Encrypting off-site with certificate's public key
- From: Losing my composure <Losing my composure@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 18 Apr 2007 06:12:03 -0700
First of all, sorry for the long post. I'm trying to be as detailed as
possible...
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 [table] 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!
.
- Prev by Date: Re: How secure is the 2005 Management Studio?
- Next by Date: Re: How secure is the 2005 Management Studio?
- Previous by thread: Re: How secure is the 2005 Management Studio?
- Next by thread: Production DBs are going to suspect mode automatically
- Index(es):
Relevant Pages
|
