question

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

+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.'

**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,

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.