Protecting Personally Identifiable Information (PII) is a major responsibility for any organization. PII Data encryption can protect consumers from credit loss and identity theft, as well as save the company money, legal and enforcement penalties, and even bankruptcy. With the help of Artificial Intelligence (AI), Azure Cognitive Service can help recognize and mask PII Data in any data management solution before it can be consumed by stakeholders.
In this article, we would use Text Analytics to recognize and mask data from a table in MS SQL database. The Text Analytics API is a cloud-based service that provides advanced natural language processing over raw text provides APIs for PII Data recognition, sentiment analysis, key phrase extraction, language detection, and named entity recognition among others
Process Flow

As shown in the figure, we will create a Time Trigger Function that would get triggered at the scheduled time. The Function would read the content from a table in an MS SQL Server database and call Azure Cognitive Service – Text Analytics API. The API would identify the PII data and return masked data as output. This output will be updated in the table.
Prerequisites
- An Azure Subscription
- PII Data in a table in MS SQL Server
Provision Azure Cognitive Service – Text Analytics
Provision Text Analytics Service to invoke client APIs and mask the PII Data in the table
- Login to Azure Portal
- Search for Text Analytics and Click on Text Analytics under Marketplace

- Provide Name and Click on Create

- Go to Resource > Keys and Endpoint. Copy Values of Key and Endpoint. These values would be configured for calling the APIs

Create Azure Function
Create a time trigger Azure Function. These trigger will get invoked at a scheduled time to read data from table for PII data masking.
- Open Visual Studio
- Click on Create a New Project

- Search for Azure Functions Template

- Select the template and click on Next
- Provide Project Name and click on Create

- Select “Timer Trigger”. Configure Schedule by providing CRON expression. Click on Create.

Add Cognitive Service Nuget Package
- Right Click on Project > Manage Nuget Packages
- Search for “Azure.AI.TextAnalytics”. Select and Click on Install

Create Azure Cognitive Services Client
- Right Click on Project > Add > New Item…
- Select “Class”, provide name and click Add

- Update the PIIDataMasking.cs with below Code.
In this code we have created a static Run method to create a Text Analytics Client and invoke RecognizePiiEntities () method.
using Azure;
using System;
using System.Globalization;
using Azure.AI.TextAnalytics;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace DemoFuntionApp
{
public static class PIIDataMasking
{
public static string Run(string endpoint, string key, string document, string language = "en")
{
try
{
AzureKeyCredential credentials = new AzureKeyCredential(key);
Uri myuri = new Uri(endpoint);
var client = new TextAnalyticsClient(myuri, credentials);
PiiEntityCollection entities = client.RecognizePiiEntities(document).Value;
string redactedText = entities.RedactedText;
return redactedText;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return "Error in finding the Sentiment";
}
}
}
}
Update the Azure Function
- Right Click on the Function and rename it.
- Update below code in the Function.
- Update database connection string, Azure cognitive service endpoint and subscription key.
The below code triggers at a scheduled time. Reads the content from the table in SQL Server database and calls the Run method on the PIIDataMasking class. The output of the method is updated back to the table in the database .
using System;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using Microsoft.Extensions.Logging;
using System.IO;
using System.Collections.Specialized;
using Microsoft.Extensions.Configuration;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc.Formatters.Internal;
using System.Text.RegularExpressions;
using System.Collections.Generic;
namespace DemoFuntionApp
{
public static class PIIDataMaskingFn
{
public static string Endpoint = "";
public static string SubscriptionKey = "";
public static string dbconnectionstr = "";
[FunctionName("PIIDataMaskingFn")]
public static void Run([TimerTrigger("0 */2 * * * *")]TimerInfo myTimer, ILogger log)
{
DataSet dataset = new DataSet();
using (SqlConnection conn = new SqlConnection(dbconnectionstr))
{
conn.Open();
var queryString = "Select IDCOl1,COl2 from TestTable where IsMasked = 0;";
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(
queryString, conn);
adapter.Fill(dataset);
foreach (DataRow dr in dataset.Tables[0].Rows)
{
string id = dr[0].ToString();
try
{
string maskedtext= string.Empty;
string feedback = dr[1].ToString();
if (feedback != "")
{
maskedtext = PIIDataMasking.Run(Endpoint, SubscriptionKey, feedback, "en");
}
var updatequery = $"UPDATE [dbo].[TestTable] SET [Col2] = '{maskedtext}' ,[IsMasked] = 1 WHERE IDCol1= {id}";
SqlCommand command = new SqlCommand(updatequery, conn);
command.ExecuteNonQuery();
}
catch(Exception ex)
{
}
}
}
}
}
}
Please note, the maximum length of text for the RecognizePiiEntities method in Text Analytics API is restricted to 5120 characters. If you have text with a length more than the restricted limit, then (a) split the text (b)invoke the text Analytics API in a for loop (C) concatenate the output to a single string.
Categories: Azure, Azure Cognitive Service, Azure Functions
An impressive share! I’ve just forwarded this onto a friend who has been conducting a little homework on this. And he in fact ordered me lunch because I found it for him… lol. So allow me to reword this…. Thanks for the meal!! But yeah, thanks for spending the time to discuss this subject here on your site.
This is really overwhelming. Thank you so much for your kind words.
I must thank you for the efforts you have put in penning this website. I’m hoping to view the same high-grade content from you later on as well. In truth, your creative writing abilities has encouraged me to get my very own blog now 😉
Thank you so much.I am glad that this post encouraged you to start your blog and share your knowledge with everyone!
Having read this I thought it was very enlightening. I appreciate you spending some time and effort to put this short article together. I once again find myself personally spending way too much time both reading and leaving comments. But so what, it was still worthwhile!
Thank you, Nicki. Really appreciate your kind words!
This is a topic that is close to my heart… Take care! Where are your contact details though?
Thank you! Details on contact page
Aw, this was an extremely good post. Spendingsome time and actual effort to create a goodarticle… but what can I say… I hesitatea whole lot and never manage to get anything done.
Thank you!
Very good blog.Much thanks again. Really Great.
thank you!!!
I have learn some just right stuff here. Certainly value bookmarking for revisiting.
I wonder how much effort you place to create one of these fantastic informative website.
Thank you so much!
This article is truly a fastidious one it helps new web visitors, who are wishing
for blogging.
Thank you
Hi there! This is kind of off topic but
I need some guidance from an established blog. Is it hard to set up your own blog?
I’m not very techincal but I can figure things out pretty fast.
I’m thinking about making my own but I’m not sure where
to start. Do you have any points or suggestions?
With thanks
You can start your blog for free on wordpress.com