1. About MSSQL

---

• ❓ What is MSSQL
• 🔑 MSSQL Security Principals: Logins vs. Users
• 📡 Accessing MSSQL Server
• 🔐 MSSQL Authentication Mechanisms
• 🧾 Basic Enumeration Queries

---

❓ What is MSSQL

Microsoft SQL Server (MSSQL) is a relational database management system developed by Microsoft. It is widely used in enterprise environments, particularly within Active Directory (AD) ecosystems, where it manages and stores data critical to business operations. MSSQL is often used to handle everything from user data and application-specific records to logs and configuration settings.

In Active Directory environments, MSSQL typically serves as the backend database for various services, including Microsoft Exchange, SharePoint, and custom applications that integrate with AD. Due to its central role in storing sensitive data, MSSQL servers are prime targets for both administrative oversight and exploitation. Understanding how MSSQL operates within AD can provide security professionals with insight into potential attack vectors and enumeration methods.

🔑 MSSQL Security Principals: Logins vs. Users

In Microsoft SQL Server, logins and users are both types of security principals, but they operate at different levels:

• Logins exist at the server level and are used to authenticate users who want to connect to the SQL Server instance.
• Users exist at the database level and are used to control access within a specific database.

A single login can be associated with multiple users across different databases, but within each database, it can only be mapped to one user. This means that a login can have access to multiple databases but must have a corresponding user entry in each database to interact with it.

📡 Accessing MSSQL Server

Here are some of the common tools used to access and interact with an MSSQL server for enumeration or exploitation:

1. Using Impacket MSSQLClient
2. Microsoft SQL Server Management Studio (SSMS)
3. sqlcmd
4. dbeaver; Supports multiple databases, including MSSQL.
5. sqsh

🔐 MSSQL Authentication Mechanisms

MSSQL supports two authentication modes, which means that users can be created in Windows or the SQL Server:

Authentication Type	Description
Windows authentication mode	This 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 mode	Mixed mode supports authentication by Windows/Active Directory accounts and SQL Server. Username and password pairs are maintained within SQL Server.

This means that we can have three types of users to authenticate to MSSQL :

1. Active Directory Account - Authenticated through Windows AD.
2. Local Windows Account – User account local to the machine.
3. SQL Account – A user account stored in the SQL Server database.

When working with databases, we will typically perform two operations:

• Execute SQL Queries
• Execute Windows Commands

🧾 Basic Enumeration Queries

1. Enumerate logins as well as their server-level roles

    SELECT r.name, r.type_desc, r.is_disabled, sl.sysadmin, sl.securityadmin, sl.serveradmin, sl.setupadmin, sl.processadmin, sl.diskadmin, sl.dbcreator, sl.bulkadmin 
    FROM master.sys.server_principals r 
    LEFT JOIN master.sys.syslogins sl ON sl.sid = r.sid
    WHERE r.type IN ('S','E','X','U','G');
   

   

2. Enumerating Databases

    SELECT a.name AS 'database', b.name AS 'owner', is_trustworthy_on
    FROM sys.databases a
    JOIN sys.server_principals b ON a.owner_sid = b.sid;
   

   

3. Enumerating (Database) Users

    USE webshop;
    EXECUTE sp_helpuser;
   

   

   This stored procedure helps list the users in a particular database along with their roles and permissions sp-helpuser.