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 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!!!

Kill–use this weapon carefully in SQL SERVER . TIP #90

When we heard “Kill” then first impression of this word is very bad. We always scare with this word.

In real world we never want this action  from anyone but in SQL Server case it is very helpful and help us many times.

This feature we can use but very carefully. It is something like we have to kill a cruel giant not the common innocent person.

Lets understand this by an example.

Suppose we wrote a stored procedure or function and by mistake we wrote a condition which is never ending. We didn’t realize this and when we run the stored procedure/function the SQL Server hangs , system is slow and many other issues occur and our stored procedure which we run still running and laughing on us.

In such situation we have to kill that particular stored procedure execution statement which is cruel giant and eating the resources.

See below statement

WHILE 1=1
BEGIN
  SELECT 'Infinite Loop'
END

It is never ending loop. and it is running on our SQL SERVER and will never stop.

Now first step is to Identify the process id for this statement for this we can use either sp_Who or sp_who2 which we discussed in TIP #82.

Now when we run it you will find on Database “IndiandotnetDB” we have a SPID 53 which is last statement on below image. This SPID 53 is reference of above while loop which we have to stop any ways.

Now we know SPID so we can stop or KILL  as shown below the syntax is very simple of KILL

KILL SPID

Now we are good to go.

Note: Please use this KILL statement very carefully.

I hope this tip will help you some where to KILL or stop SQL SERVER enemies.

Enjoy !!!

RJ!!!

Secure your password with HashBytes TIP #89

Dear Friends,

In my last article (TIP 87) I wrote about PWDENCRYPT.  I forgot to write few details but thanks to all my talented friends & blog readers who guided me by providing there feedback on post.

I respect their inputs and always interested to get more inputs. Thanks to all of you.

Now I would like to share information about “HASHBYTES”  function which is also available for secure your password using various Hash algorithms like MD2 , MD4 , MD5 , SHA ,SHA1 , SHA2_256 , SHA2_512.

The PWDENCRYPT  can be use but  HASHBYTES function provides you various options to make your content robust secure.

The Syntax is very easy as shown below

HASHBYTES (ALGORITHM, ‘INPUT WHICH YOU WANT TO SECURE’)

Now let see an example to understand it more

Just wanted to add here that SHA2_256, SHA2_512 available with 2012 version and above.

Someone said this line right “More option More confusion”

Now we have different algorithm then which one we have to use so the answer is (according to my knowledge) use the latest most secure one Like SHA1, SHA2 etc.

I hope this article may be useful to you.

Thanks !!!

RJ!!!

PWDCOMPARE–a hidden function of SQL SERVER TIP #88

 

I the last tip TIP#88 we saw how to encrypt a password. Now in this tip I would like to share how to check encrypted password ?

Means once you stored your encrypted password in database now next step is to compare that particular password with your input password and return results accordingly.

The Syntax of the PWDCOMPARE   is very simple

PWDCOMPARE(‘Password plain text’, ‘Password encrypted form’)

This function return 1 if plain text and hash value  are matched else return o.

For example

Lets suppose we have created a table with 3 columns like userId, username and password

as shown below

DECLARE @tblLogin AS TABLE (UserId INT IDENTITY,
                            Username  VARCHAR(100),
                            EncryptedPassword NVARCHAR(MAX))

Now suppose we have inserted 2 rows in to it wit encrypted password

INSERT INTO @tblLogin VALUES ('Indiandotnet',PWDENCRYPT(N'MyPassword'))
INSERT INTO @tblLogin VALUES ('SQLRaaga', PWDENCRYPT(N'Test'))

Now, Suppose we have want to write a query which return rows from @tbllogin whose password is Test then it should return SQL Raaga for this

I have to write following query

SELECT * FROM @tblLogin WHERE PWDCOMPARE(N'Test',EncryptedPassword) = 1

For detail  take a look of below snap

 

I hope you understand with above provided  example.

 

Enjoy !!!

RJ!!

How to Encrypt password in SQL Server ? TIP #87

Security is always a concern for every database developer. How to secure valuable information is one of the major and important aspect.

The first approach toward security to have a strong username & password and the next step is to have password in encrypted form.

Now this article will help you to encrypt your password in hash. Isn’t it interesting ?

So SQL Server provided a function by using that particular simple function we can encrypt a password from plain text to hash.

The valuable function is PWDENCRYPT.  By the name it is clear that it will crease the password.

The syntax is very simple PWDENCRYPT(N’String which yyou want to encrypt’)

see below snap for more detail.

I hope this tip help you to secure your password.

Enjoy !!

Thanks

RJ

Security tips- How to encrypt Stored Procedure–TIP #24

 

Sometimes for security reason it is require to encrypt the objects which you have created (Suppose you have deployed your database on client end or shared hosting and you don’t want any other person to see your logic behind your stored procedure and function).

To achieve this you need to use a simple keyword encryption

In below example I have encrypted the proc_GetListUser

 

Now when someone try to see the content of stored procedure generally he/she will write following command

sp_helptext proc_GetListUser

When he/she  will run this command he will get encryption message not the stored procedure content.

 

 

Hope you will use this tip soon.

Cheers!