none
CLR Strict Security: It is still not very clear to me RRS feed

  • Question

  • I've read the text on https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/clr-strict-security?view=sql-server-2017. I have also read the comments and the articles referred to in the comments. It is still not very clear to me. 

    I have the following questions:

    1. Why is Code Access Security no longer supported as a security boundary? Has it become unsafe? Or was it always unsafe from the beginning. Or does Microsoft decided to use something else? Or is it perhaps incompatible with .Net Core or SQL Server on Linux?

    2. If all assemblies are considered (potientially) unsafe, does signing them make them make them safer? Or is it just a way to prevent unauthorized loading of assemblies? (Or even worse, just a way to discourage loading assemblies?)

    3. Why is CLR strict security enabled by default on SQL Server 2017, and not on lower (patched) versions? 

    4. Is Code Access Security no longer supported as a security boundary only on SQL Server 2017? Or on all versions, starting with SQL Server 2005?

    5. Is setting CLR strict security to disabled a real security risk? How does this differ from older versions, starting with SQL Server 2005 with clr enabled? Does downgrading to SQL Server 2016 make things safer?

    6. How easy is it to exploit this, compared to other untrusted (virtually all) software? Or, if you need something more specific to answer, compared to extended stored procedures?

    I have tried to comment on the page, but it doesn't let login properly.
    Friday, July 27, 2018 4:25 AM

Answers

  • 1. Why is Code Access Security no longer supported as a security boundary? Has it become unsafe? Or was it always unsafe from the beginning. Or does Microsoft decided to use something else? Or is it perhaps incompatible with .Net Core or SQL Server on Linux?

    That's a decision the .NET team took many years ago. Why SQL Server did not react until now - don't know.

    2. If all assemblies are considered (potientially) unsafe, does signing them make them make them safer? Or is it just a way to prevent unauthorized loading of assemblies? (Or even worse, just a way to discourage loading assemblies?)

    Signing does not make them safer. But it gives the server-level DBA control over what assemblies that are installed on the server.

    3. Why is CLR strict security enabled by default on SQL Server 2017, and not on lower (patched) versions? 

    Compatibility reasons.

    4. Is Code Access Security no longer supported as a security boundary only on SQL Server 2017? Or on all versions, starting with SQL Server 2005?

    CAS stopped being a security boundary with .NET 3.5, which for SQL Server
    means from SQL 2012.

    5. Is setting CLR strict security to disabled a real security risk? How does this differ from older versions, starting with SQL Server 2005 with clr enabled? Does downgrading to SQL Server 2016 make things safer?

    Whether it is a security risk depends a lot on your organisation and what your applications look like.

    Basically, a thing like this can be exploited in two ways:
    1) By a rogue developer.
    2) By someone on the outside through SQL injection.

    Whether this is a risk that applies to you is something you need to assess yourself.

    6. How easy is it to exploit this, compared to other untrusted (virtually all) software? Or, if you need something more specific to answer, compared to extended stored procedures?

    It would require expertise in .NET to answer. I don't know if it can be exploited at all. But I know that if can be exploited, it will be sooner or later.

    • Proposed as answer by Xi JinModerator Monday, July 30, 2018 9:02 AM
    • Marked as answer by Arjen Krap Monday, July 30, 2018 11:47 PM
    Friday, July 27, 2018 10:33 PM
  • Am I correct to assume that CAS was used in SQL Server 2005-2016 to implement the differences between SAFE, EXTERNAL_ACCESS, and UNSAFE? While in SQL Server 2017 without CAS only UNSAFE exists, but the SAFE and EXTERNAL_ACCESS keywords are still support for backwards compatibility?

    There is no difference with regards to CAS between SQL 2017 and earlier versions.

    Note that CLR strict security is available in SQL 2012, SQL 2014 and SQL 2016 starting with specific updates, as described in https://support.microsoft.com/en-us/help/4018930/update-adds-the-clr-strict-security-feature-to-sql-server-2016
    Note that in these version, the configuration option is not there, only a trace flag.

    Next to this, the CLR strict security configuration option only mandates signed assemblies. This change on it's own is not directly related to the CAS issue. Am I right? 

    When the CLR strict security option is in effect, all assemblies are considered to be unsafe, and therefore any of these measures needs to be taken:

    1) The database is set TRUSTWORTHY and the database owner has the permission UNSAFE ASSEMBLY. (This may be good if you want to play it quick on your development server, but not on production, since TRUSTWORTHY can be a big security risk.

    2) The assembly is signed with a strong key which is imported into master as an asymmetric key and a login created from this key is granted UNSAFE ASSEMBLY.

    3) A hash for the assmebly bits is added with the stored procedure sp_add_trusted_assembly. (This option exists only in SQL 2017.)

    • Marked as answer by Arjen Krap Monday, July 30, 2018 11:47 PM
    Monday, July 30, 2018 9:25 PM
  • Ok thank you all for your responses.

    In the mean time I have found the following article: https://sqlquantumleap.com/2018/02/23/sqlclr-vs-sql-server-2012-2014-2016-part-7-clr-strict-security-the-problem-continues-in-the-past-wait-what/ 

    In this article it's explained that support for CAS actually stopped  (or changed, I'm still not sure what happend) in .Net 4.0. This makes sense because .Net 4.0 was a major release, just like .Net 2.0, while versions 3.0, 3.5, 4.5, 4.6, etc, where feature releases. Also SQL Server started to use .Net 4.0 (and higher) from SQL Server 2012 (and higher). At the same time of the release of .Net 4.0 we also had the the release of Windows 8 in which Microsoft took a different direction from Windows 7.

    So all SQL Server version from 2012 to 2017 are affected. While SQL Server 2005 to 2008 R2 are not. This also explains that there is no patch for 2008 R2. We still have less than 1 year of extend support for 2008 and 2008 R2 according to https://blogs.msdn.microsoft.com/sqlreleaseservices/end-of-mainstream-support-for-sql-server-2008-and-sql-server-2008-r2/.

    What does this mean? Well in SQL Server 2005 to 2008 R2 we had a sandbox for our .Net code running in SQL Server. In SQL Server 2012 to 2017 this sandbox is obviously broken, so we have to be extra careful. Code signing might be something for organizations that need to be extra careful and perhaps also for commercial software. But for internally developed applications or just some handy regex functions to make live more easier I don't think it will be worth it. You do need to have special privileges at the database level  to create an assembly any, and that on its own might be a bigger security issue in most cases. But is still acceptable for most companies.  Also code written for external programs, sandboxed (CLR, JRE, etc) , interpreted, or native code might have the same security risks, signed or not signed. 

    • Marked as answer by Arjen Krap Monday, July 30, 2018 11:47 PM
    Monday, July 30, 2018 11:46 PM
  • 1. Why is Code Access Security no longer supported as a security boundary? Has it become unsafe? Or was it always unsafe from the beginning. Or does Microsoft decided to use something else? Or is it perhaps incompatible with .NET Core or SQL Server on Linux?

    Code Access Security (CAS) underwent a major change (though not eliminated) in .NET 4.0 ( Summary of Changes in Code Access Security) and security was again improved in .NET 4.5 ( Enhanced Strong Naming ). From what the "Enhanced Strong Naming" documentation is talking about, it sounds like this is something that has become less secure over time. But also, I suspect that what has forced the issue in SQL Server 2017 is the same concern that got Microsoft to rip SQLCLR support out of Azure SQL Database V12 rather abruptly in mid-April, 2016.

    2. If all assemblies are considered (potientially) unsafe, does signing them make them make them safer? Or is it just a way to prevent unauthorized loading of assemblies? (Or even worse, just a way to discourage loading assemblies?)


    I think the main difference is that in the past, as long as a Login had the ability to execute CREATE ASSEMBLY, then any SAFE Assembly could have been loaded. They are now saying that there is potential for a SAFE Assembly to do things that should not be allowed in a SAFE Assembly. Both signing, and the new (and unnecessary) "trusted_assemblies" feature, ensure some level of buy-in from the DBA(s) for what is being loaded.

    3. Why is CLR strict security enabled by default on SQL Server 2017, and not on lower (patched) versions?

    Because of the vast number of Assemblies that would stop working:

    1. There is no sp_configure option to turn this on / off in SQL Server 2012, 2014, or 2016. It requires a trace flag set as a startup parameter in the registry. If it were on by default, you would need to edit the registry to turn it off. Ouch!
    2. If it is turned on, then unsigned SAFE Assemblies (and I bet most SAFE assemblies are unsigned) will stop working. This is part of why the new "trusted assemblies" feature was added to SQL Server 2017: to magically accept unsigned Assemblies (and remember, SAFE assemblies never forced anyone to sign them, it was purely optional, and Visual Studio makes it extremely difficult to load the Asymmetric Key and Key-based Login). The other part is that nobody at Microsoft knew that you could simply sign the Assembly after it had already been loaded into SQL Server. They weren't back-porting the "trusted assemblies" feature to SQL Server 2012, 2014, or 2016, so in their minds there would have been no way for customers to get their existing, already loaded and working for years, unsigned assemblies working again. Had they known that the assemblies could be signed by a Certificate (and then use that Certificate to create a Login and grant that Login the UNSAFE ASSEMBLY permission), maybe they would have considered automating that over all existing Assemblies and then enabling the option. But that still has problems for DBs that get restored, that were backed-up prior to that change, etc. Much better to have it off by default in environments where this was never planned for up-front.


    5. Is setting CLR strict security to disabled a real security risk? How does this differ from older versions, starting with SQL Server 2005 with clr enabled? Does downgrading to SQL Server 2016 make things safer?

    Greater "risk" depends on your current security practices and where your Assemblies come from. This only truly affects unsigned Assemblies. If you follow good security practices and do NOT set databases to TRUSTWORTHY ON, then EXTERNAL_ACCESS and UNSAFE Assemblies (if you even had any) already required being signed and the associated signature-based Login having the appropriate permission. For that scenario nothing has changed (well, outside of it now taking additional steps to deploy such assemblies purely from a T-SQL script without any external references to DLLs). And even SAFE Assemblies, if they are signed, work the same. This new setting doesn't change what can be done by Assemblies; it just sets the bar higher for what is required to get an Assembly to load. If nobody has CREATE ASSEMBLY permission (explicit or implied), then disabling this shouldn't have any effect.

    6. How easy is it to exploit this, compared to other untrusted (virtually all) software? Or, if you need something more specific to answer, compared to extended stored procedures?

    I don't think it is very easy, nor have I ever heard of anything like this being done. The notice sent to Azure SQL Database customers in early April, 2016 stated that no exploits had been reported, and that disabling support for SQLCLR (it was only SAFE Assemblies that were supported anyway) was merely precautionary.

    Am I correct to assume that CAS was used in SQL Server 2005-2016 to implement the differences between SAFE, EXTERNAL_ACCESS, and UNSAFE? While in SQL Server 2017 without CAS only UNSAFE exists, but the SAFE and EXTERNAL_ACCESS keywords are still support for backwards compatibility?

    Not exactly. in SQL Server 2017, the three PERMISSION_SET options -- SAFE, EXTERNAL_ACCESS, and UNSAFE -- still exist and still function as before.

    Please note that when the documentation states that "with 'CLR strict security' enable, all Assemblies are considered UNSAFE", it does not mean that the Assemblies themselves can act as if they marked as PERMISSION_SET = UNSAFE. It means that when it comes to loading them (either via CREATE ASSEMBLY or at run-time by executing a method within an Assembly), they are all treated as if they are marked as UNSAFE, and verification process for UNSAFE code is used.

    Next to this, the CLR strict security configuration option only mandates signed assemblies. This change on it's own is not directly related to the CAS issue. Am I right?

    In addition to what Erland answered for this: essentially yes. The "CLR strict security" setting only changes the requirements for loading assemblies; it does not affect what they do upon being loaded. This new configuration option isn't an actual security mechanism, it is more of a work-around that merely enforces better security practices. This is most likely because there is no easy way to update the CLR host within SQL Server to handle the new security features (such as Enhanced Strong Naming) without breaking a lot of existing code. I'm sure they are trying to figure out a way to bring SQL Server's CLR host more inline with native CLR, but in the mean-time, we get this.

    For an example of how tricky such an update must be, even adding this new restriction broke a bunch of existing code, code that was just fine in prior versions but has now been updated and can no longer load. And there is no way (no provided way, at least) to export an Assembly, create a strong name key, sign it, use the SNK to create an Asymmetric Key, create a Login from that Key, grant the UNSAFE ASSEMBLY permission, and finally reload the Assembly. Of course, you don't need to export the Assemblies in order to sign them; ADD SIGNATURE will do it, but nobody knew that, so now we have a work-around to the work-around: "trusted assemblies". This is a security feature, it's just clunky, entirely unnecessary, and accomplishes essentially the same goal as signing the code: provide an administrative stamp of approval for the assembly so that it can be loaded.


    Code signing might be something for organizations that need to be extra careful and perhaps also for commercial software. But for internally developed applications or just some handy regex functions to make live more easier I don't think it will be worth it.

    Well, it's not a bad idea to get into the habit of signing code, even if you don't use that strong name key / asymmetric key for load the Assembly into SQL Server. It takes no effort to sign it. And beyond that, there are two approaches that I have come up with that allow for automating the process (and both approaches work on their own or even within Visual Studio / SSDT):

    Of course, all of this would be a thousand times easier if Microsoft would just allow Asymmetric Keys to be created from a VARBINARY literal / variable (like CERTIFICATEs can be as of SQL Server 2012, thanks to a suggestion from Erland, I believe). Given that signing the Assembly in Visual Studio uses a strong name key which equates to an Asymmetric Key in SQL Server, this functionality should have been added back in SQL Server 2005 (i.e. when SQLCLR was introduced). Doing that would have allowed for Visual Studio / SSDT to account for it in the generated deployment script. Not doing so has pushed most people to simply set TRUSTWORTHY ON because that can be done via Visual Studio.

    Take care,
    Solomon...
    https://SqlQuantumLift.com/

    • Marked as answer by Arjen Krap Friday, September 21, 2018 4:06 PM
    Friday, September 21, 2018 6:12 AM

