Posts Tagged ‘Dynamic Data Masking’

Obfuscate column level data by using “Dynamic Data Masking” in SQL Server 2016

July 20, 2015 6 comments

This time SQL Server 2016 has made good additions in area of Security by introducing features like:

1. Always Encrypted

2. Row Level Security, check my previous post,

3. Dynamic Data Masking, this post

4. and other security features, like Transparent Data Encryption (TDE), etc.

Dynamic Data Masking provides you support for real-time obfuscation of data so that the data requesters do not get access to unauthorized data. This helps protect sensitive data even when it is not encrypted, and shows obfuscated data at the presentation layer without changing anything at the database level.

Dynamic Data Masking limits sensitive data exposure by masking it to non-privileged users. This feature 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. It’s a Policy-based Security feature that hides the sensitive data in the result set of a Query over designated database columns, while the data in the database is not changed.

–> “Dynamic Data Masking” provides you three functions/options to Mask your data:

1. default(): just replaces the column value with ‘XXXX’ by default.

2. email(): shows an email ID to this format ‘’.

3. partial(prefix,padding,suffix): gives you option to format and mask only some part of a string value.

We will see the usage of all these 3 masking functions below.

–> To setup Dynamic Data Masking on a particular Table you need to:

1. CREATE TABLE with MASKED WITH FUNCTION option at column level.

– Or ALTER TABLE columns by using this option if the table is already present.

2. Create Users and Grant Read/SELECT access for the above CREATED/ALTERED table.

–> 1. Create a sample table [dbo].[Customer] with masked columns:

CREATE TABLE dbo.Customer (
	FirstName 	 VARCHAR(250),
	LastName 	 VARCHAR(250) MASKED WITH (FUNCTION = 'default()') NULL,
	PhoneNumber  VARCHAR(12)  MASKED WITH (FUNCTION = 'partial(1,"XXXXXXXXX",0)') NULL,
	Email 		 VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NULL,
	CreditCardNo VARCHAR(16)  MASKED WITH (FUNCTION = 'partial(4,"XXXXXXXXXX",2)') NULL,

–> Now insert some test records (fictitious figures):

INSERT INTO dbo.Customer (FirstName, LastName, PhoneNumber, Email, CreditCardNo)
('Manoj',   'Pandey', '4442889882', '', '4563234576547834'),
('Saurabh', 'Sharma', '9812446452', '', '1243096778653487'),
('Vivek',   'Singh',  '6745239856', '', '8756341209876735'),
('Keshav',  'Singh',  '9867452387', '', '2938713685372618');

–> Let’s check the rows on [dbo].[Customer] table in context of my User:

SELECT * FROM dbo.Customer;

SQLServer2016 - DDM 01

Here, I can see all the row/column values as I have full access to read the masked/sensitive data.

–> 2.a. Now let’s create a Test Account and just Grant Read access to [dbo].[Customer] table:


GRANT SELECT ON dbo.Customer TO AnyUser;

–> Let’s execute the SELECT statement on [dbo].[Customer] table in the Context of this new user account:

SELECT * FROM dbo.Customer;

SQLServer2016 - DDM 02

And you can see that the user is not able to see the masked data as he is not authorized to see it.

–> Removing Masking from a column by simple ALTER TABLE/COLUMN statement:

ALTER TABLE dbo.Customer 

-- Let's check the table data again:
SELECT * FROM dbo.Customer;

SQLServer2016 - DDM 03

Here, now you are able to see contents of LastName columns, as the masking has been removed from this column by using simple ALTER TABLE/COLUMN statement.

–> 2.b. Granting the UNMASK permission to “AnyUser”:


-- Let's check the table data again:
SELECT * FROM dbo.Customer;

SQLServer2016 - DDM 01

He is able to see all data unmasked when the UNMASK permission is granted to this user.

–> 2.c. Revoking back the UNMASK permission form the same user:

SELECT * FROM dbo.Customer;

After Revoking UNMASK permission he is again not able to see complete data.

This way you can control access to your precious or PII data by masking the columns/fields that you don’t want to show to the external world or some set of users.

–> Final Cleanup:

DROP TABLE dbo.Customer;


–> Check the same demo on YouTube:


Check & Like my FB Page


Microsoft SQL Server 2016 Public Preview (CTP2) Available – download now

May 28, 2015 3 comments

Just got an email from Microsoft:

Microsoft SQL Server 2016 Public Preview Available – Try it Today!

[Register and Download CTP-2 Evaluation version (180 days)]


Direct download link:


–> Smooth Installation with new setup-option and new features in SSMS:

Microsoft SQL Server 2016, the next major release of Microsoft’s flagship database and analytics platform, provides breakthrough performance for mission critical applications and deeper insights on your data across on-premises and cloud. The first public preview, SQL Server 2016 Community Technology Preview (CTP) 2, is now available for you to download to trial via Microsoft Azure.

–> Try it today for an early look at these new capabilities:

– Always Encrypted: helps protect data at rest and in motion

– Stretch Database: dynamically stretch your warm and cold transactional data to Microsoft Azure, demo video.

– Real-time Operational Analytics: our in-memory technologies are enhanced to provide real-time analytics on top of breakthrough transactional performance

–> Additional capabilities include:

1. PolyBase: More easily manage relational and non-relational data with the simplicity of T-SQL.

2. AlwaysOn Enhancements: Achieve even higher availability and performance of your secondaries, with up to 3 synchronous replicas, DTC support and round-robin load balancing of the secondaries.

3. Row Level Security: Enables customers to control access to data based on the characteristics of the user. Security is implemented inside the database, requiring no modifications to the application, demo video.

4. Dynamic Data Masking: Supports real-time obfuscation of data so data requesters do not get access to unauthorized data. Helps protect sensitive data even when it is not encrypted, demo video.

5. Native JSON support: Allows easy parsing and storing of JSON and exporting relational data to JSON, demo video.

6. Temporal Database support: Tracks historical data changes with temporal database support.

7. Query Data Store: Acts as a flight data recorder for a database, giving full history of query execution so DBAs can pinpoint expensive/regressed queries and tune query performance.

8. MDS enhancements: Offer enhanced server management capabilities for Master Data Services.

9. Enhanced hybrid backup to Azure: Enables faster backups to Microsoft Azure and faster restores to SQL Server in Azure Virtual Machines. Also, you can stage backups on-premises prior to uploading to Azure.

–> Other Benefits:

1. Enhanced in-memory performance provide up to 30x faster transactions, more than 100x faster queries than disk based relational databases and real-time operational analytics

2. New Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without application changes

3. Built-in advanced analytics provide the scalability and performance benefits of building and running your advanced analytics algorithms directly in the core SQL Server transactional database

4. Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android

5. Simplify management of relational and non-relational data with ability to query both through standard T-SQL using PolyBase technology

6. Stretch Database technology keeps more of your customer’s historical data at your fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner without application changes, demo video.

7. Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure

Learn more about SQL Server 2016: SQL Server 2016 Preview Page

Microsoft announced SQL Server 2016 – New features and enhancements

May 9, 2015 2 comments

On Monday, 4th May 2015 Microsoft at Ignite event announced the new version of SQL Server i.e. SQL Server 2016, which will be available for Public Preview this summer.

>> MSDN Blog announcement:

>> Microsoft SQL Server 2016 official page:

This version of SQL Server is going to be a major release with new features and will also overcome some limitations of SQL Server 2014.


-> New Performance Enhancements:

– In-memory OLTP enhancements: Greater T-SQL surface area, terabytes of memory supported and greater number of parallel CPUs, provide up to 30x faster Transactions, more than 100x faster Queries than disk-based relational databases and Real-time Operational Analytics, Demo video.

– Query Data Store: Monitor and optimize query plans with full history of query execution.

– Native JSON: Parsing & storing of JSON as relational data & exporting relational data to JSON, as it is becoming a popular format to store NoSQL/Unstructured data, Demo video.

–> Security Upgrades:

– Always Encrypted: Help protect data at rest and in motion with the master key residing with the application & no application changes required.

– Row Level Security: Customers can implement Row-level Security on databases to enable implementation of fine-grained access control over rows in a database table for greater control over which users can access which data, demo video.

– Dynamic Data Masking: Real-time obfuscation of data to prevent unauthorized access, demo video.

–> Even Higher Availability, with Enhanced AlwaysOn:

– Up to 3 synchronous replicas for auto failover across domains, for more robust High Availability and Disaster Recovery

– Round-robin load balancing of replicas

– DTC & SSIS support

– Automatic failover based on database health

–> Hybrid Cloud Solution:

– Stretch Database: Stretch operational tables in a secure manner into Azure for cost effective historic data availability, that lets you dynamically stretch your warm and cold transactional data to Microsoft Azure, demo video.

– Azure Data Factory integration with SSIS

–> Deeper Insights Across Data

– PolyBase: Manage relational & non-relational data with the simplicity of T-SQL.

– Enhanced SSIS: Designer support for previous SSIS versions and support for Power Query.

– Built-in Advanced Analytics: Bringing predictive analytic algorithms directly into SQL Server.

>> Check the SQL Server 2016 Datasheet here for more information on this:


–> YouTube Videos on SQL Server 2016: