locked
Secure Sensitive Data in Sql server 2012. Design a new database Security to restrict access to Customer Information. RRS feed

  • Question

  • Hi All,

    I am working on a Project where I have to secure customers data exist in 4-5 tables in SQL Server 2012 Database. 

    1st I thought to apply Dynamic Data Masking (DDM) feature but unfortunately it [dynamic data masking (DDM)] was introduced on SQL Server 2016 and we have 2012 so can't go with this option.

    1st I am planning to review and clean up existing access and then can move customer data to another schema called CUSTOMER and restrict access to this schema. 

    My steps:

    1. Identify Orphan accounts and accounts those are not in use for last 12 months. [I need query to get this result set]. Once such user accounts are identified, I want to remove them from Server.
    2. Identify Windows account and SQL Server Account. I assume there should be only few SQL Server accounts. If there are many, clean up is required here. [Need Query to get this data set]
    3. I see lot of individual users are directly added to server login. I think these users should be part of some AD group and grant access and right role to such groups and avoid adding users directly to server login. [Need query to Identify this result set]
    4. Move Customer tables views to NEW Schema CUSTOMER.
    5. Next work on the groups those should have access to Customer data and grant them access to CUSTOMER Schema and deny access to rest of groups.

    Guru, Am I doing right thing? 

    Would love to hear other available options.

    Thank you!


    Thanks Shiven:) If Answer is Helpful, Please Vote



    Friday, January 24, 2020 10:22 PM

Answers

  • I have devoted  few weeks to explore options to Secure Sensitive data and these are my finds.  Please feel free add if I have missed anything or stated something wrong here.

    Assume, You have a table which contains Customer Sensitive information like Email, Phone Number etc and you don’t want to give access to all users to these info.

    https://www.red-gate.com/simple-talk/sysadmin/data-protection-and-privacy/introduction-to-sql-server-security-part-1/

    https://benjii.me/2010/05/how-to-use-sql-server-encryption-with-symmetric-keys/

    Different ways to Securing Sensitive data into SQL Server:

    1. Schema-based security OR Database User Securable (Schema): Create a Separate Schema like SENSITIVE and move database objects you want to secure like Tables, Views, SPs, Function etc to this schema. Restrict access to this schema. To do so go to DatabaseàSecurityàSchemasàSelect Schema “Sensitive”à Double Click à Permissions à Search for Users or Roles à Type your User or AD Groups àSelect them à Permissions and check Deny for Select. This way you can allow/deny access to objects under this Shema to Users and Groups. https://www.red-gate.com/simple-talk/sql/sql-training/schema-based-access-control-for-sql-server-databases/
    2.  Column level Security OR Database User Securable (Columns): You can restrict access to column(s) in a table to users or groups. For Example, a table has 10 column and out of 10, 2 columns are Email and Phone Number and you don’t want every one to have access to these 2 columns. Simply hide these 2 columns for users or groups. To do so go to DatabaseàSecurityàUsersàSelect User or Groupà Double Click à Securables à Click on Searchà Specific Objects àObject Types à TablesàType your table nameàCheck NamesàSelect your TablesàUnder Securables, Select your TableàClick on Column PermissionsàTick the Deny option for column you want to hide to user or group. This way you can allow/deny access to Columns under a table to Users and Groups. https://docs.microsoft.com/en-us/azure/sql-data-warehouse/column-level-security. I noticed that If user login to SQL Server DB using SSMS and Expand the columns for a table, user will able to see all column names but if try running a Select Query on Hidden column or * , then SQL will throw an error.
    3. Data encryption at Column level: Database encryption can generally be defined as a process that uses an algorithm to transform data stored in a database into "cipher text" that is incomprehensible without first being decrypted. In order to explain column-level encryption it is important to outline basic database structure. A typical relational database is divided into tables that are divided into columns that each have rows of data. Whilst TDE (Transparent/External database encryption) usually encrypts an entire database, column-level encryption allows for individual columns within a database to be encrypted. You need to create below 3 to encrypt your column data:
    • Master key in your database
    • Certificate in your Database
    • Symmetric Key
    1. Transparent Data Encryption (TDE) in SQL Server: Transparent Data Encryption (TDE) was introduced in SQL Server 2008. Its main purpose was to protect data by encrypting the physical files, both the data (mdf) and log (ldf) files (as opposed to the actual data stored within the database). Transparent Data Encryption Encrypts SQL Server, Azure SQL Databases, and Azure SQL Data Warehouse data files. https://www.sqlshack.com/how-to-configure-transparent-data-encryption-tde-in-sql-server/
    2. Dynamic Data Masking in SQL Server 2016: Dynamic Data Masking masks the sensitive data “on the fly” to protect sensitive data from non-privileged users using built-in or customized masking functions, without preventing them from retrieving the unmasked data. https://www.sqlshack.com/using-dynamic-data-masking-in-sql-server-2016-to-protect-sensitive-data/  OR https://www.red-gate.com/simple-talk/sql/sql-development/encrypting-sql-server-dynamic-data-masking/
    3. SQL Server Static Data Masking: SQL Server 2019 with SSMS 18.0 (preview 5 and higher) introduces a new security feature called Static Data Masking. This technique follows the idea of applying Static Data Masking against a production database and then creating a backup of the database with the mask applied, followed by restoring this masked copy to non-production environments. It is basically a feature that helps users create a masked copy of a SQL database. Once data is statically masked, it is permanently replaced in the cloned database and we can't change it. This feature is used for several purposes like sharing sensitive data, database development, database troubleshooting, analytics and business reporting. https://www.mssqltips.com/sqlservertip/5939/sql-server-static-data-masking-example/

    I have implemented Data encryption at Column level.  https://www.w3computing.com/sqlserver2012/encrypting-data-methods-data-encryption/

    Steps:

    1. Create MASTER KEY, CERTIFICATE and SYMMETRIC KEY. In below query when you create SYMMETRIC KEY, then you should definitely add KEY_SOURCE and IDENTITY_VALUE. This will allow you to create IDENTICAL SYMMETRIC KEY across multiple database by using same phrase. This means, you can encrypt data into 1st database and decrypt it into another database. If you don’t do so then you may not able to Decrypt the data encrypted into another database.  https://sqlity.net/en/2492/identical-symmetric-key/  and https://dba.stackexchange.com/questions/259816/backup-and-restore-of-master-key-and-certificate-are-not-working-on-same-server

    /*CREATE At Source*/

    --DROP SYMMETRIC KEY [Symmetric Key 01]

    --DROP CERTIFICATE [Your Certifiacte Name]

    --DROP MASTER KEY

    /*Create MASTER KEY*/

    CREATE MASTER KEY ENCRYPTION BY  

        PASSWORD = 'Your Password'; 

    /*Create CERTIFICATE*/

    CREATE CERTIFICATE [Your Certifiacte Name]

           WITH SUBJECT = 'This is a Database Certificate to Protect Sensitive Data',

           EXPIRY_DATE = '20251231' /*EXPIRY_DATE is optional and I think EXPIRY_DATE is 1 year from creation so it is better to specify a date here*/

    /*Create SYMMETRIC KEY*/

    CREATE SYMMETRIC KEY [Symmetric Key 01] WITH

        KEY_SOURCE = 'Any Text Phrase ABC',  /*This is to Create IDENTICAL SYMMETRIC KEY*/

        ALGORITHM = AES_256,  

        IDENTITY_VALUE = 'Any Text Phrase XYZ'  /*This is to Create IDENTICAL SYMMETRIC KEY*/

        ENCRYPTION BY CERTIFICATE [Your Certifiacte Name];

    /*Use This Query to Encrypt the Data*/

    OPEN SYMMETRIC KEY [Symmetric Key 01] 

       DECRYPTION BY CERTIFICATE [Your Certifiacte Name]

    SELECT

    ENCRYPTBYKEY(KEY_GUID('Symmetric Key 01'), EmailID) AS EncryptedEMAIL

    FROM Table

    /*Use this Query Decrypt the Data*/

    OPEN SYMMETRIC KEY [Symmetric Key 01] 

       DECRYPTION BY CERTIFICATE [Your Certifiacte Name]

    SELECT

    CONVERT(NVARCHAR, DECRYPTBYKEY([EMAIL])) AS DecryptedEmail

    FROM Table

    1. To Store Encrypted value to a column, you should have column datatype as [Varbinary].
    2. You can’t alter table and alter column to change column data type from any other data type to [Varbinary]. https://stackoverflow.com/questions/14539445/change-column-from-varcharmax-to-varbinarymax
    3. To Encrypt or Decrypt the column data, you need to run this statement 1<sup>st</sup> in that session. OPEN SYMMETRIC KEY [Symmetric Key 01] DECRYPTION BY CERTIFICATE [Your Certifiacte Name]
    4. You must add a new column as Varbinary data into table and then using ENCRYPTBYKEY(KEY_GUID('Symmetric Key 01'), EmailID) you can update the value of this column. For Example: You have a table with a column Email. You have 2 million rows in that table and this tables is SCD type-2 so you don’t want to lose the data so can’t drop and recreate table. You want to apply encryption on Email Column. To do so you will 1<sup>st</sup> create MASTER KEY, CERTIFICATE and SYMMETRIC KEY. Then next you will add a new column Email1 as Varbinary(Max) NULL into your table [ALTER TABLE [dbo].[Table1] ADD [Email1] Varbinary(MAX) NULL]. Then you will update your new column by running this query: OPEN SYMMETRIC KEY [Symmetric Key 01] DECRYPTION BY CERTIFICATE [Your Certifiacte Name] UPDATE TABLE1 SET Email1= ENCRYPTBYKEY(KEY_GUID('Symmetric Key 01'), Email). Once everything updated then you can drop the column Email and then rename Eamil1 to Email using query: EXEC sp_rename 'dbo.Table1.Eamil1', 'Email', 'COLUMN'
    5. Last you don’t want to grant access to SYMMETRIC KEY to everyone. If everyone has access to SYMMETRIC KEY then they can execute this statement OPEN SYMMETRIC KEY [Symmetric Key 01] DECRYPTION BY CERTIFICATE [Your Certifiacte Name] and then they can Decrypt the data. So you are going to deny access to SYMMETRIC KEY to groups and users you don’t want to allow them to see Decrypted data.

    --Grant View

    GRANT VIEW DEFINITION ON CERTIFICATE::[Your Certifiacte Name] TO [User OR Group]

    GRANT VIEW DEFINITION ON SYMMETRIC KEY::[Symmetric Key 01] TO [User OR Group]

     

    --Grant Control

    GRANT CONTROL ON CERTIFICATE::[Your Certifiacte Name] TO [User OR Group]

    --Deny View

    DENY VIEW DEFINITION ON CERTIFICATE::[Your Certifiacte Name] TO [User OR Group]

    DENY VIEW DEFINITION ON SYMMETRIC KEY::[Symmetric Key 01] TO [User OR Group]

    Understanding roles in SQL Server security:

    https://www.techrepublic.com/article/understanding-roles-in-sql-server-security/

    https://www.netwrix.com/sql_server_security_best_practices.html

    https://www.mssqltips.com/sqlservertip/4990/tighten-sql-server-security-with-custom-server-and-database-roles/



    Friday, February 21, 2020 4:15 AM

