none
Want to Secure Customer 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

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
    Moderator
  • 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