A simple way to check vulnerability status of your SQL SERVER database

As a product owner, you always worried about the different security aspect of your application and SQL Server Database is one of the main important parts for which you might worry.

And you always think there should be some kind of checklist which you or your team have to check whether your database is secure or not and find all the vulnerabilities.

And obviously you might have purchased different tools to this assessment as well who will provide you security loopholes but when we talk about Database the option is limited and some options are very costly.

With SQL SERVER 2017 latest  SQL Management Studio your one of the problem will be resolved cross-check your database vulnerability.

You heard it right. Although, this feature is already available in SQL Azure but now you can do this assessment of your database using SQL Server 2017’s  management studio.

This vulnerability assessment report can be generated on the database with few simple clicks and you will get different High, Medium , Low risks of your database.

The vulnerability assessment report not only provide risks details but also help you to identify which category  of it and this will not stop here you will get a recommendation as well to fix those problems. Sometimes , you will get direct scripts which you can run to fix those issues and sometimes you will get the links on how to implement those.

Let’s understand this by step by step action.

Before starting to make sure you have SQL Server 2017  Management  Studio’s latest version.

Step 1: Once you opened the SQL Server management studio right click on the database which you want to cross check.  in this example, I am using the AdventureWorks database. As shown in the below figure.

Indiandotnet_Vulnerability_Assessment_1

Here you have 2 options either Scan for Vulnerabilities or Open Existing Scan

Step 2: Now, as we are doing it the first time so click on Scan for Vulnerabilities option. and you will get the following screen where you can provide the location of scan file.

Indiandotnet_Vulnerability_Assessment_2

Step 3:-   Just click on OK button to proceed further and wow you will get all the loop holes of your database.

You can easily check what are different points on which your Database is failed with risk Assessment.

Indiandotnet_Vulnerability_Assessment_3

As shown in the above figure , we have 6 check points on which our database failed  in which 1 is on high risk , 3 medium risk  and 2 Low risk.

And if you see carefully there are different categories as well like data protection, Authentication and Authorization, Surface Area Reduction etc.

Here as the name suggest Data Protection is mostly related to encryption of your sensitive data like SSN, DOB etc or TDE.

Authentication and Authorization  is more relation to login access of the database.

Surface Area reduction  is more related to what extra option you have opened .

Step 4:- Now, move a step further and click on any row in the grid. You will find the details of the row just below the grid. As you can see in below image when we click on data protection it suggesting the column names which come under extra care and to on which we might think to apply encryption.

Indiandotnet_Vulnerability_Assessment_4

Step 5:-  The story does not end here, for some of the problems this assessment report provides script as well and if the script is not possible then provide a reference link to resolve that issue.

As you can see in below screen we are getting recommendation scripts to apply.

Indiandotnet_Vulnerability_Assessment_5

Isn’t it cool and simple to assess your database’s vulnerability in a few clicks and secure your database?

Share your thoughts.

Happy learning !

Isn’t it easy to mask your data with Dynamic data Masking #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.

Masksyntax1

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.

DropMask

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.

Create_Table_Secure_Employee

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

Indiandotnet_Insert_Default_Row

Now we are applying different masking on this table’s column

1) Default Masking : In the table, we are applying default masking on LastName

Indiandotnet_Default_Masking

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

Indiandotnet_Apply_Email_Masking

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.

Indiandotnet_Partial_Masking_SQL_SERVER_2016

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

Indiandotnet_Random_Number_Masking_SQL_SERVER_2016

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

Indiandotnet_Select_statement

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.

Indiandotnet_Create_Login_User_SQL_SERVER_

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

Indiandotnet_Login_With_New_User

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.

Indiandotnet_Masked_Data_With_Less_Preivileged_account

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.

Indiandotnet_Removing_Mask_from_column_Sqlserver2016

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

Indiandotnet_Last_Name_Visible_

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.

1

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

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

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

4) Click on apply button and restart the services.

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

I appreciate your feedback.

Enjoy!!!

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

PWDCompare_Indiandotnet

 

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 you want to encrypt’)

see below snap for more detail.

PWDEncrypted_Indiandotnet

I hope this tip help you to secure your password.

Enjoy !!

Thanks

RJ