MSSQL Enumeration

Overview

Microsoft SQL (MSSQL) is Microsoft's SQL-based relational database management system. Unlike MySQL, which is open-source, MSSQL is closed source and was initially written to run on Windows operating systems. It is popular among database administrators and developers when building applications that run on Microsoft's .NET framework due to its strong native support for .NET.

Key Characteristics:

Port 1433: Default MSSQL port
Authentication: Windows Authentication or SQL Server Authentication
Default Instance: MSSQLSERVER
Protocol: Tabular Data Stream (TDS)
Platform: Primarily Windows (Linux/MacOS versions available)

MSSQL Clients

SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) comes as a feature that can be installed with the MSSQL install package or downloaded separately. Key points:

Commonly installed on the server for initial configuration
Can be installed on any system for remote database management
May contain saved credentials on vulnerable systems
Provides full database management capabilities

Alternative MSSQL Clients

Client

Description

mssql-cli

Command-line interface for MSSQL

SQL Server PowerShell

PowerShell module for MSSQL

HeidiSQL

Lightweight GUI client

SQLPro

Professional database client

Impacket's mssqlclient.py

Python-based client (preferred for pentesting)

Locating Impacket MSSQL Client

# Find impacket mssqlclient location
locate mssqlclient

# Common locations:
/usr/bin/impacket-mssqlclient
/usr/share/doc/python3-impacket/examples/mssqlclient.py

Default System Databases

MSSQL has default system databases that help understand the structure of all databases hosted on a target server:

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

msdb

Used by SQL Server Agent to schedule jobs & alerts

tempdb

Stores temporary objects

resource

Read-only database containing system objects included with SQL server

Default Configuration

Initial Setup

When an admin initially installs and configures MSSQL to be network accessible:

Service Account: SQL service runs as NT SERVICE\MSSQLSERVER
Authentication: Windows Authentication by default
Encryption: Not enforced by default
Access Control: Uses Windows OS for authentication processing

Authentication Methods

Windows Authentication:
- Uses local SAM database or domain controller
- Integrates with Active Directory
- Can lead to privilege escalation if compromised
SQL Server Authentication:
- Uses database-specific user accounts
- Independent of Windows authentication

Dangerous Settings

Common misconfigurations that can lead to security issues:

Setting

Risk Level

Description

No encryption

High

MSSQL clients not using encryption to connect

Self-signed certificates

Medium

Can be spoofed during attacks

Named pipes enabled

Medium

Additional attack surface

Default SA credentials

Critical

Weak or unchanged SA account passwords

SA account enabled

High

Admins may forget to disable default SA account

Footprinting the Service

Comprehensive Nmap Scan

# Complete MSSQL enumeration with all scripts
sudo nmap --script ms-sql-info,ms-sql-empty-password,ms-sql-xp-cmdshell,ms-sql-config,ms-sql-ntlm-info,ms-sql-tables,ms-sql-hasdbaccess,ms-sql-dac,ms-sql-dump-hashes --script-args mssql.instance-port=1433,mssql.username=sa,mssql.password=,mssql.instance-name=MSSQLSERVER -sV -p 1433 target

Example Nmap Output Analysis

