SQL SERVER 2017 Eduardo Piairo @EdPiairo #PortoData CLR 

ABOUT ME SQL Server 2017 CLR @EdPiairo, #PortoData @EdPiairo https://pt.linkedin.com/in/jesuspiairo [email protected] http://www.eduardopiairo.com/ Eduardo Piairo Friend of Redgate Operations Engineer DevOps Porto Founder 

INDEX 1. What’s CLR 2. Enable CLR 3. Security (Host Policy) 4. 2017 Breaking change 5. CLR strict security SQL Server 2017 CLR @EdPiairo, #PortoData 

WHAT IS CLR? SQL Server 2017 CLR • .Net runtime engine (CLR) was for first integrated in SQL Server 2005 • This integration allows the execution of code within the context of a query • This integration is commonly referred as SQLCLR (Microsoft call it CLR integration) • CLR integration allows to incorporate code written in .Net language into SQL Server engine • Can be called from a stored procedure or function • Stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates can be created in managed code • Does not interact directly with SQL Server (needs to connect to database) @EdPiairo, #PortoData 

WHAT IS CLR? SQL Server 2017 CLR • Framework that bridges the environment of the SQL Server database engine with the rich programming environment of .NET • Extending capabilities of queries beyond T-SQL built-in functions • Performing certain operations faster or easier than using T-SQL • Better interaction with external resources (better then xp_cmdshell) @EdPiairo, #PortoData 

ENABLE CLR SQL Server 2017 CLR • EXEC sp_configure ‘clr enabled’, 1; RECONFIGURE; • EXEC sp_configure ‘clr enabled’, 0; RECONFIGURE; • 0 - Assembly execution not allowed on SQL Server • 1 - Assembly execution allowed on SQL Server @EdPiairo, #PortoData 

SECURITY SQL Server 2017 CLR • CLR supports Code Access Security (CAS) as security model • The permissions granted to assemblies are defined in 3 different places: • Machine policy • User policy • Host policy @EdPiairo, #PortoData 

HOST POLICY SQL Server 2017 CLR • CAS permissions granted to assemblies are determined by the permission set specified when creating the assembly (PERMISSION_SET) • SAFE • Only internal computation and local data access are allowed • Most restrictive permission set • Assembly cannot access external system resources such as files, the network, environment variables, or the registry @EdPiairo, #PortoData 

HOST POLICY SQL Server 2017 CLR • CAS permissions granted to assemblies are determined by the permission set specified when creating the assembly (PERMISSION_SET) • EXTERNAL_ACCESS • Have the same permissions as SAFE • Ability to access external system resources such as files, networks, environmental variables, and the registry @EdPiairo, #PortoData 

HOST POLICY SQL Server 2017 CLR • CAS permissions granted to assemblies are determined by the permission set specified when creating the assembly (PERMISSION_SET) • UNSAFE • Unrestricted access to resources, both within and outside SQL Server • Code executing from within an UNSAFE assembly can also call unmanaged code @EdPiairo, #PortoData 

2017 BREAKING CHANGE SQL Server 2017 CLR • CAS is no longer supported as security boundary • SQL Server new option: clr strict security @EdPiairo, #PortoData 

CLR STRICT SECURITY SQL Server 2017 CLR • EXEC sp_configure ‘clr strict security’, 1; RECONFIGURE; • EXEC sp_configure ‘clr strict security’, 0; RECONFIGURE; • 0 – Disabled • Provided for backwards compatibility • Not recommended • 1 – Enabled (default) • Causes the Database Engine to ignore the PERMISSION_SET information on the assemblies, and always interpret them as UNSAFE @EdPiairo, #PortoData 

CLR STRICT SECURITY SQL Server 2017 CLR • All assemblies (SAFE, EXTERNAL_ACCESS, UNSAFE) are authorized for UNSAFE access • Set database as TRUSTWORTHY; • Assembly is signed with a certificate that has a corresponding login with UNSAFE ASSEMBLY permission • Assembly is signed with an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission • Trusted assembly list (sys.sp_add_trusted_assembly; sys.sp_drop_trusted_assembly) @EdPiairo, #PortoData 

DEMO SQL Server 2017 CLR @EdPiairo, #PortoData 

REFERENCES https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/introduction-to-sql-server-clr-integration http://www.sqlservercentral.com/articles/Stairway+Series/104406/ https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/clr-strict-security https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/clr-enabled-server-configuration-option https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/security/clr-integration-code-access-security https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-add-trusted-assembly- transact-sql https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-drop-trusted-assembly- transact-sql https://docs.microsoft.com/en-us/sql/database-engine/breaking-changes-to-database-engine-features-in-sql-server- 2017 http://www.nielsberglund.com/2017/07/23/sql-server-2017-sqlclr-white-listing-assemblies/ SQL Server 2017 CLR @EdPiairo, #PortoData 

Q&A SQL Server 2017 CLR @EdPiairo https://pt.linkedin.com/in/jesuspiairo [email protected] http://www.eduardopiairo.com/ @EdPiairo, #PortoData