The A – Z Guide Of Create the first powerApps

In this last post, we discussed the PowerApps templates. Now, in this post finally, we are going to create our first PowerApp isn’t it exciting?

So, as mentioned earlier login with the authorized credentials and after login you will find following dashboard screen. Here, we need to select a Blank Template as shown below. Here, we have a choice to opt mobile or desktop web. We can choose as per your ease or requirement and then press make this App button.

Dashboard_Blank_app

After clicking the make this app button we get following screen which is basically our playground for App creation or actual editor where we can manage our pages, add new pages, add controls  etc..

Editor_Area

As this is our first app, we are going to make it very simple and create an addition of 2 numbers screen (Although, it seems simple but bit tricky as well.)

For which we will click 3 label controls ,3 textboxes & 1 button and arrange them properly as shown in below figure. Here we can change the control’s default text. color, etc by property pane.

Rough_Screen_of_Sum

Once this part is done, in the next step we need to show the sum of First Number, Second number’s value in 3rd text box when user clicks the Sum button.

One more point which we need to take care is format property of the textbox.

formatPropertytoText

Now, to achieve this we are going to write a function on button click as shown in the below snap.

image

Here, we need to write the code in function’s tab as shown below in screen. if you see here we are using a SUM inbuilt function and using UpdateContext (an internal function) for assigning the addition value of the textboxes to a variable called myAdd.

Sum_Function

Now, once we are done with the above function, in the next step we are going to assign the myAdd variable (who holds the addition of text1 & text2) to our result text box. To achieve this select the textbox in which we need to show summation value & click on the Property window’s function tab.

Here, we have to assign the myAdd variable  as a data’s default value (shown in below figure)

assigning_Default_Value

Once we done with above steps, next item would be to run this App and do a quick testing. To run this App click on run button (Top right corner of the editor) as shown in below figure.

run

We will get the below screen to test our sum logic.

Test_result

That’s great we created and tested our first PowerApps. Now, in next step Save this application. After clicking File menu a new screen (as we are saving this first time)  will appear where we need to provide App name , description of app and icon. After providing this details click on the Save menu .

save_App

So, congratulations we have created our first simple PowerApps and saved successfully.

Save_App_done

We will see later how to publish it which is also very easy step.

In next article we will see more stuff of PowerApps till then enjoy.

RJ

It’s All About (The) PowerApps Tempates

In the last post, we discussed what is PowerApps and an initial point where we can register our self and start working.

We can use either web studio or desktop studio for creating the PowerApps. I generally, prefer web version.

In this article, we will see different inbuilt templates Apps which give us confidence that what the PowerApps can do.

Click on https://powerapps.microsoft.com/en-us/  and log in with your authorized credentials

signin_with_Microsoft_account 

so, After login, we will find below dashboard screen.

Dashboard_Screen

There are several inbuilt PowerApps templates available along with blank Templates. Let’s start few of them to understand the capabilities of PowerApps.

Service Desk:- 

This is simply ticketing system where we can see How many tickets are open? How many are closed? and How many are in progress ? along with ticket details. so, in the organization ticketing system required the can be a good App.

Service_Desk_Template

Assets Tracker:

This template app Keep tracks of the tools and equipment your teams need to do their jobs.

Assests_Tracker

Leave Request :

This template will help the organization’s employee to raise a leave request and manager will approve it.

Leave_Request_Power_Apps 

There are many more other templates available which you can use as an instant recipe.

As it is Microsoft tool so you can easily access Office 365, planner etc. but on the top of it, you can use Camera & Location feature as well.

Accessibility_of_camera_And_Location

Now, in next article, we will see how to start with a new blank App.

step by step powerApps Made Simple – Even Your Kids Can Do It

f you are not family with this awesome thing then surely you might be curious to know what is PowerApps ?, How it works? Where can utilize it? and there are many more questions.

To resolve all these queries we are starting a Zero to Hero series in PowerApps. You will find a series of the blog going forward and we will do deep dive in PowerApps.

What is PowerApps?

