Testing / Debugging CLR UDFs and Stored Procedures RRS feed

  • Question

  • I know that this question in unusually broad but I am just getting started writing CLR code for SQL Server.  One of the challenges that I am having is that I can not execute code without first signing it with a key.  Perhaps I am missing something, but the only way I have been able to create a key is through tools like makecert, signtool, etc.  This is not going to be a very efficient way to develop.  Am I missing something?  Is there an easier way to accomplish this without changing from strict security?  This seems incredibly awkward.

    Thanks in advance for your help.

    Saturday, June 29, 2019 4:35 PM

All replies

  • You can sign the assembly with sn, and then you can create an asymmetric key from the assembly with CREATE ASSEMBLY in T-SQL. But this requires that SQL Server can see the assembly.

    An alternative is to compute a SHA_512 hash and enter this hash with sp_add_trusted_assembly. This is not too tricky to automate, and it does not require SQL Server to have access to the assembly DLL.

    Solomon Rutzky has written quite a bit on how to automate the handling of assemblies, see for a list. He seems not to like trusted assemblies, though. I disagree with him on that point.

    Erland Sommarskog, SQL Server MVP,

    Saturday, June 29, 2019 8:10 PM
  • I agree with Erland below; if you want to be "secure" in your dev environment, but still not have to jump through too many hoops - go with white-listed assemblies (if you are on SQL 2017 or later). | @nielsberglund

    Monday, July 1, 2019 3:07 AM
  • ... This is not going to be a very efficient way to develop.  Am I missing something?  Is there an easier way to accomplish this without changing from strict security?  This seems incredibly awkward.

    Hi Ray. Yes, this is inefficient and awkward. And unfortunately there is no ideal, super easy way to do it properly and easily at the same time. There could be an ideal, super easy way, if only Microsoft would implement my suggestion here: "Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE". So please vote for that suggestion.

    For initial testing and development I think it's ok to set the database to TRUSTWORTHY ON. I would not recommend that setting in any other context, and in fact am quite strongly against it in all other cases. However, in the early stages of a project, it should be fine to do in a development environment. But once the code is to move to QA or a higher level environment, then you will need something more secure than TRUSTWORTHY ON. There are two approaches to automate the build and deployment process to include proper security (i.e. module signing), and both work with Visual Studio / SSDT or with custom processes:

    In both cases you probably won't need to create new keys for each new project / assembly. At the very least you can easily share the Strong Name Key or Certificate across all projects in a solution. And, even if you have multiple solutions, if this is all in-house functionality, then you can (and probably should) use the same key across multiple solutions. By using the same key, you only need to worry about getting that installed into SQL Server (along with the associated login and granting that login the UNSAFE ASSEMBLY permission) once (per instance). Meaning, that part doesn't need to be included in every deployment script you create (although, it wouldn't hurt anything if you did, as long as the scripts are idempotent -- i.e. re-runnable -- and the two solutions I posted definitely are).

    It could be as simple as this:

    1. Create a Certificate (one-time)
    2. Install Certificate in [master] database, create login from it, and GRANT that login the UNSAFE ASSEMBLY permission (once per Instance)
    3. Update build process to sign the compiled assembly using SignTool.exe (one-time setup per project)
    4. If SSDT won't create the desired deployment script (I don't use it for deployment due to it not supporting parameter defaults, RETURN NULL ON NULL INPUT option, and several other things), you can still convert any binary files (e.g. the Assembly(ies) and possibly also Certificate) into the hex string needed for the deployment SQL script using BinaryFormatter, an open-source command-line utility I wrote. BinaryFormatter also spreads the binary string across multiple lines so that you don't have a super long line, hence more managable, especially in SSMS which doesn't like super long lines. (one-time setup per project)

    For the entire series of articles on this topic, please go to: SQLCLR vs. SQL Server 2017

    And what Erland said is correct: I very much dislike the "trusted assemblies" feature and even feel that it should be removed. It's not that I don't get that some folks are finding it to be beneficial, or that I don't sometimes appreciate the idea of not messing with a certificate or asymmetric key (but that's only due to Microsoft not allowing Asymmetric Keys to be created from a hex string), or that I can't find some silver lining in it (it does provide highly granular security on a per assembly, per compilation basis). The reason I don't like it is that it's a we-need-something-and-can't-think-of-anything-else-and-no-time-for-more-research hack. And they know it's a hack; it's doesn't work with the rest of the security infrastructure. It exists because they needed a way to allow only specific assemblies to keep working when they disabled SQLCLR in Azure SQL Database v12 back in April, 2016 (I believe). And it only exists in non-Azure / on-prem SQL Server because when they were testing SQL Server 2017, they ran into the problem where existing, unsigned assemblies, coming into SQL Server 2017 from a database restored / attached from an earlier version of SQL Server, would not load due to not being signed (here is a recent case of that scenario). The issue is that there is no built-in way to export the assembly, sign it, and then import it. Nobody working at Microsoft knew that you could simply create a certificate in SQL Server, and sign the assembly in place (no need to export), and it would work. If anyone had known this, they would not have exposed the "trusted assemblies" code as it's entirely unnecessary (at least outside of Azure SQL Database).

    "Trusted assemblies" isn't a feature that was planned out and designed. It was slapped together with little to no consideration of how it would integrate with the existing system, or what implications there might be with the typical app development life cycle. And that's fine within the context of it's original purpose: mark a fairly small and fairly static (i.e. no ongoing development) set of assemblies (that are already prohibited from being EXTERNAL_ACCESS or UNSAFE, so no need to test security implications) as "permitted to execute". This was not meant to be part of the main SQL Server product, and it shows. And, it certainly wasn't intended for new development. The assumption was that everyone would do module signing, which is why this feature wasn't added until later in the CTP / testing process. But now that it exists, people are using it for new development, and even more so due to "clr strict security" ending the ability to load an empty, SAFE assembly signed with a strong name key to create the Asymmetric Key from. (Again, all of this would be a complete non-issue if we had the ability to create the Asymmetric Key from a hex bytes string, just like we can do with Certificates).

    If you only do one-off assemblies that don't get updated all that often then sure, "trusted assemblies" might be slightly (but only slightly) less work than module signing. But, if you are working with several assemblies and/or assemblies that you will be updating every so often, then module signing is actually less work since you only need to set up the server-side of the security once (i.e. create the Asymmetric Key or Certificate, the associated Login, and grant the Login UNSAFE ASSEMBLY). Once the server-side piece is in place, you can sign any number of assemblies any number of times and the CREATE ASSEMBLY statement will just work. There is a reason why all other software uses module signing (i.e. code signing) and it's built into the OS, and no other software uses a simple hash (outside of verifying downloads).

    Take care,

    SQL# - ( SQLCLR library of over 340 Functions and Procedures )
    Sql Quantum Lift - ( company )
    Sql Quantum Leap - ( blog )


    Monday, July 1, 2019 6:59 AM