mssql-enumeration.md (4829B)
1 --- 2 title: "MSSQL - Database Enumeration" 3 section: "Databases" 4 sectionSlug: "databases" 5 sourcePath: "docs/databases/mssql-enumeration.md" 6 sourceUrl: "https://github.com/swisskyrepo/InternalAllTheThings/blob/203bb0c0b290/docs/databases/mssql-enumeration.md" 7 sha: "203bb0c0b290" 8 isIndex: false 9 --- 10 11 # MSSQL - Database Enumeration 12 13 ## Summary 14 15 - [Tools](#tools) 16 - [Identify Instances and Databases](#identify-instances-and-databases) 17 - [Discover Local SQL Server Instances](#discover-local-sql-server-instances) 18 - [Discover Domain SQL Server Instances](#discover-domain-sql-server-instances) 19 - [Discover Remote SQL Server Instances](#discover-remote-sql-server-instances) 20 - [Identify Encrypted databases](#identify-encrypted-databases) 21 - [Version Query](#version-query) 22 - [Identify Users and Roles](#identify-users-and-roles) 23 - [Identify Sensitive Information](#identify-sensitive-information) 24 - [Get Tables from a Specific Database](#get-tables-from-a-specific-database) 25 - [Gather 5 Entries from Each Column](#gather-5-entries-from-each-column) 26 - [Gather 5 Entries from a Specific Table](#gather-5-entries-from-a-specific-table) 27 - [Dump common information from server to files](#dump-common-information-from-server-to-files) 28 29 ## Tools 30 31 - [NetSPI/PowerUpSQL](https://github.com/NetSPI/PowerUpSQL) - A PowerShell Toolkit for Attacking SQL Server 32 - [skahwah/SQLRecon](https://github.com/skahwah/SQLRecon/) - A C# MS SQL toolkit designed for offensive reconnaissance and post-exploitation. 33 34 ## Identify Instances and Databases 35 36 ### Discover Local SQL Server Instances 37 38 ```ps1 39 Get-SQLInstanceLocal 40 ``` 41 42 ### Discover Domain SQL Server Instances 43 44 ```ps1 45 Get-SQLInstanceDomain -Verbose 46 # Get Server Info for Found Instances 47 Get-SQLInstanceDomain | Get-SQLServerInfo -Verbose 48 # Get Database Names 49 Get-SQLInstanceDomain | Get-SQLDatabase -NoDefaults 50 ``` 51 52 ### Discover Remote SQL Server Instances 53 54 ```ps1 55 Get-SQLInstanceBroadcast -Verbose 56 Get-SQLInstanceScanUDPThreaded -Verbose -ComputerName SQLServer1 57 ``` 58 59 ### Identify Encrypted databases 60 61 Note: These are automatically decrypted for admins 62 63 ```ps1 64 Get-SQLDatabase -Username sa -Password Password1234 -Instance "<DBSERVERNAME\DBInstance>" -Verbose | Where-Object {$_.is_encrypted -eq "True"} 65 ``` 66 67 ### Version Query 68 69 ```ps1 70 Get-SQLInstanceDomain | Get-Query "select @@version" 71 ``` 72 73 ## Identify Users and Roles 74 75 - Query Current User & determine if the user is a sysadmin 76 77 ```sql 78 select suser_sname() 79 Select system_user 80 select is_srvrolemember('sysadmin') 81 ``` 82 83 - Current Role 84 85 ```sql 86 select user 87 ``` 88 89 - All Logins on Server 90 91 ```sql 92 Select * from sys.server_principals where type_desc != 'SERVER_ROLE' 93 ``` 94 95 - All Database Users for a Database 96 97 ```sql 98 Select * from sys.database_principals where type_desc != 'database_role'; 99 ``` 100 101 - List All Sysadmins 102 103 ```sql 104 SELECT name,type_desc,is_disabled FROM sys.server_principals WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1 105 ``` 106 107 - List All Database Roles 108 109 ```sql 110 SELECT DB1.name AS DatabaseRoleName, 111 isnull (DB2.name, 'No members') AS DatabaseUserName 112 FROM sys.database_role_members AS DRM 113 RIGHT OUTER JOIN sys.database_principals AS DB1 114 ON DRM.role_principal_id = DB1.principal_id 115 LEFT OUTER JOIN sys.database_principals AS DB2 116 ON DRM.member_principal_id = DB2.principal_id 117 WHERE DB1.type = 'R' 118 ORDER BY DB1.name; 119 ``` 120 121 ## Identify Sensitive Information 122 123 ### Get Tables from a Specific Database 124 125 ```ps1 126 Get-SQLInstanceDomain | Get-SQLTable -DatabaseName <DBNameFromGet-SQLDatabaseCommand> -NoDefaults 127 Get Column Details from a Table 128 Get-SQLInstanceDomain | Get-SQLColumn -DatabaseName <DBName> -TableName <TableName> 129 ``` 130 131 - Current database 132 133 ```sql 134 select db_name() 135 ``` 136 137 - List all tables 138 139 ```sql 140 select table_name from information_schema.tables 141 ``` 142 143 - List all databases 144 145 ```sql 146 select name from master..sysdatabases 147 ``` 148 149 - List server informations 150 151 ```sql 152 SELECT * FROM sys.configurations 153 ``` 154 155 ### Gather 5 Entries from Each Column 156 157 ```ps1 158 Get-SQLInstanceDomain | Get-SQLColumnSampleData -Keywords "<columnname1,columnname2,columnname3,columnname4,columnname5>" -Verbose -SampleSize 5 159 ``` 160 161 ### Gather 5 Entries from a Specific Table 162 163 ```ps1 164 Get-SQLQuery -Instance "<DBSERVERNAME\DBInstance>" -Query 'select TOP 5 * from <DatabaseName>.dbo.<TableName>' 165 ``` 166 167 ### Dump common information from server to files 168 169 ```ps1 170 Invoke-SQLDumpInfo -Verbose -Instance SQLSERVER1\Instance1 -csv 171 ``` 172 173 ## References 174 175 - [PowerUpSQL Cheat Sheet & SQL Server Queries - Leo Pitt](https://medium.com/@D00MFist/powerupsql-cheat-sheet-sql-server-queries-40e1c418edc3) 176 - [PowerUpSQL Cheat Sheet - Scott Sutherland](https://github.com/NetSPI/PowerUpSQL/wiki/PowerUpSQL-Cheat-Sheet)