Starting Nmap 7.91 ( https://nmap.org ) at 2021-11-08 09:40 EST
Nmap scan report for target
Host is up (0.15s latency).

PORT     STATE SERVICE  VERSION
1433/tcp open  ms-sql-s Microsoft SQL Server 2019 15.00.2000.00; RTM
| ms-sql-ntlm-info: 
|   Target_Name: SQL-01
|   NetBIOS_Domain_Name: SQL-01
|   NetBIOS_Computer_Name: SQL-01
|   DNS_Domain_Name: SQL-01
|   DNS_Computer_Name: SQL-01
|_  Product_Version: 10.0.17763

Host script results:
| ms-sql-dac: 
|_  Instance: MSSQLSERVER; DAC port: 1434 (connection failed)
| ms-sql-info: 
|   Windows server name: SQL-01
|   target\MSSQLSERVER: 
|     Instance name: MSSQLSERVER
|     Version: 
|       name: Microsoft SQL Server 2019 RTM
|       number: 15.00.2000.00
|       Product: Microsoft SQL Server 2019
|       Service pack level: RTM
|       Post-SP patches applied: false
|     TCP port: 1433
|     Named pipe: \\target\pipe\sql\query
|_    Clustered: false

Key Information Extracted:

Hostname: SQL-01
Instance: MSSQLSERVER
Version: Microsoft SQL Server 2019 RTM (15.00.2000.00)
Named Pipes: Enabled (\target\pipe\sql\query)
Clustering: Not clustered

Metasploit MSSQL Ping Scanner

# Use Metasploit auxiliary scanner
msf6 > use auxiliary/scanner/mssql/mssql_ping
msf6 auxiliary(scanner/mssql/mssql_ping) > set rhosts target
msf6 auxiliary(scanner/mssql/mssql_ping) > run

# Example output:
[*] target:       - SQL Server information for target:
[+] target:       -    ServerName      = SQL-01
[+] target:       -    InstanceName    = MSSQLSERVER
[+] target:       -    IsClustered     = No
[+] target:       -    Version         = 15.0.2000.5
[+] target:       -    tcp             = 1433
[+] target:       -    np              = \\SQL-01\pipe\sql\query
[*] target:       - Scanned 1 of 1 hosts (100% complete)
[*] Auxiliary module execution completed

Connecting with mssqlclient.py

Windows Authentication

# Connect using Windows authentication
python3 mssqlclient.py Administrator@target -windows-auth

# Example connection output:
Impacket v0.9.22 - Copyright 2020 SecureAuth Corporation

Password:
[*] 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(SQL-01): Line 1: Changed database context to 'master'.
[*] INFO(SQL-01): Line 1: Changed language setting to us_english.
[*] ACK: Result: 1 - Microsoft SQL Server (150 7208) 
[!] Press help for extra shell commands

Basic Database Enumeration

# List all databases
SQL> select name from sys.databases

name                                                                                                                               
--------------------------------------------------------------------------------------
master                                                                                                                             
tempdb                                                                                                                             
model                                                                                                                              
msdb                                                                                                                               
Transactions

SQL Server Authentication

# Connect with SQL Server authentication
python3 mssqlclient.py sa@target

# Connect with specific credentials
python3 mssqlclient.py backdoor@target -windows-auth

Advanced Enumeration

Database Information Gathering

# Get MSSQL version
SQL> SELECT @@version;

# Get server information
SQL> SELECT @@servername;

# Get database information
SQL> SELECT name, database_id FROM sys.databases;

# Get user information
SQL> SELECT name FROM sys.server_principals WHERE type = 'S';

# Get database permissions
SQL> SELECT * FROM sys.database_permissions;

System Information

# Get system configuration
SQL> SELECT name, value FROM sys.configurations WHERE name = 'xp_cmdshell';

# Get linked servers
SQL> SELECT * FROM sys.servers;

# Get database files
SQL> SELECT name, physical_name FROM sys.master_files;

HTB Academy Lab Questions

Question 1: Hostname Detection

Task: Enumerate the target and list the hostname of MSSQL server

Solution:

# Step 1: Comprehensive nmap scan
sudo nmap --script ms-sql-info,ms-sql-ntlm-info -p1433 target

# Step 2: Extract hostname from nmap output
# Look for:
# |   Target_Name: SQL-01
# |   Windows server name: SQL-01

# Answer: SQL-01

Question 2: Non-Default Database Discovery

Task: Connect using account (backdoor:Password1) and list non-default database

Solution:

# Step 1: Connect with provided credentials
python3 mssqlclient.py backdoor@target -windows-auth
# Password: Password1

# Step 2: List all databases
SQL> select name from sys.databases;

# Step 3: Identify non-default databases
# Default databases: master, tempdb, model, msdb, resource
# Non-default database: Look for custom database names

# Example result: "Employees" or "Transactions"

Enumeration Techniques

1. Service Detection

# Basic MSSQL detection
nmap -p1433 -sV target

# Comprehensive enumeration
nmap -p1433 --script ms-sql-info,ms-sql-config,ms-sql-tables target

2. Authentication Testing

# Test Windows authentication
impacket-mssqlclient administrator@target -windows-auth

# Test SQL Server authentication
impacket-mssqlclient sa@target

# Test with specific credentials
impacket-mssqlclient backdoor@target -windows-auth

3. Database Analysis

# List databases
SELECT name FROM sys.databases;

# Use specific database
USE database_name;

# List tables
SELECT name FROM sys.tables;

# Query table data
SELECT * FROM table_name;

Security Assessment

Common Vulnerabilities

Default Credentials: SA account with weak passwords
Windows Authentication: Compromised domain accounts
Missing Encryption: Plaintext communication
Excessive Permissions: Over-privileged database users
Outdated Software: Unpatched MSSQL instances

Enumeration Checklist

Attack Vectors

1. Credential-based Access

# Brute force SA account
hydra -l sa -P passwords.txt mssql://target

# Password spraying
crackmapexec mssql target -u users.txt -p passwords.txt

2. Command Execution

# Enable xp_cmdshell
SQL> EXEC sp_configure 'show advanced options', 1;
SQL> RECONFIGURE;
SQL> EXEC sp_configure 'xp_cmdshell', 1;
SQL> RECONFIGURE;

# Execute commands
SQL> EXEC xp_cmdshell 'whoami';

3. Data Extraction

# Extract sensitive data
SQL> SELECT * FROM sys.sql_logins;

# Access system databases
SQL> USE master;
SQL> SELECT * FROM sys.server_principals;

Tools and Techniques

Essential Tools

# Impacket mssqlclient
impacket-mssqlclient user@target -windows-auth

# Nmap scripts
nmap --script ms-sql-* target

# Metasploit modules
use auxiliary/scanner/mssql/mssql_ping
use auxiliary/scanner/mssql/mssql_login

Defensive Measures

Security Best Practices

Disable SA account: Use Windows Authentication only
Enable encryption: Force SSL/TLS connections
Least privilege: Restrict database permissions
Regular updates: Apply security patches
Monitor access: Enable audit logging
Network security: Firewall restrictions

PreviousMySQL Enumeration NextOracle TNS Enumeration

Last updated 3 months ago