SQL 2016 Dynamic data masking

Dynamic data masking in SQL Server 2016 limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. Dynamic data masking complements other SQL Server security features, including auditing, encryption, and row-level security. Using the dynamic data masking feature, in conjunction with other SQL Server security features, helps better protect sensitive data.

Set-up is fairly simple. You choose a table and column, determine how much of the data you want the users to see, and apply a function to mask it. If there is a group that needs to see all the data, you can apply permissions for them to do so.

Before Masking

I’m going to run one query:

SELECT PO.PurchaseOrderID, PO.OrderDate, PO.SupplierID, POL.PurchaseOrderLineID, POL.OrderedOuters, POL.ReceivedOuters, POL.StockItemID, POL.Description

FROM Purchasing.PurchaseOrders AS PO

INNER JOIN Purchasing.PurchaseOrderLines AS POL ON ON POL.PurchaseOrderID = PO.PurchaseOrderID;

The result is

Applying a Mask

The mask gets applied by issuing an ALTER TABLE…ALTER COLUMN…ADD MASKED WITH command.

The options for masking functions are:

Function	Before mask	After mask
Default() – masks all the data	Number: 487 Text: Hany	(FUNCTION = ‘default()’) Number: 000 Text: XXX
Email() – masks everything but first letter, @, and suffix	Hany@email.com	(FUNCTION = ’email()’) jXX@XXXXX.com
Partial() – custom mask in which you determine how much will be shown	985-744-5238	(FUNCTION = ‘partial(1,”XXXXXXX”,0)’) 9XXXXXXXXX   (FUNCTION = ‘partial(3,”XXXXXXX”,0)’) 985XXXXXXX
Random() – mask a number within a range	487	(FUNCTION = ‘random(1,1000)’) 198, or perhaps 633, or maybe 1000

 

 

I’m applying the default mask to the Supplier ID column

 

ALTER TABLE Purchasing.PurchaseOrders

ALTER COLUMN SupplierID ADD MASKED WITH (FUNCTION = ‘default()’);

Controlling access to masked data

The key to controlling access to masked data is in the privileges granted to the user accounts. When I created the table and then queried the data, I was logged in with an administrative account. However, an account that has been granted only SELECT permission on the table will see the data as masked.

To test this out, let’s start by creating a local user account on the EmpData4 database and granting that account the SELECT permission:

1 2 3 4 5

USE EmpData4;   GO   CREATE USER user1 WITHOUT LOGIN;   GRANT SELECT ON OBJECT::dbo.EmpInfo TO user1;     GO

This should all be fairly straightforward. We’ve created an account in the database that is not linked to a login (to keep things simple for our testing), and then we granted the SELECT permission to that account to allow read access to the EmpInfo table.

Now let’s run our SELECT statement again, only this time, we’ll do it within the context of the new user:

1 2 3 4 5 6

EXECUTE AS USER = ‘user1’;   SELECT TOP 5     EmpID, FirstName, LastName,      Birthdate, CurrentFlag, SalesLastYear    FROM EmpInfo;   REVERT;

We’re simply using the EXECUTE AS and REVERT statements to get the data that the new user would see. As the following table shows, the values in the four columns are now very different from how they’re stored in the database.

EmpID	FirstName	LastName	Birthdate	CurrentFlag	SalesLastYear
274	Stephen	xxxx	1900-01-01	0	0.00
275	Michael	xxxx	1900-01-01	0	0.00
276	Linda	xxxx	1900-01-01	0	0.00
277	Jillian	xxxx	1900-01-01	0	0.00
279	Tsvi	xxxx	1900-01-01	0	0.00

To begin with, the database engine returns each LastName value as xxxx. The LastName column is defined with the NVARCHAR(50) data type. The database engine returns the xxxx value for all masked character data, unless the column is defined at a smaller size. For example, a CHAR(2) column will return a masked value of xx.

The database engine handles date/time values differently. Consider the Birthdate column, which is configured with the DATE data type. Each value is returned as 1900-01-01 (or whatever format is consistent with your regional settings). If this had been some variation of the DATETIME data type, such as DATETIME2, the returned values would be 1900-01-01 00:00:00:0000000.

For numeric columns, the database engine returns 0 or 0.00, if decimals are involved. In this case, the CurrentFlag column is configured with the BIT data type, which qualifies as numeric, and the SalesLastYear is configured with the MONEY data type.

Regardless of the data type, the default function results in the entire value being somehow masked, which means an unauthorized user would not be able to infer the actual value other than to know the type of data, such as character or date.

In some cases, you might want to allow a user to view the data as unmasked. To do so, you would grant the UNMASK permission to the account:

1 2	GRANT UNMASK TO user1; GO

Now when you run the SELECT statement as User1, the results will show the unmasked data. However, if you then decide that the account should not be able to access the data as unmasked, you can revoke the permission:

1 2	REVOKE UNMASK TO user1; GO

After you run this statement, the user will again see the data as masked, as defined by the masking rules in the column definitions.

Verifying column masks

If you want to view which columns are masked in your database and how that masking is implemented, you can use the sys.masked_columns system view, as shown in the following SELECT statement:

1 2 3 4 5

SELECT OBJECT_NAME(object_id) TableName, name ColumnName, masking_function MaskFunction FROM sys.masked_columns ORDER BY TableName, ColumnName;

If you run the statement against our example database, you should see the following results.

TableName	ColumnName	MaskFunction
EmpInfo	Birthdate	default()
EmpInfo	CurrentFlag	default()
EmpInfo	LastName	default()
EmpInfo	SalesLastYear	default()

In this case, we’ve defined masking rules on only four columns in the EmpInfo table, with all rules using the default function. If we had used functions that require arguments, those arguments would be shown here as well.

Applying an email mask

The next function we’ll look at is email, which is used specifically to mask email addresses. The email function is as simple to implement as the default function, except that it returns somewhat different results. The default function masks the entire value, whereas the email function masks all but the first letter and, in some cases, the Internet domain (e.g., .com, .org, .edu).

The behavior of the email function might seem a bit odd because it returns the .com domain for all email addresses. For example, if the email address ends in .org or .edu, the masked value will read .com, as it would if the domain actually were .com. Another way to look at this is that the function masks the domain for all email addresses except those that end with .com.

Regardless of the domain issue, the process of defining the masking rule is quite straightforward:

1 2 3

ALTER TABLE EmpInfo ALTER COLUMN EmailAddress NVARCHAR(50) MASKED WITH (FUNCTION = ’email()’) NULL;

In this case, we’re simply modifying the EmailAddress column to include the masking rule, with the email function specified. Now let’s query the table as our privileged user:

1 2	SELECT TOP 5 EmpID, EmailAddress FROM EmpInfo;

As expected, the SELECT statement returns the results shown in the following table.

EmpID	EmailAddress
274	stephen0@adventure-works.com
275	michael9@adventure-works.com
276	linda3@adventure-works.com
277	jillian0@adventure-works.com
279	tsvi0@adventure-works.com

This time, we’ll run the SELECT statement as user1:

1 2 3 4

EXECUTE AS USER = ‘user1’; SELECT TOP 5 EmpID, EmailAddress FROM EmpInfo; REVERT;

Now our results now look quite different.

EmpID	EmailAddress
274	sXXX@XXXX.com
275	mXXX@XXXX.com
276	lXXX@XXXX.com
277	jXXX@XXXX.com
279	tXXX@XXXX.com

That’s all there is to masking email addresses. You might decide it’s just as easy to go with the default function to mask the data. In most cases, the value xxxx will work as effectively as tXXX@XXXX.com. The only advantage to the latter is that it telegraphs to the user that this value is a masked email address, and perhaps that’s exactly what you want to do.

Giving users rights to see the masked data

There may be some groups of users that should be able to view the data in an unmasked state. In that case, you can grant them permission to see unmasked data.

GRANT UNMASK TO PurchaseOrdersApp;

GO

If the user or group should go back to seeing masked data, you can revoke the permission.

REVOKE UNMASK FROM PurchaseOrdersApp;

GO

Remove data masking

At a future point, you may choose to remove data masking. This is easily accomplished with the DROP MASKED command.

ALTER TABLE Purchasing.PurchaseOrders

ALTER COLUMN SupplierID DROP MASKED;

 

You can query the sys.masked_columns view to determine which columns of which tables in your database are masked

 

SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function

 

FROM sys.masked_columns AS c

JOIN sys.tables AS tbl ON c.[object_id] = tbl.[object_id]

WHERE is_masked = 1;

is_masked = 1;

Protecting sensitive data

Dynamic data masking is a great tool to use in combination with other security features (such as encryption) to protect sensitive data.

 

Introduction:

 

SQL Server 2016 is a big step forward. Microsoft released SQL Server 2016 RTM version (13.00.1601.5) and SP1 version (13.0.4001.0) with more new features summarized in this article :

I will introduce here 5 top new features:

• HEKATON Enhancements
• Stretch Database
• Polybase
• JSON Support
• Temporal Tables

HEKATON Enhancements:

