September 24, 2015 at 8:33 am
We are getting a security audit for the company I work for and got this question recently, and while my answer would be "everything is possible", I know that DIFFs alone can't restore a SQL database.
Having said that (and don't want to read hypothetical comments) how can a hacker read confidential information from an unencrypted DIFF backup? Let's say he steals the DIFF backup alone.
September 24, 2015 at 8:58 am
What do you define as confidential information?
Things like Red-Gate SQL Compare can read a diff backup to get the schema, unsure on Data Compare
http://documentation.red-gate.com/display/SC11/Working+with+backups
SQL Compare can retrieve the schema from full or differential backups, but it doesn't support partial, filegroup, or transaction log backups.
I can't see the same line in the Data Compare documentation.
September 24, 2015 at 9:11 am
anthony.green (9/24/2015)
What do you define as confidential information?Things like Red-Gate SQL Compare can read a diff backup to get the schema, unsure on Data Compare
http://documentation.red-gate.com/display/SC11/Working+with+backups
SQL Compare can retrieve the schema from full or differential backups, but it doesn't support partial, filegroup, or transaction log backups.
I can't see the same line in the Data Compare documentation.
Thanks
Not talking about schema but data itself. Whatever you have or keep inside those tables.
Also, don't see in your link, how that tool can read or compare unless you have the FULL, which is basically my main question. Hacking a DIFF without a FULL.
September 24, 2015 at 10:29 am
It can't be restored, but it can be opened in a hex editor and read raw. Won't be easy, but string data won't be that hard to get out. Numbers more so and the schema will be needed to make any real sense of it (but as Anthony points out, that's not hard).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2015 at 10:50 am
GilaMonster (9/24/2015)
It can't be restored, but it can be opened in a hex editor and read raw. Won't be easy, but string data won't be that hard to get out. Numbers more so and the schema will be needed to make any real sense of it (but as Anthony points out, that's not hard).
Thanks Gail.
September 24, 2015 at 11:44 am
Shortest possible answer:
Yes.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 24, 2015 at 12:30 pm
There are 3rd party applications that can read SQL Server backups, so the file format must be published in public domain. It may not contain all the data necessary to reconstruct a complete table, but a differential backup contains the delta of extents, or blocks of 8 contiguous pages, that were modified since the last full backup. A motivated hacker could extract complete records from it. The same probably goes for transaction logs.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 24, 2015 at 1:01 pm
I did it long time ago but we used a third party tool and it was to read the Tlog backup of an important client for recovery purposes. I honestly don't remember the tool. I wanted to know the specifics though, so I can play around at my home lab.
I Google around and found HxD, which I think Paul Randal uses for read sql pages. Not sure if HxD can open a DIFF backup.
September 24, 2015 at 1:15 pm
sql-lover (9/24/2015)
Not sure if HxD can open a DIFF backup.
Why would it not be able to? Hex editors don't care about the file type, they display the files they open in raw hexadecimal.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2015 at 1:32 pm
GilaMonster (9/24/2015)
sql-lover (9/24/2015)
Not sure if HxD can open a DIFF backup.Why would it not be able to? Hex editors don't care about the file type, they display the files they open in raw hexadecimal.
Gail's right. I can open anything I want in UltraEdit in hex editor mode and it reads everything without a problem. This includes text files, Excel files, executables and backups. Whether or not I can read it is another story, but the strings are pretty simple.
September 24, 2015 at 2:15 pm
Alright ... I will use any of those later at home or during the weekend and play around.
September 24, 2015 at 2:25 pm
If page compression is enabled, the raw data won't look like clean fixed width records when viewed with something like a Hex editor. My understanding is that recurring strings and prefixes are tokenized, etc. within the boundaries of each page, so that will somewhat frustrate attempts to isolate and make sense of whole records.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 25, 2015 at 12:39 am
It is raising the question in my mind about "when" we should encrypt.
If we encrypt to-tape then the tape has a chance to compress. If we encrypt to-backup-file that is reduced (although nowadays we are using COMPRESS in SQL BACKUP so maybe the point is moot anyway? as I doubt the Tape can further compress)
If we encrypt to tape then anything going off-site is encrypted, but internally we are passing backup files around - e.g. log shipping to DR and from Production to Test servers etc.
Where should we draw the line?
Given that we use COMPRESS in SQL Backups is there no CON to also Encrypting it?
September 25, 2015 at 1:42 am
Backup compression is lossless (IIRC) so someone with knowledge of compression could revert the compression algorithm.
Then again encryption is also reversible, you will have backups of your certs / keys which do the encryption so all it takes is for someone to intercept the cert/key also then its just a case of cracking the password.
The alternative would be to hash the data with salt and change all the applications to take the inputs hash them with salt and compare the two hashes instead as you shouldn't be able to take the hash and get it back to its raw format.
September 25, 2015 at 5:56 am
Your direct question has already been answered, however the audit will probably turn up a couple of things:
If they stole the diff backup, why wouldn't the full backup be stolen as well? Is the full backup off in another location?
Why isn't TDE being used? It protects data at rest and a text editor wouldn't be able to read the "string" as anything usable.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply