question

86abhishek avatar image
86abhishek asked

Risk associate with CLR enabling

Hi All, Will any one tell me in short the advantages and disadvantages of CLR? Why do we need to make database trustworthy for assemblies with external permission set? is there any risk associated with trustworthy database for CLR implementation? Regards Abhishek
clr
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
**Some of the advantages of CLR:** - You can write Function, Stored Procedures, Triggers which would normally not be possible to write using T-SQL - You can introduce new Data Types into the SQL server - Some functions, procedures and/or triggers is possible to write more effectively using CLR than T-SQL - many more... **Related to the TRUSTWORTHY..** Why it is necessary fro CLR with external access or UNSAFE permissions? The stored CLR assemblies, which requires external access or unsafe permission are assemblies which can access resources outside the current database and/or the SQL Server scope and/or unmanaged code. This kind of code can introduce security flaws and other issues. By setting the database TRUSTWORTHY you confirm to the SQL Server that you know what you are doing and that the code in the assemblies with external or unsafe access is safe for the SQL Server instance and other databases running on that instance. Therefore SQL Server requires it. What more, the TRUSTWORTHY option can be set only by the sysadmin, so the database administrator always should know, that the code in the assemblies cannot harm the SQL Server instance and/or data in other databases running on that instance. **Risks:** If you enable CLR and set a database TRUSWORTHY without knowing what particular assemblies can do and what code they contain, you can bring a security flaw to the SQL Server instance and eventually to all other network resources where the account under which the SQL Server is running has access.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
+1 to Pavel, and I will focus on one little bit that he mentions: > By setting the database TRUSTWORTHY you confirm to the SQL Server that you know what you are doing This is a **really** important point, especially if you are planning on running `UNSAFE` assemblies. If you don't believe me, try implementing and running this stored procedure, and see what happens: **DO NOT RUN THIS IN PRODUCTION FOR THE LOVE OF GOD AND CHOCOLATE BISCUITS** public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void BorkMyServer() { Environment.Exit(); } } **DO NOT RUN THIS IN PRODUCTION FOR THE LOVE OF GOD AND CHOCOLATE BISCUITS** Basically, UNSAFE says 'I can do whatever I like - and all those defences you have that protect the stability of the SQL Server process - don't worry about those.'
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This would make a nefarious startup stored procedure
2 Likes 2 ·
@Oleg - the `UNSAFE` permission set is a lot more restrictive than just 'unsafe' code. The source code for Environment.Exit is: [SecurityPermissionAttribute(SecurityAction.Demand, Flags=SecurityPermissionFlag.UnmanagedCode)] public static void Exit(int exitCode) { ExitNative(exitCode); } And it's the attribute that gives it the idea. Apart from the fact that `UNSAFE` is the only permission set under which you can call unmanaged code anyway :) Interesting reading here: http://msdn.microsoft.com/en-us/library/ms403276.aspx and here: http://msdn.microsoft.com/en-us/library/ms403273.aspx. It's actually **very** restrictive. For example, no singletons under anything except `UNSAFE` as you're not allowed non-readonly static fields...
2 Likes 2 ·
@Matt Whitfield Do you know how does the SQL Server know that the proc in your answer is unsafe? While I agree that it not safe, there is nothing unsafe about it as far as the framework is concerned. I mean I somehow thought that **unsafe** maps to what .NET perceives as unsafe (allow unsafe code option is checked meaning that at least one of the methods is authored as unsafe). I could be totally wrong though :( so please let me know that I am.
1 Like 1 ·
@Matt Whitfield This is very good to know, thank you! A direct mapping of framework's **unsafe** would be definitely unsafe :)
1 Like 1 ·
@Oleg - in fact, let me clarify. No proper singletons :) You could have some god-awful half cocked rubbish one based on instantiation during the static constructor execution, but that would make baby jesus cry.
1 Like 1 ·
Show more comments
SQL_87189 avatar image
SQL_87189 answered
**To be clear** there is no security issue, SQLCLR code can’t do anything more in a database than an equivalent T-SQL code module running under the same security context. Ignorantly prohibiting CLR also prevents SSISDB deployment, which drastically improves security via less RDP accounts, FileSystem management and less rights needs, Backup inheritance inside the maintenance plans, Full Package Encryption via TDE, to say nothing of SSIS package deployments and maintenance via the environment section of the SSISDB and lack of many C# functions in SSIS which require CLR. [ http://www.codemag.com/article/0603031][1] This is currently the most ignorant security claim in all of SQL Server, which can prohibit the flagship ETL deployment model, the SSISDB (requires CLR), because some 3rd party security tool which also incorrectly flags SQL Server collation coefficients via 2000, or simply a severely misinformed DBA. **CLR is not a security risk**, it actually has enhancements to security in multiple regards. Ask your DBA if he cares so much about security why Mixed Mode Authentication is set on and TDE is not enabled and they have non-existent auditing. To enable CLR simply run sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'clr enabled', 1; GO RECONFIGURE; GO [1]: http://www.codemag.com/article/0603031,
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

srutzky avatar image
srutzky answered
tell me in short the advantages and disadvantages of CLR?

In short:

  • Advantages
    • Can perform some operations not possible in T-SQL
    • Can perform some operations faster than the equivalent T-SQL
    • Scalar UDFs that do not do any data access do not prevent parallel plans
    • etc
    • Please also see: "Stairway to SQLCLR Level 1: What is SQLCLR?"
  • Disadvantages
    • General lack of understanding (and sadly, prevalence of misinformation) among the community on how to properly use and manage SQLCLR (though I am trying to counter that: SQLCLR Info ). This leads to SQLCLR sometimes being used when T-SQL would have been better / faster, or being used improperly
    • Lack of tooling support from Microsoft for straight-forward deployments (please vote for the following suggestion of mine, which would be an incredible help for deploying SQLCLR projects, even more so now that SQL Server 2017 introduced the insane "clr strict security": Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE)
    • Not supported on:
    • Azure SQL Database - Single Instance (it was supported between late 2014 and mid-April, 2016, but then got ripped out due to "security concerns"; Azure SQL DB Managed Instances do support SQLCLR)
    • SQL Server on Amazon RDS (when using SQL Server 2017 or newer)

Why do we need to make database trustworthy for assemblies with external permission set?

You do NOT need to enable TRUSTWORTHY . EVER! At least not for more than a few seconds to load something and then immediately disable it. This is an unfortunately highly common misconception. Please see:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining

What should be done is signing the assembly with a strong name key or certificate and registering that in SQL Server, creating a login from it, granting that login the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission, and then load the assembly. Please see:

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

is there any risk associated with trustworthy database for CLR implementation?

SQLCLR, on its own, is not an inherent security risk (regardless of how many people say that it is). People only believe SQLCLR is a security risk due to some people making that claim when it was introduced, and then many other people merely repeating those unfounded claims. True, SQLCLR can be used to do bad things, but only if you let it. And you don't need to let it do such things. SQLCLR is more secure than Extended Stored Procedures (deprecated) and the OLE Automation Stored Procedures (sp_OA*, not officially deprecated, but recommended against).

TRUSTWORTHY is a security risk. Mainly due to the following two reasons:

  1. Privilege escalation: if the database owner is a sysadmin, or has been granted the AUTHENTICATE SERVER permission, then an impersonated context will have any instance-level permissions of a login having the same SID as the impersonated user. Similarly, stored procedures created with the EXECUTE AS 'dbo' clause or EXECUTE AS OWNER (assuming the owner is [dbo]) will have the instance-level permissions of the login associated with the [dbo] user (which in way too many cases is [sa]).
  2. No per-Assembly permissions control: when TRUSTWORTHY is enabled, all assemblies can be set to either EXTERNAL_ACCESS or UNSAFE. There are no restrictions. But with signed assemblies, only those that have a) the asymmetric key or certificate loaded into [master], and b) have a login created from that which c) is also granted the desired permission, can be set to either level. Thus you can allow some assemblies to have a higher level of permissions while preventing those that don't meet that criteria.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.