All replies

  • 1st I thought to apply Dynamic Data Masking (DDM) feature but unfortunately it [dynamic data masking (DDM)] was introduced on SQL Server 2016 introduced and we have 2012 so can't go with this option.

    And Dynamic Data Masking is not much of a securing feature anyway. At least not for users with direct access to run queries. It's more of an aide to the applications.

    1st I am planning to review and clean up existing access and then can move customer data to another schema called CUSTOMER and restrict access to this schema. 

    This means that you need to review all application code and modify it, or else things will break.

    1. Identify Orphan accounts and accounts those are not in use for last 12 months. *[I need query to get this result set]. Once such user accounts are identified, I want to remove them from Server.*

    I don't see what this has to do with the intended schema move. In any case, unless you already have auditing in place which tracks login access, this information is not available. Also, it there are server principals for Windows groups, these can very difficult to determine whether they are in use.

    2. Identify Windows account and SQL Server Account. I assume there should be only few SQL Server accounts. If there are many, clean up is required here.*[Need Query to get this data set]*

    Not sure what query you are looking for, but you find the logins in sys.server_principals.

    5. Next work on the groups those should have access to Customer data and grant them access to CUSTOMER Schema and deny access to rest of groups.

    Be very, very careful with DENY, it can only cause you trouble. A user can be a member of several AD groups, and if any of the groups the user is a member of have been denied access to the schema, the user will not be able to access the data, since DENY takes precedence over GRANT. Since the default is "no access", there are very few situations where you need DENY.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, January 25, 2020 9:53 AM
  • Adding on to Erland's caution about DENY, there is no need for an explict DENY if you are planning to create a new AD group with only authorized users as members and only that group is granted permissions to the CUSTOMER schema or objects within.

    Also, when using schemas as a security boundary, consider creating a separate database principal for the schema owner like below. This extra precaution will ensure objects in the CUSTOMER schema cannot be accessed via ownership chaining from views, procs. etc. contained in other schemas.

    CREATE USER CustomerSchemaOwner WITHOUT LOGIN;
    ALTER AUTHORIZATION ON SCHEMA::CUSTOMER TO CustomerSchemaOwner;
    



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, January 25, 2020 12:53 PM
  • Hi Shivendoo Kumar Dubey,

     

    Are your customer data just a few columns in these tables? Would you please try to use encrypt it with symmetric or asymmetric keys?Please refer to https://www.w3computing.com/sqlserver2012/encrypting-data-methods-data-encryption/ and  https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?redirectedfrom=MSDN&view=sql-server-ver15

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, January 27, 2020 7:41 AM
  • Hi Dedmon,

    Thanks for sharing idea for Column encryption. 

    I have couple of question on this?

    1. We have source System as MS AX and we are pulling data from AX to Staging 
    2. If I encrypt data in AX in select statement which is on SERVER1 and DB1 [So I have created a master key in AX DB, Created a Certificate and Created a SYMMETRIC KEY] and I have Following Select Query in SSIS OLEDB Source SELECT ENCRYPTBYKEY(KEY_GUID('EmailID_Key_01'), [EMAIL]) AS [EMAIL] FROM dbo.MyTable
    3. I am inserting data into a destination table on different server and Database SERVER2, DB2. Here also I have created same  master key, Created a Certificate and Created a SYMMETRIC KEY.
    4. Using SSIS Data is getting Encrypted using AX DB SYMMETRIC KEY and It is loading into different DB on different server and If I try to DECRYPT here on the destination server and DB, It is coming as null. So basically It is not working because Encryption done on Different DB and DECRYPT is happening on Different DB 

    How to Solve this..? Is there anyway can take Source Certificate and using that create certificate in Destination..?


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Friday, February 14, 2020 4:28 AM
  • I get the impression that you create new certificates on each server. That is not going to fly. You need to copy the certificate across the servers (use BACKUP CERTIFICATE + CREATE CERTIFICATE FROM FILE for this).

    I assume that on the source server, you encrypt the symmetric key with the certificate. To be able to decrypt the symmetric key on the target server, you need to have the same certificate.

    If you protect the cert with the database master key, you need to copy that key as well.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, February 14, 2020 10:24 PM
  • I am following these steps but still not able to DECRYPT the data at destination. 

    In Source DB I have created Following: 

    CREATE MASTER KEY ENCRYPTION BY   
    PASSWORD = 'Analytics@2020';  

    CREATE CERTIFICATE Certificate_Customer_Data  
       WITH SUBJECT = 'Customer Sensitive Data'; 

    CREATE SYMMETRIC KEY EmailID_Key_01  
        WITH ALGORITHM = AES_256  
        ENCRYPTION BY CERTIFICATE Certificate_Customer_Data; 

    Then I backup Master Key and Certificate from Source DB to files:

    BACKUP MASTER KEY 
      TO FILE = 'C:\Shivendoo\MasterKey.bak'
      ENCRYPTION BY PASSWORD = 'Analytics@2020';


    BACKUP CERTIFICATE Certificate_Customer_Data TO FILE = 'C:\Shivendoo\DBCertificate.cer'  
        WITH PRIVATE KEY (ENCRYPTION BY PASSWORD = 'Analytics@2020',  FILE = 'C:\Shivendoo\PrivateKey.pvk'); 

    Next I restore Back to Destination DB 

    RESTORE MASTER KEY 
      FROM FILE = 'C:\Shivendoo\MasterKey.bak'
      DECRYPTION BY PASSWORD = 'Analytics@2020'
      ENCRYPTION BY PASSWORD = 'Analytics@2020';

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Analytics@2020';

    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

    CLOSE MASTER KEY;


    CREATE CERTIFICATE Certificate_Customer_Data FROM FILE = 'C:\Shivendoo\DBCertificate.cer'
      WITH PRIVATE KEY(FILE='C:\Shivendoo\PrivateKey.pvk',
                       DECRYPTION BY PASSWORD='Analytics@2020')



    CREATE SYMMETRIC KEY EmailID_Key_01  
        WITH ALGORITHM = AES_256  
        ENCRYPTION BY CERTIFICATE Certificate_Customer_Data;

    Finally I loaded data from Source using this query:

    OPEN SYMMETRIC KEY EmailID_Key_01  
       DECRYPTION BY CERTIFICATE Certificate_Customer_Data

    SELECT ENCRYPTBYKEY(KEY_GUID('EmailID_Key_01'), [EMAIL]) AS [EMAIL]
    FROM [dbo].[Source_Table]

    Data loaded with encryption to Destination but when I try to Decrypt at destination using below query, It returns as NULL value for column DecryptedEmail 

    OPEN SYMMETRIC KEY EmailID_Key_01  
       DECRYPTION BY CERTIFICATE Certificate_Customer_Data

    SELECT CONVERT(NVARCHAR, DECRYPTBYKEY([EMAIL])) AS DecryptedEmail,[EMAIL],* FROM [dbo].[Destination_Table]

    Please can you help..?


    Thanks Shiven:) If Answer is Helpful, Please Vote


    Monday, February 17, 2020 12:17 AM
  • I did some googling, and I found
    https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-identical-symmetric-keys-on-two-servers?view=sql-server-ver15

    It seems that you need to use the KEY_SOURCE and IDENTITY_PHRASE to generate the same key. On the other hand, the certificate can be different. Which makes sense, since they only encrypts the keys. But the symmetric keys must be the same in both ends, since they are the ones that are doing real work.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, February 17, 2020 10:43 PM
  • I have devoted  few weeks to explore options to Secure Sensitive data and these are my finds.  Please feel free add if I have missed anything or stated something wrong here.

    Assume, You have a table which contains Customer Sensitive information like Email, Phone Number etc and you don’t want to give access to all users to these info.

    https://www.red-gate.com/simple-talk/sysadmin/data-protection-and-privacy/introduction-to-sql-server-security-part-1/

    https://benjii.me/2010/05/how-to-use-sql-server-encryption-with-symmetric-keys/

    Different ways to Securing Sensitive data into SQL Server:

    1. Schema-based security OR Database User Securable (Schema): Create a Separate Schema like SENSITIVE and move database objects you want to secure like Tables, Views, SPs, Function etc to this schema. Restrict access to this schema. To do so go to DatabaseàSecurityàSchemasàSelect Schema “Sensitive”à Double Click à Permissions à Search for Users or Roles à Type your User or AD Groups àSelect them à Permissions and check Deny for Select. This way you can allow/deny access to objects under this Shema to Users and Groups. https://www.red-gate.com/simple-talk/sql/sql-training/schema-based-access-control-for-sql-server-databases/
    2.  Column level Security OR Database User Securable (Columns): You can restrict access to column(s) in a table to users or groups. For Example, a table has 10 column and out of 10, 2 columns are Email and Phone Number and you don’t want every one to have access to these 2 columns. Simply hide these 2 columns for users or groups. To do so go to DatabaseàSecurityàUsersàSelect User or Groupà Double Click à Securables à Click on Searchà Specific Objects àObject Types à TablesàType your table nameàCheck NamesàSelect your TablesàUnder Securables, Select your TableàClick on Column PermissionsàTick the Deny option for column you want to hide to user or group. This way you can allow/deny access to Columns under a table to Users and Groups. https://docs.microsoft.com/en-us/azure/sql-data-warehouse/column-level-security. I noticed that If user login to SQL Server DB using SSMS and Expand the columns for a table, user will able to see all column names but if try running a Select Query on Hidden column or * , then SQL will throw an error.
    3. Data encryption at Column level: Database encryption can generally be defined as a process that uses an algorithm to transform data stored in a database into "cipher text" that is incomprehensible without first being decrypted. In order to explain column-level encryption it is important to outline basic database structure. A typical relational database is divided into tables that are divided into columns that each have rows of data. Whilst TDE (Transparent/External database encryption) usually encrypts an entire database, column-level encryption allows for individual columns within a database to be encrypted. You need to create below 3 to encrypt your column data:
    • Master key in your database
    • Certificate in your Database
    • Symmetric Key
    1. Transparent Data Encryption (TDE) in SQL Server: Transparent Data Encryption (TDE) was introduced in SQL Server 2008. Its main purpose was to protect data by encrypting the physical files, both the data (mdf) and log (ldf) files (as opposed to the actual data stored within the database). Transparent Data Encryption Encrypts SQL Server, Azure SQL Databases, and Azure SQL Data Warehouse data files. https://www.sqlshack.com/how-to-configure-transparent-data-encryption-tde-in-sql-server/
    2. Dynamic Data Masking in SQL Server 2016: Dynamic Data Masking masks the sensitive data “on the fly” to protect sensitive data from non-privileged users using built-in or customized masking functions, without preventing them from retrieving the unmasked data. https://www.sqlshack.com/using-dynamic-data-masking-in-sql-server-2016-to-protect-sensitive-data/  OR https://www.red-gate.com/simple-talk/sql/sql-development/encrypting-sql-server-dynamic-data-masking/
    3. SQL Server Static Data Masking: SQL Server 2019 with SSMS 18.0 (preview 5 and higher) introduces a new security feature called Static Data Masking. This technique follows the idea of applying Static Data Masking against a production database and then creating a backup of the database with the mask applied, followed by restoring this masked copy to non-production environments. It is basically a feature that helps users create a masked copy of a SQL database. Once data is statically masked, it is permanently replaced in the cloned database and we can't change it. This feature is used for several purposes like sharing sensitive data, database development, database troubleshooting, analytics and business reporting. https://www.mssqltips.com/sqlservertip/5939/sql-server-static-data-masking-example/

    I have implemented Data encryption at Column level.  https://www.w3computing.com/sqlserver2012/encrypting-data-methods-data-encryption/

    Steps:

    1. Create MASTER KEY, CERTIFICATE and SYMMETRIC KEY. In below query when you create SYMMETRIC KEY, then you should definitely add KEY_SOURCE and IDENTITY_VALUE. This will allow you to create IDENTICAL SYMMETRIC KEY across multiple database by using same phrase. This means, you can encrypt data into 1st database and decrypt it into another database. If you don’t do so then you may not able to Decrypt the data encrypted into another database.  https://sqlity.net/en/2492/identical-symmetric-key/  and https://dba.stackexchange.com/questions/259816/backup-and-restore-of-master-key-and-certificate-are-not-working-on-same-server

    /*CREATE At Source*/

    --DROP SYMMETRIC KEY [Symmetric Key 01]

    --DROP CERTIFICATE [Your Certifiacte Name]

    --DROP MASTER KEY

    /*Create MASTER KEY*/

    CREATE MASTER KEY ENCRYPTION BY  

        PASSWORD = 'Your Password'; 

    /*Create CERTIFICATE*/

    CREATE CERTIFICATE [Your Certifiacte Name]

           WITH SUBJECT = 'This is a Database Certificate to Protect Sensitive Data',

           EXPIRY_DATE = '20251231' /*EXPIRY_DATE is optional and I think EXPIRY_DATE is 1 year from creation so it is better to specify a date here*/

    /*Create SYMMETRIC KEY*/

    CREATE SYMMETRIC KEY [Symmetric Key 01] WITH

        KEY_SOURCE = 'Any Text Phrase ABC',  /*This is to Create IDENTICAL SYMMETRIC KEY*/

        ALGORITHM = AES_256,  

        IDENTITY_VALUE = 'Any Text Phrase XYZ'  /*This is to Create IDENTICAL SYMMETRIC KEY*/

        ENCRYPTION BY CERTIFICATE [Your Certifiacte Name];

    /*Use This Query to Encrypt the Data*/

    OPEN SYMMETRIC KEY [Symmetric Key 01] 

       DECRYPTION BY CERTIFICATE [Your Certifiacte Name]

    SELECT

    ENCRYPTBYKEY(KEY_GUID('Symmetric Key 01'), EmailID) AS EncryptedEMAIL

    FROM Table

    /*Use this Query Decrypt the Data*/

    OPEN SYMMETRIC KEY [Symmetric Key 01] 

       DECRYPTION BY CERTIFICATE [Your Certifiacte Name]

    SELECT

    CONVERT(NVARCHAR, DECRYPTBYKEY([EMAIL])) AS DecryptedEmail

    FROM Table

    1. To Store Encrypted value to a column, you should have column datatype as [Varbinary].
    2. You can’t alter table and alter column to change column data type from any other data type to [Varbinary]. https://stackoverflow.com/questions/14539445/change-column-from-varcharmax-to-varbinarymax
    3. To Encrypt or Decrypt the column data, you need to run this statement 1<sup>st</sup> in that session. OPEN SYMMETRIC KEY [Symmetric Key 01] DECRYPTION BY CERTIFICATE [Your Certifiacte Name]
    4. You must add a new column as Varbinary data into table and then using ENCRYPTBYKEY(KEY_GUID('Symmetric Key 01'), EmailID) you can update the value of this column. For Example: You have a table with a column Email. You have 2 million rows in that table and this tables is SCD type-2 so you don’t want to lose the data so can’t drop and recreate table. You want to apply encryption on Email Column. To do so you will 1<sup>st</sup> create MASTER KEY, CERTIFICATE and SYMMETRIC KEY. Then next you will add a new column Email1 as Varbinary(Max) NULL into your table [ALTER TABLE [dbo].[Table1] ADD [Email1] Varbinary(MAX) NULL]. Then you will update your new column by running this query: OPEN SYMMETRIC KEY [Symmetric Key 01] DECRYPTION BY CERTIFICATE [Your Certifiacte Name] UPDATE TABLE1 SET Email1= ENCRYPTBYKEY(KEY_GUID('Symmetric Key 01'), Email). Once everything updated then you can drop the column Email and then rename Eamil1 to Email using query: EXEC sp_rename 'dbo.Table1.Eamil1', 'Email', 'COLUMN'
    5. Last you don’t want to grant access to SYMMETRIC KEY to everyone. If everyone has access to SYMMETRIC KEY then they can execute this statement OPEN SYMMETRIC KEY [Symmetric Key 01] DECRYPTION BY CERTIFICATE [Your Certifiacte Name] and then they can Decrypt the data. So you are going to deny access to SYMMETRIC KEY to groups and users you don’t want to allow them to see Decrypted data.

    --Grant View

    GRANT VIEW DEFINITION ON CERTIFICATE::[Your Certifiacte Name] TO [User OR Group]

    GRANT VIEW DEFINITION ON SYMMETRIC KEY::[Symmetric Key 01] TO [User OR Group]

     

    --Grant Control

    GRANT CONTROL ON CERTIFICATE::[Your Certifiacte Name] TO [User OR Group]

    --Deny View

    DENY VIEW DEFINITION ON CERTIFICATE::[Your Certifiacte Name] TO [User OR Group]

    DENY VIEW DEFINITION ON SYMMETRIC KEY::[Symmetric Key 01] TO [User OR Group]

    Understanding roles in SQL Server security:

    https://www.techrepublic.com/article/understanding-roles-in-sql-server-security/

    https://www.netwrix.com/sql_server_security_best_practices.html

    https://www.mssqltips.com/sqlservertip/4990/tighten-sql-server-security-with-custom-server-and-database-roles/



    Friday, February 21, 2020 4:15 AM
  • 3. SQL Server Static Data Masking: SQL Server 2019 with SSMS 18.0 (preview 5 and higher) introduces a new security feature called Static Data Masking.

    Note that Microsoft dropped this feature when SSMS 18 became official.

    Furthermore, static data masking is nothing you would use in a production database - static data masking is a destructive process. That is, the original data cannot be regained. This process is intended for the situation where you copy a producton database to a test or dev environment.

    While Microsoft withdraw their implementations, there are a couple of third party options out there. Red Gate has a Static Data Masker. There is also a free option at dbatools.io.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, February 21, 2020 10:48 PM