HEKATON was introduced in SQL Server 2014. Although there were many good reasons to use Memory-Optimized tables in the first release of In-Memory OLTP, several limitations restricted the number of use cases for which In-memory OLTP was suitable. In this section, I will describe many enhancements that make putting memory-optimized tables to good use easier. All of the points below are supported by SQL Server 2016::

• FOREIGN KEY constraints between Memory-Optimized tables.
• UNIQUE AND CHECK constraints.
• UNIQUE indexes
• Triggers (AFTER) for INSERT/UPDATE/DELETE operations are now available to use with Memory-Optimized tables, as long as you use WITH NATIVE_COMPILATION.
• Columns with large object types like (varchar(max), nvarchar(max), and varbinary(max)) can be used now.
• Collation using any code page supported by SQL Server.
• A non-unique index can allow NULL values in its key.
• Now we can ALTER Memory-Optimized tables

Stretch Database:

One of the new features provides Hybrid-Cloud environments in SQL Server 2016 and it allows us to move our cold data with a very transparent way to Microsoft Azure cloud which means there will be no more changes to the existing queries or applications. We set the policy that determines where the data should be stored and SQL server handles the data movement in the background. The table is always online and can access easily through the application.

 

Stretch database benefits

• Less cost for cold data
• There are no changes to queries or applications
• Reduce On-premises maintenance time and storage usage for data
• Keeps your data secure even during migration

 

Hence now , to configure a database to stretch database run the enable database for stretch wizard by right click on the desired database, choose Tasks, then select stretch and then click enable to launch the following wizard:

Figure 1 enable database for stretch wizard

You will go through its sections providing the required information about the tables you want to enable for stretch ,information about your Windows Azure subscription, information about your database master key and the IP address of your SQL Server to create a firewall rule on Azure that lets SQL Server communicate with the remote Azure server.

Polybase:

 

PolyBase was introduced in SQL Server 2014 as an interface exclusively for Microsoft Analytics Platform System (APS; formerly known as Parallel Data Warehouse) with which you could access data stored in Hadoop Distributed File System (HDFS) by using SQL syntax in queries. In SQL 2016, you can use PolyBase to query data in Hadoop or Azure Blob Storage and combine the results with relational data stored in SQL Server.

Polybase benefits

To keep it simple, by using polybase you can:

• Query data stored in Hadoop using standard T-SQL statements.
• Query data stored in Azure blob storage using standard T-SQL statements.
• Import data from Hadoop, Azure blob storage, or Azure Data Lake Store without ETL or any other import tools.
• Export data to Hadoop, Azure Blob Storage or Azure Data Lake Store and keep it online.
• Integrate with BI tools.

 

You can install only one instance of PolyBase on a single server which must also has a SQL Server instance installed because the PolyBase installation process adds the following 3 databases: DWConfiguration, DWDiagnostics, and DWQueue.

The installation process also adds the PolyBase engine service and PolyBase data movement service to the server.

PolyBase Installation Requirements

• Installed software: Microsoft.NET Framework 4.5 and Oracle Java SE RunTime Environment (JRE) version 7.51 or higher (64-bit).
• Minimum memory: 4 GB.
• Minimum hard disk space: 2 GB.
• TCP/IP connectivity enabled

 

PolyBase Installation Wizard:

• Select PolyBase Query Service for External Data on the Feature Selection page as shown in figure2

Figure 2 Enabling polybase feature in the installation wizard

• Next, on the PolyBase Configuration page, you specify whether your SQL Server instance is a standalone PolyBase instance or part of a PolyBase scale-out group:

Figure 3 polybase configuration steps in the installation wizard

• Then on the Server Configuration page, you must configure the SQL Server PolyBase engine service and the SQL Server PolyBase data movement service to run under the same account.

 

JSON Support:

JSON is data in text format and it is popular because its language independence and simple data structure make it easy to use in modern web and mobile applications. With the addition of JSON support to SQL Server 2016, you can now support both relational and nonrelational data structures in your environment and choose the appropriate model for your data requirements and workloads.

Using new T-SQL functions and operators, you can easily export relational data to JSON or parse and import JSON into relational storage, even if you are working with in-memory OLTP, columnstore, or temporal relational tables. You can also write queries that combine results of relational and JSON data. Working with JSON in SQL Server is much like working with XML, except JSON is not a data type.

 

 

As a quick example to get familiar with the structure of JSON data, if we executed the following code:

use SQL_shack2016

SELECT TOP 3 [ID], [FIRSTNAME], [LASTNAME]

FROM [dbo]. [Students]

FOR JSON AUTO;

 

 

We will get this result:

[{“ID”:”526993″,”FIRSTNAME”:” Lazaro”,”LASTNAME”:”Oneal”}

, {“ID”:”710325″,”FIRSTNAME”:” Dorie”,”LASTNAME”:”Salinas”}

,{“ID”:”496813″,”FIRSTNAME”:” Mathilde”,”LASTNAME”:”Hooper”}]

Statements and functions are included in JSON support:

• Using FOR JSON clause with select statement for exporting data as JSON as mentioned in the previous example.
• Using OPENJSONfunction for importing JSON data into SQL Server, or converting JSON data to rows and columns.
• So to reverse JSON data we got in the previous example we will execute the following code:

To get the following result:

 

SELECT * FROM OPENJSON (‘{“ID”:”526993″,”FIRSTNAME”:” Lazaro”,”LASTNAME”:”Oneal”}

, {“ID”:”710325″,”FIRSTNAME”:” Dorie”,”LASTNAME”:”Salinas”}

, {“ID”:”496813″,”FIRSTNAME”:” Mathilde”,”LASTNAME”:”Hooper”}’)

 

• The ISJSONfunction is to test if a string contains valid JSON.
• The JSON_VALUEfunction extracts a scalar value from a JSON string.
• The JSON_QUERYfunction extracts an object or an array from a JSON string.
• The json_modifyfunction updates the value of property in a JSON string and returns the updated JSON string.

Temporal Tables:

 

Temporal tables introduced as a new standard in ANSI SQL 2011. Also Transact-SQL has been extended to support the creation of temporal tables and the querying of these tables about a specific point in time. A temporal table allows you to find the state of data at any point in time.

When you create a temporal table, the system creates  2 tables. One table is the current table (also known as the temporal table), and the other is the history table. The history table is created as a page-compressed table by default to reduce storage utilization. As data changes in the current table, the database engine stores a copy of the data as it was before the change in the history table.

Temporal table limitations and restrictions:

1. System versioning and the FileTable and FILESTREAM features are incompatible.
2. You cannot use CASCADE options when a temporal table is a referencing table in a foreign-key relationship.
3. You cannot use INSTEAD OF triggers on the current or history table, although you can use AFTER triggers on the current table.

 

As a simple example to create a Temporal Table by the following code:

use [SQL_shack2016]

CREATE TABLE [Teachers_Temporal_Auto] (

[TeacherID] [int] NOT NULL,

[FistName] [nvarchar] (50) NOT NULL,

[LastName] [nvarchar] (50) NOT NULL,

[Address] [nvarchar] (100) NOT NULL,

[Department] [nvarchar] (100) NOT NULL,

[HireDate] [datetime] NOT NULL,

CONSTRAINT [PK_Teachers_Temporal_Auto_TeacherID] PRIMARY KEY CLUSTERED

(

[TeacherID] ASC

), ValidFrom datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,

ValidTo datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,

PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

)

WITH (SYSTEM_VERSIONING = ON);

We can now preview this created table in SSMS like the following figure:

Figure 4 Temporal Tables in SSMS

For more information in Temporal tables check this article Concept and basics of Temporal tables in SQL Server 2016

 

Becoming a sponsor means, giving a boost to your business and giving a major push to your marketing. Every business should definitely consider sponsorship. To be a sponsor means to hold up an event, an organization or an individual. You have a powerful initiative and you are looking a way to reach companies like yours? Similar business models? And tired of renting bill boards, wasting time on ads?

Here are 3 reasons you should use your precious marketing budget on Sponsorship:

1- Get Your Brand Recognized:
If you decide to sponsor an event big or small, think of your target audience. They are under the impression that if your company is capable enough to sponsor an event, then your company is big, reputable, stable and professional. By projecting and event, your company is gaining a lot of respect and integrity towards your targeted market. Think of it like this, your company logo is everywhere, posters, banners, giveaways, flyers and more. On top of that you are showing your community that you are out there for them and giving back to them and thanking them for all of their support.

2- Generate Leads:
Want to know other businesses and generate leads? Sponsoring an event is the best way to do that. You get a chance to increase your network and meet new businesses and decision makers whom you do your business with in near future. Event is also a perfect place to snatch some good leads. Meet the attendees, other sponsors, speakers and exchange business cards. You never know, you might find your future client from one of these.

3- Sponsoring deliver great (ROI):
While sponsoring an event, your targeted audience is right there with you in that room. You do not need to look elsewhere.  Marketing your business in front of that audience is a great way to save thousands of dollars, because we all know how much advertising is going to cost. Investing half of that amount on sponsoring an event is a great investments, not only for one time or certain amount of period but it’s for many years to come.  If you run a comparison between marketing/advertising and sponsoring, you will know that sponsoring have a very high return of investment.

If your organization is ever approached to become a sponsor, it is something you should definitely give a shot by keeping these three reasons in mind.