That’s right. It’s changed in SQL Server 2019 and for the better! Older versions of SQL Server stored classification meta data in Extended Properties which is not ideal. Starting with SQL Server 2019 a new system table was added: sys.sensitivity_classifications.

There are two main reasons why this is much better than the old way. First, it’s much easier to query. Second, it not has its own permissions.

Securing the data classifications can be done by locking down the “VIEW ANY SENSITIVITY CLASSIFICATION” permission. You can grant this permission to any data dictionary tools you may have easily as well.

Adding classifications can be done via SSMS or by using “ADD SENSITIVITY CLASSIFICATION”.

Viewing existing classifications can also be done with SSMS or with the following query.

SELECT
     s.name AS schema_name,
     t.name AS table_name,
     c.name AS column_name,
     Label AS sensitivity_label_name,
     Label_ID AS sensitivity_label_id,
     Information_Type AS information_type_name,
     Information_Type_ID AS information_type_id
FROM
     sys.sensitivity_classifications
     JOIN sys.tables t ON sys.sensitivity_classifications.major_id = t.object_id  AND t.temporal_type <> 1
     JOIN sys.schemas s ON t.schema_id = s.schema_id
     JOIN sys.columns c ON sys.sensitivity_classifications.major_id = c.object_id
         AND sys.sensitivity_classifications.minor_id = c.column_id

Enjoy and happy classifying!

It always seems that when I give a talk on performance there are 100+ people packed in the room but when it comes to security there’s 10 to 15 people. No one likes patches, unless you’re talking about a puppy named patches or something that’s not related to updates; yet, it’s so important that we all do them.

Hopefully, this new feature will help those that are interested and those that are not so interested in security.

To run the report simply right click a database; choose Tasks; choose Vulnerability Assessment; and finally click Scan for Vulnerabilities…

Once clicked the scan dialog will open and there’s not really much to do except click “OK”.

From here the Assessment Results will be shown. There’s not hundreds of data points but it’s a great start with 52 checks.

The really amazing thing about this is that it even provides a script that you can run to “fix” your issues. Though it should be noted that you may actually “need” the code it’s fixing. Before running these scripts you’d want to discuss with your team and remediate any potential concerns before you break something.

GDPR is the European law that is intended to empower individuals with the ability to control the surface area of their individual data. GDPR is short for General Data Protection Regulation. This law carries heavy penalties for non-compliance.

Classification could be the first step in understanding your data. Think of this step as placing your data into buckets that are defined by various levels, such as financial or health care.If you don’t classify your data how do you know what’s sensitive and what’s not?

The latest version of SQL Server Management Studio (SSMS) includes tools for data classification. This tool is used to help you identify and classify the column for all types of scenarios; not just GDPR.

It’s very easy to run and use. From Object Explorer, Right-Click a database and choose Tasks. Next click Classify Data…

I once got a call from “Microsoft Tech Support” stating that my machine was infected and he wanted to access it to help me clean up the problem.

This is the same exact scam that my father in law fell for a few years back. I entertained the scammer for a bit to waste his time trying to keep him on as long as possible without actually doing anything he wanted and granting absolutely NO access to my machine.

As he grew impatient with me talking about apples or whatever else came to mind I informed him that I work at Microsoft and he then told me “%&# You! %&# You! %&# You!” and hung up.

Sadly this is not the same fun experience my father in law had. He instead had the fun of changing bank account information and passwords, Social Security passwords, account information for all his online trading, and I’m sure much, much more.

Today he fell victim again to the “Microsoft System Security Alert” pop-up scam. Each time he allowed the scammer to access his machine and take or plant who knows what.

I am truly astonished that this smart man would fall victim not once but twice to these social engineering scams.

A line in the sand

I’d like to take a stand and help our non-technical community understand how to avoid these scams. Writing here isn’t going to reach them and that isn’t what this post is about.

Instead; I’m planning on doing several free educational sessions with PTA groups, retirement homes, and other groups in my community to help our tech Muggles know which way is up and down when it comes to these sorts of things.

I’m posting here to reach out to each of you; the experts in communities around the world to do the same. It doesn’t take much effort and I promise you’ll feel rewarded for helping.

Call to action

I ask that each of you reach out to a local group and offer 30 minutes of your time to help educate the community on how to avoid scams like these. Even if it’s just a quick chat with your extended family it will be worth it. We can make a difference if we all do this together!

I plan on posting a simple slide deck on my page and will link back to it at some point this week.

Please feel free to reach out to me with any comments or questions.

Thank you!

Daniel

I’ve spent the last couple years writing, improving, and using this tool at my customers. This health check now has more than 300 data points and even pulls out bad practice and concerns from your procs. As of today, I gift it to the community. #SQLFAMILY

Where to find the tool:

Navigate to www.SQLDATAPROS.COM and click the download free button. Note that there is no registration. If you’d like to send me your email, please feel free. You can also follow me on Twitter at @SQLSME or find me on LinkedIn. A link to my profile is on my blog at www.SQLTECHBLOG.com.

This will take you to my dropbox where you will need to click the download button.

If you don’t have a dropbox account, you can click the “No thanks, continue” button on the bottom to bypass signup.

That’s it! It’s that simple. The only other thing you need to worry about is having .NET 4.5 or higher and possibly the ReportViewer 2015 files. There is no install for this. If you don’t want it anymore, simply delete the files.

You can also get the ReportViewer (2015) and SQLCLRTypes prerequisites from the application zip file on DropBox. This should make finding the items easier since they will be in the zip in a prereq folder. Be sure to install them to allow the tool to run.

How do I use this thing?

Unzip the files to any directory you like. After that, simply open the FitDB application.

Once the application is open, type an instance name into the instance box and then click Test Connection.

Once the connection is successful, the databases list will be populated. Deselect any databases you want to ignore.

Next right click a category on the right and click “Collect Data”.

After you’ve collected data, click the “Reporting” button to view any issues that were found.

If you get an error about “Microsoft.ReportViewer.Common” then you don’t have the Report Viewer 2012 installed. Be sure to grab it from the link referenced above.

Drill into the bar chart or click “View All Data” to get a detailed explanation of each issue.

Please be kind and remember this is being released for the first time and is a BETA.

I wish everyone in the #SQLFamily a happy 4th!

Corrections:

• Fixed a crash on launch from non-USA date formatting
• Corrected blog post link to ReportViewer.2015 from RV 2012. The application uses 2015, not 2012.

I’ve been to hundreds of clients across many verticals and it never ceases to amaze me when I see SQL Server logins that have either a blank password or one that matches the name. What’s even more worrisome is that 60% of these logins were a member of the sysadmin builtin role and 90%+ were granted the DBO role for their database.

A while back I posted some code to the Microsoft TechNet Gallery that can help you identify these logins.

Check it out here: https://gallery.technet.microsoft.com/Identify-SQL-Server-33925ae3

If you like it or find it helpful, be sure to give the gallery a 5 star rating.  : )

A couple considerations are:

• SQL Server passwords are case sensitive.
• The script compares the same case as the login
• The script compares the password to ‘’ (blank)
• Common passwords are not compared but could easily be with a quick tweak. You could also use the brute tool against a suspect login but it’d be better to take the library from it and add it to the query.

It’s easy enough to use an UPPER or a LOWER to the query and just about as easy to add in a list of common passwords. Leave some comments and let me know if you’d like to see a modified version that does a bit more.