Security features in SQL Server 2017

SQL Server has many features that support creating secure database applications. In this blogpost I’ll guide you through the latest security features in SQL Server 2017: Common Language Runtime (CLR), dynamic data masking, row level security and always encrypted.

Common Language Runtime (CLR)

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.

“SQL Server contains many extensibility features and mechanisms. Most of these mechanisms are disabled by default. However, we advise customers to review each production instance for extensibility feature use. We recommend that each of these features be restricted to the minimum set of binaries, and that customers restrict access to prevent arbitrary code from running on the same computer as SQL Server. We advise customers to determine whether to trust each binary, and to disable or remove untrusted binaries.

More info: https://support.microsoft.com/en-us/help/4073225/guidance-protect-sql-server-against-spectre-meltdown

In other words, you 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.

Dynamic data masking

DDM is used to encrypt sensitive information on certain columns. 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.

Example

We create a table, fill it with some records, and apply some masking functions on it:

 
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;

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:

An important thing to note however, is to also restrict the ability for users to execute ad-hoc queries, since more information can be accessed by being able to apply where clauses to the queries.

Row level security

“Row-Level Security (RLS) simplifies the design and coding of security in your application. RLS enables you to implement restrictions on data row access. For example ensuring that workers can access only those data rows that are pertinent to their department, or restricting a customer’s data access to only the data relevant to their company.”

https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security

So in other words, if you have an employee who should only see his own records or the ones from his team, this can easily be implemented, while still accessing the same table.

You can even handle application security with this feature, for example by using SESSION_CONTEXT parameter so you can act accordingly to which application is connecting (e.g. HR application vs Sales Application)

Example

(source: https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security):

What if we have a table, on which the manager should see everything, and the sales representatives should only see their own records:

You can add functions to see which user is accessing the data.

Sales1:

EXECUTE AS USER = 'Sales1';  
SELECT * FROM Sales;   
REVERT;  

Sales2:

EXECUTE AS USER = 'Sales2';

SELECT * FROM Sales;

REVERT;

Manager:

EXECUTE AS USER = 'Manager';  
SELECT * FROM Sales;   
REVERT;  

Always Encrypted

Always Encrypted was already present in SQL Server 2016, and naturally still present in SQL Server 2017. It is included in standard as long as you are on SQL server 2016 SP1 or higher. If you have high-sensitive data, that is not being ad-hoc queried (e.g. select name from dbo.credentials where credential = %123%’) this feature might be useful for you.

For example credit card information: if you use always encrypted, the customer will be able to store his credit card information through the application in a trusted key store. And since SQL Server has no access to the keys in the trusted key store, the SQL admins will not be able to access the sensitive information that will be encrypted and stored on the server (for client and data on prem).

Fig 2. https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine

Example:

(https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine)

Create a column master key, column encryption key

CREATE COLUMN MASTER KEY MyCMK  
WITH (  
     KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',   
     KEY_PATH = 'Current User/Personal/f2260f28d909d21c642a3d8e0b45a830e79a1420'  
   );  ---------------------------------------------  
CREATE COLUMN ENCRYPTION KEY MyCEK   
WITH VALUES  
(  
    COLUMN_MASTER_KEY = MyCMK,   
    ALGORITHM = 'RSA_OAEP',   
    ENCRYPTED_VALUE = 0x017… (abbreviated.)
);

Create a table to hold the encrypted data

CREATE TABLE Customers (  
    CustName nvarchar(60)   
        COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,  
        ENCRYPTION_TYPE = RANDOMIZED,  
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),   
    SSN varchar(11)   
        COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,  
        ENCRYPTION_TYPE = DETERMINISTIC ,  
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),   
    Age int NULL  
);  

Now your application connection string can be pointed to the encrypted tables, and these will always be encrypted inside SQL Server. Note that you might need to add certain Encrypted parameters or similar to your application code.https://test.uncoded.be/nl/blog/sql-server-nl/security-features-in-sql-server-2017/