Why Do People Think Dynamic Data Masking is a Good Idea? - SQL SERVER 2016 #5

Data security is always one of the important points which can not be ignored. Nowadays if you are working for any specific domain like Banking or Healthcare then there are a lot of compliance rules which you have to follow.
Data Masking is one of the best ways to help you to secure your sensitive data by a dynamic mask encryption.
This is one of the best features of SQL SERVER 2016 which I personally like most.
With the help of Dynamic Data Masking, you are just applying a mask to your sensitive data.  for example, if your system is storing SSN data then it should be visible to privileged or we can say authorized user only.
Dynamic Data Masking has following features:-
1) It masked the Sensitive data.
2) There will be no impact on functions & Stored Procedures and other SQL statement after applying this.
3) Applying the Data Masking is super easy.
4) You can allow any database user/role to see unmasked data by just simple Grant & Revoke Statement .
5) Data is not physically changed.
6) It is just on the fly obfuscation of data query result .
7) It is just  a T-SQL command with basic syntax.
Now , let us understand how to implement it.
Data masking implementation is very easy and below is the syntax for it.

Here, if you see the syntax is very simple the only new thing is MASKED and with (function=function name) only.
The function is nothing but the way to mask the data. SQL SERVER 2016 has following  different functions to mask the data
1) Default() function:- This is basic masking with the help of this function you can easily mask any field.
for example, your first name or last name field can be masked like XXXX etc.
2) Email() function :- If your column is email type or you we can say if you store Email in your column then you should use the Email() function for masking.
for example, your email can be mask like  RXXXX@XXXX.com
3) Partial () function:- With the help of this function you can mask specific data length and exclude some part of data from masking logic. for example, 123-4567-789 is your phone number then with partial masking feature you can mask like 12X-XXXX-7XX.
4) Random() function – By the name it is clear that you can mask the data with any random number range we will see more below in the hands on.
Remove Masking :- This is also possible that you applied a masking to a column and later on you don’t want that masking. So , don’t worry it very easy to remove masking from a column. below is the syntax for same.

Now, let’s understand this by an example.
In the example we are using a new database “SecureDataMask” in this database we are creating a tblSecureEmployee as shown in below figure.

Now, in this table, we are inserting couple of data for testing as shown below

Now we are applying different masking on this table’s column
1) Default Masking : In the table, we are applying default masking on LastName

2) Email Masking :- In the table, we are going to apply Email masking to email column below is the syntax for it.

3) Partial Masking:- For SSN we are going to apply custom masking. below is the syntax for same. Here as we aware that SSN is 11 characters long in our database. we applied the partial masking to show first two & last two characters in original value and rest other in the mask.

4) Random Number Masking :-  In our table, we are going to apply Random number masking to Securepin column as shown below.

Here, so far we are done with all the masking now.  let me run the select statement to test it.

If you see the data is still in the original state because I logged in using  privilege account “SA”. now, to test the masking let me create a new user account.

After creating the account we are trying to log-in with a new account as shown in below screen.

After our successful log in, we will run the select statement on same database’s table as we did earlier. If you see below snap you will find that we got masked data for LastName, Email, SSN, and securePin.

Now, it might be a rare case but suppose you want to remove the mask from any column on which you applied masking then don’t worry it is super easy.
Suppose, from the same table we don’t want mask on the LastName then below is the syntax for same.

Now, let me run the same select statement seeMask_user. You will find the Last Name is unmasked now.

From above few changes you can secure your data via Dynamic masking and as mentioned above there will be no impact on your existing function ,stored procedure because data is not physically changed.
I hope you may like this feature.   Please, share your input for same.
Enjoy !!
RJ

How easy or difficult to write dynamic SQL ? TIP#110

This is one of the challenge for most of the developer to write dynamic SQL. Generally we follow the approach of string concatenation.

This seems very easy but we need to cast the  parameters in VARCHAR and sometimes we stuck in single code.

I am sure this happened with all of us. 

Let’s understand first a straight forward way which we (most of  us) are  using.

In example I am using person table of Adventureworks database and it is just a simple query  which provide person row according to primary key.

Now the above query is OK but it can be write in much better way with one of the SQL server in build stored procedure which is sp_executeSQL. This is one of the best way which have certain advantage which will discuss in next tip. Now see how we can write above query in much better way

DECLARE @BusinessEntityID   INT
DECLARE @DynamicSQL NVARCHAR(200)
DECLARE @Parameters NVARCHAR(100)

SET @BusinessEntityID = 1
SET @Parameters =N'@ParameterBusinessEntityID INT'

SET @DynamicSQL = N'SELECT * FROM [Person].[Person] WHERE BusinessEntityId = @ParameterBusinessEntityID '
EXECUTE sp_executesql @DynamicSQL, @Parameters,  @ParameterBusinessEntityID =@BusinessEntityID

If you see above query, you will find we are not using any type conversion the statement is clear without any type casting.

This is one of the best way to write dynamic query which is not only increase your statement’s readability but also increase performance of your query with certain amount.

Which we will discuss in next tip.

I hope now you are eager to use this and replace all your old fashion dynamic queries.

Please do post your feedback.

Enjoy !!!

RJ !!!

How to hide my SQL Server instance in network ? TIP #99

 

In TIP #70  we saw how to find all the running SQL SERVER instance in a network or a machine.

to revise see below image.

This tip is just opposite to tip #70 you don’t want that your co-worker see your SQL Server instance running on your machine machine. (There are several reason behind this   and security is one of the most valuable aspects)

To achieve this you just need to do a very simple setting. Just follow below steps

1) Open “SQL SERVER Configuration Manager”

2) Once the screen is open right click on the instance which you want to hide from network (under  SQL SERVER network  configuration ) as shown below

3) When you click on Properties menu you will get a new screen as shown below

You need to set the value of Hide Instance option to Yes.

4) Click on apply button and restart the services.

Great , We achieved it. Isn’t it simple ?

I appreciate your feedback.

Enjoy!!!

RJ!!!