MSSQL - Database Enumeration

contents

mssql-enumeration.md

Summary#

Tools
Identify Instances and Databases
Identify Users and Roles
Identify Sensitive Information

Tools#

NetSPI/PowerUpSQL - A PowerShell Toolkit for Attacking SQL Server
skahwah/SQLRecon - A C# MS SQL toolkit designed for offensive reconnaissance and post-exploitation.

Identify Instances and Databases#

Discover Local SQL Server Instances#

Get-SQLInstanceLocal

Discover Domain SQL Server Instances#

Get-SQLInstanceDomain -Verbose# Get Server Info for Found InstancesGet-SQLInstanceDomain | Get-SQLServerInfo -Verbose# Get Database NamesGet-SQLInstanceDomain | Get-SQLDatabase -NoDefaults

Discover Remote SQL Server Instances#

Get-SQLInstanceBroadcast -VerboseGet-SQLInstanceScanUDPThreaded -Verbose -ComputerName SQLServer1

Identify Encrypted databases#

Note: These are automatically decrypted for admins

Get-SQLDatabase -Username sa -Password Password1234 -Instance "<DBSERVERNAME\DBInstance>" -Verbose | Where-Object {$_.is_encrypted -eq "True"}

Version Query#

Get-SQLInstanceDomain | Get-Query "select @@version"

Identify Users and Roles#

Query Current User & determine if the user is a sysadmin

select suser_sname()Select system_userselect is_srvrolemember('sysadmin')

Current Role
```
select user
```

All Logins on Server

Select * from sys.server_principals where type_desc != 'SERVER_ROLE'

All Database Users for a Database

Select * from sys.database_principals where type_desc != 'database_role';

List All Sysadmins

SELECT name,type_desc,is_disabled FROM sys.server_principals WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1

List All Database Roles

SELECT DB1.name AS DatabaseRoleName,isnull (DB2.name, 'No members') AS DatabaseUserNameFROM sys.database_role_members AS DRMRIGHT OUTER JOIN sys.database_principals AS DB1ON DRM.role_principal_id = DB1.principal_idLEFT OUTER JOIN sys.database_principals AS DB2ON DRM.member_principal_id = DB2.principal_idWHERE DB1.type = 'R'ORDER BY DB1.name;

Identify Sensitive Information#

Get Tables from a Specific Database#

Get-SQLInstanceDomain | Get-SQLTable -DatabaseName <DBNameFromGet-SQLDatabaseCommand> -NoDefaultsGet Column Details from a TableGet-SQLInstanceDomain | Get-SQLColumn -DatabaseName <DBName> -TableName <TableName>

Current database
```
select db_name()
```

List all tables

select table_name from information_schema.tables

List all databases
```
select name from master..sysdatabases
```
List server informations
```
SELECT * FROM sys.configurations
```

Gather 5 Entries from Each Column#

Get-SQLInstanceDomain | Get-SQLColumnSampleData -Keywords "<columnname1,columnname2,columnname3,columnname4,columnname5>" -Verbose -SampleSize 5

Gather 5 Entries from a Specific Table#

Get-SQLQuery -Instance "<DBSERVERNAME\DBInstance>" -Query 'select TOP 5 * from <DatabaseName>.dbo.<TableName>'

Dump common information from server to files#

Invoke-SQLDumpInfo -Verbose -Instance SQLSERVER1\Instance1 -csv