All replies

  • 1. Why is Code Access Security no longer supported as a security boundary? Has it become unsafe? Or was it always unsafe from the beginning. Or does Microsoft decided to use something else? Or is it perhaps incompatible with .Net Core or SQL Server on Linux?

    That's a decision the .NET team took many years ago. Why SQL Server did not react until now - don't know.

    2. If all assemblies are considered (potientially) unsafe, does signing them make them make them safer? Or is it just a way to prevent unauthorized loading of assemblies? (Or even worse, just a way to discourage loading assemblies?)

    Signing does not make them safer. But it gives the server-level DBA control over what assemblies that are installed on the server.

    3. Why is CLR strict security enabled by default on SQL Server 2017, and not on lower (patched) versions? 

    Compatibility reasons.

    4. Is Code Access Security no longer supported as a security boundary only on SQL Server 2017? Or on all versions, starting with SQL Server 2005?

    CAS stopped being a security boundary with .NET 3.5, which for SQL Server
    means from SQL 2012.

    5. Is setting CLR strict security to disabled a real security risk? How does this differ from older versions, starting with SQL Server 2005 with clr enabled? Does downgrading to SQL Server 2016 make things safer?

    Whether it is a security risk depends a lot on your organisation and what your applications look like.

    Basically, a thing like this can be exploited in two ways:
    1) By a rogue developer.
    2) By someone on the outside through SQL injection.

    Whether this is a risk that applies to you is something you need to assess yourself.

    6. How easy is it to exploit this, compared to other untrusted (virtually all) software? Or, if you need something more specific to answer, compared to extended stored procedures?

    It would require expertise in .NET to answer. I don't know if it can be exploited at all. But I know that if can be exploited, it will be sooner or later.

    • Proposed as answer by Xi JinModerator Monday, July 30, 2018 9:02 AM
    • Marked as answer by Arjen Krap Monday, July 30, 2018 11:47 PM
    Friday, July 27, 2018 10:33 PM
  • Ok, thank you for your answers.

    Am I correct to assume that CAS was used in SQL Server 2005-2016 to implement the differences between SAFE, EXTERNAL_ACCESS, and UNSAFE? While in SQL Server 2017 without CAS only UNSAFE exists, but the SAFE and EXTERNAL_ACCESS keywords are still support for backwards compatibility?

    Next to this, the CLR strict security configuration option only mandates signed assemblies. This change on it's own is not directly related to the CAS issue. Am I right? 



    Monday, July 30, 2018 12:19 PM
  • Am I correct to assume that CAS was used in SQL Server 2005-2016 to implement the differences between SAFE, EXTERNAL_ACCESS, and UNSAFE? While in SQL Server 2017 without CAS only UNSAFE exists, but the SAFE and EXTERNAL_ACCESS keywords are still support for backwards compatibility?

    There is no difference with regards to CAS between SQL 2017 and earlier versions.

    Note that CLR strict security is available in SQL 2012, SQL 2014 and SQL 2016 starting with specific updates, as described in https://support.microsoft.com/en-us/help/4018930/update-adds-the-clr-strict-security-feature-to-sql-server-2016
    Note that in these version, the configuration option is not there, only a trace flag.

    Next to this, the CLR strict security configuration option only mandates signed assemblies. This change on it's own is not directly related to the CAS issue. Am I right? 

    When the CLR strict security option is in effect, all assemblies are considered to be unsafe, and therefore any of these measures needs to be taken:

    1) The database is set TRUSTWORTHY and the database owner has the permission UNSAFE ASSEMBLY. (This may be good if you want to play it quick on your development server, but not on production, since TRUSTWORTHY can be a big security risk.

    2) The assembly is signed with a strong key which is imported into master as an asymmetric key and a login created from this key is granted UNSAFE ASSEMBLY.

    3) A hash for the assmebly bits is added with the stored procedure sp_add_trusted_assembly. (This option exists only in SQL 2017.)

    • Marked as answer by Arjen Krap Monday, July 30, 2018 11:47 PM
    Monday, July 30, 2018 9:25 PM
  • Ok thank you all for your responses.

    In the mean time I have found the following article: https://sqlquantumleap.com/2018/02/23/sqlclr-vs-sql-server-2012-2014-2016-part-7-clr-strict-security-the-problem-continues-in-the-past-wait-what/ 

    In this article it's explained that support for CAS actually stopped  (or changed, I'm still not sure what happend) in .Net 4.0. This makes sense because .Net 4.0 was a major release, just like .Net 2.0, while versions 3.0, 3.5, 4.5, 4.6, etc, where feature releases. Also SQL Server started to use .Net 4.0 (and higher) from SQL Server 2012 (and higher). At the same time of the release of .Net 4.0 we also had the the release of Windows 8 in which Microsoft took a different direction from Windows 7.

    So all SQL Server version from 2012 to 2017 are affected. While SQL Server 2005 to 2008 R2 are not. This also explains that there is no patch for 2008 R2. We still have less than 1 year of extend support for 2008 and 2008 R2 according to https://blogs.msdn.microsoft.com/sqlreleaseservices/end-of-mainstream-support-for-sql-server-2008-and-sql-server-2008-r2/.

    What does this mean? Well in SQL Server 2005 to 2008 R2 we had a sandbox for our .Net code running in SQL Server. In SQL Server 2012 to 2017 this sandbox is obviously broken, so we have to be extra careful. Code signing might be something for organizations that need to be extra careful and perhaps also for commercial software. But for internally developed applications or just some handy regex functions to make live more easier I don't think it will be worth it. You do need to have special privileges at the database level  to create an assembly any, and that on its own might be a bigger security issue in most cases. But is still acceptable for most companies.  Also code written for external programs, sandboxed (CLR, JRE, etc) , interpreted, or native code might have the same security risks, signed or not signed. 

    • Marked as answer by Arjen Krap Monday, July 30, 2018 11:47 PM
    Monday, July 30, 2018 11:46 PM
  • 1. Why is Code Access Security no longer supported as a security boundary? Has it become unsafe? Or was it always unsafe from the beginning. Or does Microsoft decided to use something else? Or is it perhaps incompatible with .NET Core or SQL Server on Linux?

    Code Access Security (CAS) underwent a major change (though not eliminated) in .NET 4.0 ( Summary of Changes in Code Access Security) and security was again improved in .NET 4.5 ( Enhanced Strong Naming ). From what the "Enhanced Strong Naming" documentation is talking about, it sounds like this is something that has become less secure over time. But also, I suspect that what has forced the issue in SQL Server 2017 is the same concern that got Microsoft to rip SQLCLR support out of Azure SQL Database V12 rather abruptly in mid-April, 2016.

    2. If all assemblies are considered (potientially) unsafe, does signing them make them make them safer? Or is it just a way to prevent unauthorized loading of assemblies? (Or even worse, just a way to discourage loading assemblies?)


    I think the main difference is that in the past, as long as a Login had the ability to execute CREATE ASSEMBLY, then any SAFE Assembly could have been loaded. They are now saying that there is potential for a SAFE Assembly to do things that should not be allowed in a SAFE Assembly. Both signing, and the new (and unnecessary) "trusted_assemblies" feature, ensure some level of buy-in from the DBA(s) for what is being loaded.

    3. Why is CLR strict security enabled by default on SQL Server 2017, and not on lower (patched) versions?

    Because of the vast number of Assemblies that would stop working:

    1. There is no sp_configure option to turn this on / off in SQL Server 2012, 2014, or 2016. It requires a trace flag set as a startup parameter in the registry. If it were on by default, you would need to edit the registry to turn it off. Ouch!
    2. If it is turned on, then unsigned SAFE Assemblies (and I bet most SAFE assemblies are unsigned) will stop working. This is part of why the new "trusted assemblies" feature was added to SQL Server 2017: to magically accept unsigned Assemblies (and remember, SAFE assemblies never forced anyone to sign them, it was purely optional, and Visual Studio makes it extremely difficult to load the Asymmetric Key and Key-based Login). The other part is that nobody at Microsoft knew that you could simply sign the Assembly after it had already been loaded into SQL Server. They weren't back-porting the "trusted assemblies" feature to SQL Server 2012, 2014, or 2016, so in their minds there would have been no way for customers to get their existing, already loaded and working for years, unsigned assemblies working again. Had they known that the assemblies could be signed by a Certificate (and then use that Certificate to create a Login and grant that Login the UNSAFE ASSEMBLY permission), maybe they would have considered automating that over all existing Assemblies and then enabling the option. But that still has problems for DBs that get restored, that were backed-up prior to that change, etc. Much better to have it off by default in environments where this was never planned for up-front.


    5. Is setting CLR strict security to disabled a real security risk? How does this differ from older versions, starting with SQL Server 2005 with clr enabled? Does downgrading to SQL Server 2016 make things safer?

    Greater "risk" depends on your current security practices and where your Assemblies come from. This only truly affects unsigned Assemblies. If you follow good security practices and do NOT set databases to TRUSTWORTHY ON, then EXTERNAL_ACCESS and UNSAFE Assemblies (if you even had any) already required being signed and the associated signature-based Login having the appropriate permission. For that scenario nothing has changed (well, outside of it now taking additional steps to deploy such assemblies purely from a T-SQL script without any external references to DLLs). And even SAFE Assemblies, if they are signed, work the same. This new setting doesn't change what can be done by Assemblies; it just sets the bar higher for what is required to get an Assembly to load. If nobody has CREATE ASSEMBLY permission (explicit or implied), then disabling this shouldn't have any effect.

    6. How easy is it to exploit this, compared to other untrusted (virtually all) software? Or, if you need something more specific to answer, compared to extended stored procedures?

    I don't think it is very easy, nor have I ever heard of anything like this being done. The notice sent to Azure SQL Database customers in early April, 2016 stated that no exploits had been reported, and that disabling support for SQLCLR (it was only SAFE Assemblies that were supported anyway) was merely precautionary.

    Am I correct to assume that CAS was used in SQL Server 2005-2016 to implement the differences between SAFE, EXTERNAL_ACCESS, and UNSAFE? While in SQL Server 2017 without CAS only UNSAFE exists, but the SAFE and EXTERNAL_ACCESS keywords are still support for backwards compatibility?

    Not exactly. in SQL Server 2017, the three PERMISSION_SET options -- SAFE, EXTERNAL_ACCESS, and UNSAFE -- still exist and still function as before.

    Please note that when the documentation states that "with 'CLR strict security' enable, all Assemblies are considered UNSAFE", it does not mean that the Assemblies themselves can act as if they marked as PERMISSION_SET = UNSAFE. It means that when it comes to loading them (either via CREATE ASSEMBLY or at run-time by executing a method within an Assembly), they are all treated as if they are marked as UNSAFE, and verification process for UNSAFE code is used.

    Next to this, the CLR strict security configuration option only mandates signed assemblies. This change on it's own is not directly related to the CAS issue. Am I right?

    In addition to what Erland answered for this: essentially yes. The "CLR strict security" setting only changes the requirements for loading assemblies; it does not affect what they do upon being loaded. This new configuration option isn't an actual security mechanism, it is more of a work-around that merely enforces better security practices. This is most likely because there is no easy way to update the CLR host within SQL Server to handle the new security features (such as Enhanced Strong Naming) without breaking a lot of existing code. I'm sure they are trying to figure out a way to bring SQL Server's CLR host more inline with native CLR, but in the mean-time, we get this.

    For an example of how tricky such an update must be, even adding this new restriction broke a bunch of existing code, code that was just fine in prior versions but has now been updated and can no longer load. And there is no way (no provided way, at least) to export an Assembly, create a strong name key, sign it, use the SNK to create an Asymmetric Key, create a Login from that Key, grant the UNSAFE ASSEMBLY permission, and finally reload the Assembly. Of course, you don't need to export the Assemblies in order to sign them; ADD SIGNATURE will do it, but nobody knew that, so now we have a work-around to the work-around: "trusted assemblies". This is a security feature, it's just clunky, entirely unnecessary, and accomplishes essentially the same goal as signing the code: provide an administrative stamp of approval for the assembly so that it can be loaded.


    Code signing might be something for organizations that need to be extra careful and perhaps also for commercial software. But for internally developed applications or just some handy regex functions to make live more easier I don't think it will be worth it.

    Well, it's not a bad idea to get into the habit of signing code, even if you don't use that strong name key / asymmetric key for load the Assembly into SQL Server. It takes no effort to sign it. And beyond that, there are two approaches that I have come up with that allow for automating the process (and both approaches work on their own or even within Visual Studio / SSDT):

    Of course, all of this would be a thousand times easier if Microsoft would just allow Asymmetric Keys to be created from a VARBINARY literal / variable (like CERTIFICATEs can be as of SQL Server 2012, thanks to a suggestion from Erland, I believe). Given that signing the Assembly in Visual Studio uses a strong name key which equates to an Asymmetric Key in SQL Server, this functionality should have been added back in SQL Server 2005 (i.e. when SQLCLR was introduced). Doing that would have allowed for Visual Studio / SSDT to account for it in the generated deployment script. Not doing so has pushed most people to simply set TRUSTWORTHY ON because that can be done via Visual Studio.

    Take care,
    Solomon...
    https://SqlQuantumLift.com/

    • Marked as answer by Arjen Krap Friday, September 21, 2018 4:06 PM
    Friday, September 21, 2018 6:12 AM
  • Thank you. I think I read your articles before posting this question. I do value your follow up and effort.

    About setting up startup parameters like trace flags in the registry, this can be also be done using SQL Configuration Manager, which is the prefered way. But of course, you probably already knew this. 

    Friday, September 21, 2018 4:26 PM