Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE (SQLCLR / SSDT / Module Signing)

Creating an Asymmetric Key (for the purpose of creating an associated Login to provide { EXTERNAL ACCESS | UNSAFE } ASSEMBLY permission for SQLCLR Assemblies and hence avoid setting TRUSTWORTHY ON) requires a file or EKM provider or Assembly. Prior to SQL Server 2017 it was at least possible to load an Assembly (marked as SAFE) that is signed with the same private key into [master] to then create the Asymmetric Key from, and then drop the Assembly.

But, with SQL Server 2017, ALL Assemblies will require being signed with a matching key / cert -based Login having the UNSAFE ASSEMBLY permission, else TRUSTWORTHY ON is required. In order to avoid enabling TRUSTWORTHY (and not disabling the new "clr strict" config option -- https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/clr-strict-security ) one needs to create the Asymmetric Key FIRST. Yet when doing a REAL deployment (i.e. self-contained SQL script -- no DLL !!!), you don't have the DLL to create the Asymmetric Key from.

Doing a Certificate is not an acceptable work-around as:
1) Visual Studio doesn't use SignTool to add a Certificate from the pfx file, it just gives a strong name using the pfx file.
2) Even if we can find a way to get Visual Studio to use SignTool before SSDT converts the DLL into the hex bytes for the SQL script (I have a couple of ideas), this will still be a bit clunky / certainly not a smooth rollout process.

At the moment, deploying a SQLCLR project via Visual Studio / SSDT and/or through most any Continuous Integration process requires bad, insecure practices:
1) set TRUSTWORTHY ON
2) disable new "clr strict" server config option

Yes, once an Instance has the Asymmetric Key (or Certificate) installed and the Login created and granted the permission this doesn't need to be done again, and so it "could" be a configuration step, but realistically it won't happen. We need to be able to deploy a project from Visual Studio, as part of the normal publishing process.

AND, while the "Trusted Assemblies" feature was added in RC1 of SQL Server 2017, and would "appear" to negate the need for this suggestion, there are too many problems with that feature and it should be removed. "Trusted Assemblies" is simply not a viable option for anyone ever and should be avoided at all costs. For details on the various problems with "Trusted Assemblies", please see https://SqlQuantumLeap.com/2017/08/28/sqlclr-vs-sql-server-2017-part-4-trusted-assemblies-the-disappointment/

26 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base