1

I have created a certificate, signed my assembly (UNSAFE) and it works, All as described there

https://nielsberglund.com/2017/07/01/sqlclr-and-certificates/

Now I need to deploy the solution to many customers databases. I do not want to change the TRUSTWORTHY setting. Is it possible to deploy it do not using file system? I.e. now I am using the binary to create the assembly. But how I can create the signed assembly and deploy it to other db?

ZedZip
  • 5,794
  • 15
  • 66
  • 119

1 Answers1

1

Yes, you can create everything straight from T-SQL scripts and never reference the file system. It is possible to create a Certificate from hex bytes (i.e. VARBINARY literal), and you can get the string form of the hex bytes from a binary file using a small command-line utility I wrote, BinaryFormatter (open source; on GitHub). BinaryFormatter also works for converting the compiled DLL into hex bytes for use with the CREATE ASSEMBLY statement.

Also, I should point out that if you are using certificates, then you do not need to also enable TRUSTWORTHY. This is one of the reasons to use certificates. Just make sure to load the certificate into [master], create the associated login from the certificate, and grant that login the UNSAFE ASSEMBLY permission, all before attempting to load the assembly into any database. But, once the certificate is loaded, then you can load the assembly into any database with no more configuration needed (especially not needing to enable TRUSTWORTHY anywhere).

Also, if you are looking to streamline/automate your build process while still using certificates, please see the following two posts of mine:

Additional details can be found in my answer to the following S.O. question:

CLR Strict Security on SQL Server 2017

P.S. This process would be a thousand times easier if the CREATE ASYMMETRIC KEY command were enhanced to allow for being created from a binary string. I have made this suggestion on Microsoft Connect – Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE – so please support it :-).

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • Hi Solomon, great! Btw, I go this way after reading your article about do not touch the Trustworthy setting! In my case I do not know what really setting is because it is in customer's admin hands. Thanx! I will try to do it . – ZedZip Jan 09 '21 at 16:50
  • Another question: how to be with Service Broker? After db restoring on other server it stopped. The restored db has a Trustworthy Off and I set it to On. I understand that it is bad practice. How to restore Broker working in that case? (I can create separate question) – ZedZip Jan 09 '21 at 16:53
  • Done: I have voted your suggestion on MS Connect! – ZedZip Jan 09 '21 at 17:40
  • @ZedZip - please do create another question in regards to Service Broker; it is an unrelated concern to your original question. – Ben Thul Jan 11 '21 at 20:39
  • Done: the new question about Service Broker https://stackoverflow.com/questions/65681589/how-to-recover-service-broker-after-restoring-database – ZedZip Jan 12 '21 at 09:57