SQL Information Protection – why should you care and what you can do about it?

SQL Information Protection – why should you care and what you can do about it?

SQL Information Protection - why should you care?

SQL Data Protection
A little background

In today’s world data privacy and protection is one of the biggest concern for the organizations across the globe. Most of the apps, websites we use today captures some sort of personal data like email address, phone number, date of birth and even our credit card information in some form or the other. Recent data breaches are making app and website users less confident on the companies who manages their data. This is hampering the brand image and trust for the companies.  One of such recent Cambridge Analytica Facebook data scandal triggered movement like #DeleteFacebook. This caused billions of dollars for brand like Facebook and trust factors went negative for them.

To help protect Citizen’s personal and sensitive data –  data protection and regulatory laws are in place for many countries. These laws and regulations are not something very new, they are there for quite a while. Recent data breaches and scandals triggered various governments to take strict actions to protect their citizen’s data, one of such example is European Unions General Data Protection Regulation (GDPR). 

Many tech titans and entrepreneurs across the globe stepping up and demanding stronger and well-crafted data protection regulation. It is needless to say in future data privacy and it’s protection is going to be a huge thing and organizations will be imposed with more and more regulatory, audit and reporting requirements around the data that they manage.

Since database is the heart of any organizations IT infrastructure where most of the data gets stored and processed. It is needless to say the most of the regulatory controls applies to database first. SQL Information Protection (SQLIP) is one of such technique from the house of Microsoft – using which you can stay compliant with regulatory and audit compliance by discovering, classifying, labeling & reporting enssitive data in your databases.

Ask yourself whether you should care about SQLIP or not?

Now, it’s time to ask yourself a few questions to understand your current situation and to assess whether you should really need to care about SQLIP or not?

Is your organization deals with a lot of your customers personal data? data of Personally Identifiable Information (PII) category? deals with health care information, financial information, business sensitive data, and moreover are you managing data of European Union (EU) Citizens? 

If any one of the above answer is yes and you store your customer data in Microsoft SQL Server on-premise or in Azure SQL databases then you must care about SQL Information Protection to stay compliant.

SQLIP will act as an infrastructure of your organization in data and information protection paradigm. This will help enormously on the following front:

  • It will help your organization to meet data privacy standards and regulatory compliance requirements. One of such compliance requirement is EU’s GDPR which is due on May 2018 (yes it’s very close).
  • In data security scenarios, such as auditing and alerting on anomalous access to sensitive data.
  • Help hardening security and controlling access to the databases with highly sensitive data.
How SQLIP works and how you can be benefited?

SQLIP comes with a set of advanced capabilities which protects the data, not only just the database. SQLIP is based on few fundamental blocks of capabilities as follows:

  • Discovery & recommendations – It has In-built automated classification engine which scans the database and identifies columns with potentially sensitive data. It gives us an easy user interface to review and apply appropriate classification in Azure portal or in Sql Server Management Studio (SSMS).
SQL Information Discovery
  • Labeling – Using new classification metadata attributes you can label columns with sensitive information and this can be persisted. This metadata can further be utilized in advanced sensitivity-based auditing and protection scenarios.
  • Visibility – Database discovery and classification state can be viewed in a dashboard in the Azure portal or in SSMS. This report can be downloaded in Excel, PDF and Word formats for compliance & auditing purposes further.
SQL Data classification report
  • Monitoring/Auditing – Currently in Azure SQL DB, sensitivity of the query result set can be calculated in real time and used for auditing to see who accessed the sensitive data.
Azure SQL Data Audit
So, in a nutshell SQLIP helps you to identify potentially sensitive data in your SQL database, helps you to review and label the data based on the sensitivity of the information, generates reports out of the data classification and database which then can be used for all regulatory and compliance requirements, additionally in Azure SQL it provided a monitoring on who has accessed which data.  
What next?

I hope by now you have got a basic understanding of what SQL Information Protection is, how it works and why it’s important for you. Now it’s time to get into action. Refer to these articles from Microsoft to get started 

Data Discovery and Classification in Azure SQL Database using Azure Portal.

Data Discovery and Classification in SQL Server On-Premise using SSMS.

Finally if you are done with the data discovery and classification,  you can look at the column’s extended properties to see the physical changes made by the data discovery and classification.

