Possible Security Issue with SQL Backups
-
I posted this in the Security thread but was interested in getting more people to view it. I may be over exagerating but...if this issue is true for all SQL backups, it seems to me that the implications could be pretty big.
As part of a security audit, one of my company's security staff opened up a SQL 2000 backup in MS Word. (He did not know the source of the file and therefore randomly opened the backup in word). While most of the data in Word was unreadable, there was some readable text. The interesting part of this is that the text was not from any data that was in the database that was backed up. It was data from other databases on the same server that were not part of the backup. In this case, the backup was for a db that had non sensitive data but much of the text that could be read through Word was sensitive.
Has anybody else encountered this issue before? I could not find any info on it but that is understandable because most DBA's would not think of trying to open a backup in Word. If this is a common problem, however, then it could be a huge security breech.
-
This issue has been around at least since SQL Server 7.0. Recommendations for backup security include setting a password when backing up data, encrypting backup files, and storing backup files in secure locations.
This article covers some of the options: http://www.sqlservercentral.com/columnists/bkelley/securingsqlbackups.asp
Greg
Greg
-
Thanks for the response Greg. I am aware about the possibility of text data being in the binary file. What is interesting is that the text is from a different database that resides on the same server. The backup was a straight backup via Enterprise Manager. No maintenance plan or anything.
-
Did the data ever exist in the database, I had posted sometime back that even when you alter data a Ghost of the data may still exist until it get's overwritten. It is just part of the recovery from transactions.
Other than that the only way this could happen is someone used a backup file that already existed and left the default answer in EM which is append to the backup file instead of overwrite. Quite possibly they pointed to a file that had been previously used to backup the other database.
Otherwise this issue does not occurr in any backup I have ever made or tested.
-
The data did not ever exist in the database. The database that was backed up was a new database that we had just created a couple of days earlier. For the most part, it was only schema.
I did wonder if that may be the case also. For example, if I had created the database by performing a restore of the other database and changing the name, then deleted unnecessary tables. I could see where this would happen. Since it was recently created, I was sure that this was not the case. In addition, the database and it's backup are only about 7 MB and none of the files have ever been shrunk. The data that was viewable in word came from a database that is over 6 GB.
As a test, I performed another backup of the database, made sure to choose overwrite, and still had the same issue. I looked at backups from some other databases and did not find the issue in any of them.
-
You could report it to MS. AFAIK, this should never happen. My suspicion is the data got put there, possibly without your knowledge. The backup stream processes pages from only that database, or it's supposed to.
I haven't really dug through my backup files, so it's possible that something happened, but I doubt it.
-
There are two ways you can find out for sure.
1) You could use DBCC PAGE to read the physical pages 1 at a time (there is a bit of work to do this btw and there can be hundreds of pages).
2) My favorite, stop the SQL Server, copy the MDF file to another location, then you can restart the SQL Server. Then using MS Word open the MDF file and search look for the offending string. If you find it then it did exist in the DB at one time. Maybe someone imported the table where it would have come from and then deleted the table or trunctaed it but if you find it in the DB's MDF file it was there at some point and is now a ghost record until something writes over it and will show in the backup as you see.
-
MS is involved.
I am going to try Antares suggestions. Let you know what happens.
-
This is just a thought. Consider that the data from the other database has just been shrunk by a maintenance plan or manual intervention. The data would normally reside on the disk (not in a directly accessible form mind, but the data could be extracted by third party disk readers). I am assuming that the shrink type function is not wiping the parts of the hard disk that is no loner in use.
Your database has then expanded by 10% say to include part of the disk that the previously shrunk database used. Whilst the data would not be readily accessible via Enterprise Manager, etc., the underlying data on the disk would still be allocated (at a disk level) to the data or log files. Any extra data added to your tables or the creation of new views, udfs, etc would overwrite this allocated data on the hard disk.
It would make sense for the SQL backup process to backup the data and log files as they appear on the hard disks rather than each individual table, view, udf, etc. for performance and practicality reasons. Result data from the other database now "appears" to reside in your new database when viewing your SQL backup file. Of course, the design of SQL Server should prevent any application or data access attempt from viewing the data.
If this is the case, then it would prove that high security databases should not be stored on the same disk array (including SANs, etc.) that regular production databases within any organisation.
Just my 2p worth!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply