• SQL_Student (8/30/2016)


    Thanks that seems interesting, have never used that before... Will that encryption allow the database to be accessed remotely (eg allowing other instances to write into that db with a restritced logon)

    What TDE does is encrypt the .mdf database files; it mitigates the scenario where someone (like another system administrator) gains access to the physical files. Similar in concept to table/index compression, it is transparent to the users in terms of authentication, authorization, and querying. If you have a scenario where there are multiple system administators, and there is a specific database that should accessed only by some administrators, then you want to place that database on a separate instance, enable TDE, and then only grant login to the appropriate administrators. You also want to insure that the backup certificate file is stored somewhere where only athorized administrators can get to it, because that certificate can be used to restore the database on yet another instance.

    Actually, this super-secret database and it's SQL Server instance should be installed on a separate server where the unauthorized administrators are not a local server admin. The problem is that, if someone is a local admin on a server, then they can restart SQL Server in single-user mode and then login to the instance that way.

    Connect to SQL Server When System Administrators Are Locked Out

    https://msdn.microsoft.com/en-us/library/dd207004.aspx

    So, just to recap, you need to:

    1. Create this database on a separate SQL Server instance where only you are the sysadmin

    2. The instance should be hosted on a separate Windows server where only you are the local admin

    3. You need to enable TDE on that database.

    However, trying to protect an on-premises database from an on-premises DBA is like trying to protect the President from the possibility of a rogue Secret Service Agent working in the White House; if they are determined; the breach is simply going to happen eventually. So, if you're concerned in earnest about protecting this database from a rogue sysadmin administrator, then you probably want to keep it behind a separate firewall or move it off premises to Microsoft Azure or AWS.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho