Sign a document by SmartCard on the .NET client and verify on the SQL 2005 server

Hi!
I'm working on the following application:

In the client application (written in C#) an user signs a document by private key, stored on chip card. Signed document and the signature are stored into database on SQL 2005 server.
There is my client code for demonstration:< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Client application sample:

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=Test;Integrated Security=True");
conn.Open();
try
{
   CspParameters csp = new CspParameters(1, "Schlumberger Cryptographic Service Provider");
   csp.KeyNumber=1;

   // Initialize an RSACryptoServiceProvider object using
   // the CspParameters object.
   RSACryptoServiceProvider rsa = new RSACryptoServiceProvider(csp);

   // Create some data to sign.
   string text = "plain text to sign";

   // Sign the data using the Smart Card CryptoGraphic Provider.
   byte[] sig = rsa.SignData(Encoding.Unicode.GetBytes(text), "SHA1");

   Console.WriteLine("Signature:\t" + BitConverter.ToString(sig));

   // Write to db
   SqlCommand cmd = new SqlCommand("insert into signed (data, sign) values (@data, @sign) ", conn);
   SqlParameter parData = new SqlParameter("@data", text);
   cmd.Parameters.Add(parData);
   SqlParameter parSign = new SqlParameter("@sign", sig);
   cmd.Parameters.Add(parSign);
   cmd.ExecuteNonQuery();

   // Verify the data using the Smart Card CryptoGraphic Provider.
   bool verified = rsa.VerifyData(Encoding.Unicode.GetBytes(text), "SHA1", sig);
   Console.WriteLine("Verified:\t" + verified);
}
finally
{
   conn.Close();
}

The document and the signature are stored into the Signed table in the Test database:

use [Test]
CREATE TABLE [dbo].[signed](
[id] [int] IDENTITY(1,1) NOT NULL,
[data] [nvarchar](50) COLLATE Czech_CI_AS NULL,
[sign] [varbinary](max) NULL,
CONSTRAINT [PK_signed] PRIMARY KEY CLUSTERED
) ON [PRIMARY]

Now I want check signature validity on server side.
I found the certificate stored on my chip card in certificate store Current User/Personal/Certificates (the Certificates MMC snapin). I exported it into the binary DER encoded file (cer extension).
Then I imported this certificate into the database [Test] using CREATE CERTIFICATE statement:

CREATE CERTIFICATE testCert
FROM FILE='c:\temp\My Test Certificate.cer'

Now, I want to verify signature by the VerifySignedByCert() statement:

select VerifySignedByCert( Cert_ID('testCert'), data , [sign] ) as verified FROM signed

The VerifySignedByCert returns 0. I don't understand WHY
WHAT'S WRONG

Please help me!

Thankyou
Fipil.

Answer this question

Sign a document by SmartCard on the .NET client and verify on the SQL 2005 server

BodeColander
Hi Andrei!!

I have exactly the same problem you mention in you post. Did you find any solution

Any help will be really apreciated.
SPAWN_UK
VerifySignedByCert currently expects the signature to have been computed with an MD5 hash, like it would be if it was computed using SignByCert. This is why the builtin cannot verify the signature that you computed. We are looking into expanding these builtins to use various other hashing algorithms.

Thanks
Laurentiu
ASedorian

Have you verified whether the certificate that you imported into the server is the same that was used to sign the data Also, try signing using SignWithCert as well and see if the signature is the same as the one that was computed by your program. If there is a difference, then either you did not use the same certificate or there is a difference in the signing algorithms. In the latter case, you could write your own CLR function in SQL Server to verify the signatures computed by your program.

Thanks
Laurentiu
Arjun
I can't try signing using SignWithCert, because the private key of the certificate is stored on the chip card. SQL server didn't use the key and a resulting signature is NULL.
But, I'll try creating another certificate with private key stored on the machine certificate store.
Writting my own CLR function is good idea, I'll try it.
thanks a lot!

Fipil

Wendy Tu

Laurentiu, thanks for response.
I changed the sample client code to direct using private key from selected certificate.
But, it doesn't work even if i changed the hash algorithm to MD5 too.

Here is the changed code:

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=Test;Integrated Security=True");
conn.Open();
try
{
   // Open the My certificate store.
   X509Store storeMy = new X509Store(StoreName.My,
      StoreLocation.CurrentUser);
   storeMy.Open(OpenFlags.ReadOnly);
   X509Certificate2Collection certs=X509Certificate2UI.SelectFromCollection
      (storeMy.Certificates, "Selection", "Select a certificate",
      X509SelectionFlag.SingleSelection);
   X509Certificate2 cert=certs[0];
   storeMy.Close();

   // Initialize an RSACryptoServiceProvider object using
   // the certificate PrivateKey
   RSACryptoServiceProvider rsa = (RSACryptoServiceProvider)cert.PrivateKey;

   // Create some data to sign.
   string text = "plain text to sign";< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

   // Sign the data using the Smart Card CryptoGraphic Provider.
   byte[] sig = rsa.SignData(Encoding.Unicode.GetBytes(text), "MD5");

   Console.WriteLine("Signature:\t" + BitConverter.ToString(sig));

   // Write to db
   SqlCommand cmd = new SqlCommand("insert into signed (data, sign) values (@data, @sign) ", conn);
   SqlParameter parData = new SqlParameter("@data", text);
   cmd.Parameters.Add(parData);
   SqlParameter parSign = new SqlParameter("@sign", sig);
   cmd.Parameters.Add(parSign);
   cmd.ExecuteNonQuery();

   // Verify the data using the Smart Card CryptoGraphic Provider.
   bool verified = rsa.VerifyData(Encoding.Unicode.GetBytes(text), "MD5", sig);
   Console.WriteLine("Verified:\t" + verified);
}
finally
{
   conn.Close();
}

What's wrong

Thanks!
Fipil

univac_les
Hi brothers ! I'm having the same problem to access the private key of the certificate stored on the chip card.

If i access the private key of a certificate with private key stored on the machine certificate store it works well.....but in the case of the chip card, when i read the X509Certificate2.PrivateKey property, i receive a System.Security.Cryptography.CryptographicException: There are more data available.

If some of you discover something about it, please post here !

thanks !!!
guthm114

You may have more success by posting such inquiry on a security CLR forum. This isn't really a SQL Server specific issue.

Thanks
Laurentiu
Bheki

Hello,

Can you share me code of signing documents please

Best Regards,

Mehmet Ceylan
Sign a document by SmartCard on the .NET client and verify on the SQL 2005 server

Answer this question