In this short article, I'm going to explore three different vectors for attacking a SQL Server 2005 database instance. Before I begin, I must explain this isn't a hacking piece - rather, this post may benefit those who have lost, or not been given (and have a genuine need for) sysadmin credentials on a SQL instance. Perhaps you have taken over the DBA responsibility for databases previously under another company's control - or maybe the passwords have been lost.
Two of the attack vectors discussed will assume you have local administrator or domain administrator access to the server holding the database installation. The third assumes you have any access (and can remove data from the server, be it over the network or to USB device).
The first method to explore is the well known method of Dedicated Administrator Connection (DAC) access. This feature was introduced by Microsoft specifically to deal with, for example, the SA password being lost, or other administrative credentials. It assumes local/domain admin access to the server and SQL Server 2005. It does not work with 2008 or 2008 R2 (or Denali), simply because the exploit relies on SQL 2005 including the BUILTIN\Administrators group in the sysadmin role during build time by default.
Firstly, log in to the database server using your administrative credentials. Now open services.msc, and stop the following services (if they exist):
- SQL Server(INSTANCE Name)
- SQL Server Browser
- SQL Server Agent(INSTANCE Name)
- SQL Server VSS Writer
- SQL Server Analysis Services
- SQL Server Integration Services
- SQL Server Reporting Services
- Distributed Transaction Coordinator
Now right-click on SQL Server(INSTANCE Name) and go to Properties. // The following dialog box will appear (may vary depending on your OS):
Hit Stop if not already stopped, then in the Start Parameters box, type '-m' without the single quotes. Click Start, then OK.
Now open a command window. Use the following command to connect to SQL Server as an administrator, substituting (SERVER NAME\INSTANCE NAME) for the appropriate parameters (without the ( and ) marks):
SQLCMD -S (SERVER NAME)\(INSTANCE NAME) -E
This should present you with the prompt 1 and a pointed bracket. At this point you can create a new login. Issue:
CREATE LOGIN [DOMAIN\YOUR_NAME] FROM WINDOWS; GO
Substituting DOMAIN for your domain, YOUR_NAME for your login name. Include the square brackets. Now issue:
EXEC SP_ADDSRVROLEMEMBER @loginame='DOMAIN\YOUR_NAME' @rolename='sysadmin'; GO
This will associate your account with the sysadmin role.
Now exit, and restart all SQL Services including SQL Server without the -m parameter. When you log into SQL Server Management Studio and browse the Security - Logins tree, you will see your new login and right-clicking then going to Properties, Server Roles will reveal your new sysadmin access.
If this method fails, you may wish to try using a different method - hijacking SQL Server account groups. Be aware these may or may not exist in your configuration and in some configurations, such as Server 2008 R2 with Active Directory handling Local Users and Groups, the next approach may fail.
The second method of attack also relies on local or domain administrator access. You may find that for any reason (most often, BUILTIN\Administrators was deliberately excluded at build time), DAC fails. Or another connection is hoovering up the one available administrative connection (this happens when you try to use DAC using SSMS). If this is the case, we can hijack (if it exists for your system) any group specified as the SQL Server service account group.
Go to Users->Manage in your Windows operating system (varies by version). Find your local user account - if you don't have one, create one - then Properties, and in the Other field, find the MSSQL service account. // See below for an example - again, the dialog windows that appear will vary depending on your OS:
Now hit OK, log off and log in again. Try accessing SQL Server Management Studio using your normal Windows login credentials. You should find you have securityadmin or sysadmin role - check the Properties of your login and Server Roles to confirm.
The third and final attack vector I will discuss assumes only that you have access to the server with any credentials. Although this can be blocked, e.g. by folder security in Windows, you may find that the files are not protected in any way - especially in MS Windows Server 2000/2003. Or perhaps you have only console access to the server with a public login. Again, please don't abuse this method - it's fairly straightforward and has probably occurred to most DBAs at some point.
Open a command window on the server and issue CD \ . Then DIR /S *.mdf > whereAreThey.log. Open the log file in Notepad, this will show you the location of the .mdf files if they exist. If you can't find them, try a different drive, or verify you are on the correct server. // If done correctly, you'll get a text file with the list of .mdf files as per below. Ensure you use the /S parameter for a recursive search:
Now navigate to the directory containing the .mdf files. Then open services.msc and stop the SQL Server(INSTANCE Name) service. Copy the .mdf file from the directory to the media of your choice. Then restart the service.
Next, with your newly-copied .mdf file, install SQL Server 2005 on the target system (e.g. another workstation or server). Use default settings for everything. Alternatively, you can use an existing instance. You are able to use SQL Server Express Edition providing the database you are accessing is no more than 4GB in size. (10GB if using SQL Server 2008 R2 Express).
On your target system, open a command window. Issue:
SQLCMD -S (SERVER NAME)\(INSTANCE NAME) -E
Them issue these commands in the window:
1> SP_ATTACH_SINGLE_FILE_DB @dbname='(DBNAME)' @physname='(PATH)\(FILENAME).mdf'; 2> GO
This will bypass the need to include the transaction log file. Now go into SSMS and open the Databases tab in the Object Explorer window. You will see the database you attached. Assuming you have already been given, given yourself, or own the target database instance, you will have sysadmin credentials on the new database and full access to all data within.
Bear in mind that you will lose all configuration data for this DB normally stored in the master table, such as logins and certificates.
Be aware that this approach may fail if the source instance uses master key certificates (stored in the master database), or the source instance uses Transparent Data Encryption. If this is the case your only other option might be to use a third-party password recovery tool, such as a brute-force 'John The Ripper' or Hydra attack, social hacking or something targeted from Red Gate, etc.
One final tip - many application developers are sloppy coders. Check the registry and/or application settings for plaintext passwords in connection strings - ASP.NET pages are a good place to find these, likewise web.config in the .NET Framework root folder. Finding plaintext information in here will save you much time and effort. I have personally found plaintext information in the registry before and anyone who has set up ADO.NET will be familiar with web.config.