none
EXTERNAL_ACCESS Assembly Signing Question RRS feed

  • Question

  • 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 asymmetric key login when EXTERNAL_ACCESS is required?

       

    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    www.ChildrensOncologyGroup.org

    Monday, June 26, 2017 5:05 AM

Answers

  • God forbid - is the database set TRUSTWORTHY?

    When it comes to the expiration, I am not sure that SQL Server cares, but I have not tested. This far I have relied keys created with sn.

    • Marked as answer by JediSQL Tuesday, June 27, 2017 7:08 PM
    Monday, June 26, 2017 10:03 PM

  • 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.

    Outside of the immediate question of "why did this work" that Erland has answered, it should be stated for other readers that the overriding concern about expiration dates is absolutely nothing to be concerned about. Asymmetric Keys don't have expiration dates as they do not contain the extra metadata that Certificates do. And the end date within the .pfx file is just metadata. It is up to each application reading the metadata to determine whether or not it honors the begin and end dates. In SQL Server, the documentation for the built-in functions to encrypt and decrypt with certificates even states that those functions ignore the Certificate's expiration date. I have been using the same .pfx file for many years now for SQL# without encountering any problems in either Visual Studio or SQL Server.

    • Marked as answer by JediSQL Wednesday, July 5, 2017 8:13 PM
    Monday, July 3, 2017 7:45 PM

All replies

  • God forbid - is the database set TRUSTWORTHY?

    When it comes to the expiration, I am not sure that SQL Server cares, but I have not tested. This far I have relied keys created with sn.

    • Marked as answer by JediSQL Tuesday, June 27, 2017 7:08 PM
    Monday, June 26, 2017 10:03 PM
  • TRUSTWORHTY is ON.  It is just a test database.  I'll try turning TRUSTWORHTY off and let you know if behavior is more as expected.  I need it to work with non-TRUSTWORHTY databases in production.


    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    www.ChildrensOncologyGroup.org


    • Edited by JediSQL Tuesday, June 27, 2017 5:36 PM typo
    Tuesday, June 27, 2017 5:34 PM
  • OK, cool. I turned off TRUSTWORTH and it broke (good). Then I created the asymmetric key based login with EXTERNAL_ACCESS and then it worked again.  Thanks.


    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    www.ChildrensOncologyGroup.org

    Tuesday, June 27, 2017 7:07 PM

  • 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.

    Outside of the immediate question of "why did this work" that Erland has answered, it should be stated for other readers that the overriding concern about expiration dates is absolutely nothing to be concerned about. Asymmetric Keys don't have expiration dates as they do not contain the extra metadata that Certificates do. And the end date within the .pfx file is just metadata. It is up to each application reading the metadata to determine whether or not it honors the begin and end dates. In SQL Server, the documentation for the built-in functions to encrypt and decrypt with certificates even states that those functions ignore the Certificate's expiration date. I have been using the same .pfx file for many years now for SQL# without encountering any problems in either Visual Studio or SQL Server.

    • Marked as answer by JediSQL Wednesday, July 5, 2017 8:13 PM
    Monday, July 3, 2017 7:45 PM
  • Solomon,

    With respect to "each application reading the metadata to determine whether or not it honors the begin and end dates," that makes sense.  I hope the same works for my .pfx file.  I guess I'll find out next year.


    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    www.ChildrensOncologyGroup.org

    Wednesday, July 5, 2017 8:13 PM
  • Solomon,

    With respect to "each application reading the metadata to determine whether or not it honors the begin and end dates," that makes sense.  I hope the same works for my .pfx file.  I guess I'll find out next year.

    Hi Dan. Perhaps I was not clear enough. Yes, the metadata in the .pfx file is merely metadata and not guaranteed to be used, but more importantly the pfx file in the first place is merely being used so that the data otherwise contained in the .snk file can be password protected. The process is not using a Certificate. The Assembly is not being signed with a Certificate, it is just being given a strong name. That is, IF you are using the functionality built into Visual Studio or sn manually. This is why you need an Asymmetric Key in SQL Server and not a Certificate. Now, if you were to create a Certificate manually and then use signtool (again, manually) to sign the Assembly with an actual Certificate, THEN you would possibly be subject to the Certificate's end date.

    Also, assuming you have been going through Visual Studio for the signing (strong naming) and using an Asymmetric Key, then you do not need to wait until next year to see: I am telling you with absolute certainty that there is no expiration date, regardless of what you are seeing in the dump of the pfx file. If there was an expiration date (one that mattered), then my pfx files would have stopped working 3 years ago, yet they are still working just fine.

    This is why Niels's answer on your related question is misleading and shouldn't be marked as an answer: it takes you away from what you were doing (where there were no expiration dates) to a scenario involving a bunch of extra work (it doesn't work with Visual Studio) where you would be subject to expiration dates.


    Wednesday, July 5, 2017 9:23 PM
  • Solomon,

    I am grateful for your posting.  Please don't take my intractable cynicism personally. :-)


    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    www.ChildrensOncologyGroup.org

    Thursday, July 6, 2017 4:19 PM
  • Solomon,

    I am grateful for your posting.  Please don't take my intractable cynicism personally. :-)

    Hey there. No, nothing taken personally. I was just making sure that the over all issue was clearly defined,  not just for you, but for others that read this in the future. Still, I will try to listen more carefully for the ";-)" tone of voice :-).
    Thursday, July 6, 2017 8:47 PM