Upgrade to Pro — share decks privately, control downloads, hide ads and more …

SQL Server 2017 CLR

SQL Server 2017 CLR

Talk presented @ PortoData (07-10-2017)

7b47fb5bb7f32e0ffdbd7bba5598ba98?s=128

Eduardo Piairo

October 07, 2017
Tweet

Transcript

  1. SQL SERVER 2017 Eduardo Piairo @EdPiairo #PortoData CLR

  2. ABOUT ME SQL Server 2017 CLR @EdPiairo, #PortoData @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, #PortoData
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 2017 BREAKING CHANGE SQL Server 2017 CLR • CAS is

    no longer supported as security boundary • SQL Server new option: clr strict security @EdPiairo, #PortoData
  12. 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
  13. 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
  14. DEMO SQL Server 2017 CLR @EdPiairo, #PortoData

  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, #PortoData
  16. Q&A SQL Server 2017 CLR @EdPiairo https://pt.linkedin.com/in/jesuspiairo eduardopiairo@gmail.com http://www.eduardopiairo.com/ @EdPiairo,

    #PortoData