PASSMN June 2020 – Data Classification with SQL Server and Azure

I presented at the virtual Minnesota SQL Server User Group meeting on June 16, 2020. The topic was data classification with SQL Server 2019 and Azure SQL Database.

Data Classification Basics

Data classification in both SQL Server and Azure allow you to discover and label data based on information type and sensitivity. Information type is a way to describe the content of the data at high level. This includes types such as Address, Name, Networking, and Credit Card. By tagging your columns with types you will be able to easily see the types of data stored in your tables. You can also label the sensitivity. This includes labels such as Confidential and Confidential-GPDR.

Using SQL Server 2019 and SSMS 18.4+

For on premises implementations, you can use SQL Server Management Studio. I would recommend that you use SSMS 18.4 or greater. This has the most capability. SQL Server 2019 includes the sys.sensitivity_classifications system catalog view so you can query to see what field have been labeled.

To get started, open up SSMS. Right click the database and choose Tasks > Data Discovery and Classification > Classify Data. This will allow you to

Finding the Data Discovery and Classification Options in SSMS

view the Data Classification window in SQL Server. You will get a list of recommendations and the ability to add custom classifications in your SQL Server database.

The Data Classification view in SSMS

Once you have classified some of your data, you are able to view a report that shows the coverage of the classification work you have done.

Data Classification Report in SSMS

Adding Data Classification in Azure SQL Database

Azure SQL Database supports similar functionality for discovering and classifying data. The primary differences are (1) it requires Advanced Data Security which costs $15/month per server and (2) audit logging support is built in.

You can find this in the Azure portal with your SQL Database.

Advanced Data Security in Azure SQL Database

As you can see above, you get a visual here initially. Click the Data Discovery & Classification panel to open a similar classification window that we see in SSMS. This will allow you to discover and classify your data.

The key difference is turning on auditing and logging information about people querying the classified data. In the Security section in your SQL Database view in the Azure portal, choose Auditing. You can now add auditing to your server or database. (Click here for information about setting up Auditing.) I chose to use Log Analytics which is in preview. Log Analytics has a dashboard which shows activity in your database with this data.

Log Analytics Dashboard which Shows Access to Sensitive Data

You can click into the dashboard to dig into details. You can also use the Log Analytics query features to build your own queries to further analyze the data. The details contain who accessed the information, their IP address, and what was accessed. You can build more reports from that information to support more sophisticated auditing.

Final Thoughts

I think that there is still work to be done on SQL Server to better support auditing. Azure is ahead of the game in this area. More importantly, Azure logging is a platform level solution. You should be able to integrate your logging from the applications to the database in Azure.

You do have the ability to update the policy in SQL Server with a JSON file. I recommend you export the file and modify it. In Azure, you can update the information policy in the Security Center. Updating this policy allows you to discover data or information that you want to classify based on rules you set up. This should be part of your data governance plan.

One other follow up from the meeting. The question was raised about Visual Studio support in database projects. The answer is “sort of”. First, you need to make sure your project is targeting SQL Server 2019 or Azure SQL Database. Once that is set, you can use the following code to add the classification manually or you can apply it to your database and do a scheme compare to bring it in.

ADD SENSITIVITY CLASSIFICATION TO
    [SalesLT].[Customer].[FirstName]
    WITH (LABEL = 'Confidential - GDPR', LABEL_ID = 'fe62dcde-72c0-475c-b1af-fb8de4c8fc7e', INFORMATION_TYPE = 'Name', INFORMATION_TYPE_ID = '57845286-7598-22f5-9659-15b24aeb125e', RANK = MEDIUM);

You will need to know the GUIDs for the labels and types in your solution to do this manually. However, once this is done, you can see the information in the Properties window for the field as well.

Data Classification Properties in Visual Studio

The key thing to be aware of is that the properties are read only. You have to use the code to change them or do the changes in the database and use Schema Compare to bring them in.

Thanks again to those of you who joined us at the meeting. Here is the slide deck from that meeting. I look forward to sharing more with all of you later.

Rochester – Welcome to the PASS MN Family

It started as a vision for expanding the reach of the Minnesota SQL Server User Group a year ago. At the time, Paul Timmerman ( T | B ) and I were discussing the fact that only one user group existed in Minnesota. As we looked at other areas and talked with SQL pros from St. Cloud and Rochester, a dream was born — what if PASSMN, a large and very mature user group, helped kick off meetings in other areas? Throughout the past year, Paul has been working with Patrick out of Rochester to make this a reality.

image Source: http://binged.it/1eB310K

Tonight was our first meeting in Rochester. We had an awesome turn out with over twenty people attending. Everyone is already looking forward to the next meeting.

Paul and the crowd Paul presenting

To begin with, the Rochester group will be supported by PASSMN as an extension group. The meetings are planned to be quarterly and we will be working with local and regional speakers to participate. Our hope is that the group will grow and can become a stand alone chapter. However, the goal of PASS is community and training. Whether independent or as extension group, the goal is to meet these needs within the SQL Server development community where ever we can.

PASSMNLogo

For more information about PASS MN and PASS MN – Rochester meetings, check out http://minnesota.sqlpass.org/.

Traveling and Talking to Wrap Up 2013

I am writing this blog post en route to New York City for SQL Saturday #235. This begins a fairly busy travel and speaking schedule for me this year. Having spent much of the summer at home with my family which was great, I now embark on some trips, primarily out east.

For starters, I am kicking off my busy end of summer, beginning of imagefall, in New York City. I am looking forward to being there for the second time. This should be a great event. As Regional Mentor for the NorthEast this trip allows me to be in that region for an event.  I will be speaking on Building BI Solutions with Microsoft Excel.

Next, up is some quality time in Boston. I have a contract that will put me in downtown Boston for about 4 weeks. After that trip, I cm-logoskip a week and then return to speak at CodeMastery which is a Magenic event in late September.  If you are in the Boston area, I will be speaking on the difference between SQL Server Analysis Services Models – the Multidimensional and Tabular Model Smackdown.  More details of this event will be coming soon, but pencil it in for Thursday 9/26.

While not traveling far, I am speaking at SQL Saturday #238 in Minneapolis right before PASS Summit in Charlotte. This is right in my backyard, so to speak, and I look forward to another great event in the Upper Midwest. I currently do not have the sessions that were picked, but look forward to seeing you there.

October will be spent at PASS Summit. While I was accepted to speak this year, I look forward to meeting a number of people at the Community Zone and the other events at the conference. I will follow that up by going to SQL Saturday # BI Edition in Charlotte. While I have not received word about speaking, I plan to attend the event in either case. Should be a nice way to wrap up my time there.

In November, I am headed to Atlanta for another CodeMastery event. This should be a nice time to visit there and spend some time with people there.

cm-logo  X 2

Upon returning from Atlanta, I am back in Minneapolis for a CodeMastery event there. Having had a major role in running this event over the past couple of years, I am truly looking forward to speaking there. Check out http://codemastery.com for details on all of the CodeMastery events as they are announced.

Modern Apps Live!SQL Server Live!

Finally, at least at this time, I will be presenting 5 times right before Thanksgiving. This will be my first time presenting at SQL Live and second time presenting at Modern Apps Live. Join us for this awesome 360 Live event in Orlando.

I look forward to meeting new people and visiting with old friends throughout all of these events and travels. If you see me there, say Hi and ask for one of my “famous” superhero cards! See you around.

PASSMN–Passing the Baton: A Board Transition Story

Each year the Minnesota SQL Server User Group elects new board members and resets its leadership team.  I have been on the board for the past three years during which we switched term lengths to two years with staggered elections, but more about that later.

PASSMNLogoDuring the PASS Summit this past year I had the privilege of hosting a table during the Chapter Leaders meeting on building boards and recruiting leadership for SQL Server User Groups.  First, I have to say, leadership teams/boards come in many flavors, counts, etc.  For instance, some groups are managed by one or two people who are truly passionate about the community and really enjoy leading the groups.  Other groups, such as ours, have regular elections with as few as three and as many as eight on the board.  While I may spend more time later discussing the pros and cons of various approaches, this post is about the peaceful transition within our user group.

First a bit of history, I have worked with the Minnesota SQL Server User Group for a number of years.  We joined PASS quite a while ago but a few years after we started.  After joining we did not change our organizational structure.  We have always had a board of four or more.  We currently have six roles: Chair, Corporate Relations, Programs, Membership and Treasurer, Technology, and SQL Saturday.  We added SQL Saturday last year to have someone focused entirely on getting that program rolling, Paul (@mnDBA) did great!  I have held the Corporate Relations role and this year I was the Chair.

Elections and Role Distribution

Prior to last year, our terms were one year.  However, we rotated the entire board at least once which is very difficult. So, starting last year, we rotate 1/2 of the board each year with terms running twoelection button years.  What is really cool about our election process is that we are able to recruit new board members each year allowing PASSMN to take on some new ideas without issue.   We have also done a good job of mixing up members between consulting and nonconsulting members.  We also try to limit a company’s participation to one board member.  This process has been successful for years as this blend has motivated time changes, content direction and even sponsorships through the years.

Another unique part of our board election is that members are voted to the board, not to a role.  This is strategic as it allows the members to rotate roles as well as work with each other to find a good fit for them within the board.  It also means that the nominees understand that they will work with their fellow members to distribute responsibility.

Transition Lunch

That leads us to today.  We had our transition lunch 12/12/12 at 12:12. (Yes, that was the actual appointment time, and we survived.)  Once again we had a good time as volunteers reflecting on what happened in the past year, allowing the new board to select roles, and handing off insights as we have them.  And thus, it is the beginning of the end for me as a board member.

It’s Been Great!

Personally, it has been and will continue to be great working with such a good group of people.  Thanks to Andy (@SQLQuill)  and Tim (@tjplas) for the time these past couple of years and we welcome Shirley (@SQLLatina), Will (@williamweber), and Mike (@SQLMD) to the board along with returning members Paul, Josh (@SQLJosh) and Bill (@billpreachuk).  I plan to continue working with the board and helping with some of their new initiatives this next year.  Let’s keep the tradition of quality and fun going.

SQL Saturday #149 and CodeMastery–Minnesota Events

sqlsat149_webWe are less than two weeks away from SQL Saturday #149 in Minneapolis on September 29, 2012 with two preconference sessions on September 28.  In case you haven’t heard, we are having the main event on a Saturday.  Yes, the precons are on Friday this year.  Check out the details here.  I am really excited about this event as we have a great group of local, regional, and national speakers at this event.  There are nine rooms being used for this event, so go out to the site and build your schedule.

cm-logoThe following Tuesday, Magenic is hosting CodeMastery with a BI track at the Microsoft Technology Center in Edina, MN.  This event includes a sessions on managing the BI stack in SharePoint and xVelocity.  The other track is Windows 8 development with sessions on WinRT and Game Development.

I’m a Speaker at Both Events

Besides plugging these two awesome events on their own, I am also a speaker for both events.  Here is what I will be speaking on at each event:

SQL Saturday #149: A Window into Your Data: Using SQL Window Functions

In this session, I will walk through the window functions enabled by the OVER clause in SQL Server.  Come join me as we celebrate the SQL Server 2012 release of analytic functions and expansion of aggregate functionality to support tasks such as running totals and previous row values.  Thankfully, this is a demo heavy session as it is one of the last sessions of the day.

CodeMastery: Data Mining with the Tools You Already Have

The next week, I will be presenting on data mining tools which Microsoft has made available to us in SSAS and Excel.  The goal of this session is to help developers understand how to implement data mining algorithms into their business intelligence solutions.

I look forward to seeing you at both events.  They are priced right, FREE!