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


  • 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 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');

    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("-", "");


    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.


    Leon Meijer

    • แก้ไขโดย LeonM 24 เมษายน 2555 16:56
    24 เมษายน 2555 15:06