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/

35 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

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

6 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
  • Solomon Rutzky commented  ·   ·  Flag as inappropriate

    Please see related suggestion: "Add function to extract Asymmetric Key similar to CERTENCODED for Certificates" ( https://feedback.azure.com/forums/908035-sql-server/suggestions/32897317-add-function-to-extract-asymmetric-key-similar-to )

    Please note that while related, these two suggestions are not of equal importance. This suggestion (creating Asymmetric Key from varbinary literal) is a necessity, while the other suggestion (extracting the varbinary representation of the asymmetric key) is a "nice to have".

  • Anonymous commented  ·   ·  Flag as inappropriate

    The importance of this item cannot be judged by the sheer quantity of Votes, because most people do not know about how the modules they need get signed, or even if they are signed.

  • Daniele Fusi commented  ·   ·  Flag as inappropriate

    I adhere to Solomon's suggestion; it is becoming overwhelmingly difficult to just add some basic UDF (e.g. regex support) to an SQL database, and I fear many developers might just be forced to take the easy route of disabling strict... which goes in the opposite direction of this change.

  • Troubladore commented  ·   ·  Flag as inappropriate

    Agreed with Solomon's comment below. If there is a reason this feature isn't being picked up, it would be nice to know.

  • Solomon Rutzky commented  ·   ·  Flag as inappropriate

    Can someone please take a look at this? I do not believe that it requires that much work (though I do realize that there might be technical factors that I have no knowledge of that complicate this request), but the gains would be HUGE. This is something that really should have rolled out with SQL Server 2005. Being able to create an Asymmetric Key from binary hex bytes (i.e. FROM 0x...) would not only negate the need for trusted assemblies, but it would FINALLY allow folks to deploy from Visual Studio without resorting to setting "TRUSTWORTHY ON", which currently is really the only way to deploy SQLCLR from Visual Studio without jumping through lots of hoops ( https://sqlquantumleap.com/2017/08/09/sqlclr-vs-sql-server-2017-part-2-clr-strict-security-solution-1/ and https://sqlquantumleap.com/2017/08/16/sqlclr-vs-sql-server-2017-part-3-clr-strict-security-solution-2/ ).

    This would not only greatly assist in people being more successful with SQLCLR, but it would greatly reduce the number of databases that are currently set to "TRUSTWORTHY ON". And doesn't Microsoft recommend keeping TRUSTWORTHY OFF? I certainly do ( https://sqlquantumleap.com/2017/12/30/please-please-please-stop-using-impersonation-execute-as/ ). By not allowing Asymmetric Keys to be created inline via a varbinary literal or variable, Microsoft is pushing users to decrease the security of their systems (by setting TRUSTWORTHY ON) while at the same time recommending that they not do that.

    All that is _required_ to make life much, MUCH better for thousands (easily) of your customers is to allow creation of _just_ the public key, similar to creating the Asymmetric Key from an assembly or file. Allowing for the private key to be imported is a nice-to-have for consistency with CREATE CERTIFICATE, but it is absolutely not required as it would not affect these scenarios. Importing the private key would also require more work updating CREATE ASYMMETRIC KEY, and then require updates to ALTER ASYMMETRIC KEY. The effort required for dealing with the private key might not be worth it, hence it can be a separate project. But simply creating the basic Asymmetric Key (with just the public key, just like it currently works when creating from an assembly or file) will be a huge win for what should be not a lot of work. Please?

    PLEASE ? ? ? ? ? ? ? ?

Feedback and Knowledge Base