Tuesday, April 24, 2012 3:06 PM
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');
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)
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.
- Edited by LeonM Tuesday, April 24, 2012 4:56 PM
Thursday, April 26, 2012 8:46 AMModerator
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.
TechNet Community Support
Thursday, April 26, 2012 3:31 PMModerator
We are looking into this question for you. But, meanwhile, does this apply to your scenario?
Thursday, April 26, 2012 10:53 PM
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.
Friday, May 04, 2012 7:35 PM
Hi Stephanie, Cathy,
Can you give an update on this issue ?
Friday, May 11, 2012 4:13 PMModerator
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
Sunday, May 20, 2012 9:46 AM
Did you get any solution for the same.?
Wednesday, September 05, 2012 1:00 PM
No unfortunatly I never got this solved. For my application, I skipped signing and only used encryption.