You will find a lot of different definition for PowerApps but for me, it is a tool which gives you the power to create your business application which can run on web/mobile/tab with a little writing or without writing code.

PowerApps tool is super easy to use and with the help of this tool, you can create the various business app in which you can use any data source like SQL Server, Excel,  twitter, facebook etc.

Although, PowerApps is basically created for Non-developers but still as a developer you can use it.

In nutshell, we can say it Platform As A Service model. PowerApps has various controls & templates which we can utilize to create our business App.

We can create PowerApps by 2 ways either we can download PowerApps app in Window 10 from Microsoft Store as shown in the figure below.

image

Or just use following URL http://powerapps.microsoft.com

image

Once you downloaded or open the PowerApps first or foremost important is to login into the tool using Microsoft /OutLook/office365 account.

Once you  done with sign up process. you have to provide your valid username & Password to access it further.

In next blog, we will see how to create our first simple Power app.

Keep learning

How easily you can unpivot the pivot data in SQL Server?

Pivot table indiandotnet

I know when we talk about the pivoting & unpivoting the data then most of the time we are making our faces and we feel it would be a tough task. Trust me after reading this post you feel unpivot is super easy.

Before jumping directly into unpivot want to share pivot link to take a glimpse if you are not aware of it.

Pivot in SQL Server pivot 

Now, let us assume that we have following table of employee with id,name,weekid and Dayname columns.

DECLARE @tblEmployeeDayWiseAttendace AS TABLE (Id INT IDENTITY(1,1),
EmployeeName VARCHAR(100),
WeekId SMALLINT,
Monday TINYINT,
Tuesday TINYINT,
Wednesday TINYINT,
Thursday TINYINT,
Friday TINYINT,
Saturday TINYINT,
Sunday TINYINT)

Now let’s insert few rows into it

INSERT INTO @tblEmployeeDayWiseAttendace (EmployeeName,WeekId,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)
VALUES(‘Sandeep’,1,8,8,8,8,8,0,0),
(‘Sunil’,1,8,8,8,8,8,0,0),
(‘Shreya’,1,7,6,8,8,8,0,0),
(‘Shweta’,1,8,8,8,0,5,0,0),
(‘Priya’,1,8,8,8,8,8,8,0),
(‘Rashmi’,1,9,8,9,8,8,4,0),
(‘Bhushan’,1,4,8,5,8,2,0,0)

If you run SELECT * FROM @tblEmployeeDayWiseAttendace then you will get following data as shown in below snap.

Pivot table indiandotnet

Now, the challenge is to Convert Columns Monday,Tuesday,Wednesday and other day columns to row corresponding to employee and show their value.

To make it very easy you have to write below CROSS APPLY query

SELECT tmp.Id, tmp.EmployeeName,tmp.WeekId,tmp2.weekdayname,tmp2.weekValue
FROM @tblEmployeeDayWiseAttendace tmp
CROSS APPLY(values(‘Monday’,tmp.Monday),
(‘Tuesday’,tmp.Tuesday),
(‘Wednesday’,tmp.Wednesday),
(‘Thursday’,tmp.Thursday),
(‘Friday’,tmp.Friday),
(‘Saturday’,tmp.Saturday),
(‘Sunday’,tmp.Sunday))tmp2(WeekDayname,weekValue)

Once you run this query you will get the output which you require.

Now, tell me do you still afraid from unpivot.

Share your thoughts & inputs in comment.

Cheers!

RJ

Do you know filter, map, reduce in JavaScript ?

Do you know filter, map & reduce function in JavaScript? If not then you are at the right place and surely you will enjoy this post.

 

I  am pretty much sure in your JavaScript programming life you might face the situation where you have to deal with large array objects where you have to fetch each individual array object and process it. You might need to select specific objects or manipulate the entire objects or find one of the objects.

In above situations, you might have traversed entire array object one by one and then processed it.
Filter, map & reduce these three functions help us to deal such situation. After using these functions our code is much cleaner and easy to understand.

Let’s understand it by an example.

Suppose we have an array of employee objects as shown below. On which we have to perform some operation.

Indiandotnet_Employee_Collection

Now, let’s understand the map, filter & reduce function and compare with traditional way with different cases.

Case 1. Suppose we have to traverse each employee in the array and add the Bonus to their salary. so if we are going to achieve this via traditional way then below snap will show you traditional way.

Indiandotnet_Traditional_Traverse_way

Now, here we can use the map function to make this code more readable format. see, the map function uses as below.

Indiandotnet_MAP

so in nutshell, a map function is used when we want to change each element of the array into another set of values.

Case 2:- Now, suppose if we want only those employee collection whose salary more than 5000 then to achieve this we will use following traditional way.

Traditional_Processing_Way

Now, here we can use the filter function to make this code more readable. See the filter function as below.

Indiandotnet_Filter

So in nutshell filter function is used when we want to filter unwanted objects from an array collection then in such cases, we can use filter function.

Case 3:- Now, suppose if want to sum all the salary amount of employee than in such situation you will write following code

Traditional_Processing_Way

Now here we can use reduce function to make this code more readable. Same above code can be written using reduce.

Indiandotnet_Reduce

So in nutshell reduce function is use when we want cumulative value of array.

I hope you may like these awesome functions.  Please, share your feedback.

Thanks Smile

Everywhere JSON so why not in SQL SERVER–New feature in SQL SERVER 2016

If you are a developer then surely you might have used JSON (JavaScript Object Notation) but, if not then don’t worry you might use sooner than later. JSON is kind of ecosystem which is most popular in the various area for exchanging the data. If you talk about charting solution, AJAX, Mobile services or any 3rd party integration then generally JSON is the first choice of the developers.

 

If you see nowadays most of the NOSQL database like Microsoft Azure Document DB, MONGODB etc. also using JSON ecosystem and some of them are based on JSON.

 

As it is such a popular growing system So, why not in SQL SERVER?

In SQL SERVER 2016 JSON introduced. This we can say a step or bridge between NON-relation database and relational database by Microsoft SQL SERVER

 

SQL Server 2016 providing following capabilities when you are using JSON

  1. Parse JSON by relation query
  2. Insert & update  JSON using query
  3. Store JSON in database

 

If you see it then conceptually it is similar to XML data type which you might use in SQL SERVER.

The good thing  in SQL SERVER 2016 for JSON there is no Native data type.  This will help in migration from any NOSQL to SQL SERVER.

 

SQL server provides bidirectional JSON formatting which you can utilize in a various way. Suppose data is coming from the external source in the JSON format then you can parse it and store in table structure (if required) in another case external source require data in JSON format while data in SQL SERVER in tabular format so both the purpose can easily solve with  SQL SERVER’s JSON feature.

 

Now, let’s jump directly to the practical to check JSON capabilities in SQL SERVER

 

1) FOR JSON AUTO

It is similar to  FOR XML AUTO.  It will return JSON object of selected column where column name is treated as a Key or in other words we can say it will format the query result in JSON.

 

JSON_Feature_Indiandotnet_1

when you run above command the result will be like as shown in below figure.

JSON_Feature_Indiandotnet_2

 

2) FOR JSON PATH: –

It’s exactly like JSON auto the only difference is instead of SQL SERVER we have full control over the format. JSON Auto take predefined column schema while with JSON path we can create a complex object.

For example, we are using AdventureWorks Sales order table and joining that with product table to get sub-node. If you see in below image we have added Root node as well. This root Node can be added in JSON auto as well if required.

JSON_Feature_Indiandotnet_3

 

Now, when you run the above query we can get complex JSON object as follows

JSON_Feature_Indiandotnet_4

3) IsJSON function:-

By the name, it is clear that this is a validating function.

To cross check whether the provided string is a valid JSON or not we can run ISJSON.

JSON_Feature_Indiandotnet_5

 

4) JSON_VALUE:-

  By the name, it is clear that if you want to get the value of the particular key of JSON then you can use this beautiful function which is JSON_VALUE.

JSON_Feature_Indiandotnet_6

5) OPENJSON function:-

This is a very beautiful function which you can use to parse external schema. Suppose, you got a JSON string from a mobile service which you will directly pass to SQL Sever and SQL SERVER stored procedure will do rest of the operation to parse it. The parsing and other operation can be easily handled by OPENJSON. The only tweak here that it required database compatibility level 130   which you need to do (if not compatible with level 130)

JSON_Feature_Indiandotnet_7

 

There are many other interesting things which we will cover later.

Please, provide your inputs.

RJ

ROW LEVEL Security SQL SERVER 2016

To understand RLS (ROW LEVEL SECURITY) let’s understand the different problems first.

Problem 1 Suppose, you have a Multi-tenant e-commerce website and different companies registered on your website and you have centralized single database for all the client. Now as a product owner it is your responsibility that one tenant’s data should not be available to another tenant.  This is a very common problem.

2. Now, Suppose you have hospital database in which you have login user of different doctors & nurses. Now, your challenge is to show data to doctor or nurses to their relevant patient to whom they are giving treatment, not any other patient data should be available .

Here, limiting the user’s access to only certain rows of the data in database many have various reasons like compliance standards, regulatory need or security reasons.

Now, I know you were thinking that all the above problem can be resolved at code side easily by writing custom logic. I will say here yes you are right but this is not the 100% solution.  For example, if you have 4 different application like web, mobile, console, windows (Excel) and all has their own DAL then you have to implement this custom logic to every application and suppose  tomorrow if any time a new 3rd party came which want to integrate your data  or access database directly then in such cases it is tuff to apply same logic.

So, all the above problem can be easily handle using SQL SERVER 2016’s feature which is ROW Level Security (RLS). Security is one of the key areas which is handled in SQL SERVER 2016 very seriously.  As RLS (Row Level Security) is centralized security logic so you don’t need to repeat same security logic again and again.

As the name suggested Security implemented at Row Level in SQL SERVER 2016. In the Row Level, Security data is access according to user roles. It is a centralized data access Logic.

RLS has following properties

  • Fine-grained access role ( control both read & write  access to specific rows)
  • Application transparency  ( No application changes required)
  • Centralized the access within the database
  • Easy to implement & maintain

How RLS works?

RLS   is a predicate based function which runs seamlessly every time when a SQL is run on particular table on which RLS  predicate function implemented.

There are 2 predicates  which can be implemented in RLS

1) Filter Predicate: – By the name, it is clear that it will filter the row or we can say exclude the rows which do not satisfy the predicate and stop further option like select, Update & Delete.

for example: Suppose, you want to restrict doctor to see other doctor’s patient data then in such case you can apply filter predicate.

2) Block Predicate: –  This predicate helps in implementing policy by which insert, update and delete rows will prevent which violate the filter predicate. In other words, we can say it explicitly block write operation.

For example, you have multi-tenant application and you want to restrict one tenant user to insert or update other tenant’s data. Or suppose you have sales representative who belongs to specific region so they can not insert , update or delete other region’s data.

Demo:-

I know you will be super excited to see the demo of this feature so. Let’s do it right away.

There are 2 basic steps to create RLS

a) Create inline table function  or we can say predicate function  and write custom logic to control user access to every row

b) create the security policy and apply it.

In this demo ,I am creating a  new table called Patients which has following schema.

RLS_Demo_Indiandotnet_1

Here, I have inserted 2 rows for Nurse1 & 2 rows for Nurse2

RLS_Demo_Indiandotnet_2

The objective is to show only those rows to Nurse1, Nurse2 in which they are the in charge and a doctor user can see entire table’s data.

To achieve this let first create 3 users  in database

RLS_Demo_Indiandotnet_3

Once the users are created the next step is to grant permission of select to Nurse1 & Nurse2 user and full permission to doctor user.

RLS_Demo_Indiandotnet_4

Now, before creating function it is a standard to create a security schema in our case we are creating a schema with name sec as shown in below figure.

Now, create a function which will have security logic. The Logic is very simple if the user is doctor Or any in charge name then return 1 else 0.

