EXTERNAL_ACCESS Assembly Signing Question

• JediSQL

  SSCrazy

  Points: 2121

  More actions

  June 25, 2017 at 11:09 pm

  #337900

  I want to be able to deploy CLR assemblies to SQL Server for SQL CLR procedures and functions that need EXTERNAL_ACCESS.
  I don't want to have to create a separate master db asymmetric key and associated login for each assembly I deploy.
  So I tried this:
  1) Create an empty SQLCLR class, add a .pfx signing file created by Visual Studio, and compile the dll.
  2) Create an asymmetric key in SQL Server master database from the dll.
  3) Create a login from the asymmetric key and grant it EXTERNAL_ACCESS.
  4) In another Visual Studio project: a) create a SQLCLR class that has methods, b) import the .pfx file I created above for signing and c) compile the project to a dll.
  5) Create a SQL Server assembly from the second dll.
  6) Create CLR procedures and functions from the SQL Server assembly.

  That all worked well about three months ago, and I was happy. Now someone has informed me that .pfx files have a 12 month expiration date. We have a SQLCLR procedure with EXTERNAL_ACCESS that was created several years ago, and I can see that its EXTERNAL_ACCESS ability has not expired. However, I am concerned that after another nine months I will no longer be able to sign new/modified assemblies with my "shared" .pfx file, and I will have to create a new master db asymmetric key and associated login.

  With a little research I found that one can also sign an assembly with a .snk file (which does not have an expiry date). I created a new Visual Studio project and used the "Signing..." button to create a new .snk file by choosing to not put a password on my signing file. I compiled my new dll (which makes network calls), created a SQL Server assembly from it, and created a stored procedure on the assembly. I executed it an it worked fine. However, as it was executing successfully that first time, I realized that I had overlooked creating the master db asymmetric key and associated login from the new assembly that had the new signing file.

  Why is the new SQL assembly from the new dll signed with the new signing file able to work properly without the asymetric key login when EXTERNAL_ACCESS is required?

  Sincerely,
  Daniel