Subhankar is an Information Technologist, Solution Architect and Software Engineer from India. He has more than a decade long Industry experience in solving various business critical problems by infusing goodness of technology. He is a coffee person and has a lot of enthusiasm for gadgets and space exploration. He cares for Cancer research and prevention.

SQL Server on Linux – Install and use it with SQL Operations Studio

SQL Server on Linux – Install and use it with SQL Operations Studio

SQL Server 2017 on Linux - install database engine and manage it using cross platform SQL Operations Studio.

sql server on linux install and use it with sql operations studio

As we already know that Microsoft loves Linux and similarly SQL Server also fell in love with Linux. On October 2017 Microsoft announced the general availability of SQL Server 2017 for Linux. This relationship brought us many goodness in the table. This gives us freedom of Platform of our choice, unprecedented security of Linux, pricing of opensource with all great features, this are just few examples. Few of the studies also found that moving secondary databases to Linux paid for itself in less than nine months. Now you can imagine the possibilities of this movement.

To bring the SQL Server on Linux experience – in continuation of my previous post, in this post we are going to build an environment in Linux. We are going to Install SQL Server 2017 there. Will witness and feel how quickly we can install SQL server on Linux and start using it right away.

So… few of the things needs to be performed to have our SQL Server on Linux. These are as follows –

  • Installing SQL Server 2017 on Linux Virtual Machine in Azure cloud.
  • Create our first database in SQL Server on Linux using SQL Operations Studio (MSOS).

So far SQL Server is not supported in Windows Subsystem for Linux (WSL). Using WSL for SQL Server installation is not an option at this moment. We are going to create a Linux Virtual Machine in Azure. Will install SQL Server in the VM. Here Ubuntu is our preferred Linux distribution. Since installation of SQL Server needs 2GB and 6GB of disk space memory we are taking Standard A2 v2 plan with 4GB of memory for the VM creation.

Once the VM is provisioned then we can either start working with Azure CLI in the Azure portal or we can go a bit further and install remote desktop feature for Linux. Since we are familiar with the bash by now we will use Azure CLI to install SQL Server. We need to follow the following steps now –

Import the public repository GPG keys:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –
Register the Microsoft SQL Server Ubuntu repository:
sudo add-apt-repository “$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)”

*** the default user in the CLI may not have sudo privilege. We need to create an another user with sudo privileges or change the existing ones password. You can do so by Reset Password option in Azure Portal.

Once the user is created then change the user using this ssh command:

ssh [username]@[vm dns name]

Now you will have sudo privilege to run the following commands to install SQL Server:

 sudo apt-get update
sudo apt-get install -y mssql-server
Above steps will install the required packages. Now we need to run mssql-conf setup which will prompt for SA password and will have to chose edition of SQL Server. We are choosing 2) Developer edition.
sudo /opt/mssql/bin/mssql-conf setup
SQL Server Setup Linux

Did you also notice how quickly SQL Server is installed on Linux under just 5 mins?. Now you might be wondering since the installation was so quick whether the SQL server instance is running at all or not? Okay now let’s check it. Go ahead and run this command in the CLI.

systemctl status mssql-server
Can you see the word active (running) came up in color green ? It means your SQL Server Database engine is active and running. It is giving you Green signal to hit the gas pedal and play around with it. Starting from creating a database, creating tables to running query on top of it.



By now we have got the green signal and got the go ahead from you database engine. Without further ado it’s time so see whether we can create a database, a table and run query on top of it. 

Since we are talking Cross platform a lot, why not use a cross platform tool for our database development and management here?. Instead of using any command line tool or over used SQL Server management studio (SSMS) we are going to use a brand new lightweight database management tool called SQL Operations Studio.

You can download it from here (just make sure 1433 post is open). After installing it try to connect to your database in the Linux VM. SQL authentication will be used with the SA password that we have created during the setup process. If connection is successful then we should be able to see all system databases in the database explorer (looks similar to SSMS). 

Now it’s time to create our own database in SQL server on Linux and run few queries to satisfy eyes 🙂 

SQL operations studio

Once the database is connected we need to select the server node and click on the new query. A query window with intellisense will come up where you can write all our database query. 

