Post reply

database backup file and encrption

  • March 6, 2019 at 3:25 pm

    #379550

    I have setup a TDE on my SQL server. It should encrypt data on the rest - data files and backup files.

    Is there a way to identify if the database file is encrypted by opening and reading the file?

    I open the database backup files using notepad, it sees encrypted and cannot read, a lot of strings like NULL NULL NULL.

    But I also open another backup file on another server that has no encryption setup, it looks similar, I still cannot read any valid data in it, a lot of nulls.

    Thanks

  • March 6, 2019 at 3:56 pm

    #2023741

    sqlfriends - Wednesday, March 6, 2019 3:25 PM

    I have setup a TDE on my SQL server. It should encrypt data on the rest - data files and backup files.

    Is there a way to identify if the database file is encrypted by opening and reading the file?

    I open the database backup files using notepad, it sees encrypted and cannot read, a lot of strings like NULL NULL NULL.

    But I also open another backup file on another server that has no encryption setup, it looks similar, I still cannot read any valid data in it, a lot of nulls.

    Thanks

    You cannot open a backup file, encrypted or not, with a text editor.

    If you want to determine 100% if the backup is in fact encrypted, restore it to a different server.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • March 6, 2019 at 5:28 pm

    #2023745

    You can open in an editor, but you really need a hex editor. Here's a backup of my sample database before I enable TDE.

    Note the offset: 29252A. Now, I enable TDE and take another backup. I'll open that file and go to the offset. I see this

    If I search for my name in the first one, I get a result. The second one shows no result because it's encrypted.

    The file header isn't encrypted either way, and you'll see the database name, files, etc. As Michael mentioned, you can try to restore this to another instance and it will tell you if it can't open the file because of encryption.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply