{"id":6410,"date":"2026-02-06T08:21:44","date_gmt":"2026-02-06T16:21:44","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=6410"},"modified":"2026-02-23T11:18:55","modified_gmt":"2026-02-23T19:18:55","slug":"mask-data-in-azure-sql","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/mask-data-in-azure-sql\/","title":{"rendered":"Masking Sensitive Data in Azure SQL"},"content":{"rendered":"

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.<\/p>\n

\n

Learn more about SQL MCP Server<\/a><\/p>\n<\/blockquote>\n

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.<\/p>\n

\n

Working demo https:\/\/gist.github.com<\/a><\/p>\n<\/blockquote>\n

What We Are Building<\/h2>\n

A table that stores sensitive data and rules that mask that data.<\/p>\n

Step 1: Create the User<\/h2>\n
CREATE USER AppUser WITHOUT LOGIN;\nGO\n<\/code><\/pre>\n
Step 2: Create the Table<\/h2>\n
CREATE TABLE dbo.Customers\n(\n    Id        INT IDENTITY(1,1) PRIMARY KEY,\n    FullName  NVARCHAR(200) NOT NULL,\n    SSN       CHAR(11) NOT NULL,\n    Email     NVARCHAR(256) NOT NULL,\n    Phone     NVARCHAR(20) NOT NULL\n);\nGO\n\nINSERT INTO dbo.Customers (FullName, SSN, Email, Phone) VALUES\n    ('Alex Johnson', '123-45-6789', 'alex@example.com', '555-123-4567'),\n    ('Maria Lopez', '987-65-4321', 'maria@example.com', '555-987-6543');\nGO\n<\/code><\/pre>\n
Step 3: Apply Data Masking<\/h2>\n
Dynamic Data Masking hides data at query time. The data stays unchanged in storage.<\/p>\n
Mask Columns<\/h3>\n
-- Mask SSN (123-45-6789 -> XXX-XX-6789)\nALTER TABLE dbo.Customers\nALTER COLUMN SSN\nADD MASKED WITH (FUNCTION = 'partial(0,\"XXX-XX-\",4)');\nGO\n\n-- Mask Email (maria.lopez@contoso.com -> mXXX@XXXXXXX.com)\nALTER TABLE dbo.Customers\nALTER COLUMN Email\nADD MASKED WITH (FUNCTION = 'email()');\nGO\n\n-- Mask Phone (123-456-7890 -> XXX-XXX-7890)\nALTER TABLE dbo.Customers\nALTER COLUMN Phone\nADD MASKED WITH (FUNCTION = 'partial(0,\"XXX-XXX-\",4)');\nGO\n<\/code><\/pre>\n
Dynamic Data Masking includes several built-in masking functions. The email()<\/code> 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()<\/code> for full masking, partial()<\/code> for custom prefix and suffix preservation, and random()<\/code> for numeric data. These functions are applied at query time and do not change the underlying stored values.<\/p>\n
Step 4: See It Work<\/h2>\n
Admin view<\/h3>\n
SELECT * FROM dbo.Customers;\n<\/code><\/pre>\n
Result<\/h4>\n\n\n\n\n\n\n
Id<\/th>\nFullName<\/th>\nSSN<\/th>\nEmail<\/th>\nPhone<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\nAlex Johnson<\/td>\n123-45-6789<\/td>\nalex@example.com<\/a><\/td>\n555-123-4567<\/td>\n<\/tr>\n
2<\/td>\nMaria Lopez<\/td>\n987-65-4321<\/td>\nmaria@example.com<\/a><\/td>\n555-987-6543<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
User view<\/h3>\n
EXECUTE AS USER = 'AppUser';\nSELECT * FROM dbo.Customers;\nREVERT;\n<\/code><\/pre>\n
Result<\/h4>\n\n\n\n\n\n\n
Id<\/th>\nFullName<\/th>\nSSN<\/th>\nEmail<\/th>\nPhone<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\nAlex Johnson<\/td>\nXXX-XX-6789<\/td>\naXXX@XXXXXXX.com<\/a><\/td>\nXXX-XXX-4567<\/td>\n<\/tr>\n
2<\/td>\nMaria Lopez<\/td>\nXXX-XX-4321<\/td>\nmXXX@XXXXXXX.com<\/a><\/td>\nXXX-XXX-6543<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The same rows are returned, but sensitive values are masked automatically for the application user.<\/p>\n
Why This Is Safe<\/h2>\n
    \n
  • Masking is enforced by the database<\/li>\n
  • Applications cannot bypass it<\/li>\n
  • Storage remains intact for audits and reporting<\/li>\n
  • Admins retain full access<\/li>\n
  • Reduces accidental exposure<\/li>\n
  • Zero application changes<\/li>\n<\/ul>\n
    When to Use Something Stronger<\/h2>\n
    Dynamic Data Masking is not encryption. Privileged users can still see data.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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. […]<\/p>\n","protected":false},"author":96788,"featured_media":6605,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[648,710,619],"tags":[560,679,38,711,34],"class_list":["post-6410","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-security","category-sql-mcp-server","category-t-sql","tag-data-api-builder","tag-mcp-server","tag-security","tag-sql-mcp-server","tag-t-sql"],"acf":[],"blog_post_summary":"
    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. […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/6410","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/96788"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=6410"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/6410\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/6605"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=6410"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=6410"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=6410"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}