Slide 1

Slide 1 text

SQL SERVER 2017 Eduardo Piairo @EdPiairo #SQLPort CLR

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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)

Slide 5

Slide 5 text

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)

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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)

Slide 14

Slide 14 text

DEMO SQL Server 2017 CLR @EdPiairo, #SQLPort

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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