SQL Server: can dynamic data masking be safe when providing a database backup?

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
7
down vote

favorite
2












I have a SQL Server 2016 SP1 with data that I am ready to share with a partner (developer), except for a few columns, example:



email varchar(64) MASKED WITH (FUNCTION = 'email()') NULL


I give the partner the credentials for logging in to the test copy of the database as a certain user, without unmask permission. I understand this is reasonably safe.
Now, we are considering sharing the database backup with our partners, so they can reload in their own server and environment.



I would probably need to have contained users, so that partner cannot create new users or connect them to existing logins (not sure about this).



CREATE USER user_name WITH PASSWORD = 'strong_password';


Is there a way that I can share a database backup, and still trust that the masked columns are safe, or do I have to physically delete the data?










share|improve this question



























    up vote
    7
    down vote

    favorite
    2












    I have a SQL Server 2016 SP1 with data that I am ready to share with a partner (developer), except for a few columns, example:



    email varchar(64) MASKED WITH (FUNCTION = 'email()') NULL


    I give the partner the credentials for logging in to the test copy of the database as a certain user, without unmask permission. I understand this is reasonably safe.
    Now, we are considering sharing the database backup with our partners, so they can reload in their own server and environment.



    I would probably need to have contained users, so that partner cannot create new users or connect them to existing logins (not sure about this).



    CREATE USER user_name WITH PASSWORD = 'strong_password';


    Is there a way that I can share a database backup, and still trust that the masked columns are safe, or do I have to physically delete the data?










    share|improve this question























      up vote
      7
      down vote

      favorite
      2









      up vote
      7
      down vote

      favorite
      2






      2





      I have a SQL Server 2016 SP1 with data that I am ready to share with a partner (developer), except for a few columns, example:



      email varchar(64) MASKED WITH (FUNCTION = 'email()') NULL


      I give the partner the credentials for logging in to the test copy of the database as a certain user, without unmask permission. I understand this is reasonably safe.
      Now, we are considering sharing the database backup with our partners, so they can reload in their own server and environment.



      I would probably need to have contained users, so that partner cannot create new users or connect them to existing logins (not sure about this).



      CREATE USER user_name WITH PASSWORD = 'strong_password';


      Is there a way that I can share a database backup, and still trust that the masked columns are safe, or do I have to physically delete the data?










      share|improve this question













      I have a SQL Server 2016 SP1 with data that I am ready to share with a partner (developer), except for a few columns, example:



      email varchar(64) MASKED WITH (FUNCTION = 'email()') NULL


      I give the partner the credentials for logging in to the test copy of the database as a certain user, without unmask permission. I understand this is reasonably safe.
      Now, we are considering sharing the database backup with our partners, so they can reload in their own server and environment.



      I would probably need to have contained users, so that partner cannot create new users or connect them to existing logins (not sure about this).



      CREATE USER user_name WITH PASSWORD = 'strong_password';


      Is there a way that I can share a database backup, and still trust that the masked columns are safe, or do I have to physically delete the data?







      sql-server backup sql-server-2016 dynamic-data-masking






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Aug 17 at 10:39









      carlo.borreo

      7054921




      7054921




















          2 Answers
          2






          active

          oldest

          votes

















          up vote
          11
          down vote



          accepted











          I would probably need to have contained users, so that partner cannot create new users or connect them to existing logins (not sure about this).




          If you're sharing the backup, contained database or not, they can do whatever they want with the data. Sysadmins (of which I'm sure they have access to on their servers) can see all of the data.




          Is there a way that I can share a database backup, and still trust that the masked columns are safe, or do I have to physically delete the data?




          Delete the data, then cycle through the transaction log multiple times taking backups and re-using VLFs by inserting dummy data (that way they can't mine the log) or better yet, create a schema only copy of the database and insert only the data needed. Note that you can still mine information from statistics, I wouldn't script those if I were making a copy to send and wanted it to be secure.



          Another option would be to restore the backup to a staging area and encrypt the data in sensitive columns using some form of encryption (column, AE, etc.). Drop the requisite keys before sending the database. This way they won't have the keys to decrypt the data, but it's still there. This may be acceptable if you didn't want to do any of the other above options.






          share|improve this answer



























            up vote
            13
            down vote













            No, I'm sorry. Your partner has a sysadmin login for their SQL Server, and a sysadmin can do everything with everything inside a SQL Server instance - including the databases.



            I suggest that you develop a routine to clear out the sensitive data. I.e., perform the backup. Restore it to a different database name, clean it. And now produce a new backup.






            share|improve this answer




















              Your Answer







              StackExchange.ready(function()
              var channelOptions =
              tags: "".split(" "),
              id: "182"
              ;
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function()
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled)
              StackExchange.using("snippets", function()
              createEditor();
              );

              else
              createEditor();

              );

              function createEditor()
              StackExchange.prepareEditor(
              heartbeatType: 'answer',
              convertImagesToLinks: false,
              noModals: false,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: null,
              bindNavPrevention: true,
              postfix: "",
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              );



              );













               

              draft saved


              draft discarded


















              StackExchange.ready(
              function ()
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f215172%2fsql-server-can-dynamic-data-masking-be-safe-when-providing-a-database-backup%23new-answer', 'question_page');

              );

              Post as a guest






























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes








              up vote
              11
              down vote



              accepted











              I would probably need to have contained users, so that partner cannot create new users or connect them to existing logins (not sure about this).




              If you're sharing the backup, contained database or not, they can do whatever they want with the data. Sysadmins (of which I'm sure they have access to on their servers) can see all of the data.




              Is there a way that I can share a database backup, and still trust that the masked columns are safe, or do I have to physically delete the data?




              Delete the data, then cycle through the transaction log multiple times taking backups and re-using VLFs by inserting dummy data (that way they can't mine the log) or better yet, create a schema only copy of the database and insert only the data needed. Note that you can still mine information from statistics, I wouldn't script those if I were making a copy to send and wanted it to be secure.



              Another option would be to restore the backup to a staging area and encrypt the data in sensitive columns using some form of encryption (column, AE, etc.). Drop the requisite keys before sending the database. This way they won't have the keys to decrypt the data, but it's still there. This may be acceptable if you didn't want to do any of the other above options.






              share|improve this answer
























                up vote
                11
                down vote



                accepted











                I would probably need to have contained users, so that partner cannot create new users or connect them to existing logins (not sure about this).




                If you're sharing the backup, contained database or not, they can do whatever they want with the data. Sysadmins (of which I'm sure they have access to on their servers) can see all of the data.




                Is there a way that I can share a database backup, and still trust that the masked columns are safe, or do I have to physically delete the data?




                Delete the data, then cycle through the transaction log multiple times taking backups and re-using VLFs by inserting dummy data (that way they can't mine the log) or better yet, create a schema only copy of the database and insert only the data needed. Note that you can still mine information from statistics, I wouldn't script those if I were making a copy to send and wanted it to be secure.



                Another option would be to restore the backup to a staging area and encrypt the data in sensitive columns using some form of encryption (column, AE, etc.). Drop the requisite keys before sending the database. This way they won't have the keys to decrypt the data, but it's still there. This may be acceptable if you didn't want to do any of the other above options.






                share|improve this answer






















                  up vote
                  11
                  down vote



                  accepted







                  up vote
                  11
                  down vote



                  accepted







                  I would probably need to have contained users, so that partner cannot create new users or connect them to existing logins (not sure about this).




                  If you're sharing the backup, contained database or not, they can do whatever they want with the data. Sysadmins (of which I'm sure they have access to on their servers) can see all of the data.




                  Is there a way that I can share a database backup, and still trust that the masked columns are safe, or do I have to physically delete the data?




                  Delete the data, then cycle through the transaction log multiple times taking backups and re-using VLFs by inserting dummy data (that way they can't mine the log) or better yet, create a schema only copy of the database and insert only the data needed. Note that you can still mine information from statistics, I wouldn't script those if I were making a copy to send and wanted it to be secure.



                  Another option would be to restore the backup to a staging area and encrypt the data in sensitive columns using some form of encryption (column, AE, etc.). Drop the requisite keys before sending the database. This way they won't have the keys to decrypt the data, but it's still there. This may be acceptable if you didn't want to do any of the other above options.






                  share|improve this answer













                  I would probably need to have contained users, so that partner cannot create new users or connect them to existing logins (not sure about this).




                  If you're sharing the backup, contained database or not, they can do whatever they want with the data. Sysadmins (of which I'm sure they have access to on their servers) can see all of the data.




                  Is there a way that I can share a database backup, and still trust that the masked columns are safe, or do I have to physically delete the data?




                  Delete the data, then cycle through the transaction log multiple times taking backups and re-using VLFs by inserting dummy data (that way they can't mine the log) or better yet, create a schema only copy of the database and insert only the data needed. Note that you can still mine information from statistics, I wouldn't script those if I were making a copy to send and wanted it to be secure.



                  Another option would be to restore the backup to a staging area and encrypt the data in sensitive columns using some form of encryption (column, AE, etc.). Drop the requisite keys before sending the database. This way they won't have the keys to decrypt the data, but it's still there. This may be acceptable if you didn't want to do any of the other above options.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Aug 17 at 10:46









                  Sean Gallardy

                  13k11942




                  13k11942






















                      up vote
                      13
                      down vote













                      No, I'm sorry. Your partner has a sysadmin login for their SQL Server, and a sysadmin can do everything with everything inside a SQL Server instance - including the databases.



                      I suggest that you develop a routine to clear out the sensitive data. I.e., perform the backup. Restore it to a different database name, clean it. And now produce a new backup.






                      share|improve this answer
























                        up vote
                        13
                        down vote













                        No, I'm sorry. Your partner has a sysadmin login for their SQL Server, and a sysadmin can do everything with everything inside a SQL Server instance - including the databases.



                        I suggest that you develop a routine to clear out the sensitive data. I.e., perform the backup. Restore it to a different database name, clean it. And now produce a new backup.






                        share|improve this answer






















                          up vote
                          13
                          down vote










                          up vote
                          13
                          down vote









                          No, I'm sorry. Your partner has a sysadmin login for their SQL Server, and a sysadmin can do everything with everything inside a SQL Server instance - including the databases.



                          I suggest that you develop a routine to clear out the sensitive data. I.e., perform the backup. Restore it to a different database name, clean it. And now produce a new backup.






                          share|improve this answer












                          No, I'm sorry. Your partner has a sysadmin login for their SQL Server, and a sysadmin can do everything with everything inside a SQL Server instance - including the databases.



                          I suggest that you develop a routine to clear out the sensitive data. I.e., perform the backup. Restore it to a different database name, clean it. And now produce a new backup.







                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Aug 17 at 10:47









                          Tibor Karaszi

                          9115




                          9115



























                               

                              draft saved


                              draft discarded















































                               


                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function ()
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f215172%2fsql-server-can-dynamic-data-masking-be-safe-when-providing-a-database-backup%23new-answer', 'question_page');

                              );

                              Post as a guest













































































                              Popular posts from this blog

                              How to check contact read email or not when send email to Individual?

                              Displaying single band from multi-band raster using QGIS

                              How many registers does an x86_64 CPU actually have?