Here we are creating a database named LinuxDotNet using statement – CREATE DATABASE LinuxDotNet

Then creating a Customer table, inserting data using INSERT statement and finally running a SELECT query on top of the Customer table.

We have got our data back all the way from the SQ Server database running on Linux. So… our SQL server database is running inside a VM, and is fully wired up with MSOS. Similarly we can connect this database with SSMS and any SQL Command line tools to experience all the goodness.

## If you want to run ASP.NET Core MVC web app with Entity Framework Core against this database then get the project from GitHub and run it.

## Currently SQL Server is not supported in Windows Subsystem for Linux. If you like to see the feature in WSL then vote at Microsoft user voice here.

Subhankar is an Information Technologist, Solution Architect and Software Engineer from India. He has more than a decade long Industry experience in solving various business critical problems by infusing goodness of technology. He is a coffee person and has a lot of enthusiasm for gadgets and space exploration. He cares for Cancer research and prevention.

Windows Subsystem for Linux – build ASP.NET Core MVC web app in Linux

Windows Subsystem for Linux – build ASP.NET Core MVC web app in Linux

Build Cross Platform ASP.NET Core MVC Web application in Windows subsystem for Linux.

Cross Platform Application

Have you ever imagined of running .NET in Linux? it sounded like a crazy idea or a wildest dream of a Microsoft stack developer up until a couple of years ago. But not anymore… this is a reality now. With Windows Subsystem for Linux (WSL) in Windows 10 we can do many crazy things, one of such thing is running .NET applications inside Linux (yes your wildest dream is not a dream anymore, it’s a reality now).

Today we are going to witness one of such things by building a ASP.NET Core MVC web application in Linux. We are going to install .NET Core in native Ubuntu installation. Will build a MVC web application and then run it from Linux. 

Check my previous post to know how to setup native Linux bash on Windows 10.

Okay…so first thing first. We need to install .NET Core in Linux, in order to do so – we’ll need to register the Microsoft signature key and add the Microsoft Product feed as a pre-requisite. This is needed once per machine.



Let’s fire up the bash (windows key + R >> type bash and hit enter) and run the following commands. This will register the trusted Microsoft signature key.

curl https://packages.microsoft.com/keys/microsoft.asc | gpg –dearmor > microsoft.gpg

sudo mv microsoft.gpg /etc/apt/trusted.gpg.d/microsoft.gpg

Register the trusted Microsoft signature key

Once you are done with registering the key, you need to Register the Microsoft Product feed. This command varies with the version of Ubuntu. To check Ubuntu version run lsb_release -a command in the bash. Look for the word Release and you will see the build number there. Based on the release version of your build you will need to run the command accordingly. In my case build number is 16.4, so I ran the below command.

sudo sh -c ‘echo “deb [arch=amd64] https://packages.microsoft.com/repos/microsoft-ubuntu-xenial-prod xenial main” > /etc/apt/sources.list.d/dotnetdev.list’

Check this for other various Ubuntu version specific commands.

Check ubuntu version

By now you have completed all the pre-requisites of installing .NET Core. Now you have to install the real thing, – ‘The .NET Core‘ in Linux. 



Execute the following commands – 

sudo apt-get update
sudo apt-get install dotnet-sdk-2.0.2

install dot net core

Okay…, lets now check whether .NET Core is installed properly or not? Just run the command dotnet

If you see your bash is trying to help you out with various options, then you know that .NET is installed in your Linux properly.

Now that you observed things are in place, let’s try to run our first .NET application inside Linux (yes this is the historic moment you have been waiting for 😊)



We are going to keep all our files and folders in an accessible location in Windows file system. Let’s pick either C drive or D drive. You can find these drives from bash in this path:  /mnt

Go to /mnt by typing command cd /mnt

Now go inside D drive by typing cd d

Looked into all the contents of d drive using ls command

Use any existing folder or create new one. I am using a folder called WORK and there I will create a folder for our application.

I have created a dedicated folder named LinuxDotnet inside the WORK folder using mkdir LinuxDotnet command (name it as per your preference).

ubuntu mkdir

Once done you can see your Folder is created and you can see it from windows explorer.



If you want to know more about what dotnet command does, then type dotnet –help and see what all you can do with dotnet command.

