none
DB Encryption

    Question

  • if we're trying to protect the db from admin, then where to store the key?

    Thanks in advance

    Wednesday, July 11, 2018 3:12 PM

All replies

  • which admin? you mean dbo?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, July 11, 2018 3:15 PM
  • Are you referring to the Transparent Data Encryption (TDE)?
    Wednesday, July 11, 2018 3:21 PM
  • If you want to prevent dba from reading data use Always Encrypted. But this is at column level and not at db level

    https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-encryption-always-encrypted/

    Another method is this

    https://www.mssqltips.com/sqlservertip/2840/sql-server-encryption-to-block-dbas-data-access/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, July 11, 2018 3:31 PM
  • if we're trying to protect the db from admin, then where to store the key?

    Thanks in advance

    First please learn to post clear question, nobody is going to guess what you are trying to ask. Just posting one line is not going to help anybody. If you will not update your question I would either have to lock it or move it to off topic forum. Please add more information

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, July 11, 2018 4:31 PM
    Moderator
  • if we're trying to protect the db from admin, then where to store the key?

    Thanks in advance

    There are different way to store the keys.. by 3rd party(if legally signed)(or) by your organization only might  have this mechanism but important thing is that who owns that Key either from DBA or application.

    if decides & agrees then you have to check accordingly.

    or As Shanky said you have tel to more information about requirement.


    Regards, S_NO "_"

    Wednesday, July 11, 2018 4:36 PM
  • Hi rgelfand,

    If you are trying to hide the key password from any users, one option is using powershell to encrypt the key using the lines below, and then save that encrypted key wherever you want (sql table, plain text file, etc). Only the account who ran the lines below can decrypt it and get the password key.

    $key = "Passwordkey"
    $Password = $key
    $secureStringPwd = $Password | ConvertTo-SecureString -AsPlainText -Force
    $secureStringText = $secureStringPwd | ConvertFrom-SecureString


    Best Regards, GMVS

    Wednesday, July 11, 2018 7:21 PM
  • Hi rgelfand,

    Do you mean that you want to deny the permission of SQL Server admin on the database? SQL Server admin has  maximum permissions, the general encryption method will be useless to them.

    An easy method is using Always encrypted as mentioned by Visakh16, but this is a new feature in SQL Server 2016, if you are going to use some older version, we will need to find other method.

    We can also put the process of encryption and decryption on the front-end, we can store the certificate on the front-end and then encrypt and decrypt data on it,

    Best Regards,

    Teige


    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.

    Thursday, July 12, 2018 2:15 AM
  • Hi rgelfand,

    Do you mean that you want to deny the permission of SQL Server admin on the database? SQL Server admin has  maximum permissions, the general encryption method will be useless to them.

    An easy method is using Always encrypted as mentioned by Visakh16, but this is a new feature in SQL Server 2016, if you are going to use some older version, we will need to find other method.

    We can also put the process of encryption and decryption on the front-end, we can store the certificate on the front-end and then encrypt and decrypt data on it,


    How are you sure OP is asking about always encrypted, did he reverted with his requirement in clear way. As a MS community support I feel pity for you guys who just want to close thread by giving/marking answer. Take a feedback from me this is really wrong approach from you guys. No one asked OP to post clear requirement. Someone comes and just posts one line and people start replying so much for getting points on the forum.  I would not point to other but as MS community support I will point to you please encourage people to post clear question so that answer can be given in limited replies I am sure this makes me bad guy on this thread but that is just me. 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, July 12, 2018 6:55 AM
    Moderator