none
Long Lasting PFX File for SQLCLR EXTERNAL_ACCESS Signing

    Question

  • (This is a new question with the same lead-in as a different, recently asked 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.

    Question 1: Will my shared .pfx file stop working for signing assemblies after its expiration date?

    Question 2: Is it possible, with Microsoft tools, to create a .pfx file with an expiration date of my choosing that will work for signing Visual Studio 2015 assemblies?  (I tried using MakeCert.exe and Pvk2Pfx.exe to make one.  I was able to make a .pfx file, but Visual Studio 2015 would not build an assembly with it.  Maybe I just used the programs incorrectly.)


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

    Tuesday, June 27, 2017 8:48 PM

Answers

  • Hej Erland!

    Vad gör en fin man som du i denna här syltan?!! :)

    So in your reply, was there an answer in there, or was it just a "decree from the ivory tower"? Anyway, I do believe that both the OP and myself know the perceived dangers with TRUSTWORTHY, and I consider the dangers not being so much dangers, it all comes down to your environment.

    Anyway, so let us get back to the question, how not to have to use one key per assembly. Today I had some time to spare and dug out some code from the time I just to also spoke form the Ivory Tower, i.e. when I was teaching SQLCLR.

    So here's what we do:

    Create a self signed cerificate, I created the cert with a password of testPwd:

    makecert -r -pe -n "CN=Niels Test Root Authority" 
             -a sha256 -sky signature -cy authority 
             -sv NielsTestPvk.pvk -len 2048 
             -m 144 NielsTestCert.cer

    The -m flag defines the valid lifetime in months for the certificate (so 12 years).

    We then create a pvx file from the pvk file by using the Pvk2Pfx tool:

    PVK2PFX -pvk NielsTestPvk.pvk 
            -spc NielsTestCert.cer 
            -pfx NielsTestPfx.pfx -po testPwd

    The -pfx flag defines the name of the pfx file you want, and the -po is the password for the cert/pvk.

    Let's now switch over to the database and create a SQL server cert on the certificate we created in the first step:

    USE master;
    GO
    
    CREATE CERTIFICATE NielsTestCert 
    FROM FILE = 'D:\CertTest\cert\NielsTestCert.cer'
    GO

    The certificate file has to be somewhere where SQL Server can get to it.

    We then create a login from the SQL certificate and grant unsafe access to the login (I went with UNSAFE, as most of our SQLCLR assemblies are UNSAFE):

    CREATE LOGIN login_NielsTestCert 
    FROM CERTIFICATE NielsTestCert
    GO
    
    GRANT UNSAFE ASSEMBLY TO login_NielsTestCert;
    GO

    My unsafe assembly looks something like this:

    using System.Threading;
    
    namespace SqlClrTest
    {
      public class Functions
      {
        public static int fn_clr_LongRunning()
        {
          Thread.Sleep(10000);
          return 999;
        }
      }
    }

    Notice in the code how we use threads, which is definitely not safe. And if we were to try and deploy it as it stands straight after building it, we would get told that the assembly is not meeting the requirements for being UNSAFE. So what we want to do is to sign the assembly with the previously created pfx file, and we use the SignTool.exe for this:

    signtool sign /f NielsTestPfx.pfx 
                  /p testPwd SqlClrTest.dll

    Having done this we can now deploy the assembly, create the function and test it:

    CREATE ASSEMBLY SqlClrTest
    FROM 'D:\CertTest\cs\SqlClrTest\bin\Release\SqlClrTest.dll'
    WITH PERMISSION_SET = UNSAFE;
    GO
    
    CREATE FUNCTION fn_clr_LongRunning()
    RETURNS INT
    EXTERNAL NAME SqlClrTest.[SqlClrTest.Functions].fn_clr_LongRunning
    GO
    
    SELECT dbo.fn_clr_LongRunning()
    GO

    And that's about it, the pfx file can now be used for as many assemblies as needed - and AFAIK there is no expiry on the pfx file, it is however on the certificate.

    Hope this helps!

    Niels



    http://www.nielsberglund.com | @nielsberglund

    • Marked as answer by JediSQL Friday, June 30, 2017 10:31 PM
    Thursday, June 29, 2017 1:03 PM
    Moderator
  • 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.

    The accepted is incorrect: it ignores the actual issue and goes down a needless, overly complex path. The question itself is based on a misunderstanding of what is going on, and that is never addressed, hence the thread that follows is just confusion.

    ALL that needs to be said regarding this issue is: It is much ado about nothing, a complete non-issue! Why? Because .pfx files are not Certificates, per se. A .pfx file can contain a Certificate, but it can also contain other things that are not Certificates. And in the case of Visual Studio creating the .pfx file, it is merely a password-protected container for the .snk file (well, the public and private key pair that is in the .snk file). And, even if it looks like the contents of the .pfx file are a certificate with an end-date, that is irrelevant as the end-date is merely metadata and neither Visual Studio nor SQL Server make use of it.

    Yes, Certificates, if when creating you don't specify the end date or the number of months that it is valid, will default to an end date of 1 year from the creation date. This is easy to remedy when creating Certificates by simply specifying the end date (I usually use 2099-12-31). BUT, why are we even talking about Certificates in the first place? If a .pfx file is created from Visual Studio by clicking the "Signing..." button and creating a new file, that does not create a Certificate. It creates a strong name key file that has an extension of .snk if not protected by a password, or .pfx if protected by a password (my preference). Strong name keys do not have expiration dates. I created a .pfx file many years ago for SQL# and it still works for signing new builds and allowing all existing Assemblies to function properly.

    In your cross-posted version of question on SQL Server Central, you stated:

    I checked my .pfx file with "C:\Windows\System32\certutil.exe", and it has an expiration date about 9 months from now.

    Yes, when using certutil -dump it does appear that there is a Certificate with an end-date (NotAfter) of 1 year after the begin date (NotBefore). But that is a bit misleading. If it is a real Certificate then it was only due to that being the only "type" of thing that can be contained in a .pfx file that can have both the public and private keys together. And again, the begin and end dates are merely metadata and are not honored / used by either Visual Studio or SQL Server. As I mentioned above, I use .pfx files for all of my projects as I prefer to have the public - private key pair password protected, and have multiple VS Solutions going back 4 or more years, each using their own .pfx file (for multiple Projects / Assemblies per Solution) and have never had a problem, either in Visual Studio or SQL Server. And there is no option for the Asymmetric Key within SQL Server to have an end date / expiration date anyway.

    So, whatever experience your coworker had with signing their Assemblies with a .pfx file is 100% irrelevant to what you are doing.

    I created a PFX file using the above steps (posted 6/29/17). Using the Visual Studio 2015 SQLCLR project properties [Signing…] button, I set my new PFX file as the signing file. With that, Visual Studio could not build the dll; I just get "Build failed" in the status bar with no explanation.

    There was an explanation, but you either didn't scroll up enough in the "output" window to see it, or you have the build output level set to "minimal" or "normal" instead of "detailed" / "debug" (whichever it is). The error preventing the build was that the Certificate (or at least the private key) had not been added to the container since you created it manually. The error message gives you the container name and you need to use that along with sn -i from a command line to install it to that container.

    HOWEVER, even though that will let you build successfully, it won't allow the Assembly to be marked as EXTERNAL_ACCESS or UNSAFE since you are now using a Certificate and Visual Studio neither creates, nor makes use of, Certificates. But that shouldn't be a problem as you never needed to go down this path in the first place given that your original setup of using an Asymmetric Key was just fine. After all, if you check the sys.asymmetric_keys system catalog view you will see that there is no "expiration_date" column like there is in sys.certificates.

    CONCLUSION: What you were doing prior to asking this question was exactly what you should have been doing. Don't over complicate things by making unnecessary changes to the already proper process.


    Now, regarding some of the other things mentioned in other responses here:

    • Yes, TRUSTWORTHY most definitely should be set to OFF unless ABSOLUTELY necessary, and there are hardly any (good) reasons for setting it to ON. The only valid reason I have seen so far for enabling TRUSTWORTHY is to be able to import an unsupported .NET Framework library. Outside of that, and even outside of SQLCLR, I have yet to come across a scenario that wasn't solvable via module signing.
    • Regarding the new "clr strict" configuration setting in SQL Server 2017 and the need to load ALL Assemblies with an already existing Certificate or Asymmetric Key, I have posted two solutions that both work with Visual Studio / SSDT and require no external files (.dll, .cer, .snk, etc):




    • Marked as answer by JediSQL Wednesday, July 5, 2017 8:18 PM
    • Edited by Solomon Rutzky Thursday, August 24, 2017 1:45 AM
    Monday, July 3, 2017 5:56 AM

All replies

  • Hi Dan,

    Not really an answer, as I haven't had time to look into it at all. However, is there a reason you don't want to / cannot use TRUSTWORTHY? E.g. ALTER DATABASE some_name SET TRUSTWORTHY ON;

    That was you would not need to do any signing.

    Niels


    http://www.nielsberglund.com | @nielsberglund

    Wednesday, June 28, 2017 3:19 AM
    Moderator
  • I am seeking information to improve my expertise so that I am properly informed when deciding between best practices implementations and quick & dirty implementations.


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

    Wednesday, June 28, 2017 4:56 PM
  • Not really an answer, as I haven't had time to look into it at all. However, is there a reason you don't want to / cannot use TRUSTWORTHY? E.g. ALTER DATABASE some_name SET TRUSTWORTHY ON;

    Nisse! You should know better!

    Casually, setting a database trustworthy can be a big security risk. If
    the database is owned by sysadmin, and there are users in the database who are db_owner, but who should have no permissions on server level, they can elevate their permissions to sysadmin. On some servers that is not an issue, because there are no such users. At least as long you don't engage a consultant who you give full rights in the database, but limited rights on server level...

    If the database is owned by a non-priv SQL login (which is best practice) and you grant that owner EXTERNAL_ACCESS ASSEMBLY, TRUSTWORTHY is not equally bad, as now all the local db_owner can do is to create external access assemblies.

    But since this is all a little complex, the best is to avoid trustworthy altoghether. Signing is definitely the way to go.

    Wednesday, June 28, 2017 9:33 PM
  • Hej Erland!

    Vad gör en fin man som du i denna här syltan?!! :)

    So in your reply, was there an answer in there, or was it just a "decree from the ivory tower"? Anyway, I do believe that both the OP and myself know the perceived dangers with TRUSTWORTHY, and I consider the dangers not being so much dangers, it all comes down to your environment.

    Anyway, so let us get back to the question, how not to have to use one key per assembly. Today I had some time to spare and dug out some code from the time I just to also spoke form the Ivory Tower, i.e. when I was teaching SQLCLR.

    So here's what we do:

    Create a self signed cerificate, I created the cert with a password of testPwd:

    makecert -r -pe -n "CN=Niels Test Root Authority" 
             -a sha256 -sky signature -cy authority 
             -sv NielsTestPvk.pvk -len 2048 
             -m 144 NielsTestCert.cer

    The -m flag defines the valid lifetime in months for the certificate (so 12 years).

    We then create a pvx file from the pvk file by using the Pvk2Pfx tool:

    PVK2PFX -pvk NielsTestPvk.pvk 
            -spc NielsTestCert.cer 
            -pfx NielsTestPfx.pfx -po testPwd

    The -pfx flag defines the name of the pfx file you want, and the -po is the password for the cert/pvk.

    Let's now switch over to the database and create a SQL server cert on the certificate we created in the first step:

    USE master;
    GO
    
    CREATE CERTIFICATE NielsTestCert 
    FROM FILE = 'D:\CertTest\cert\NielsTestCert.cer'
    GO

    The certificate file has to be somewhere where SQL Server can get to it.

    We then create a login from the SQL certificate and grant unsafe access to the login (I went with UNSAFE, as most of our SQLCLR assemblies are UNSAFE):

    CREATE LOGIN login_NielsTestCert 
    FROM CERTIFICATE NielsTestCert
    GO
    
    GRANT UNSAFE ASSEMBLY TO login_NielsTestCert;
    GO

    My unsafe assembly looks something like this:

    using System.Threading;
    
    namespace SqlClrTest
    {
      public class Functions
      {
        public static int fn_clr_LongRunning()
        {
          Thread.Sleep(10000);
          return 999;
        }
      }
    }

    Notice in the code how we use threads, which is definitely not safe. And if we were to try and deploy it as it stands straight after building it, we would get told that the assembly is not meeting the requirements for being UNSAFE. So what we want to do is to sign the assembly with the previously created pfx file, and we use the SignTool.exe for this:

    signtool sign /f NielsTestPfx.pfx 
                  /p testPwd SqlClrTest.dll

    Having done this we can now deploy the assembly, create the function and test it:

    CREATE ASSEMBLY SqlClrTest
    FROM 'D:\CertTest\cs\SqlClrTest\bin\Release\SqlClrTest.dll'
    WITH PERMISSION_SET = UNSAFE;
    GO
    
    CREATE FUNCTION fn_clr_LongRunning()
    RETURNS INT
    EXTERNAL NAME SqlClrTest.[SqlClrTest.Functions].fn_clr_LongRunning
    GO
    
    SELECT dbo.fn_clr_LongRunning()
    GO

    And that's about it, the pfx file can now be used for as many assemblies as needed - and AFAIK there is no expiry on the pfx file, it is however on the certificate.

    Hope this helps!

    Niels



    http://www.nielsberglund.com | @nielsberglund

    • Marked as answer by JediSQL Friday, June 30, 2017 10:31 PM
    Thursday, June 29, 2017 1:03 PM
    Moderator
  • Niels,

    Thanks, I'll give that a try (it might take a couple days to get to it).  I'll let you know how it goes.


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




    • Edited by JediSQL Thursday, June 29, 2017 7:08 PM
    Thursday, June 29, 2017 7:07 PM
  • Vad gör en fin man som du i denna här syltan?!! :)

    Jag är utsänd från Hälsovårdskontoret. :-)

    So in your reply, was there an answer in there, or was it just a "decree from the ivory tower"? Anyway, I do believe that both the OP and myself know the perceived dangers with TRUSTWORTHY, and I consider the dangers not being so much dangers, it all comes down to your environment.

    It was certainly not an answer a question to the original question. Rather I am interested in the answer myself, as I am considering to switching to using pfxfile myself, since the new CLR strict security will kill my current plot of loading a key through a safe dummy assembly.

    Yes, it is true that whether TRUSTWORTHY is an issue depends on the situation, and I pointed out situations where it is not an issue. However, the situations where it definitely is an issue are far too common for the dangers to be ignored.

    Thursday, June 29, 2017 9:29 PM
  • Niels,

    Thank you, I was able to make that work, but it has significant downsides.

    In Visual Studio 2015, in the SQLCLR project properties, there is the [Signing…] button.  If you use that to create a .pfx file to sign the assembly with as it is compiled, then you can use "CREATE ASYMMETRIC KEY [keyName] FROM EXECUTABLE FILE = 'myAssembly.dll'".  From that ASYMMETRIC KEY you can create the LOGIN with EXTERNAL ACCESS ASSEMBLY permission.  To deploy the assembly to another server in the future, you only need to keep track of the dll, because a SQL Server sysadmin can always create the ASYMMETRIC KEY and LOGIN from the dll.

    I created a PFX file using the above steps (posted 6/29/17).  Using the Visual Studio 2015 SQLCLR project properties [Signing…] button, I set my new PFX file as the signing file. With that, Visual Studio could not build the dll; I just get "Build failed" in the status bar with no explanation.

    When I had Visual Studio not signing the file, I was able to sign the dll using signtool.exe as shown above.  But, SQL Server would not let me "CREATE ASYMMETRIC KEY [keyName] FROM EXECUTABLE FILE = 'myAssembly.dll'.  That means that I would have to keep track of the certificate file as well as the dll for future deployments.  And as certificates and dlls build up over time, which goes with which.

    Like Erland, I would like to know how to make my own .pfx file that works seamlessly with the "Visual Studio SQLCLR project properties [Signing…] button" and the SQL Server's "CREATE ASYMMETRIC KEY [keyName] FROM EXECUTABLE FILE = 'myAssembly.dll'".


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


    • Edited by JediSQL Friday, June 30, 2017 3:30 PM typo
    Friday, June 30, 2017 3:27 PM
  • Hi Dan,

    OK, I hear what you say. First of all - we never use the Visual Studio SQLCLR Project when building assemblies, so I would not really know how it behaves.

    With the way I laid it out above, you can get by with one certificate, one login based on the certificate, and one pfx file. E.g, you can use the same cert, etc., for multiple assemblies.

    Niels


    http://www.nielsberglund.com | @nielsberglund

    Friday, June 30, 2017 4:09 PM
    Moderator
  • Like Erland, I would like to know how to make my own .pfx file that works seamlessly with the "Visual Studio SQLCLR project properties [Signing?] button" and the SQL Server's "CREATE ASYMMETRIC KEY [keyName] FROM EXECUTABLE FILE = 'myAssembly.dll'".

    No, no, I certainly don't want to do it with Visual Studio. I'm a database guy. I can manage Source Code Explorer in VS, and sometimes also Team Explorer. The rest makes my head spin. It happens that I write simple stuff in C#, but I do that in EditPlus and compile on the command line. Have no idea how to do it in VS.

    I think I know how do to the signing with pfx files and how to load them, at least approxmitely, but I will package this in an existing tool (written in Perl). But your post about expiration made me a little nervous.

    Friday, June 30, 2017 9:07 PM
  • I use Visual Studio to edit and compile the code, but I do not use it's SQLCLR deployment features. MS products can be hit or miss, but I have found that they generally have pretty good code editors. The Visual Studio 2015 F1-key context sensitive help for .Net classes, methods, etc. is very helpful.

    After I compile the code, I just copy the dll to a file server from which I do my assembly deployments. I write my own CREATE ASSEMBLY statements using a template that knows where the file server share is.  That way, as I deploy the assembly to Dev, Staging, QA and Production, I know I'm getting the exact same bits.

    Putting together some cmd/PowerShell scripts to do the compile, sign and copy to file server doesn't sound half bad either.  But I do really want the stand alone dll files, not ones that need a tag-along cert file.

    Niels,

    Even though that wasn't the exact answer I was looking for, I'll mark it as an answer, because I did learn some useful stuff from it.   (Is this a Geico ad, or something???)

    I'm going to start a new thread in my quest for the perfect .pfx file.


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

    Friday, June 30, 2017 10:30 PM
  • Hi Dan!

    If you come up with some new "stuff" about pfx, please let us know.

    I just published a blog-post, loosely related to this thread here: http://www.nielsberglund.com/2017/07/01/sqlclr-certificates/

    Niels


    http://www.nielsberglund.com | @nielsberglund

    Saturday, July 1, 2017 5:47 AM
    Moderator
  • 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.

    The accepted is incorrect: it ignores the actual issue and goes down a needless, overly complex path. The question itself is based on a misunderstanding of what is going on, and that is never addressed, hence the thread that follows is just confusion.

    ALL that needs to be said regarding this issue is: It is much ado about nothing, a complete non-issue! Why? Because .pfx files are not Certificates, per se. A .pfx file can contain a Certificate, but it can also contain other things that are not Certificates. And in the case of Visual Studio creating the .pfx file, it is merely a password-protected container for the .snk file (well, the public and private key pair that is in the .snk file). And, even if it looks like the contents of the .pfx file are a certificate with an end-date, that is irrelevant as the end-date is merely metadata and neither Visual Studio nor SQL Server make use of it.

    Yes, Certificates, if when creating you don't specify the end date or the number of months that it is valid, will default to an end date of 1 year from the creation date. This is easy to remedy when creating Certificates by simply specifying the end date (I usually use 2099-12-31). BUT, why are we even talking about Certificates in the first place? If a .pfx file is created from Visual Studio by clicking the "Signing..." button and creating a new file, that does not create a Certificate. It creates a strong name key file that has an extension of .snk if not protected by a password, or .pfx if protected by a password (my preference). Strong name keys do not have expiration dates. I created a .pfx file many years ago for SQL# and it still works for signing new builds and allowing all existing Assemblies to function properly.

    In your cross-posted version of question on SQL Server Central, you stated:

    I checked my .pfx file with "C:\Windows\System32\certutil.exe", and it has an expiration date about 9 months from now.

    Yes, when using certutil -dump it does appear that there is a Certificate with an end-date (NotAfter) of 1 year after the begin date (NotBefore). But that is a bit misleading. If it is a real Certificate then it was only due to that being the only "type" of thing that can be contained in a .pfx file that can have both the public and private keys together. And again, the begin and end dates are merely metadata and are not honored / used by either Visual Studio or SQL Server. As I mentioned above, I use .pfx files for all of my projects as I prefer to have the public - private key pair password protected, and have multiple VS Solutions going back 4 or more years, each using their own .pfx file (for multiple Projects / Assemblies per Solution) and have never had a problem, either in Visual Studio or SQL Server. And there is no option for the Asymmetric Key within SQL Server to have an end date / expiration date anyway.

    So, whatever experience your coworker had with signing their Assemblies with a .pfx file is 100% irrelevant to what you are doing.

    I created a PFX file using the above steps (posted 6/29/17). Using the Visual Studio 2015 SQLCLR project properties [Signing…] button, I set my new PFX file as the signing file. With that, Visual Studio could not build the dll; I just get "Build failed" in the status bar with no explanation.

    There was an explanation, but you either didn't scroll up enough in the "output" window to see it, or you have the build output level set to "minimal" or "normal" instead of "detailed" / "debug" (whichever it is). The error preventing the build was that the Certificate (or at least the private key) had not been added to the container since you created it manually. The error message gives you the container name and you need to use that along with sn -i from a command line to install it to that container.

    HOWEVER, even though that will let you build successfully, it won't allow the Assembly to be marked as EXTERNAL_ACCESS or UNSAFE since you are now using a Certificate and Visual Studio neither creates, nor makes use of, Certificates. But that shouldn't be a problem as you never needed to go down this path in the first place given that your original setup of using an Asymmetric Key was just fine. After all, if you check the sys.asymmetric_keys system catalog view you will see that there is no "expiration_date" column like there is in sys.certificates.

    CONCLUSION: What you were doing prior to asking this question was exactly what you should have been doing. Don't over complicate things by making unnecessary changes to the already proper process.


    Now, regarding some of the other things mentioned in other responses here:

    • Yes, TRUSTWORTHY most definitely should be set to OFF unless ABSOLUTELY necessary, and there are hardly any (good) reasons for setting it to ON. The only valid reason I have seen so far for enabling TRUSTWORTHY is to be able to import an unsupported .NET Framework library. Outside of that, and even outside of SQLCLR, I have yet to come across a scenario that wasn't solvable via module signing.
    • Regarding the new "clr strict" configuration setting in SQL Server 2017 and the need to load ALL Assemblies with an already existing Certificate or Asymmetric Key, I have posted two solutions that both work with Visual Studio / SSDT and require no external files (.dll, .cer, .snk, etc):




    • Marked as answer by JediSQL Wednesday, July 5, 2017 8:18 PM
    • Edited by Solomon Rutzky Thursday, August 24, 2017 1:45 AM
    Monday, July 3, 2017 5:56 AM
  • Solomon,

    Thank you for the detailed explanation.


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

    Wednesday, July 5, 2017 8:20 PM
  • I am considering to switching to using pfxfile myself, since the new CLR strict security will kill my current plot of loading a key through a safe dummy assembly.

    Hello Erland. Good news: you don't need to give up using an empty Assembly as a means of creating the Asymmetric Key :-). I posted a solution for that, including a working example of the technique, here:

    SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1

    Also, I came up with a simplified approach that uses just a certificate (no strong name key / asymmetric key) and posted that solution, also with a working example, here:

    SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2

    In either case, no need to switch to using an external file!

    No, no, I certainly don't want to do it with Visual Studio. I'm a database guy. I can manage Source Code Explorer in VS, and sometimes also Team Explorer. The rest makes my head spin. It happens that I write simple stuff in C#, but I do that in EditPlus and compile on the command line. Have no idea how to do it in VS.

    I am also a database guy, but I completely agree with Dan about using Visual Studio for compiling, but not deployment. There are so many command-line switches for csc.exe that I couldn't imagine not having Visual Studio to help with that, plus the IntelliSense, etc. True, the two solutions I mentioned previously use Visual Studio / SSDT for the deployment, but that is mainly because that needs to be a viable option for most people. However, neither of those solutions requires VS / SSDT for the deployment. Personally, I don't use VS / SSDT for deployments due to several deficiencies in them (e.g. no support for parameter defaults) and instead use custom CMD scripts and T4 templates. But development is in VS and compilation is VS or MSBuild (for automation).

    I think I know how do to the signing with pfx files and how to load them, at least approxmitely, but I will package this in an existing tool (written in Perl). But your post about expiration made me a little nervous.

    Strong name keys do not expire. The PFX file created when you protect the strong name key with a password is merely a password-protected container for the strong name key pair. Yes, the PFX file has an expiration date in it, but it doesn't matter. I have been using the same PFX file for SQL# for 5 - 8 years now, and would be shocked if it ever stopped working due to an expiration that doesn't exist had I not protected it with a password, hence it would have remained as an SNK file.

    But fortunately that is all moot since these files still aren't needed :-)


    Thursday, August 24, 2017 7:36 AM
  • I use Visual Studio to edit and compile the code, but I do not use it's SQLCLR deployment features. MS products can be hit or miss, but I have found that they generally have pretty good code editors. The Visual Studio 2015 F1-key context sensitive help for .Net classes, methods, etc. is very helpful.

    Hi Dan. I completely agree here. I too use Visual Studio for editing and compiling, but I have my own custom build system using CMD scripts and T4 templates.

    After I compile the code, I just copy the dll to a file server from which I do my assembly deployments. I write my own CREATE ASSEMBLY statements using a template that knows where the file server share is.  That way, as I deploy the assembly to Dev, Staging, QA and Production, I know I'm getting the exact same bits.

    Putting together some cmd/PowerShell scripts to do the compile, sign and copy to file server doesn't sound half bad either.  But I do really want the stand alone dll files, not ones that need a tag-along cert file.

    I would argue that relying upon an external file is still too brittle of a setup, even if you use the same file share for each environment. Using a file share in the first place introduces points of failure that wouldn't be there if the code being deployed was 100%  contained in the SQL script. Then there is no doubt about what is being deployed or what was deployed, at any point in time, even years down the road. So I recommend against deploying from the DLL and instead suck the DLL into the SQL deploy script. I have just published two blog posts detailing two methods of doing the deployments entirely from SQL scripts. Both techniques work with SSDT for deployment, but can just as easily be worked into a custom deployment:


    Thursday, August 24, 2017 9:32 PM
  • Thanks Solomon. I looked at your blog posts. I first the first solution to be horrendeously complicated, and I see absolutely no reason to make it that difficult. As I understand it, it is a kludge to workaround that you are using Visual Studio, which already itself is horrendeously complicated.

    The second solution is something I've looked into myself, although I have not come around to automate it, as we have yet come to the point where we need it.

    I entirely disagree with your opinion on  sp_add_trusted_assembly. This can indeed serve as a very simple solution. Say that you work for an ISV (which my main client happens to be), and you develop a product that use assemblies. Say furthermore, that you cannot assume that the database part of your application will be installed by someone with sysadmin rights (we usually get away with it, but we can't be sure).

    Our current approach is that if we don't have sysadmin at install time, we leave the database non-functional with its dummy assemblies and hope the DBA runs a post-deploy procedure. (Which is also needed if they move the database to a different server.)

    With trusted assemblies, an ISV can publish scripts to add hashes for their assemblies on their web site. The page can be signed with the private key, and the customers can use their public key to guarantee that the contents is authentic. At install time, the installer can check whether the hashes has been loaded or not and abort installation if not. (If the install process has VIEW SERVER STATE, it can check sys.trusted_assemblies directly. Else it will have to try brute force.) If the installer runs as sysadmin, it can load the hashes itself obviously.

    On the other hand, if you can make the assumption that the installation as sysadmin, using sp_add_trusted_assembly is very simple. You can compute the hash with some command-line tool or in PowerShell. Or you can load the assmebly into a binary variable in SQL Server and use hash_bytes. Not complicated at all.
    The only issue I see is that by time, as you change assemblies, you may have tons of rows in sys.trusted_assemblies and it can be difficult to figure which are needed.

    I am also a database guy, but I completely agree with Dan about using Visual Studio for compiling, but not deployment. There are so many command-line switches for*csc.exe* that I couldn't imagine not having Visual Studio to help with that,

    "csc /?" lists the command-line options nicely divided into areas. I find no problems in reading there, the few times I needed.

    plus the IntelliSense, etc.

    I have never been fond of Intellinonsense, and I always turn it off in SSMS. Way back, when I did occasional work in Visual Basic, I kept it on, because I tried to tell myself that given my level that I was better off using it. But every once in a while there were errors can only can be explained by incorrect selections from Intellisense.

    Saturday, August 26, 2017 9:34 AM
  • Thanks Solomon. I looked at your blog posts. I find the first solution to be horrendously complicated, and I see absolutely no reason to make it that difficult. As I understand it, it is a kludge to workaround that you are using Visual Studio, which already itself is horrendously complicated.

    Hello. To a degree yes, Solution 1 is a bit complicated, though I did admit that in the post itself. Part of what makes it complicated is that I broke it down into very discreet steps so that it would be easier to follow. And, several of those steps are Visual Studio-specific. If you take VS out of it, then the same concept works just as well but with fewer steps. Yes, it would ideally be simpler, which is why I have suggested "Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE" (which is really something that should have been there from day 1 when SQL Server 2005 was released). So without that, it requires a few extra steps. BUT, you need to keep in mind that these are one-time setup steps. And for an ISV, it could be once per product or once for all products if you want to use the same signing Certificate and Strong Name Key files for everything. This is certainly not per Assembly. For example, I did the initial setup years ago for SQL# and over time I have changed Assemblies, and added Assemblies, and never had to touch that initial part again. Not until I needed to deal with "CLR strict security". And then, I only needed to:

    1. Create a Certificate
    2. Sign the existing empty Assembly used for creating the Asymmetric Key with the new Certificate
    3. Create the Certificate in master
    4. Create the Login for that Certificate

    That's it. Very little work and the stuff that has been in place for many years has not been touched and yet keeps working. So, to say it is "horrendously" complicated is a bit overstated, especially when performing all of the steps on a new project is maybe 10 minutes of work (even less if you copy my demo script which is already idempotent) :-).


    I entirely disagree with your opinion on  sp_add_trusted_assembly. This can indeed serve as a very simple solution. Say that you work for an ISV, and you develop a product that uses assemblies. Say furthermore, that you cannot assume that the database part of your application will be installed by someone with sysadmin rights (we usually get away with it, but we can't be sure).

    Our current approach is that if we don't have sysadmin at install time, we leave the database non-functional with its dummy assemblies and hope the DBA runs a post-deploy procedure. (Which is also needed if they move the database to a different server.)

    With trusted assemblies, an ISV can publish scripts to add hashes for their assemblies on their web site. The page can be signed with the private key, and the customers can use their public key to guarantee that the contents is authentic. At install time, the installer can check whether the hashes has been loaded or not and abort installation if not. (If the install process has VIEW SERVER STATE, it can check sys.trusted_assemblies directly. Else it will have to try brute force.) If the installer runs as sysadmin, it can load the hashes itself obviously.

    On the other hand, if you can make the assumption that the installation as sysadmin, using sp_add_trusted_assembly is very simple. You can compute the hash with some command-line tool or in PowerShell. Or you can load the assmebly into a binary variable in SQL Server and use hash_bytes. Not complicated at all.
    The only issue I see is that by time, as you change assemblies, you may have tons of rows in sys.trusted_assemblies and it can be difficult to figure which are needed.

    Thanks for this feedback. I realize that when I posted the comment that you were responding to I had been rather vague about what I did not like about "Trusted Assemblies" outside of them being unnecessary for new development. However, I have now had the time to document the specifics in the following post:

    SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment

    That post just goes over the general concepts. I added another post to detail my thoughts regarding the use case you suggested above. To summarize here: while it is an interesting usage, it still a) provides no benefits beyond what Certificates have been providing since SQL Server 2012, and b) has several drawbacks. Certificates provide far more security, can be created ahead of time just as easily as registering the silly "trusted" hash, and work across 4 versions of SQL Server instead of just 1. Also, they don't require sysadmin / CONTROL SERVER / VIEW STATE to work with, so they don't need a pre-install step as you are describing here. But, if one prefers, they can be handled in a pre-install step, and this would still be better than Trusted Assemblies because:

    1. public keys are meant to be just that: public. Posting a hash, on the other hand, is inherently insecure as it allows anyone to get it and spend time manipulating a harmful Assembly until it matches the hash. But having the public key doesn't allow for that in any practical sense. In fact, what you are advocating here is backwards from what is usually done: companies will provide a public key and then a public hash is used to verify it. In this setup, the hash being public is not an issue because it provides no insight into the private key.
    2. only a single public key needs to be loaded once, before the first installation. After that, updates to the Assemblies, and even adding more Assemblies, will not require any additional admin work. Trusted hashes will need to be registered for each Assembly, and per each change. AND, there is the issue you mentioned about the existing hashes that become obsolete upon each change.

    Hence, what you are suggesting is actually more work, less secure, and potentially more long-term maintenance than Certificates. The post detailing all of this is:

    SQLCLR vs. SQL Server 2017, Part 5: “Trusted Assemblies” – Valid Use Cases?

    And, I have one more post to type up regarding the fact that Trusted Assemblies do not allow for module signing of the T-SQL wrapper objects of SQLCLR methods.

    So, due to finding nothing but problems with this feature, I have submitted a request to have it removed:

    Trusted Assemblies are more problematic yet less functional than Certificates - Please Remove



    Friday, September 15, 2017 10:40 PM
  • I have long been a fan of Perl, and in Perl the motto is "There is more than way to do it", and that is a motto I have found applicable in other environments as well. Thus, I see a benefit of that there are more than one way to achieve the same thing. If trusted assemblies are not for you - well, don't use them.

    If I would set up a mechanism for a new environment, I would rely on trusted assemblies, because they are simpler to automate. You make an argument of that trusted assemblies is nothing that can be used on older SQL versions. First of all, with that sort of reasoning we can ditch all new features. E.g. "there is no point with CREATE CERTIFICATE FROM BINARY, because it does not work on SQL 2008". New features may not always be usable directly, because you need to support older platforms, but one day all those old systems have been upgraded. And in this particular case, this is a new feature that addresses a new security mechanism. If you only have safe assemblies, those older SQL Server versions are not of a concern for you.

    You have a point that the hashes provide weaker security than certificates and keys. But if you think this is important, don't use them. You seem to be using something that has its own security problems anyway.

    To wit, you suggest that the certificate/asymmetric key has to be installed in master once. But this means that the same private key must be used to sign the assemblies, and this means that the password that protects the key must be known. And a password, or what other mechanism that is used to protect the cert/key, can obviously be compromised. My preference is defintely for creating new keys every time and throw password and private key away.

    You also say that once the key/cert that there is no need for sysadmin. That is only partly true. There is no need to run SQL statements as sysadmin. But still someone has to verify that the assembly is safe and does not perform unacceptable actions. After all, that is the whole point with the permission business. And it is that person who is going to sign the assembly to give approval. Obviously that does not have to be the DBA, but the DBA is the one who is ultimately responsible.

    If you are in a small shop, maybe you trust the developers (or you are the developer yourself), and give them full access to all parts of the key. But in that case, you could just as well write a small procedure that wraps sp_add_trusted_assembly, and then you sign that wrapper with a certificate and grant a login created with that certificate CONTOL SERVER, so that any one can enter hashes without being sysadmin.

    If you are in a big shop, you cannot trust random developers. You are probably more likely to trust a list of hashes from a reputable vendor, than a key from Joe Random inside the company. So that requires a bit of admin red tape in some shape of form anyway, be that inside or outside SQL Server.
    You also make a point about the littering. And it is true that by time you could acquire quite a big list of hashes. However, both you and I only need an hour or two to write a stored procedure that trawls all databases, and drops all trusted assemblies which has no match among the existing assemblies (possibly exempting those that are recently loaded if you pre-load hashes).

    I will have to admit that the solution with white-listening is not my ultimate favourite. I would prefer to be able to load the assembly, but having it passive until it has been signed. However, I am told that when an assembly is loaded, part of it is executed, and thus malicious code could execute right there. So there would have to be a way to load an assembly as a bunch of stupid bits, and later activate it. It goes without saying, that this is a solution that requires more work for Microsoft, whereas the white-listning mechanism was fairly cheap implementation-wise.

    Saturday, September 16, 2017 12:15 PM