We are not going to create any plain vanilla “Hello World” console application here like every one does. We are going to create a ASP.NET Core MVC web application. Let’s see what and how we can start from here.

Using dotnet new command we can create a new project of various supported template. Before doing this let’s see what are the options available for us.

Type dotnet new –help

dotnet new-options

You can clearly see the number of supported templates, it allows you to create simple console app to class library to MVC, web app with Angular, so on and so forth. If you want to create a console application in C# use command dotnet new console -lang c#

Similarly, as we are going to build a MVC web app we are going to use dotnet new mvc command with appropriate options.



In your bash before creating the app you may want to see what are all the options available for mvc template by using the below command –

dotnet new mvc –help

we are going to create a new MVC app and since it’s our first application we are not using any authentication for now. We are going to use –auth None option. Our command will now look like this –

dotnet new mvc –auth None 

run this in bash

dotnet new mvc command

Observe that inside the project directory, a lot of files and folders got added. This is the default template structure of ASP.NET Core MVC web application. If you create a new project from Visual Studio then also you will see the same file and folder structure.

The application from the default MVC template is created now, it’s time to run it. Before firing up the run command, package restoration is needed. Run dotnet restore for restoring the packages.

dotnet restore nuget



Okay we are in the final stage to run our ASP.NET Core MVC application from Linux.

use dotnet run command and observe the bash.

Your application started running in http://localhost:5000 . (This port number may not be always the same.) Open this URL in any browser of your choice and see the magic… 

There you go… you have your first .NET Core app running in Linux. You are now looking into a ASP.NET Core MVC web application running inside Linux in your browser. Yes a Microsoft product running in Linux. 

So…Congratulation on your first ASP.NET Core MVC app on Linux



Now lets try to see whether we can modify the project in Windows 10 in a Visual Studio IDE and run it back from Linux again. By doing so we are testing cross platform capability to it’s best – develop in one platform run it in another platform

Before we do so exit from the app by pressing Ctrl +C in the bash.

As we have created the project inside D:\WORK\LinuxDotnet directory. Navigate to that path and open the project file (.csproj) in Visual Studio.

modify MVC app in Linux

Modify the code in Visual Studio IDE as you would like to. Save it, if you want to build and run it in IIS then you can do so also. Here we will run the IDE modified code back from Linux. So…go back to to the bash and open the site using dotnet run (make sure you are not running the website in debug mode in Visual Studio before running it from bash), check the url in the bash and open the url in a browser.

Can you see the new website that you have just modified in Windows? yes you just ran a MCV application created in native Linux, modified in Windows using Visual Studio and executed back in Linux.

Now it’s time to conclude. In this article we have covered how to prepare your native Linux installation on Windows for .NET Core applications, how to create and run your first ASP.NET Core MVC web application and finally modified the application in Visual Studio for Windows and run it back in Linux.

In my upcoming post, will talk about installing SQL Server in Linux.

Till then stay tuned and stay healthy.

Subhankar is an Information Technologist, Solution Architect and Software Engineer from India. He has more than a decade long Industry experience in solving various business critical problems by infusing goodness of technology. He is a coffee person and has a lot of enthusiasm for gadgets and space exploration. He cares for Cancer research and prevention.

Run native Linux bash on Windows 10

Run native Linux bash on Windows 10

Native Linux on Windows 10? Yes! it's real. Let's see how.

Run native Linux on windows

Remember those old days when we had to use Linux’s VI editor to write C, C++ , Java code in our School/ College days? Do you also remember how we used to get that black UNIX terminal (Bash) in our Windows machine? Well…mostly we had to do telnet from our Windows system to the Linux server to get the terminal back so that we can use the VI editor and write some code.

I can remember in our programming lab we had a Linux server and it was connected to multiple Windows terminal. We used to telnet to the Linux Server to run the Bash and form there all other subsequent commands. If anyone wanted to configure a standalone system on their own with Windows and Linux then they had to undergo a lot of challenges. Starting from partitioning the hard disk, installing Linux from multiple CD, configuring dual booting and so on and so forth. Unfortunately if you miss a little thing, you mess the whole thing up, specially the boot loader. It was a golden time for the IT guys back then, they used to charge huge to Install Linux on your PC. Then Virtualization came and Installing Linux in a VM became a bit easier and less painful.

