I have backed up a database and backed up the database master key to the very same folder. Everyone has full access to this folder, however, only the database backup inherits the permissions of the folder whereas the key backup does not.
I need to use admin privileges to view the security of the master key backup file.
Can anyone suggest why this may be? Both were created by the same account via backup command in T-SQL.
Best Answer
Whenever you run
BACKUP CERTIFICATE
orBACKUP MASTER KEY
, SQL Server will modify the Access Control List (ACL) on each resulting filesystem file it creates1 so that no-one other than the following Windows principals have access to the file:S-1-3-4
that represents the current owner of the object. When an Access Control Entry that carries this SID is applied to an object, the system ignores the implicit READ_CONTROL and WRITE_DAC permissions for the object owner.NT SERVICE\MSSQLSERVER
. For a named instance, the account is namedNT SERVICE\MSSQL$INSTANCE_NAME
.This behavior is by design, and is an attempt to limit access to the resulting certificate and its private key. Allowing "everyone" to access the certificate and private key files allows everyone to restore a backup of the TDE encrypted database onto the server of their choice, and be able to view all the encrypted data as if it was not encrypted. What you're proposing to do seems like a very bad idea to me since it essentially circumvents the encryption.
See my blog post for more details, and example code.
1 - assuming the SQL Server Service has "full control" rights to the target folder in the backup statement.