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

-- 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
-----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?

  • maybe the problem is that you're trying to use a Self-Signed certificate (which was not issued by a Certificate Authority) and CyberChef can't verify the certificate to validate it. – Ronaldo Jun 5 at 10:40
  • @Ronaldo CyberChef doesn't know anything about certificate authorities, and it isn't using the full certificate anyway -- it only has the public key of the RSA keypair. – NReilingh Jun 5 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.