> For the complete documentation index, see [llms.txt](https://cajac.gitbook.io/ctf-notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://cajac.gitbook.io/ctf-notes/misc/databases/sql-databases/microsoft-sql-server.md).
# Microsoft SQL Server
select name from sys.databases;Microsoft SQL Server is a proprietary [relational database management system](https://en.wikipedia.org/wiki/Relational_database_management_system) developed by Microsoft using [Structured Query Language](https://en.wikipedia.org/wiki/SQL) (SQL, often pronounced "sequel"). As a [database server](https://en.wikipedia.org/wiki/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 (including the Internet). Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.
The default port is 1433/TCP.
## Impacket's mssqlclient.py
### Connect to the server
To connect to the database server with known Windows credentials
```bash
impacket-mssqlclient sql_svc:M3g4c0rp123@$TARGET_IP -windows-auth
```
Example run
```bash
┌──(kali㉿kali)-[/mnt/…/Hack_the_Box/HTB_Starting_Point/Tier_2/Archetype]
└─$ impacket-mssqlclient sql_svc:M3g4c0rp123@$TARGET_IP -windows-auth
Impacket v0.12.0 - Copyright Fortra, LLC and its affiliated companies
[*] Encryption required, switching to TLS
[*] ENVCHANGE(DATABASE): Old Value: master, New Value: master
[*] ENVCHANGE(LANGUAGE): Old Value: , New Value: us_english
[*] ENVCHANGE(PACKETSIZE): Old Value: 4096, New Value: 16192
[*] INFO(ARCHETYPE): Line 1: Changed database context to 'master'.
[*] INFO(ARCHETYPE): Line 1: Changed language setting to us_english.
[*] ACK: Result: 1 - Microsoft SQL Server (140 3232)
[!] Press help for extra shell commands
SQL (ARCHETYPE\sql_svc dbo@master)>
```
### Disconnect from the server
To disconnect from the server
```
exit
```
## Builtin commands
The Impacket `mssqlclient.py` has a number of nice builtin commands
```
SQL (sa dbo@master)> help
lcd {path} - changes the current local directory to {path}
exit - terminates the server process (and this session)
enable_xp_cmdshell - you know what it means
disable_xp_cmdshell - you know what it means
enum_db - enum databases
enum_links - enum linked servers
enum_impersonate - check logins that can be impersonated
enum_logins - enum login users
enum_users - enum current db users
enum_owner - enum db owner
exec_as_user {user} - impersonate with execute as user
exec_as_login {login} - impersonate with execute as login
xp_cmdshell {cmd} - executes cmd using xp_cmdshell
xp_dirtree {path} - executes xp_dirtree on the path
sp_start_job {cmd} - executes cmd using the sql server agent (blind)
use_link {link} - linked server to use (set use_link localhost to go back to local or use_link .. to get back one step)
! {cmd} - executes a local shell cmd
upload {from} {to} - uploads file {from} to the SQLServer host {to}
download {from} {to} - downloads file from the SQLServer host {from} to {to}
show_query - show query
mask_query - mask query
```
### Enumeration
#### Enumerate databases
```
enum_db
```
Example run
```
SQL (sa dbo@master)> enum_db
name is_trustworthy_on
------ -----------------
master 0
tempdb 0
model 0
msdb 1
SQL (sa dbo@master)>
```
#### Enumerate DB Users
```
enum_users
```
Example run
```
SQL (sa dbo@master)> enum_users
UserName RoleName LoginName DefDBName DefSchemaName UserID SID
--------------------------------- -------- --------------------------------- --------- ------------- ---------- -------------------------------------------------------------------
##MS_AgentSigningCertificate## public ##MS_AgentSigningCertificate## master NULL b'6 ' b'0106000000000009010000004c1967c27feb2ead332894c5a0779eae202847c8'
##MS_PolicyEventProcessingLogin## public ##MS_PolicyEventProcessingLogin## master dbo b'5 ' b'5681cce7a1f1ff41b2f95ced7d792e70'
dbo db_owner sa master dbo b'1 ' b'01'
guest public NULL NULL guest b'2 ' b'00'
INFORMATION_SCHEMA public NULL NULL NULL b'3 ' NULL
sys public NULL NULL NULL b'4 ' NULL
SQL (sa dbo@master)>
```
#### Enumerate logins that can be impersonated
```
enum_impersonate
```
Example run
```
SQL (sa dbo@master)> enum_impersonate
execute as database permission_name state_desc grantee grantor
---------- -------- --------------- ---------- -------- ----------------------------
b'USER' msdb IMPERSONATE GRANT dc_admin MS_DataCollectorInternalUser
SQL (sa dbo@master)>
```
## Enumeration
### Enumerate databases
To enumerate available databases
```sql
SELECT name FROM sys.databases;
```
or
```sql
SELECT name FROM master.dbo.sysdatabases;
```
The *master*, *tempdb*, *model*, and *msdb* are default databases.
### Current database
To get the name of the current database
```sql
SELECT DB_NAME();
```
### Enumerate schemas
You enumerate schemas from the `app` database by querying the corresponding *information\_schema*
```sql
SELECT schema_name FROM app.information_schema.schemata;
```
The schema we want is usually `dbo`.
### Enumerate tables
You enumerate table information from the `offsec` database by querying the corresponding *information\_schema*
```sql
SELECT * FROM offsec.information_schema.tables;
```
Or just the table names with
```sql
SELECT table_name FROM offsec.information_schema.tables;
```
### Enumerate columns
You enumerate column information from the `exercise` database and the `secrets` table
```sql
SELECT * FROM exercise.information_schema.columns WHERE table_name='secrets';
```
Or only the column names with
```sql
SELECT column_name FROM exercise.information_schema.columns WHERE table_name='secrets';
```
### Current version
To show the current version
```sql
select @@version;
```
### First user
To select the first user from the `sysusers` table inside the master database
```sql
select top 1 * from master.dbo.sysusers;
```
## Enable xp\_cmdshell
To enable [xp\_cmdshell](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-ver15) and be able to execute Windows commands from inside the SQL server
```sql
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
```
## Resources
Impacket for Pentester: MSSQL Exploitation - Hacking Articles:
Microsoft SQL Server - Wikipedia:
mssqlcleint.py - Impacket - GitHub:
MSSQL (Microsoft SQL Server) - Hackviser:
MSSQL Injection Cheat Sheet - pentestmonkey:
Pentesting MSSQL - Microsoft SQL Server - HackTricks:
xp\_cmdshell (Transact-SQL) - Microsodft Learn: