SQL Server 2016 Dynamic Data Masking

Dynamic Data Masking is a new feature introduced in SQL 2016. This is feature helps you to protect your sensitive data from unauthorized access. Microsoft has done a lots of stuff around security and Dynamic Data Masking is one of them.

Here is an easy example from Microsoft Documentation which explains this feature

A service representative at a call center may identify callers by several digits of their social security number or credit card number, but those data items should not be fully exposed to the service representative. A masking rule can be defined that masks all but the last four digits of any social security number or credit card number in the result set of any query. As another example, an appropriate data mask can be defined to protect personally identifiable information (PII) data, so that a developer can query production environments for troubleshooting purposes without violating compliance regulations.

Below is the different masking rules which you can define on your columns

• Default -> Full masking according to the data type defined 
• Email -> This is to mask the email address stored in your table
• Random -> Use on any numeric type to mask the original value with a random value within a specified range.
• Custom String -> If above listed rules doesn’t meet your needs, you can define your own masking function with custom string

Now let’s see in demo on how to implement DDM

Run the below code to have your setup ready to implement Dynamic Data Masking (DDM) on your SQL 2016 instance.

USE master

GO

CREATE DATABASE DataMaskingDemo

GO

USE DataMaskingDemo

GO

CREATE TABLE MASKING (

ID INT IDENTITY (1,1),

FirstName CHAR(20),

LastName CHAR(20),

EmailID NVARCHAR(70),

MembershipID INT,

CreditCard NVARCHAR(16)

)

INSERT INTO DataMaskingDemo..MASKING VALUES 

('Naveen','Kumar','thilagar.naveen@gmail.com',869583,'7654234592457826'),

('ABC','XYZ','abc.xyz@gmail.com',869583,'7654578242349245')

GO

SELECT * FROM DataMaskingDemo..MASKING

We will also have a SQL Login created for testing how the data looks like once its masked.

Run the below script to create your SQL login with read access to our demo database.

USE [master]

GO

CREATE LOGIN [ServiceDesk] WITH PASSWORD=N'P@ssword123' ,DEFAULT_DATABASE=[DataMaskingDemo], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

GO

USE [DataMaskingDemo]

GO

CREATE USER [ServiceDesk] FOR LOGIN [ServiceDesk]

GO

USE [DataMaskingDemo]

GO

ALTER ROLE [db_datareader] ADD MEMBER [ServiceDesk]

GO

Data from the table before its masked

So now we've got the setup ready. Ok, first let’s see how to mask the EmailID column 

T-SQL to mask the EmailID column

ALTER TABLE DataMaskingDemo..MASKING_

ALTER COLUMN EmailID NVARCHAR(70) MASKED WITH (FUNCTION = 'Email()');

Now run the same select statement from the ServiceDesk user session. Our data should be masked now

Now let’s mask the MembershipID and CreditCard details as well

T-SQL to mask MemberShipID and CreditCard information

ALTER Table DataMaskingDemo..MASKING

ALTER COLUMN MembershipID ADD MASKED WITH (FUNCTION='default()')

ALTER Table DataMaskingDemo..MASKING

ALTER COLUMN CreditCard ADD MASKED WITH (FUNCTION='partial(0,"XXX-XXXX-XXX",4)')

For CreditCard column how the partial function masks is 0 represents how many values from the starting should be visible. Second one “XXX-XXXX-XXX” represents the padding type and 4 defines from the last how many characters should be visible. 

Here is the output from ServiceDesk user after masking MemberShipID and CreditCard information

Try to use mask either FirstName or LastName column with default() masking function. You will get a new masking format, as default() function masks data as per the data type defined on the table.

Now we will see how to get the list of columns which are masked using DDM

sys.masked_columns is the new view which was added in SQL 2016 to get details about masked columns. 

T-SQL to get list of masked columns

SELECT 

DB_NAME() AS 'Database Name', 

T.name as 'Table Name', 

MC.name AS 'Column Name',

MC.masking_function as 'Masking Function' 

FROM sys.masked_columns MC 

INNER JOIN SYS.tables T 

ON MC.object_id=T.object_id

WHERE MC.is_masked=1

T-SQL Script to unmask the columns

ALTER TABLE DataMaskingDemo..MASKING 

ALTER COLUMN EmailID DROP MASKED;  

ALTER TABLE DataMaskingDemo..MASKING 

ALTER COLUMN MembershipID DROP MASKED;  

ALTER TABLE DataMaskingDemo..MASKING 

ALTER COLUMN CreditCard DROP MASKED;  

Implementing DDM is pretty straight forward and main advantage is you need not make any changes to your application. That’s one BIG advantage. This can be combined with Row-level security and TDE to make your data more secure. Hope this helps.

Cheers,

Naveen

PowerShell Pipelines and Filtering Output – Part 3

In this post we will see how pipeline works and how you can filter output in PowerShell.

If you are new to PowerShell, I would recommend you to read my previous blog Part-1 and Part-2 before going through this post.

By understanding Pipelines and filtering objects, you take your PowerShell scripting to the next level. What pipeline does is it take a set of output (which is referred as object) and its sent across the pipe as an input to next cmdlet to do something.

Let's jump into some examples to see pipeline in action

First, I am going to start a notepad process. Then we will see how the process information can be sent as an input to stop/terminate the same process.

-Confirm parameters comes handy when you writes scripts which is doing some changes to your system. Its always good to get a message like whether you want to perform this action

Now let's see internally on what logic these objects are passed over the pipeline


To demonstrate this, let's take the below example

This is a best example I have seen to explain the way how pipeline works. Thanks to Jeffery Snover who showed this in one of his presentation.

So let's do a Get-Help on Get-ChildItem to see list of parameters which accepts pipeline inputs

Then pass Get-Process to Get-Memeber which show us the list of methods, properties and typename for a object.

In our case -Path parameter matches with the Path property in Get-Process

So internally the path property of Calc process is passed as an input to Get-ChildItem. This is how pipelines work in PowerShell. By knowing the way how pipeline works will definitely help you to troubleshoot your scripts easily and write better codes as well.

So that's it about pipelines. Now we'll see how to filter the output using where-object

Let's take Get-Process cmdlet for this demonstration. Assume you got a requirement to generate a report(html format) for every 15 minutes to get the list of process whose handles are greater than hundred and output should be sorted (descending).

Just the output of Get-Process

Filter our results using where-object. These are the different ways to write your where-object cmdlet. Everything gives you the same output.

Now sort the output in descending order, covert the output to html and make it as a report.

Get-Process | where Handles -ge 1000 |Select-Object Name, Handles, Product, Description, Company | Sort-Object Handles -Descending | ConvertTo-Html | Out-File ProcessReport.htm

We just created a html report. But wait, I know what you might be thinking? Format doesn't looks good, right? Yes, but again this can be customized which I will cover in the future post.

In the next blog we will see how this can be done on multiple machines from one server

Cheers,
Naveen

SQL Server Dedicated Admin Connection – What’s that? How do I use it?

DAC - Dedicated Admin Connection

Dedicated Admin Connection was introduced way back in SQL 2005. This a back door connection to SQL Server Instance which should be used ONLY when your SQL instance run is serious problem. Dedicated Admin Connection has a dedicated scheduler with a worker thread and a memory node. SQL Server internally allocates one worker thread to DAC. Also please note DAC connection doesn’t support parallelism, so it’s used to run simple troubleshooting queries on an emergency situation.

How do I use it?

By default you will not be able to use this connection. DAC should be firstly enabled at instance level to use it.

Enabling DAC from SQL Server Management Studio

Right click on your SQL Instance -> Facets -> From the Facets drop down list select Surface Area Configuration -> Set RemoteDACenabled to True

Enabling DAC using T-SQL

--To Check the status of DAC

EXEC sp_configure 'remote admin connections'

--To enable DAC connection

EXEC sp_configure 'remote admin connections',1

RECONFIGURE

Now we will see how to connect using DAC

To connect from management studio

Admin:SQLInstanceName

To connect from command line

SQLCMD –S local –E -A

or

SQLCMD -S SQLInstanceName -E -A

Script to check if you are really using DAC

select session_id, net_transport, name as 'Connectivity Name' from sys.dm_exec_connections C

inner join sys.endpoints E on C.endpoint_id=E.endpoint_id

where session_id=@@spid

Frequent Issues which you can run into while using DAC

1) When you try connecting from Connect to Server dialog from the object explorer you will get the below error

Error Message:

Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

Connect using the Database Engine Query button in SSMS

 2) When browser service is not running

Error Message:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 43 - An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for the port number) (Microsoft SQL Server, Error: -1)

Make sure your SQL Server browser service is started to use DAC.

Hope this help. Check whether your DAC is enabled in your environment, because you don’t know when you will need it.

Cheers,

Naveen