News & Opinion
New SQL Server Management Studio 17.5: It’s Classified
SQL Server Management Studio 17.5 is out, and new in this release is a SQL Data Discovery & Classification feature. The idea is that it’ll scan your database, identify columns containing potentially sensitive data, and help you become compliant with regulations like PCI, HIPAA, and GDPR.
Let’s see how it works on the Stack Overflow public data dump.
Start by opening up SSMS, and then right-click the database, Tasks, Classify Data:
Within moments, your compliance needs are…hang on, that can’t be right:
I’ve tried it on 3 VMs now, even tried with AdventureWorks, and no dice. My guess – and this is just a guess – is that maybe it has to do with the upgrade process not working, even though I even tried a full install as opposed to an upgrade. We’ll probably get another build in the next few days.
Compliance. Sure is hard.
Update: looks like nobody tested it on case-sensitive database servers:
C’mon, guys….
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
Works for me.
Works for me as well, Brent. Obviously this is a PICNIC issue 😉
As I noted in the post – are you guys running case-sensitive server collations like SQL_Latin1_General_CP1_CS_AS?
17.4 AG latency reporting had issues with non standard collation that was promised to be fixed in 17.5. Sounds like it still doesn’t work or maybe just some pieces
We run some Latin1_General_BIN (systems migrated based on 6.5 and 7.0 times) and the AG latency report now runs with 17.5.
I spoke TOO SOON. It creates the job but then the job fails because it has USE TEMPDB (note the capitals). For a case sensitive collation this will fail. One step forward. I changed the code in the jobs steps and it will work.
Our server collation is case insensitive, but out user databases are all case sensitive. I am able to run the Classify Data task just fine.
Server collation ‘SQL_Latin1_General_CP1_CI_AS’
User database collation ‘Latin1_General_CS_AS’
I know it’s not the same case as you posted, as your server is CS. Just wanted to provide some additional data because I’m using CS databases.
Update: The initial data classification screen comes up with 19 recommendations for me, but I get the same ‘Invalid object name: information_schema.columns’ error when I click on the ‘View Report’ button.
Makes sense. My guess is that the SSMS testing environments at Microsoft aren’t using case-sensitive databases. I’m also guessing they’ll change that now. 🙂
My… classification algo brings us close to GDPR,
and they’re like “You’re so compliant!”
“Damn right! I’m so compliant!
I would teach you, but I’d have to get it working on my case-sensitive DBs first”
That song is the gift that keeps on giving – laughter. I love that.
Solved (sensitive bug and reports bug) for me with the last version of SQL SERVER Management Studio 17.6:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017
Looking for a way to change the SENSITIVITY Label to a few that we have in government. Like HIPAA, CJIS and fire operations. Any suggestion?
Joe – your best bet would be to post that on a Q&A site like https://dba.stackexchange.com.