The following post lists and describes various useful stored procedures and port information for MS SQL. The information is relevant for all versions unless stated (there may be a couple of mistakes, so corrections are welcome). The information is from many different sources including MS Technet, various books and several people’s brains (mostly mine - such as it is!). Its main use is as a learning tool or reference for performing SQL injection attacks.
Important Stored procedures
sp_columns – returns column names of tables
sp_configure – Returns internal database settings. Allows you to specific a particular setting and retrieve the value.
sp_dboption – Views or sets user configurable database options
sp_who2 and sp_who – Displays usernames, the client from which they’re connected, the application used to connect to the database, the command executed on the database and several other pieces of info.
Parameterised Extended stored procedures
xp_cmdshell - The default current directory is %SystemRoot%\System32. This procedure is disabled in SQL 2005 onwards by default, but can be re-enabled remotely by running the following command (either as a straight query or as part of an injection):
;exec sp_configure 'show advanced options',1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell',1;RECONFIGURE
SQLmap (--os-cmd) will do this automatically, but I haven’t had much success with it on real-world test.
xp_regread – Reads a registry value.
xp_servicecontrol – Stops or starts a windows service.
xp_terminate_service – Kills a process based on its process ID.
Non-parameterised Extended Stored Procedures
xp_loginconfig – Displays login information, particularly the login mode (mixed etc) and default login.
xp_logininfo – Shows currently logged in accounts (NTLM accounts).
xp_msver – Lists SQL version and platform info.
xp_enumdsn – Enumerates ODBC data sources.
xp_enumgroups – Enumerates Windows groups.
System Table Objects
Many of the system tables from earlier releases of SQL Server are now implemented as a set of views. These views are known as compatibility views, and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available in SQL Server 2000. However, the compatibility views do not expose any of the metadata related to features that are introduced in SQL Server 2005 and later.
syscolumns (2000) – All column names and stored procedures for the current database
sysusers – All users who can manipulate the database
sysfiles – The file and pathname for the current database and its log file.
systypes – Data types defined by SQL or users.
Master DB Tables
sysconfigures – Current DB config settings.
sysdatabases – Lists all DBs on server
sysdevices –Enumerates devices used for DB
sysxlogins (2000) – Enumerates user info for each permitted user of the database
sql_logins (2005) – Enumerates user info for each permitted user of the database
sysremotelogins – Enumerates user info for all users permitted to remote access DB
The default ports for MS SQL are TCP/1433 and UDP/1434. However the service can be deployed ‘hidden’ on 2433 (this is MS’s idea of hiding!).
UDP 1434 was introduced in SQL 2000 and provides a referral service for multiple instances of SQL running on the same machine. The service listens on this port and returns the IP address and port number of the requested database.
Below is a script from MS TechNet showing the ‘fix’ for opening ports on Windows Firewall for MS SQL 2008. This is pretty interesting!
@echo ========= SQL Server Ports ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer"
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection"
@echo Enabling conventional SQL Server Service Broker port 4022
netsh firewall set portopening TCP 4022 "SQL Service Broker"
@echo Enabling Transact-SQL Debugger/RPC port 135
netsh firewall set portopening TCP 135 "SQL Debugger/RPC"
@echo ========= Analysis Services Ports ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services"
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser"
@echo ========= Misc Applications ==============
@echo Enabling HTTP port 80
netsh firewall set portopening TCP 80 "HTTP"
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL"
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser"
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE
It’s also worth bearing these other ports in mind when port scanning or enumerating instances of MS SQL.