SQL Server 2017 CLR

Talk presented @ SQLPort (30-09-2017)

Transcript

1. SQL SERVER 2017 Eduardo Piairo @EdPiairo #SQLPort CLR
2. ABOUT ME SQL Server 2017 CLR @EdPiairo, #SQLPort @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@gmail.com http://www.eduardopiairo.com/ Eduardo Piairo Friend of Redgate Operations Engineer DevOps Porto Founder
3. 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, #SQLPort
4. WHAT IS CLR? SQL Server 2017 CLR @EdPiairo, #SQLPort • .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)
5. WHAT IS CLR? SQL Server 2017 CLR @EdPiairo, #SQLPort • 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)
6. ENABLE CLR SQL Server 2017 CLR @EdPiairo, #SQLPort • 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
7. SECURITY SQL Server 2017 CLR @EdPiairo, #SQLPort • 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
8. HOST POLICY SQL Server 2017 CLR @EdPiairo, #SQLPort • 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
9. HOST POLICY SQL Server 2017 CLR @EdPiairo, #SQLPort • 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
10. HOST POLICY SQL Server 2017 CLR @EdPiairo, #SQLPort • 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
11. 2017 BREAKING CHANGE SQL Server 2017 CLR @EdPiairo, #SQLPort • CAS is no longer supported as security boundary • SQL Server new option: clr strict security
12. CLR STRICT SECURITY SQL Server 2017 CLR @EdPiairo, #SQLPort • 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
13. CLR STRICT SECURITY SQL Server 2017 CLR @EdPiairo, #SQLPort • 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)
14. DEMO SQL Server 2017 CLR @EdPiairo, #SQLPort
15. 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, #SQLPort
16. Q&A SQL Server 2017 CLR @EdPiairo, #SQLPort @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@gmail.com http://www.eduardopiairo.com/