Hello again readers and welcome back! I hope that this new year has been treating you well so far! I recently worked a case with an interesting twist that I never had to deal with before, so I figured I would make a blog post about it and share my experiences. I also wanted to document the whole process just in case I have to deal with it again!
The case that I worked involved a SQL Server backup file (with a ".bak" file extension), which was created from a Microsoft SQL Server instance. Loading and parsing a SQL Server backup file is fairly trivial if you have a SQL Server environment, but I do not have a SQL Server environment and had to come up with a way to be able to process the data.
Edited March 10, 2017 - The reddit user fozzie33 made a fantastic point that I did not specify in this particular post. I was working from a copy of the data that was originally provided, but it is best to change the attributed to read-only in an effort to ensure the raw data itself does not change. In any forensic investigation you should always be working from a copy of the data and never the original, but changing the attributes to read-only is another step one should take to limit any changes to the data, even if it is a working copy!
I followed a total of nine steps to accomplish analysis of the backed up SQL database:
1) Download SQL Server 2016 SP1 Developer edition
2) Download Microsoft SQL Server Management Studio
3) Copy executables to flash drive
4) Copy executables to offline system
5) Install SQL Server
6) Install SSMS
7) Launch SSMS & restore the SQL database
8) Make your SQL queries using SSMS
9) Great success! High five!
Step 1: Download SQL Server 2016 SP1 Developer edition https://msdn.microsoft.com/library/dd206988.aspx
Hopefully you have a Microsoft Developer Network account, if not, pop over to the MSDN page and sign up for one, it is free and quite easy to do. Once you are logged in, you can download the SQL Server 2016 SP1 Developer edition. The reason for using this version, compared to the Express version, is that the Express version limits the size of your database to 10GB. If you know your database is going to be smaller than that, you can definitely use the Express version, but I prefer the Developer edition just to be sure I can handle the database regardless of what size the database will be.
IMPORTANT NOTE: The license of the Developer edition explicitly prohibits using "Production data". While the backup file is indeed "Production data", I recommend installing the needed items and processing all of the data on a completely offline machine, and when you are finished with the analysis completely uninstall everything from your system. My personal take on the EULA is that Microsoft does not want you to use the Developer edition to power an online database backend, as they of course want you to purchase the license to allow you to do that. My opinion is that performing offline analysis of a SQL Server backup file is well within the limitations of the Developer license, but if you have any question on the legality of the issue please consult proper legal counsel, as I am not a lawyer nor did I stay at a Holiday Inn Express last night!
To download the files for your offline machine, first choose the "SQL Server 2016 Developer Edition Download" option.
|Choose the "SQL Server 2016 Developer Edition Download" option|
The download page will load, then choose the "SQL Server 2016 Developer with Service Pack 1" option.
|Choose the "SQL Server 2016 Developer with Service Pack 1" option|
You will be presented with an option to download the .iso, or you can use the "Click here to utilize the SQL installer." option which will download a file with a name like "SQLServer2016-SSEI-Dev.exe". This installer will let you download the files so you can install it all to your offline machine.
|Choose "Click here to utilize the SQL installer." option|
|The file "SQLServer2016-SSEI-Dev.exe" was downloaded|
When you run the program, you will be presented with a screen containing three options. We are going to select the "Download Media" option, as we want to install it on another machine.
|Choosing the "Download Media" option|
|Choose "CAB" option|
The download will take a few seconds (or minutes, depending on your ISP) and there will be a friendly new screen informing you that the download is finished upon completion.
|Congratulations, the download is now complete!|
When the download is complete, you should have the files "SQLServer2016-DEV-x64-ENU.box" and "SQLServer2016-DEV-x64-ENU.exe" saved in your directory:
|The files "SQLServer2016-DEV-x64-ENU.box" and "SQLServer2016-DEV-x64-ENU.exe" in the download folder|
Step 2: Download Microsoft SQL Server Management Studio https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
The Microsoft SQL Server Management Studio (SSMS) allows you to interact with data from the SQL database in a fairly easy, fairly straight forward manner. Even if you have very limited experience dealing with data from SQL, you can pretty easily start to navigate your way through with some of the built in options from SSMS.
|Choose "Download SQL Server Management Studio" option|
There should a file with a name similar to "SSMS-Setup-ENU.exe" now saved in your downloads folder.
|SSMS-Setup-ENU.exe saved in the "Downloads" folder|
Step 3: Copy executables to flash drive
The filenames themselves may change based on exactly when you download them, but you should now copy the two SQL Server installation files (.box and .exe) and the SSMS installation file to a flash drive so you can transfer it to your offline system.
|Files copied to flash drive for offline system|
Step 4: Copy executables to offline system
Although you can install it directly from the flash drive, in my experience it is always better to copy the needed files to your offline system.
|Files copied to offline system|
Step 5: Install SQL Server
The first thing we are going to do is install SQL Server to our offline system. When you double-click the file you are greeted with a popup asking for the directory in which you wish to save the extracted files. I just left this as the default option and clicked "OK".
|Choose the directory for extracted files|
You will see a file extraction progress bar.
|File extraction progress|
and when that is done, you will see a new window titled "SQL Server Installation Center". We are going to install SQL Server on our system, so click on the Installation link.
|Choosing the Installation link|
There are several options that are presented here, but we are only interested in the first one, labeled "New SQL Server stand-alone installation or add features to an existing installation".
|Choose to install a new SQL Server instance|
Once you click that option, you will see a the installation screen. Because we have the developer edition, there is no need to insert a product key, so just click Next.
|"Product Key" screen|
Check the box on the next screen next to "I accept the license terms".
|Accept the license terms (you did read all the way through it, right?)|
Your system is offline, so there is no need to check the box about using Microsoft Update, so just click Next.
|Our system is offline, so this does not apply|
Again, because the system is offline, you will see an error message saying it could not search for updates. This is fine, so just click Next.
|Looks bad, but it is ok as our system is offline, so this is fine!|
You should now see a screen labeled "Install Rules" that should list a couple of passed items and a couple of failed items. The .NET Application security should have a warning because the system is offline. However, depending on your system settings, the Windows Firewall may generate a warning because it is on, or it may pass because it is off.
|"Install Rules" status|
You should now see a screen labeled "Feature Selection". With this you can choose to install everything, but in my limited testing just selecting "Database Engine Services" should be enough. You can also choose where to install the files, but again the default(s) should be sufficient.
|Feature Selection. Select as little, or as much, as you would like!|
It may take a few minutes, but when it is finished you will see a screen labeled "Instance Configuration". You can choose whatever options that you would like, but I personally prefer to leave the default options again.
It may take a few minutes, but when it is finished you will see a screen labeled "Server Configuration". You can choose different options of course, but again I prefer to leave the defaults.
Next you should see a screen labeled "Database Engine Configuration". I prefer to just leave the "Windows Authentication Mode" checked. You must also choose an account(s) for the SQL Server Administrator, the easiest option for this is to click the "Add Current User" button and it will populate. Once that is finished, click Next.
|Database Engine Configuration. Don't forget to add a SQL Server administrator!|
Now that ALL that work is done, you should a see a screen that resembles a tree hierarchy. Now you can click the Install button and install your SQL Server instance! This will probably take some time, so be patient!
|Ready to install at last!!|
Once that is finished, you should see a screen that is labeled "Complete" and several options should all say "Succeeded" next to them. You can now click "Close".
Step 6: Install SSMS
Now, despite there being a link for Install SQL Server Management Tools in the Installation link on the SQL Server installation option, that simply opens a new page and tries to install it, which means you need an internet connection to do so. That is exactly why we downloaded SSMS separately and have it on our offline system ready to install!
To begin the process, double click the executable, and you should see a screen with "Microsoft SQL Server Management Studio" on it. All we have to do here is click "Install".
|Installation screen for SSMS|
You should see a screen that involves loading packages, as the process will likely take a few minutes to install.
|Packages are loading, this may take a bit!|
Once the installation is "complete", you will have to restart the system in order for the installation to "complete" (because it is Windows, after all!)
|Installation is complete, but we have to restart to complete the installation. Huh??|
Step 7: Launch SSMS & restore the SQL database
Now that SQL Server and SSMS are both installed on our system, we can launch SSMS. Navigate to Program Files and launch the executable.
|Getting ready to launch SSMS for the first time!|
There may be a brief loading screen for user settings, then you should see the SSMS console, complete with the Connect to Server Window.
|SSMS main console|
All you should have to do is click the "Connect" button and you should see a tree view options in the "Object Explorer" window.
|The "Object Explorer" window is populated|
We are interested in the "Databases" option, since we are going to be restoring a database from a backup file. Right click on the "Databases" folder and choose the "Restore Database" option.
|Choose the "Restore Database" option|
Now we will get a new popup window that is labeled "Restore Database".
|The "Restore Database" popup window|
We are going to choose the "Device" option under "Source", then click on the box with the three dots.
|Tick "Device", then click the box with three dots|
This brings up a new window titled "Select backup devices". Our Backup media type will be file, and we will click the "Add" button to add our .bak file (PRO TIP: Saving the .bak file on the root of a drive (like in "C:\" makes it much easier to find and navigate to)). Select the file and then click "OK".
|Click the "Add" button|
|Browse to the folder containing the .bak file|
Now the Select backup devices should be populated with our backup file. As long as it is properly in box, click "OK".
|Select backup devices is now populated!|
There will be a pause as the system processes the information, and you should see the box under "Backup sets to restore" populate with information. As long as it populates properly, you can click "OK".
|The fields are populated, so we can click OK and let the backup restore process start!|
The backup process will take some time to fully restore depending on the size of the database, but once it is done restoring, it will be fully loaded and we can start to make our queries!
|The restore has been completed!|
Step 8: Make your SQL queries using SSMS
Once the database is loaded, you will see it under the "Databases" folder.
|The database, seen under the databases folder|
You can expand on the database and see all of the associated information, but more than likely "Tables" is going to be the main area that you are going to focus on.
|Some of the tables in this database. There are SOOO many tables!|
Thanks to the power of SSMS, you can actually use some of the preconfigured queries to get you started!
|Some of the options. "Select Top 1000 Rows" is your friend!|
You can select the top 1000 rows, and then build out your specific queries accordingly, however you would like!
|The results of selecting the top 1000 rows from this particular table|
Step 9: Great success! High five!
I definitely hope that this rather lengthy blog post helps in the event that you ever find yourself in a situation like this. It is of course much easier to get data from whatever database front end that is available, but if you can only get a backup of the raw database, it takes some time and research to build up good queries to find the information that you are after!