Create a table and insert a couple of rows:
Then back up the database without using compression, and open up the backup file with a hex editor:
The same trick works on the data file, too.
That’s it.
That’s all. That’s the post.
Look, not every post is groundbreaking: sometimes it’s just about writing down something that I had to explain to a client, and I’m pretty sure I’m going to have to explain it again. PII is like a sexually transmitted infection: it gets around in ways that you’re just not prepared to face.
27 Comments. Leave new
Want to really have fun, change the extension on a .docx, .xlsx or .ispac file to .zip and you can see all the internal and manifest files that make up the custom file type. For example if you ever have an excel file that seems to large for the data you can do this then go into the zip, check the data folder to see if old deleted data is still in there in csv format.
How does compressing the backup affect this (if at all)?
Darshan – you can run an experiment to find out in 30 seconds. Go for it! That’s how you learn.
Hi Brent, I have tried this and couldn’t see any readable values in a hex editor.
There you go! Hope you enjoyed the exercise. I love stuff like that.
Just to clarify, I don’t think that you are safe just because you cannot read the value in the hex editor. If just compression was applied to the data, then it could potentially be reversed in a straightforward way because no encryption was done to the data and the compression algorithm is probably public knowledge or easy to reverse engineer for a dedicated person. To truly have that data encrypted, you have to use the encryption option.
I have real problems with enabling instant file initialization because of this belief by clients that the data is magically unreadable when its in a database. If they have enterprise, I will always push for implementation of TDE regardless of what is in the database, but some clients are at a point where using TDE would pose a major risk of having backups that are essentially bad when they lose the key backups as well. you never know when a vendor could come in for an upgrade, detach a bunch of databases for safe keeping and then put them on a volume that will later hold databases with extremely low access restrictions to a large number of staff.
I also try very hard to not have an autogrow however.
It’s not even just the IFI feature that TDE impacts on.
Anyone using the Buffer Pool Extension feature needs to remember that pages in BPE’s are decrypted just like those in-memory, so you better be putting BitLocker over, or an encrypted file system underneath, BPE files too!
And how about In-Memory OLTP? Are you on SQL Server 2014 and not 2016? Then your MEMORY_OPTIMIZED_DATA filegroup files aren’t encrypted even if your row store filegroups are!
The road to fully-encrypted-at-rest is long and winding…
That’s a good point on BPE. I have found it to be an entirely useless feature so far, but I definitely have had clients who like to use features just because they are available. I am sure there are cases though where it could be useful.
Fear of crossing the street and of balloons … interesting.
Thankfully, there’s medication to help us cope.
Is there medication to help with TDE-related stress? XD
Something else to consider is that SQL Server decrypts your data when it’s read off disk and into memory and encrypts it when it’s written to disk. So you could have unencrypted data in your swap file and in any core dumps. I put both on an encrypted disk volume.
Steve I believe there is now a way to encrypt SQL data that has been queried out, in memory. You can also either encrypt your page file, or configure windows to wipe it on shut down. Hopefully your SQL page file is only a couple Gb so either options shouldn’t introduce too much overhead.
Keith – it sounds like you’re referring to the Always Encrypted feature, but that requires serious application changes, compatible drivers, no linked server queries or replication to non-encrypted destinations, etc., etc. It’s nowhere near as easy as it sounds.
I have an encrypted disk volume that I use for encrypting the page file and core dumps. Anything written to that volume gets encrypted. My main point is that you have to think outside of just SQL server if you want to be sure all of your data is encrypted at rest.
Steve – unfortunately, folks who take a backup aren’t restricted to only writing to that drive.
Yeah, that’s on top of using TDE which then means the backups are encrypted as well.
*hurriedly goes and updates his high-level design documents to include encrypted volumes especially for swap files and memory dumps to live in*
lol just set it to either encrypt the page file or wipe it and to only create mini-dumps
A beautiful thing is happening in SQL Server 2019, TDE is in the Standard Edition, YEA!!!!
I have the PII encrypted with EncryptByPassphrase (don’t have server level access so no certs). I’d be interested to know your perspective on how secure it is?
I’ve read numerious articles around hacking this approach, but they all seem to be variants of “loop around all letters in the alphabet until you find the passphrase”. Whilst this would work with 8 characters, 128 characters of nonsense would be siginificantly more difficult …
Gauging the effectiveness of your custom encryption is a little beyond what I can do in a blog post comment. 😉 You would be best off hiring a security firm to analyze something like that.
Oops, meant to hit reply! \/
>Gauging the effectiveness of your custom encryption …
Not sure I agree with “custom”. It’s baked into SQL Server 🙂 – Granted there are better approaches … my rational being it’s still better than clear text …
>… is a little beyond what I can do in a blog post comment. ?
Totally agree. Was more wondering if you’d come across any real-world usage with your depth of experience.
>You would be best off hiring a security firm to analyze something like that.
Ideally yes, but more than my moth-eaten wallet could muster for a pet/side project 😀
[…] Instance itself. Want to know easy it is to read data from an unencrypted backup file? Read this post from Brent Ozar, and then take several deep […]
[…] itself. Want to know how easy it is to read data from an unencrypted backup file? Read this post from Brent Ozar, and then take several deep […]