Applications often need access to data without needing access to everything. Social Security numbers, email addresses, and phone numbers are common examples. Storing them is required. Exposing them broadly is not. This is especially valuable when exposing a database to an AI agent through MCP servers like SQL MCP Server, where safety and reversibility matter.

Learn more about SQL MCP Server

Azure SQL includes built-in features that let the database protect sensitive values automatically. The application does not decide what is visible. The database does.

Working demo https://gist.github.com

What We Are Building

A table that stores sensitive data and rules that mask that data.

Step 1: Create the User

CREATE USER AppUser WITHOUT LOGIN;
GO

Step 2: Create the Table

CREATE TABLE dbo.Customers
(
    Id        INT IDENTITY(1,1) PRIMARY KEY,
    FullName  NVARCHAR(200) NOT NULL,
    SSN       CHAR(11) NOT NULL,
    Email     NVARCHAR(256) NOT NULL,
    Phone     NVARCHAR(20) NOT NULL
);
GO

INSERT INTO dbo.Customers (FullName, SSN, Email, Phone) VALUES
    ('Alex Johnson', '123-45-6789', 'alex@example.com', '555-123-4567'),
    ('Maria Lopez', '987-65-4321', 'maria@example.com', '555-987-6543');
GO

Step 3: Apply Data Masking

Dynamic Data Masking hides data at query time. The data stays unchanged in storage.

Mask Columns

-- Mask SSN (123-45-6789 -> XXX-XX-6789)
ALTER TABLE dbo.Customers
ALTER COLUMN SSN
ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)');
GO

-- Mask Email (maria.lopez@contoso.com -> mXXX@XXXXXXX.com)
ALTER TABLE dbo.Customers
ALTER COLUMN Email
ADD MASKED WITH (FUNCTION = 'email()');
GO

-- Mask Phone (123-456-7890 -> XXX-XXX-7890)
ALTER TABLE dbo.Customers
ALTER COLUMN Phone
ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)');
GO

Dynamic Data Masking includes several built-in masking functions. The email() function used here is built in and requires no user-defined logic. It preserves the first character of the email name and the top-level domain while masking the rest. Other built-in options include default() for full masking, partial() for custom prefix and suffix preservation, and random() for numeric data. These functions are applied at query time and do not change the underlying stored values.

Step 4: See It Work

Admin view

SELECT * FROM dbo.Customers;

Result

User view

EXECUTE AS USER = 'AppUser';
SELECT * FROM dbo.Customers;
REVERT;

Result

The same rows are returned, but sensitive values are masked automatically for the application user.

Why This Is Safe

• Masking is enforced by the database
• Applications cannot bypass it
• Storage remains intact for audits and reporting
• Admins retain full access
• Reduces accidental exposure
• Zero application changes

When to Use Something Stronger

Dynamic Data Masking is not encryption. Privileged users can still see data.