2

I've read plenty about asymmetric keys and certificates in SQL Server (2012) but those seem to be specifically for signing data. What I have is a SQL CLR assembly that I have signed with a digital certificate and have successfully loaded as an UNSAFE assembly and executed the SqlFunction successfully as well. However, I want to add logic to the SqlFunction to verify the assembly was signed by my cert. For all other assemblies we extract the public key and compare it to a key in the license file. But there seems to be no way to extract the public key when the assembly is executing inside of SQL. Is there some piece of this SQL CLR or CERT/ASK architecture that I am not understanding in order to get access to this? TIA!

Clarification of Need:

We sell software that generates data in binary format into our database schema, therefore a customer cannot easily query the data directly using T-SQL. This is done both for performance and to black-box our data analysis algorithms that output the data. Currently customers must use our software to "unpack" this blob data via "BusnLogicCore.dll". This dll will only execute if the calling assembly has been signed by a certificate that we have approved in advance. For customers who purchase our SDK, the customer sends us the public key of their own cert. and we add the pk to the license file we issue to them, so that assembly can confirm that they are allowed to call BusnLogicCore.dll. We're now creating SQL CLR function that would allow a customer to use T-SQL to call BusnLogicCore.dll and unpack the blob data as part of their query. However, we have been unable to confirm that the SQL CLR function has been signed by a certificate that we have approved to call BusnLogicCore.dll. If we poke a hole in this logic to have an exception such that SQL CLR functions don't have to go through this verification, then a customer who has not purchased the SDK can write their own SQL CLR function to call BusnLogicCore.dll. It is a tad more complicated than that, and there are additional checks we can do on the license, but it would be preferable not to have to bypass this verification. Is this an edge case? Probably, but we want to protect our IP as best we can, and not having this option is frustrating.

Update: I have checked the sys.certificates table, but neither the cert_serial_number, nor the thumbprint columns match the public key of the cert.

Improve this question
2
  • My post is up less than 20 minutes and it's already down-voted. Could you please say why? Commented Apr 6, 2018 at 19:18
  • Not sure why it was down-voted, but I just up-voted. Also not sure why you even want / need to do this as the answer is implied simply by being able to create the Assembly in SQL Server in the first place. Still, I provided some details in my answer. Perhaps you could update the question to explain a little more about exactly what you are trying to accomplish ;-) Commented Apr 11, 2018 at 3:02

1 Answer 1

Reset to default
3

Assuming that you first load the Certificate into the master DB, create a Login from that Certificate, and grant that Login the UNSAFE ASSEMBLY permission, such that you can create the Assembly with PERMISSION_SET = UNASFE, then I don't know why you would want / need to do what you are asking for since that question will have already been answered at that point.

Meaning, if you are able to create the Assembly as UNSAFE (assuming that the DB has TRUSTWORTHY set to OFF, hence using a Certificate -- which is the better way to go anyway), then that could have only happened if the Assembly was signed with a Certificate (or Strong Name Key / Asymmetric Key) that was first loaded into master, etc (as noted at the beginning of this answer). If the Assembly was either not signed, or signed by a different Certificate (or Strong Name Key), then it would error when trying to create it as UNSAFE or EXTERNAL_ACCESS (or even as SAFE starting in SQL Server 2017).

Still, there is some info in the sys.certificates system catalog view that can be used to match Certificates between Databases. If you have your Assembly loaded prior to creating the Certificate in master (either by setting the DB to TRUSTWORTHY ON -- bad idea, or by loading as SAFE with the intention of altering to UNSAFE after setting up the Certificate in master -- won't work starting in SQL Server 2017), then you can extract the Certificate from the Assembly by using CREATE CERTIFICATE [bob] FROM ASSEMBLY [YourAssembly]; and then grab the cert_serial_number or thumbprint of that Certificate from sys.certificates.

And to clarify a little more:

  1. You cannot directly get the actual public key (sometimes referred to as the "certificate" because that's not confusing, right?) within SQL Server by any built-in mechanism. You can get the public key of an Asymmetric Key / Strong Name Key via sys.asymmetric_keys, but not for Certificates, unfortunately.

  2. The serial number, found in the cert_serial_number field of sys.certificates, is exactly that: the serial number, not the public key. It matches the "Serial Number" returned when doing a certutil -dump certificate.cer in a command prompt.

  3. The thumbprint, found in the thumbprint field of sys.certificates, is the SHA1 hash of the public key (sometimes referred to as "certificate"), not the public key. It matches the "Cert Hash(sha1)" returned when doing a certutil -dump certificate.cer in a command prompt.

So, if you need to analyze an Assembly within SQL Server, you probably need to use the .NET methods for doing that, though I don't know if that library is in the "supported" list, and if not, then it would require adding the library and setting your Assembly to UNSAFE.

Else, perhaps you can have a few options for what to check: public key (if available), else thumbprint and/or serial number.

OR, since you have the public key, you could get the full Certificate via SELECT CERTENCODED(CERT_ID(N'certificate_name')). That will give you the full series of bytes for the certificate, which includes the public key. You can search for the public key as a substring of the string form of the full certificate.

OR, along those same lines, you can scan the [content] field of sys.assembly_files for the presence of the public key bytes. This is more direct than scanning the certificate, but it allows for merely that particular series of bytes being somewhere in the Assembly to validate, and does not imply that the Assembly was in any way signed with those bytes as the public key.

So perhaps a more guaranteed method is to create the certificate from the Assembly (i.e. CREATE CERTIFICATE [tmp] FROM ASSEMBLY...), use CERTENCODED() to get the full Certificate, and scan that for the public key substring. Do that once per loading of the class via a static class constructor. Of course, this means that you will need to make a regular / external connection to the DB, meaning that the Assembly will need to at least be EXTERNAL_ACCESS. And you will need to ensure that the process has permissions to create a Certificate. AND, you should wrap that CREATE CERTIFICATE in a TRY...CATCH construct since it might already exist (after the first time, either it will exist or you will need to drop the Certificate each time, but that might just make the initial verification process take longer so I guess you can just leave it in the DB).

Not sure if the full Assembly bytes are available via Reflection like the version and some other stuff is. Might be worth looking into.

Also, please consider supporting the suggestion I just submitted to have the public_key field added to sys.certificates:

Expose public_key of Certificate in sys.certificates, just like sys.asymmetric_keys is doing

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Solomon, thank you for up-voting, and replying. I updated my OP to further explain why I'm looking for the cert's public key. Unfortunately I have checked the sys.certificates table, but neither the cert_serial_number, nor the thumbprint columns match the public key of the cert. If we don't check the pk, our customers could use a test cert they generated themselves to sign it and we wouldn't know that the cert is not one we've given the ok to. I hope my clarifications help. Thanks!
@VG1 I think I understand. I have updated my answer, towards the bottom.
Thank you Solomon! This is very thorough and helpful. I agree that using CERTENCODED() in a static ctor sounds like the best option. I also voted for your suggestion. Thank you again!
@VG1 Yer welcome. I just updated to add a little clarification regarding that suggestion. And thanks for voting for that suggestion :-).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.