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

SQL Server 2017 CLR

7b47fb5bb7f32e0ffdbd7bba5598ba98?s=47 Eduardo Piairo
September 30, 2017

SQL Server 2017 CLR

Talk presented @ SQLPort (30-09-2017)

7b47fb5bb7f32e0ffdbd7bba5598ba98?s=128

Eduardo Piairo

September 30, 2017
Tweet

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/