RLS_Demo_Indiandotnet_5

Now create a security policy to proceed further

RLS_Demo_Indiandotnet_6

Till now we are good to go. Now, let’s test the security policy.

Firstly, running the select query with default user “dbo.”  and we have not given permission for this user if you see fn_RLSPredicate we have not mentioned it so obviously the result would show “0” records.

RLS_Demo_Indiandotnet_7

Now, running the same select statement but executing with “Nurse1” login then you will find 2 records which are relevant to Nurse1 is visible.

RLS_Demo_Indiandotnet_8

Similarly, I am running the same statement for Nurse2 user by running command “Execute as user” so, again I will get 2 records

RLS_Demo_Indiandotnet_9

Now, running the same statement with Doctor user and as per our expectation, it should show all 4 records.

RLS_Demo_Indiandotnet_10

So, as you can see we have achieved the goal using RLS (Row Level Security) feature. Now, next thing which might occur in your mind how to disable this policy if required then doesn’t worry it is very simple. Just alter the security policy and make state = off as shown in below figure.

RLS_Demo_Indiandotnet_11

I hope till now we are good to work on RLS. In next couple of post, we will dig deeper in RLS.

Please, share your thought for RLS.

How easy to create Your first chat bot using Azure Bot Services in 10 steps

Friends,

BOT is one of Buzz word now days. Everyone is taking about BOT Services.  I thought to explore it and came here with my understanding and 10 easy steps to create your first chat Bot using Azure Bot Services.

So, let’s start by understanding What is CHAT BOT? Just think Chat BOT/BOT an intelligent program which interacts with human or users. or we can say it is a simulator which simulates conversation with human users.

As mentioned that it is an intelligent program which use Machine learning to understand the pattern and respond accordingly. In Nutshell, we can say an Artificial Intelligent program which trend using Machine learning to mimic human chat.

Microsoft, Facebook, and other companies came up their own chat Bot framework.

Here in this example, we are talking about Microsoft Azure Bot Service which is super easy. You can create chat Bot and deploy & inject with different sources like Skype, your own site, slack, Facebook etc. Although, in this post, we are going to create chat bot only. Integration with other sources like Facebook, Slack is not part of this post. We will see in upcoming posts.

There is only one major prerequisite for creating your chatbot which is Azure portal account.

Now, let’s create your first Chat bot using Azure Bot Service.

Step 1:-  You will get Bot Service option which is currently in preview mode through the following option

App Services  —> Intelligence + analytics  —->  Bot Service (Preview)

Indiandotnet_AzureBot

Once you click the Bot Service you will get following option where you have to define the basic information like App Name & Location where you want the server.

1 

Step 2:-  Once you created it you will get the following screen where you need to create AppId which is a prerequisite to authenticate your bot with bot framework.

2

when you click “Create Microsoft App ID and password” button you will get the following screen. Where you  need to create password and paste in the below screen once you have done with copy /paste of password, you have to click “Finish and go back to Bot Framework” button

3

Step 3:-  You will below screen where your need to select your code languages like C#  or Node.Js.   As I like C# so I have selected C# from the below screen.

4

Just, below this language option, you have some other options as well as shown in below screen which is basically a template of your bot. You can select any template which make sense or fulfill your objective. In this example I have selected Language understanding template.

5

Step 4:- Now, when you continue with above option. The next interesting objective is to teach your Bot through LUIS where LUIS stands for Language Understanding Intelligence service.  Here, LUIS  helps to teach BOT.  You can directly login to https://www.luis.ai  and work for AppID which we created earlier in the steps.

6

7

 

Step 5 : Once you logged in LUIS you will get the following screen which a dashboard from where you can manage your Chatbot application’s model.

10

The next step is to teach your Model.

Step 7 : If you are not aware of LUIS currently just understand it is a teacher which teach your bot for the intents. Suppose you said “Hello” then your intention is greeting and so in this way we can teach our model. I will share a separate post for LUIS soon.

11

