Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78

    Unanswered: Column Encryption and the DBA

    We are in the process of implementing a payroll system using SQL 2008 as a backend which will obviusly need to store some sensitive data including SSN, salary, and routing information for direct deposit. Unfortunately none of these columns are encrypted by default, and without a lot of rework I can't add encryption to the system. I could add it in code, but there are reports (like W-2s) that need to include this information.

    I'd like to add column encryption at the SQL level so the process is fairly transparent to the application. Basically I want a CYA incase someone has there identity stolen and they want to start pointing fingers because I can see the data.

    I have successfully created a new table replacing SSN with the encrypted column, and created a view to do the decryption with triggers to update the underlying table. The only problem is that as sa and db_owner I have rights on the Certificate used to encrypt, so it doesn't hide it from me. Even if I could hide it from me, I can use EXECUTE AS LOGIN='app_user' to run any query I want in the context of the application.

    We have a separate security group here (not SQL experts) but one thought is for them to create and own the certificate used for the encryption, but I still run into problems because I can use EXECUTE AS...

    Does anyone have any ideas on how I can lock myself out of specific data while staying in the sysadmin role for basic administration tasks?
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Since you are using SQL 2008, look into Transparent Database Encryption. This will encrypt all of the structures on disk, and in the backups, but the application (with the correct permissions) does not know that the database has been encrypted. Even the transaction log is encrypted, so you can't even use log reading tools to get at the data. I/O is a bit more costly, as each page that is read or written needs to be encrypted, but that is the price you pay for not having to change anything else in the application.

  3. #3
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    That is included in another part of the plan. We're not sure if we're going to use the Transparent Encryption, or just use RedGate's SQL Backup to get encrypted backups for things that go to tape. We're not too concerned about having the SAN walk out the door with the live database.

    What I'm trying to prevent is me from running a query like "SELECT SSN FROM EmployeeTable" and getting the SSN back. I know I won't do anything with the data and my boss knows I won't do anything with the data, but that doesn't make someone whose identity just got stolen feel any better. I'm trying to save our database team from accusations should anything happen.

    That's where the security team was going to come into play. We have access to the database, they have access to the encryption key, so only together could we actually see the data, but since I'm building it into a View it doesn't really buy me anything.
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    We're not too concerned about having the SAN walk out the door with the live database.
    Do you happen to send your backups offsite?

  5. #5
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    yep.
    Quote Originally Posted by wayneph
    ...or just use RedGate's SQL Backup to get encrypted backups for things that go to tape...
    this way we get encryption for anything that leaves the data center.
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  6. #6
    Join Date
    Jul 2009
    Posts
    9
    My organization uses a transparent storage encryption product from Bloombase which does quite well for Oracle and DB2.. I guess it also works fine for SQLServer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •