CLR

Intro

CLR assemblies are created to execute .NET code inside SQL Server.
All assemblies that are ‘Safe’ or have ‘external access’ as permission set will be treated as unsafe assemblies
in SQL 2017. This, because safe assemblies in theory don’t access external resources, can still happen.
This means that starting from SQL 2017 all assemblies need to be signed with either a certificate or an
assymetric key.

While implementing this is useful, you also need to be able to trust the assemblies and other external resources running on your
instance.

Example

 

The next snippet succeeds on SQL Server 2016, but fails on SQL Server 2017:

 

CREATE ASSEMBLY UnsignedAssembly FROM

 'C:\Users\vertora\Desktop\ \DLLFiles\SafeAssembly.dll' --> UnSigned "safe" Assembly

WITH PERMISSION_SET= SAFE;

 

Error message:

Msg 10343, Level 14, State 1, Line 45

CREATE or ALTER ASSEMBLY for assembly ‘SafeAssembly’ with the SAFE or EXTERNAL_ACCESS option failed because the ‘clr strict security’ option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.

 

Remarks

You can disable this setting, but that is not advised, since assemblies are never safe, and should only be executed if they are trusted.

Be advised, when upgrading your SQL Server too SQL Server 2017, that your assemblies will stop working if they are not signed.

 

 

The new security features in SQL Server 2017 are something that every business could use.

In this 4 part series, i will be talking about the most important features in SQL Server 2017.

 

Dynamic data masking

Intro

Encrypt sensitive information on certain columns by using DDM.

Something to note with this, is that you need to make sure that ad hoc querying for these users is locked down, since they can still filter on these encrypted rows,

and find matching data based on predicates for example by adding a where clause to the queries.

 

Example

 

We create a table, apply a couple of masking functions on it , and fill it with some records,.

 

CREATE TABLE Kohera 
(ID int IDENTITY PRIMARY KEY, 
FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL, 
LastName varchar(100) NOT NULL, 
PhoneNumber varchar(12) MASKED WITH (FUNCTION = 'default()') NULL, 
EmailAddress varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);
INSERT Kohera (FirstName, LastName, PhoneNumber, EmailAddress) VALUES 
('Randi', 'Vertongen', '0488434015', 'randi.vertongen@kohera.be'), 
('Frederik', 'Bogaerts', '0476673854', 'frederik.bogaerts@kohera.be'), 
('Stijn', 'Wynants', '0476348910', 'stijn.wynants@kohera.be');

If I select everything in the table (sysadmin) everything is returned:

SELECT * FROM Kohera;

IDFirstNameLastNamePhoneNumberEmailAddress
1RandiVertongen0488434015randi.vertongen@kohera.be
2FrederikBogaerts0412345678frederik.bogaerts@kohera.be
3StijnWynants0412345678stijn.wynants@kohera.be

 

But if a basic user has select permissions:

 

CREATE USER TestUser WITHOUT LOGIN; 
GRANT SELECT ON Kohera TO TestUser;
EXECUTE AS USER = 'TestUser'; SELECT * FROM Kohera;
REVERT;

 

The result is different:

 

IDFirstNameLastNamePhoneNumberEmailAddress
1RXXXXXXXVertongenxxxxxxxxxrXXX@XXXX.com
2FXXXXXXXBogaertsxxxxxxxxxfXXX@XXXX.com
3SXXXXXXXWynantsxxxxxxxxxsXXX@XXXX.com

 

Final thoughts

As noted above, if the user can apply ad hoc queries to the database,

you will need to remember that they can apply filters  on  the masked data.

But if handle accordingly, and restrict your user permissions, this can be a very useful tool.