signing with same certificate in C# code and sql server gives different results

Philip Stuyck 1 Reputation point

I created a selfsigned certificate in IIS and exported it in pfx format Then I used pvkconverter to create a cer file and a pvk file

I import these 2 files in localdb using :

FROM FILE = 'c:\sql\pvkder.cer'   
    WITH PRIVATE KEY (FILE = 'c:\sql\pvkder.pvk',   
    DECRYPTION BY PASSWORD = 'sec4ChipSoft!');  

This import is successfull

if i now use signbycert

select SIGNBYCERT(Cert_Id( 'test3' ),'Philip');

the result is :


now in C# code i do this :

string password = "sec4ChipSoft!";
X509Certificate2 certificate = new X509Certificate2("C:\\SQL\\ChipSoftCertificate.pfx", password);//public X509Certificate2 (string fileName, string? password, System.Security.Cryptography.X509Certificates.X509KeyStorageFlags keyStorageFlags);
RSA provider = certificate.GetRSAPrivateKey();
var data = Encoding.UTF8.GetBytes("Philip");
var signature = provider.SignData(data, HashAlgorithmName.MD5, RSASignaturePadding.Pkcs1);
var ssignature = Convert.ToHexString(signature);
signature = provider.SignData(data, HashAlgorithmName.SHA1, RSASignaturePadding.Pkcs1);
ssignature = Convert.ToHexString(signature);
signature = provider.SignData(data, HashAlgorithmName.SHA256, RSASignaturePadding.Pkcs1);
ssignature = Convert.ToHexString(signature);
signature = provider.SignData(data, HashAlgorithmName.SHA384, RSASignaturePadding.Pkcs1);
ssignature = Convert.ToHexString(signature);
signature = provider.SignData(data, HashAlgorithmName.SHA512, RSASignaturePadding.Pkcs1);
ssignature = Convert.ToHexString(signature);

then none of the signatures are the same as the one created on sql server.

As a matter of fact if i do the signing in sql server then the signature is 8 bytes longer. It seems that 0x0100050204000000 is some kind of header. But the remaining bytes are then again allways different from any signature i create in C#.

The reason i do this is that ultimately i want to sign something in C# code and verify the signature in sql server for security purposes

But as it looks now this mechanism does not work. What am I doing wrong

The thing is that the verification on sql server works with the sign created on sql server and the same is true for the C# code. But i cannot use the combination of sql server and C#

Any help is appreciated. Does sql server use different padding ? a different hashing ? Some sort of encapsulation ? Is there a way to find out ?

select @@VERSION


Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64) Jul 19 2021 15:37:34 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19045: )

And the target framework of the C# code is .NET 7

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,600 questions
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
9,469 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Bruce ( 48,636 Reputation points

    With RSA encryption, unlike a hash, the result string will be different with encryption. You need to decrypt to compare values.

    0 comments No comments