For web development or for basic programming we never needed a full-blown Linux setup with all the bells and whistles. The bash terminal was good enough to accomplish most of the tasks in hand. So why carry this heavy baggage of Linux with Windows all the time? Don’t we have a light weight solution for that?

Yes, fortunately we have a solution now. Windows 10 comes with – Windows Subsystem for Linux (WSL). This helps us to install Linux inside Windows OS. It may sound like a crazy thing but, yes, it’s true and we are going to see it in action in a bit.

So…no more dual booting system, no more VM. It’s a native Linux system inside Windows 10 we are talking about here. We can directly Install Linux distribution of our choice in Windows 10, such as Ubuntu, SuSe etc. We can now have a full-blown Linux Bash running on Windows 10. We can run Ruby, Python, C# (.NET Core) all inside Linux sitting at your Windows 10 PC. Just like using another command prompt side by side.

Exciting isn’t it? Without further ado let’s jump into it right away and see how we can make this happen.

Step 1: Check your Windows 10 version:

Windows Anniversary edition or above version can only supports WSL. To check : Run (Windows key  + R) then type winver and hit enter. Now see the build number. It should be 16215 or later.

Windows 10 Version Check

Step 2:  Enable developer mode: Go to Settings >> Update and Security >> For Developers >> under Use developer feature check Developer Mode. It will show a warning message. Press Yes.

It may take some time to install the developer mode packages. Once you see this then exit from there.

Windows 10 developer mode check

Step 3: Enabling Windows Subsystem for Linux:

Go to control panel by –  Run (Windows key  + R) then type appwiz.cpl >>  Turn Windows  feature on or off.

Turning on windows feature - WSH
Turning on windows feature - WSH

It’s going to search for required files and apply the changes and then will ask for Restart your PC.

Now all pre-configuration done. It’s time to install the Linux on your Windows 10 PC. If you have Windows 10 fall creator update (build 16215 or later) then you can download your Linux distribution from Microsoft store. (Ask Cortana to find it for you, if you are feeling Lazy 😊). Else open Microsoft Store and Search for Ubuntu (you can choose other distros also).

Step 4: Once you or your Virtual Assistant Cortana found the App then hit on Get button. It will download the file.

Ubuntu from Windows store

Once installed you will see a notification from Microsoft store saying that now you can launch Ubuntu. Hit the launch button.

Ubuntu launch

After hitting the launch button, for the very first time you will see a terminal with Ubuntu logo.

Ubuntu installing

Step 5: Now you will be asked to setup a new UNIX username and password.

Ubuntu set Username Password

*** for older version of the windows (older than build no. 16215)

Open Command Prompt in Administrator mode >> type Bash >> it will ask you to accept the terms and conditions >> then it will start downloading Bash on Windows. Now follow above Step#5.

Once user name and password is set run apt-get update to get the latest update and you are done.

 Your UNIX user name and password is created now. Observe the terminal now, can you see the prompt? username@hostname:~$ – are you able to recall your old days memories of Linux shell? Does this terminal look familiar? yes this is the Unix terminal running at your PC.

Windows 10 Unix Bash

Can’t wait to test it’s functionality? Okay…Let’s run some basic commands like cal and date. Also let’s see if we could find Windows file system (under /mnt) from the bash or not.

Full blown Unix Bash

Look the bash is in action. It just ran some Unix commands, and that too from Windows.

So finally we have a fully functional Linux Bash terminal in our Windows 10. We are good to go for now. Going forward if you want to use this Bash then ask Cortana for Ubuntu and run it from there or alternatively Windows Key + R >> type bash and hit enter. Your bash is all yours to play around with.

If you have gone through my previous post then this post is the preparation phase of running .NET Core apps inside Linux. We will see how we can Configure .NET core inside this Linux and run C# applications in upcoming post.

Till then stay tuned and stay healthy.

Subhankar is an Information Technologist, Solution Architect and Software Engineer from India. He has more than a decade long Industry experience in solving various business critical problems by infusing goodness of technology. He is a coffee person and has a lot of enthusiasm for gadgets and space exploration. He cares for Cancer research and prevention.

Microsoft Loves Linux – what it means to you and your clients?

Microsoft Loves Linux – what it means to you and your clients?

Microsoft’s new open philosophy – see how you can help your clients.

Microsoft Loves Linux

 ‘Microsoft loves Linux’ – yes you read it right. There was a time when Linux was compared with Cancer once. The scenario has totally changed. Now they are kind of in a romantic relationship. If you have followed some recent announcements by Microsoft in Satya Naddela era, they are really focused on making things open source, making things cross platform, building things for Cloud and Mobile first world. Few years ago, we got .NET Core – a modular, high performing light weight .NET framework to build cross platform applications targeting Windows, Linux and MacOS. Which enabled us modernising the application in the era of client side heavy, modular, containerised applications built for cloud and obviously targeting operating system of our choice. Build in one platform run in another. This was not possible at all in the past.

    Recently Microsoft has released SQL Server 2017 for Linux. One of the biggest server product built for Linux by Microsoft. It is to give more freedom to their customers to choose operating system of their choice. Oracle is doing the same thing for years now, their database can live in Windows as well as in Linux servers.  This is to tap into the market where there are players already exists and firms are heavily into Linux Eco system.

    Having said all these, how is it going to affect you and your clients? Let’s talk about a typical Web application development scenario – as a consultant of Microsoft stack development team, I was asked many times for a new client Implementation to choose right tools and technology platform for their web applications. As an obvious choice ASP.NET with C# for web, and for data storage SQL Server database was the number one candidate. Since ASP.NET and SQL Server both were tightly coupled with Windows OS so far, we had no other choice than to suggest our clients to go for Windows Server only.

    Now, when giving a ball park estimate about the Server OS and other software licensing cost to our clients, it was found most of the times that – it was overshooting clients budget for a small to mid-sized implementation. Since the licensing model for Windows Server is complex and costly, it was a driving factor for over budgeting. For some other clients who already has their Eco-system built in Linux, and doesn’t want to move from there to Windows Eco system, for those cases also it was a deal breaker. If you are a Microsoft technology development shop than you wouldn’t feel comfortable suggesting your clients to choose another low cost open source platform. The reason being – 

  • You already have a resource pool of Microsoft technology stack development. 
  • It will take significant amount of time and money for your development team to learn any new open source platform. – and for sure clients won’t wait for that.

    So how would you deal with it? as everyone knows Linux is free and open source distribution, but in reality it’s not completely free. Industry leading Linux distributions like ReadHat or Ubuntu charges for support. For sure customers won’t go for production without the support from the OS vendors. So, you have to purchase support from the vendors. Still you are going to save money for your clients. Wondering how much? It was found in many studies that total cost of ownership (TCO) of Linux is about 34% lesser than Windows. Another study says – Unix and Unix-like operating systems powers about 67% percent of all web servers and the majority runs on Linux. In-fact Microsoft says more than 1/3 of their VM’s in the Azure are in Linux. Microsoft now supports two of their biggest candidate .NET framework as .NET Core and SQL Server in Linux. Recently Amazon AWS also started supporting .NET core and SQL Server in Linux. Eventually all other cloud and shared hosting providers started supporting this and coming up with competitive pricing model. This is just the beginning.

    After going through all the above texts and numbers – it’s a no-brainier now. If you use Linux as web server for your ASP.NET website and SQL Server for Linux for database – you are good to go. You are saving your client’s money on server operating system licensing. You are saving money by not spending in skill set development for your team.

    Now roll up your sleeves and start developing ASP.NET Core website in MVC, deploy it in the Linux web server such as Apache. Deploy SQL Server 2017 in Linux database server or may be in a Docker container, you are all set. **

    Your development team is happy, since they don’t have to slog long hours to learn a new thing. Your client is happy, since you have saved a good amount money. So…It’s a win-win situation for you and your client both.



** My upcoming posts will talk about building and deploying ASP.NET Core MVC web apps for Linux. Till then Stay tuned and stay healthy.

Check this post on how to install Linux on Windows 10.

Subhankar is an Information Technologist, Solution Architect and Software Engineer from India. He has more than a decade long Industry experience in solving various business critical problems by infusing goodness of technology. He is a coffee person and has a lot of enthusiasm for gadgets and space exploration. He cares for Cancer research and prevention.