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

    Frage

  • 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


    • Bearbeitet LeonM Dienstag, 24. April 2012 16:56
    Dienstag, 24. April 2012 15:06

Alle Antworten

  • Hi Leon,

    I would like to involve someone familiar with to have a look, and give an update later. Thanks for your understanding.

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support

    Donnerstag, 26. April 2012 08:46
  • Hi Leon,

    We are looking into this question for you. But, meanwhile, does this apply to your scenario?

    http://blogs.msdn.com/b/shawnfa/archive/2005/12/05/500144.aspx

    Thanks,

    Cathy Miller

    Donnerstag, 26. April 2012 15:31
    Moderator
  • Hi Cathy,

    Thanks for the update. I have seen that blog post and I actually applied the Reverse-info for the encryption/decryption part of my app. For that, it works.

    I wasn't sure if I have to reverse for signing too, the blog post doesn't mention it. So in my Proof of Concept app I tried reversing the bytes before, after, before and after and not. In all cases, got a different signature than SQL SIGNBYCERT.

    I think the crucial piece of info that's missing for me, is which asymmetric algorithm to use, like MD5 or SHA1... Or does it depend on the certificate? There's little or no info on the internet regarding this.

    Thanks.

    Donnerstag, 26. April 2012 22:53
  • Hi Stephanie, Cathy,

    Can you give an update on this issue ?

    Thanks.

    Freitag, 4. Mai 2012 19:35
  • Hi Leon,

    This issue is going to require more in-depth support.

    Please visit the following site to see the various support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    Thanks,

    Cathy Miller

    Freitag, 11. Mai 2012 16:13
    Moderator
  • Did you get any solution for the same.?

    Sonntag, 20. Mai 2012 09:46
  • Hi Linda,

    No unfortunatly I never got this solved. For my application, I skipped signing and only used encryption.

    Leon

    Mittwoch, 5. September 2012 13:00