locked
Encrypt master database backup? RRS feed

  • General discussion

  • Hi.

    I am currently utilising database backup encryption via certificate on my production server. This includes all my system databases

    Just wondering what the general consensus is out there in the big wide world:

    • Is it worth encrypting your system database backups (master, model, msdb)?
    • Does anyone out there think that this is overkill? If so, why?

    Thanks guys.

    • Changed type warnerrj79 Thursday, October 31, 2019 9:58 AM
    Thursday, October 31, 2019 9:58 AM

All replies

  • IIRW  it is not supported  to encrypt system databases 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Thursday, October 31, 2019 10:09 AM
  • IIRW  it is not supported  to encrypt system databases 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Hi Uri - thanks for your feedback.

    So I am currently undergoing a DR test - which basically entails restoring a whole SQL Server instance to a freshly installed copy of SQL Server.

    One of the tasks is to restore the master database to the new server.

    When I tried to do it, it wouldn't restore - as the backup encryption certificate was not present on the server.

    After creating a database master key and restoring the backup encryption certificate, I was then able to restore the master database.

    So I believe it is possible to encrypt the backup file of master database.

    My question is does the community generally recommend this?

    Thanks.

    Thursday, October 31, 2019 10:18 AM
  • Hmmm when you attempt encrypt a system database, SQL Server complains:

    Cannot encrypt a system database. Database encryption operations cannot be performed for 'master', 'model', 'tempdb', 'msdb' or 'resource' databases.

    >>>>My question is does the community generally recommend this?

    I see NO reason


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, October 31, 2019 12:01 PM
  • Hmmm when you attempt encrypt a system database, SQL Server complains:

    Cannot encrypt a system database. Database encryption operations cannot be performed for 'master', 'model', 'tempdb', 'msdb' or 'resource' databases.

    >>>>My question is does the community generally recommend this?

    I see NO reason


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    I think we may be talking at cross purposes here....

    I am not trying to encrypt the system databases - I am encrypting the backup files of the system databases - ie the .bak and .trn files

    Thursday, October 31, 2019 12:15 PM
  • Ok, I have never seen people encrypt system databases

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, October 31, 2019 12:21 PM
  • There is nothing in system databases worthy of encrypting a backup.  Everything important, like passwords, are already encrypted inside the system database.

    Thursday, October 31, 2019 12:24 PM
  • There is nothing in system databases worthy of encrypting a backup.  Everything important, like passwords, are already encrypted inside the system database.

    Hi Tom - thanks for your reply.

    So essentially (in my view) the only thing worth protecting in the master DB is the backup certificate itself, which would be stored in the master database (as per sys.certificates).

    Example:

    • master database .bak file (not encrypted, in this case) is restored to a new server
    • user database .bak file (encrypted at backup stage) restored onto the new server without a problem - as the DMK and Backup Certificate are in the master database already

    If someone was to access the backups folder unauthorised and gain access to the .bak files, then theoretically the unauthorised user could (in theory) copy the master DB .bak file and other encrypted user DB .bak files and restore to a fresh server, without any issue....?

    Friday, November 1, 2019 3:55 PM