{"id":39456,"date":"2018-12-06T10:39:03","date_gmt":"2018-12-06T10:39:03","guid":{"rendered":"https:\/\/cold-voice-b72a.comc.workers.dev:443\/https\/www.sqlshack.com\/?p=39456"},"modified":"2019-12-03T15:27:03","modified_gmt":"2019-12-03T15:27:03","slug":"static-data-masking-in-ssms-18","status":"publish","type":"post","link":"https:\/\/cold-voice-b72a.comc.workers.dev:443\/https\/www.sqlshack.com\/static-data-masking-in-ssms-18\/","title":{"rendered":"Static Data Masking in SSMS 18"},"content":{"rendered":"

\n In this article we are going to talk about Static Data Masking, a new feature for the SQL Databases in SSMS. Static Data Masking feature previously was available only for the Azure SQL DB.\n<\/p>\n

<\/p>\n

\n Database administrators are responsible for database security and compliance issues. In a normal workflow, we used to have multiple environments for applications such as Production, Staging, UAT, Dev, and Sandbox. It is a regular task for the DBA to refresh the lower (staging, test, UAT etc.) environments with a backup of the production database backup. Sometimes we need to share the database backup with the external vendors as well. The database may contain critical information such as Personally Identifiable Information (PII data) such as email address, contact information, national id number etc. We might have other sensitive information such as bank account number, credit card number, CVV etc. which we do not want anyone to access. We need to be compliant with GDPR, PCI, and SOX regulatory compliance as well.\n<\/p>\n

\n If we restore the database to lower environment, all of the data gets transferred to the lower environment which put our critical data at a risk since we do not have security or encryption enabled at that level. It may be a requirement to mask or shuffle the data prior to a database restore.\n<\/p>\n

\n Consider the below example, where our production database has sensitive credit card information and we have performed a database restore to dev, UAT and sandbox environment.\n<\/p>\n

\n

\n You can see here that data is transferred to all environments, which makes our data accessible to a large audience and at risk too.\n<\/p>\n

\n In SQL Server 2016, we come across a new solution \u2018Dynamic Data Masking\u2019 to prevent unauthorized users to access the defined sensitive information. We need to define curtails rules to mask the data. When any user request for the data, SQL Server checks his access and if he is not having necessary permission, he gets masked data. In this process, there are no changes in the source data. But this does not satisfy our case described above.\n<\/p>\n

\n

\n SSMS 18.0 contains the Static Data Masking feature for the SQL Databases. Previously it was available for the Azure SQL DB only. Static data masking creates a copy of the database and applies the data transformation rules on it. We can backup this masked database to lower environments so that sensitive data is not transferred to the other end. We can use this features for the databases since SQL Server 2012.\n<\/p>\n

\n

\n In the above diagram, we can see that there is no sensitive data information is transferred to the other environments using the static data masking.\n<\/p>\n

\n We can use the Static Data Masking for the various purpose as below\n<\/p>\n