MSSQL

MSSQL (Microsoft SQL)

SQLCMD allows SQL queries to be run through the windows command prompt or even remotely from another machine.

Way to connect to MSSQL:

impacket-mssqlclient Administrator:[email protected] -windows-auth

breakdown:

Administrator:[email protected]
{username}   :{passwd}@{IP_addr}

check the version:

SELECT @@version;

When using a SQL Server command line tool like sqlcmd, we must submit our SQL statement ending with a semicolon followed by GO on a separate line. However, when running the command remotely, we can omit the GO statement since it's not part of the MSSQL TDS protocol.

list of schema in database:

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

list all available database:

SELECT name FROM sys.databases;

master, tempdb, model, and msdb are default databases

To get a list of tables

SELECT name FROM sys.tables; 							

To get table column names:

SELECT * FROM {database-name}.information_schema.tables;

To get all the values from the table:

select * from offsec.dbo.users;

breakdown

select * from offsec            . dbo            .     users;
              {TABLE_CATALOG}     {TABLE_SCHEMA}      {TABLE_NAME}

Below is an expanded set of notes and useful queries for MSSQL enumeration and usage, including commonly used views, functions, and procedures. This should help fill in the gaps and provide a more comprehensive reference.

---

Connecting and Running Commands

Using sqlcmd Locally:

sqlcmd -S <server_name_or_ip> -U <username> -P <password>

• -S specifies the server name or IP (e.g., localhost or 192.168.50.18)

• -U specifies the username

• -P specifies the password

• Within sqlcmd, end statements with a semicolon and use GO on a new line to execute. Example:

  SELECT @@VERSION;
  GO

Using impacket-mssqlclient (Remote):

impacket-mssqlclient Administrator:[email protected] -windows-auth

• No need to use GO when running queries this way, as GO is a sqlcmd batch terminator and not part of the TDS protocol.

---

Basic Information and System Details

Check the Server Version:

SELECT @@VERSION;

Check Current User / Login:

SELECT SYSTEM_USER;       -- Current server login
SELECT USER_NAME();       -- Current database user

Check the Current Database:

SELECT DB_NAME();

List All Available Databases:

SELECT name FROM sys.databases;

Default system databases are usually: master, tempdb, model, and msdb.

Switch to a Specific Database:

USE offsec;

---

Enumerating Schemas, Tables, and Columns

List All Schemas in the Current Database:

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

List All Tables in the Current Database:

SELECT name FROM sys.tables;

List All Tables in Another Database:

SELECT * FROM [other_database].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';

List All Columns for a Specific Table:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users';

If you need to qualify with a specific database and schema:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM [offsec].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users' AND TABLE_SCHEMA='dbo';

Select All Data From a Table:

SELECT * FROM offsec.dbo.users;

---

Enumerating Users, Logins, and Roles

Enumerating Users, Logins, and Roles

List All Server-Level Principals (Logins):

SELECT name, type_desc, is_disabled FROM sys.server_principals;

List All Database Users:

SELECT name, type_desc FROM sys.database_principals;

List All Roles in the Current Database:

SELECT * FROM sys.database_role_members;

Check Permissions of the Current User:

SELECT * FROM fn_my_permissions(NULL, 'SERVER');
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

---

System Stored Procedures and Functions

System Stored Procedures and Functions

Show All Databases (System Procedure):

EXEC sp_helpdb;

Show Tables in the Current Database:

EXEC sp_tables;

Get Detailed Info About a Specific Table:

EXEC sp_help 'users';

Get Column Details for a Specific Table:

EXEC sp_columns 'users';

Find the Current Database Name (again, as a function):

SELECT DB_NAME();

---

Checking Active Connections and Sessions

Checking Active Connections and Sessions

View Current Sessions:

EXEC sp_who;     -- Basic session info
EXEC sp_who2;    -- Extended session info (not documented by Microsoft, but common)

View Currently Running Queries:

SELECT 
    session_id, blocking_session_id, wait_type, status, command, sql_handle
FROM sys.dm_exec_requests
WHERE session_id > 50;

---

Advanced Features

(Might Require Elevated Privileges)

Enable xp_cmdshell (if you have the right permissions):

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

Use xp_cmdshell to Run OS Commands (If Enabled):

EXEC xp_cmdshell 'whoami';

Directory Enumeration Using xp_dirtree:

EXEC xp_dirtree 'C:\', 1, 1;

---

Other Useful System Views

• sys.syslogins (deprecated, but sometimes still accessible) can show logins.

• sys.sql_logins can show SQL logins with hashed passwords (in certain conditions).

• sys.server_principals and sys.database_principals for detailed user and login info.

---

Notes on Authentication and Connections

• Integrated Authentication / Windows Auth: If you have a valid domain user that can connect with Windows auth:

  sqlcmd -S <server> -E

  The -E flag indicates trusted connection using current Windows credentials.

• Port Changes: By default, SQL Server uses port 1433. If the instance is listening on a different port, specify it as -S <server>,<port> (e.g., sqlcmd -S 192.168.50.18,1433).