Share via

Using a cutom policy file for Get-SqlSensitivityRecommendations

Jeremie Bedard 20 Reputation points
2025-10-03T14:56:21.73+00:00

I'm trying to import a custom Information Protection Policy file into SSMS to add more patterns, mainly because I have a French audience, so most of the default terms don’t apply in my case. Since the interface works only for one database at a time and you cannot see the content of a column to distinguish empty cells from filled ones—or to confirm the content of a row without manually opening the designated table—I decided to use the cmdlet Get-SqlSensitivityRecommendations.

With a little scripting, I managed to achieve the following: retrieve all columns that match my patterns, check if they contain data, and even take a sample of the content to visually verify it.

The problem started when I wanted to change the policy file. I updated it in SSMS, and it works for filtering in the Task menu → Classify Data, but the cmdlet still uses the default policy file. I just can’t figure out where or how to change this.

Thanks!

SQL Server | SQL Server Transact-SQL
0 comments
Answer accepted by question author
  1. Lakshmi Narayana Garikapati 470 Reputation points Microsoft External Staff Moderator
    2025-10-06T09:00:57.27+00:00

    HI @jeremie bedard ,

    Thank you for reaching out to SQL forum

    SQL Server Management Studio (SSMS) lets you import a custom policy file through its user interface, which is why your French-language patterns appear correctly when using the “Classify Data” task. However, the PowerShell cmdlet Get-SqlSensitivityRecommendations is hardwired to use the default policy defined in Microsoft Purview (formerly Azure Information Protection) and doesn’t recognize or reference the custom policy file you imported into SSMS. It also doesn’t offer any parameter to override this behavior, which means your custom rules won’t be applied when using the cmdlet.

    To work around the limitations of Get-SqlSensitivityRecommendations, you can start by using SSMS for initial classification import your custom policy file, classify the data manually, and then export the results to a .csv or .json file for further processing. From there, you can use PowerShell to automate validation or apply additional logic. Since you've already scripted column sampling and pattern matching, another option is to build your own classification logic directly in PowerShell by defining regex-based rules that reflect your custom policy, scanning columns manually with Invoke-Sqlcmd, and tagging matches using Set-SqlSensitivityClassification. If your organization uses Microsoft Purview, you can define custom sensitivity labels and classification rules centrally, which will be respected by both SSMS and PowerShell though this approach requires Azure integration and admin privileges.

    https://free.blessedness.top/en-us/powershell/module/sqlserver/get-sqlsensitivityrecommendations?view=sqlserver-ps

    https://free.blessedness.top/en-us/sql/relational-databases/security/sql-data-discovery-and-classification?view=sql-server-ver17&tabs=t-sql

    If you have any questions or need further clarification, please don’t hesitate to reach out.

    We appreciate your collaboration and support!

    Best regards,

    Lakshmi.

    2 people found this answer helpful.
    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.