Step 8:- You can train your Model by option “Train “ in left bottom. Once your training is done and you think it is working fine then you can deploy or publish it. You can test it query parameter as shown below in the screen.

12

Step 9: Once you are done with LUIS you came back to your Azure Bot service application here you can manage the C# code and modify it further for the different intent which you set in LUIS. for example, In above screen, you will find three intents which are none, Location & Greetings. Similar way you can create multiple intents to make your chatbot robust. Here if you see the code in Azure itself and you don’t need to worry for different settings for publishing and make the code up and running.

9

Step 10:- As we added different Intents in our chat model so we need to update our C# code as well. To add any intent you need to modify “BasicLuisDialog.csx” as highlighted in yellow below.

13

if you see above image you will find we have added the our two intents which are Greetings, Locations and wrote our custom logic.

We can test right away by entering the different statement in chat in right chat panel.

Once you satisfied with your work just publish it. I hope this might be enough content for hands on your first Azure Chat Bot Service. I know you might face some challenges in LUIS but trust me it is super easy just try it your end and in any case, I am going to write another post for LUIS.

Please, share your inputs what you think about this post and Azure Bot Service.

What your face says let’s find out using Microsoft Emotion service

We always hear your face say everything. Your emotions on your face says everything. Microsoft’s did a great job to identify these expressions,these emotions  with Microsoft Cognitive Service.

Microsoft Cognitive service is an umbrella which has various APIs to help us intelligently.

Emotion API is one of them to determine the the expression or emotion in a image.

With the help of this Emotion service we can easily identify the emotion like happy, sad, fear,surprise etc.  The best part of this API is that it detect all the faces in an image and provides the emotion collection object. Another good thing about this API is it is easy to use you just need to pass the image and rest other thing is handle by API itself.

Now, I am very sure you are curious to know more and want to implement it at your end.  I am trying to share basic steps how you can use this in your project. just wanted to share that I am using MVC C# in my example .

In my example, I tried to upload  an image and passing that image to API and according to API result showing the result. so Let’s begin with step by step

Step1 :- First & for most important step is register for this API and grab your subscription key.  To get this  you have to register on https://www.microsoft.com/cognitive-services/

Once you registered you can get the subscription key from my account. As you notice in the snap below in free trial we have 30,000 transaction per month & 20 per minutes API calling facility.

Indiandotnet_Congintive_Service_Key

Once you got the key the next step is implement it in your project.

Step 2:- You can implement this via API URL or Nuget package manager in visual studio.  Just to update you that Microsoft’s Oxford team is working on this so the namespace name is Project oxford.

Indiandotnet_Nuget_Package_Oxford

In my project , I successfully installed nuget package manager

Indiandotnet_Installed_Through_nuget_Package_Manager

Step 3:- Once the Emotion package installed in your project simply create the object of Service API and call.

Pass the image steam or required parameters as per the documentation. As you can see in below image, I have created a new object of EmotionServiceClient & passing subscription parameter to avail it.

Once my object is Created, I am calling RecognizeAsync method in and passing the uploaded file stream.  This Recognize Asynch  method.

The best part is it return Emotion’s array by determining the number of faces. but in current, code I am just interested in determining the emotion of single face. So, I did code accordingly.

Indiandotnet_Calling_emotion_API

Step 4:-  To capture all the emotion’s score I have created a EmotionScore class as you can see below.

Indiandotnet_Different_Emotion_Capture

Step 5:- Once Everything setup just run the page and upload image.when you upload the image you will get emotion collection to play. See below snap in which I tried the same.

Indiandotnet_Sprised_Aayush

I tried with several expression (Thanks to my little champ“Aayush” to help me out with his cute expressions).

Indiandotnet_Different_Expression

As mentioned above you can use API URL as well to call it.

I hope you may like this new face expression API. For more information you can visit following sites

https://www.microsoft.com/cognitive-services/

https://www.microsoft.com/cognitive-services/en-us/emotion-api

Different samples https://www.microsoft.com/cognitive-services/en-us/SDK-Sample

Please, feel free to share your inputs.

Happy coding !!

RJ

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