SQL Server 2016

The Community Technical Preview (CTP) release of SQL Server 2016, has given us the opportunity to try out some of the new features that will be available in the forthcoming version.

Dynamic data masking

This feature allows you to protect confidential data from users who are not authorised to access it by obscuring some or all of a column's contents. For example, you might expose part of a credit card number to enable help desk staff to see the last four digits; you might choose to obscure data held in a salary column completely.

If you're using SQL Server CTP 2.0, you need to run this command to enable dynamic data masking:

DBCC TRACEON(209,219,-1)

Do not use this command if you're using a later version of the CTP, as this will turn off dynamic data masking. (If you're using CTP 2.0 and you do not run this DBCC command or you are running CTP2.1+ and you do run this command, you will get the error "Incorrect syntax near 'masked'" when attempting to define a dynamic mask for a column.)

The following SQL creates a table that uses dynamic data masking to obscure the contents of three columns. The contents of the CreditCard column are partially exposed. What gets exposed in the Phone column is left to the dynamic data mask. The default for a string column is "xxxx". The default for a numeric column is "0". The Email column uses a dynamic data mask specifically geared towards the contents of that column:

CREATE TABLE Customer
  (ID int IDENTITY PRIMARY KEY,
   Name varchar(100) NOT NULL,
   CreditCard varchar(9) MASKED WITH (FUNCTION = 'partial(0,"xxxxx",4)') NULL,
   Phone varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
   Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);

INSERT INTO Customer VALUES('A Name', '111222333', '01937 860000', 
                            'someone@somedomain')

Using the isql program included in our SQL Server ODBC driver distribution, we log in with a SQL Server account that is not authorised to view the contents of a masked column:

$ /usr/local/easysoft/unixODBC/bin/isql.sh -v SQLSERVER_2016 myuser mypassword
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from customer
+----+-------+-----------+-------+---------------+
| ID | Name  | CreditCard| Phone | Email         |
+----+-------+-----------+-------+---------------+
| 1  | A Name| xxxxx2333 | xxxx  | sxxx@xxxx.com |
+----+-------+-----------+-------+---------------+

We then log in with an account that does have sufficient privileges:

$ /usr/local/easysoft/unixODBC/bin/isql.sh -v SQLSERVER_2016 sa mypassword
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from customer
+----+-------+-----------+--------------+------------------------+
| ID | Name  | CreditCard| Phone        | Email                  |
+----+-------+-----------+--------------+------------------------+
| 1  | A Name| 111222333 | 01937 860000 | someone@somedomain.com |
+----+-------+-----------+--------------+------------------------+

JSON support

JavaScript Object Notation (JSON) is a text format that facilitates the interchange of data. As more application developers adopt JSON as their preferred data format, the need is growing for JSON-friendly databases. As a consequence, several NoSQL databases have chosen JSON as their primary data format. One such database is MongoDB.

On our test machines, we used SQL Server 2016's JSON support to exchange data between SQL Server and MongoDB.

We used the bulk copy program (bcp) included in our SQL Server ODBC driver's distribution to export the customer data we created earlier in JSON format. (The bcp command connects to SQL Server as "sa" and so the data in the masked columns is exposed.)

$ cd /usr/local/easysoft/sqlserver/bcp
$ ./bcp  "select * from customer for json auto" queryout customer.json -U sa -c
Password:
Starting copy...
1 row successfully bulk-copied to host file. Total received: 1
Clock Time (ms.) Total     : 12129       Average : 0.082 rows per second
$ more customer.json
[{"ID":1,"Name":"A Name","CreditCard":"111222333","Phone":"01937 860000",
  "Email":"someone@somedomain"}]

We then imported the JSON data into MongoDB:

$ cd /opt/mongodb-linux-x86_64-ubuntu1404-3.0.7/bin
$ ./mongoimport --db=SQLServer --collection=Customer --file=customer.json --jsonArray
$ connected to: localhost
  imported 1 document
$ ./mongo
MongoDB shell version: 3.0.7
connecting to: test
> use SQLServer
switched to db SQLServer
> db.Customer.find()
{ "_id" : ObjectId("56334017f6df768ab87f2e8c"), "ID" : 1, "Name" : 
  "A Name", "CreditCard" : "111222333", "Phone" : "01937 860000",
  "Email" : "someone@somedomain" }
>

Row-level Security

With its Row-Level Security feature, SQL Server 2016 is able to restrict access to row data based on a SQL Server login. Row-Level Security is transparent to SQL Server users, they are unaware that rows they are not authorised to access are being filtered from their query results.

To try this feature with our SQL Server ODBC driver, we reproduced Microsoft's row-level security example. We did this in a database that had SQL Server users named "Sales1" and "Sales2," who have SELECT privileges.

We created and populated a table with some sales data. The SalesRep column stores the user name of the relevant sales representative.

CREATE TABLE Sales
(
  OrderID int,
  SalesRep sysname,
  Product varchar(10),
  Qty int
);

INSERT Sales VALUES 
(1, 'Sales1', 'Valve', 5), 
(2, 'Sales1', 'Wheel', 2), 
(3, 'Sales1', 'Valve', 4),
(4, 'Sales2', 'Bracket', 2), 
(5, 'Sales2', 'Wheel', 5), 
(6, 'Sales2', 'Seat', 5);

Row-level Security is implemented with a table valued function that either returns a single row, if the user has the appropriate access, or no results. In the following example, the table valued function returns a row if the SalesRep column is the same as the user executing the query.

CREATE SCHEMA Security;
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result 
WHERE @SalesRep = USER_NAME();

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep) 
ON dbo.Sales
WITH (STATE = ON);

We used the SQL Server ODBC driver to connect to the database as the Sales2 user. Row-level security ensures that this user can only return sales made by the user Sales2.

$ /usr/local/easysoft/unixODBC/bin/isql.sh -v SQLSERVER_2016 Sales2 mypassword
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from Sales
+------------+----------+-----------+-------+
| OrderID    | SalesRep | Product   | Qty   |
+------------+----------+-----------+-------+
| 4          | Sales2   | Bracket   | 2     |
| 5          | Sales2   | Wheel     | 5     |
| 6          | Sales2   | Seat      | 5     |
+------------+----------+-----------+-------+
SQL> select * from Sales where OrderID = 1
+------------+----------+-----------+-------+
| OrderID    | SalesRep | Product   | Qty   |
+------------+----------+-----------+-------+
|            |          |           |       |
+------------+----------+-----------+-------+

In-database R

With Microsoft's purchase of Revolution Analytics, a provider of software and services for the R programming language, they are able to integrate R with SQL Server. SQL Server 2016 will be the first version of the database to incorporate R, allowing R code to be run inside the SQL Server database engine.

If you have an earlier version of SQL Server, the alternative is to extract data from SQL Server into R by using ODBC. The RODBC package provided an ODBC interface for R. We built RODBC against the unixODBC Driver Manager included in our SQL Server driver's distribution and then retrieved some SQL Server data from R:

# export ODBC_LIBS=/usr/local/easysoft/unixODBC/lib
# export ODBC_INCLUDE=/usr/local/easysoft/unixODBC/include
# R CMD INSTALL RODBC_1.3-12.tar.gz
$ R
> library("RODBC")
> ch <- odbcConnect("SQLSERVER_2016")
> sqlQuery(ch, paste("SELECT * from Customer"))
  ID   Name CreditCard        Phone              Email
1  1 A Name  111222333 01937 860000 someone@somedomain