I want to sign plaintext using a SQL Server certificate and then verify that signature using the certificate's public key on an external system. Here's what I've tried.
-- Create a self-signed certificate CREATE CERTIFICATE MyCert WITH SUBJECT = 'Integrations'; -- Output the public certificate SELECT [der_hex] = CERTENCODED(CERT_ID('MyCert'));
Using CyberChef, I can convert this binary output into a PEM format and also verify that it parses as a X.509 certificate with an RSA public key: CyberChef example.
After writing this PEM-format certificate to a file, then using
openssl I can extract this RSA Public Key in PEM format:
% openssl x509 -inform pem -in cert.pem -pubkey -noout -----BEGIN PUBLIC KEY----- MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAvHYJf/jNWetCyySQBWVO S9M8maFmMQXOZDG2eXHsw92rCjhNbtFsWBi077rQrN/zAXuiiWC1oFAwHMOrMleI jZCaghqdBr9HI5IbCe1eZvucg3K+AfLx3iZ9zr//HSh+0bKVScWW9POW19F2F8z/ uIWnHDOXEDO0BrZ7W9thl0WRaY+dG8jPtQSxL8fVhcho1g6fF3vmD1zpiIHmAiZI h0np0rdYQ/pj8aK7jRLgPlsz4JqQ5JZBhtLmxK5Vz6OZ6+ocqfltmuL0f5wGbd8M e2qmXcBB6DFJm4xs+Ey7FqSjAMFiMBNYWt2NbEeAKHxBjXOjM0vbtGaVyT8uaSJW JQIDAQAB -----END PUBLIC KEY-----
Now, I can have SQL Server sign some data:
SELECT [signature] = SIGNBYCERT(CERT_ID('MyCert'), 'hello');
And now, again using CyberChef, I want to verify the signature against the same plaintext using the public key: CyberChef example.
Unfortunately, this signature does not verify and I'm unable to figure out where I'm going wrong. The documentation for
SIGNBYCERT() is not exactly tremendous, in that it is not specific about any particular standards or formats that are used to generate the signature.
I am assuming that it is an RSA signature by virtue of the metadata in the certificate used to create the signature. But I recognize that this could be an issue of text encoding; I'm sure the text I'm verifying in CyberChef is UTF-8, and nowhere in the SQL code have I declared what text encoding to use when generating the signature -- and indeed, if I pass in
N'hello' instead of
'hello', the signature output changes.
However, this seems to be a moot point since when attempting to verify a signature in CyberChef, the error I receive is
Error: Encryption block is invalid. This would indicate an issue with the signature format, not with the data simply not validating -- valid or invalid data instead would receive a
Verification failure or
Verified OK message. This GitHub issue indicates it could be a difference in padding algorithm. I think I am also concerned about the internal structure of SQL Server's signature output not being "formatted" the right way, but I am not really knowledgeable enough to know how far-fetched that is.
Assuming that nothing super proprietary is going on here, what do I have to do to verify a SQL Server signature with external systems?