We have recently migrated to SQL Server 2017 (64 bit - version: 14.0.1000.169 - Host OS: Windows Server 2016 Datacenter). I created the test certificate in database and used it to sign sample text. The SQL method SignByCert returned signature of length 264 bytes.
Ideally I expect the signature size should be length of mod length which is 2048 bits (256 bytes) in my case. I am wondering why the SignByCert returned signature of length 264.
Below is sample code.....
Create certificate MyTestCertificate
EXPIRY_DATE = '20231010' ;
DECLARE @ClearText as varchar(8000);
DECLARE @Signature as varbinary(max);
DECLARE @Signature_len as int;
DECLARE @RESULT as int;
SET @ClearText = 'This is some clear text to encrypt';
SET @Signature = SignByCert(Cert_ID('MyTestCertificate'), @ClearText);
SET @Signature_len = datalength(@Signature) ;
SET @RESULT = VerifySignedByCert(Cert_ID('MyTestCertificate'), @ClearText, @Signature);
select @Signature_len, @RESULT
Also common thing I observe here that signature always starts with 0x01 (which is not the case for SQL Server 2008). Sample signature 0x0100050204000000CE5F2A45FE3E637609DF95B10B3E1642B56D3C3208EE5769..
I would need to verify the signature using openssl. however for signature of length 264, openssl gives below error
"data is greater than mod len"