none
How to sign data in .NET with a X509 certificate, and verify it in SQL Server >= 2005

    Question

  • Hi, 

    I'm working on an application that encrypts/decrypts and signs/verifies data with X509 both in .NET and SQL Server (T-SQL).

    My problem is that .NET creates another X509 signature than SQL does. Data signed by .NET cannot be verified in SQL and vice versa. To debug, I compared the output of the RSACryptoServiceProvider.SignData method with the T-SQL SignByCert and I get different results (for debug, both sides have the private key certificate). So, how exactly do SIGNBYCERT and VERIFYSIGNEDBYCERT work?

    Step 1 of my implementation is the following: Call from C# a SP with @Request nvarchar(max). The @Request contains a semicolon separated string. The first part is a random string, the second part is the X509 signed value (in hex) of part 1. The goal is that the SP can verify if @Request was really sent from the .NET client. 

    I generated a self-signed X509 certificate with the following code:
    makecert.exe -n "CN=My Company,C=NL" -pe -m 120 -sky exchange -a md5 -len 1024 -sv "mycert.pvk" -r "mycert.cer"
    pvk2pfx.exe -pvk "mycert.pvk" -spc "mycert.cer" -pfx "mycert.pfx" -pi mypassword

    I choose MD5 because Laurentiu Cristofor wrote that VerifySignedByCert expects MD5, see http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/bc85bd92-5449-45d1-971d-aef83b85330a/ This post is similar, and not correctly answered.

    This certificate was imported to SQL Server (master key was already set):

    CREATE CERTIFICATE [MyCertificate] 
        FROM FILE = 'mycert.cer' 
        WITH PRIVATE KEY (FILE = 'mycert.pvk', DECRYPTION BY PASSWORD = 'mypassword');
    GO

    C# Code:
    Reads the same X509 from a PFX file (which is the combination of the CER and PVK that went into SQL). I convert the text to Unicode because the T-SQL uses nvarchar. With the private key, I sign the data and convert the output to Hex.

    private static string SignMyData()
            {
                string part1 = "5BC44FA0945347E590EB002F519F1D73";
                X509Certificate2 x509 = new X509Certificate2("mycert.pfx", "mypassword");

                UnicodeEncoding encoding = new UnicodeEncoding();
                byte[] originalData = encoding.GetBytes(part1);            

                // Create an instance of the RSA encryption algorithm
                // and perform the actual encryption
                using (RSACryptoServiceProvider csp = (RSACryptoServiceProvider)x509.PrivateKey)
                {
                    byte[] computedHash = csp.SignData(originalData, new MD5CryptoServiceProvider());

                    string part2 = ByteArrayToHexString(computedHash);

                    return part1 + ";0x" + part2;
                }
            }

            public static string ByteArrayToHexString(byte[] ba)
            {
                string hex = BitConverter.ToString(ba);
                return hex.Replace("-", "");
            }

    SQL-code:

    declare @guiddish nvarchar(32)
    set @guiddish = N'5BC44FA0945347E590EB002F519F1D73'
    declare @signaturemd5 varbinary(max)    
    set @signaturemd5 = signbycert(cert_id('MyCertificate'), @guiddish)

    select @signaturemd5

    EXAMPLE OUTPUT: 0x4A7AF7F1323084A4985995AD21700BB0FB7577E4DF86929A70B4C62DD444F40F9AC60ADBAA4C87E64FABF81DB253A88C65DF0C1A0DEE518170052AC55DCC2E9FD7B0591F27D7A1E959D2EF401BADEE2B9F667142FEA8A41AC74CC0F24E033450FA49B652D95134A5E7218D01E43AB0FD2F7AB5EB09057037AA5F7C10AEBBE97E

    My problem here is that the C# variable 'part2' is never equal to '@signaturemd5'. They do have the same length. I also tried to reverse the originalData and computedHash arrays and tried to use varchar with ASCIIEncoding.

    Help is greatly appreciated.

    Thanks,

    Leon Meijer


    • Modifié LeonM mardi 24 avril 2012 16:56
    mardi 24 avril 2012 15:06

Toutes les réponses