Examining a SQL Database
Since I have not had to do analysis on a SQL server, I am looking to the forum for a little guidance. Here is what I have.
We were given by a vendor images that contained VM's. These VM's contain MS SQL databases in them that we would like to extract the data from. We have extracted the .mdf files and we are able to see some data in a viewer, but cannot seem to figure out how to extract any of the data in there. Is there a better route to go? Do I need to create an SQL DB and then import the mdf files in? Any advise is greatly appreciated.
Do you have the table structure and linking documentation? If not you have a long road ahead of you to make any sense of the data.
You can try to parse the data, however if you can mount the database in a SQL instance it will be much easier to deal with.
If those images have VMs in them then you should try to boot the images. If you can get the VMs running and they have SQL server installed, then the database should come online.
If you get this far, then you could either use std SQL tools to query the Db. If you don't know what that means, then you should set up Microsoft ODBC links into the SQL database. You can find that app in the Control Panel as Data sources on Win7. Setup a User or System DSN to link into the SQL server.
When that works, then open MS Access and create a new DB and add Linked tables and link them from the ODBC name that you just created.
This will let you view the data in the tables.
The other option, if this is MS SQL, will only work if you have the original database creation scripts. IF you have them, make a VM with the same version of SQL server and then run the script to create the database on that machine. Once that's done, you can shutdown the SQL service. Then copy in the mdf files from your evidence and then restart the SQL service.
At that point, you want to use the techniques I listed above to access the data.
I have done both of these technique before when I was a DBA on my production systems, the last version I tried it on was SQL Server 2005 - but the techniques should stay the same no matter.
If you can't run the VMs directly I'd definitely take the .mdf and .ldf files and attach them to a SQL instance. If the DBs are small (<10GB) they can be attached to a free 'Express' version of SQL Server if larger you may need one of the paid versions, although you can obtain 180 day evaluations of them too.
Thanks for the replies, greatly appreciated. The VMs are pwd protected. I can view them in Encase and extract the files. So I have a little more work and play ahead of me.
SQL Express is a free version from MS that allows one to view SQL server files. You need the .mdf and .ldf files. If that doesn't work, because the files are too big for SQL Express (the 2008 version allows up to 10gb), then you will need to get the full version of SQL Server. I do not believe that the ODBC drivers will work without the MDF/LDF file mounted and running in SQL Server. The ODBC driver facilitates a communication to the database through an SQL Server instance.
Once you have the database open, you can attempt to determine what data the tables hold. Hopefully the table names and field names accurately describe the data that they contain. But that is not always the case. Also understand that the data may not make complete sense without linking data in one table with data in another.
I would attempt to launch the VMs. If this is a civil matter, the opposing side should provide the passwords. If that isn't possible, you should be able to crack the passwords by extracting the SAM and system files and using a tool such as Passware. If you have rainbow tables, that crack will take a few minutes if not shorter.