Search code examples
azureazure-sql-databaseazure-sql-server

Azure SQL: exporting database as non-admin with dynamic data masking

I've tried to implement sensitive data masking with dynamic data masking from Azure Portal. Inspiration came from this article: https://joeydantoni.com/2016/11/07/exporting-masked-data-with-dynamic-data-masking

Essentially I've created user with limited privileges, defined some masks and tried to export database (both from Azure Portal and SSMS). When no masks are defined, export with my non-admin user goes as expected. However, when I've added at least one mask - export fails with message:

Could not export schema and data from database. One or more errors occurred. One or more errors occurred. One or more errors occurred. One or more errors occurred. One or more errors occurred. Invalid value.

Is it a bug in Azure SQL? I'm out of ideas.

EDIT: From SSMS "Export Data-tier Application" summary I've found out that the problem occurred on few (4-5 out of 60+ tables) - yet mask was applied only on one of them (one column to be precise).

Solution

  • I've contacted Azure support to resolve this issue.

    This problem affects only Azure SQL databases. It seems that exporting masked data require some permissions that only admin have. So, following are true for non-admin:

    1. He can export database without masking enabled.
    2. He cannot export database with masking enabled. When granted with UNMASK grant he can export masked database but exported data is unmasked.

    That's a shame.