MS ODBC and OLE DB failed
Hello, In SQL Server 2022 (16.0.4250.1) showed two fails and can´t continue (see screenshot) On system are installed those versions of ODBC and OLE DB System was previously working (not stopped on this window for fail). We did repair of both installation and restart pc, but not helpful. Whta and how to repair it, please? Thank you.
Documentation contradictory
Hi, ALL, Page https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql?view=sql-server-ver17&tabs=sqlpool states [quote] SIZE, MAXSIZE, and FILEGROWTH parameters can be set when a UNC path is specified for the file. [/quote] However later on that same page it states [quote] SIZE can't be specified when the os_file_name is specified as a UNC path. [/quote] I think those 2 sentences contradicts each other.....
Architecture Risk Brief: Silent Data Integrity Failures in Distributed Criminal Justice Systems
Why Modernized Public Safety Environments Need Stronger Data Integrity Controls In criminal justice information services systems, the most dangerous failures are often the ones you cannot see. A system may appear fully operational—dashboards green, services responsive, transactions flowing—while critical data is incomplete, inconsistent, or out of sync across connected platforms. In these environments, the absence of alerts does not necessarily mean the absence of problems. Instead, it can signal that data integrity issues are developing silently beneath normal system behavior. As agencies modernize criminal justice information services (CJIS) systems, adopt cloud platforms, and expand data sharing across jurisdictions, the challenge is not only keeping systems online; it is ensuring the data moving between them remains accurate, consistent, and trustworthy. Why This Risk Is Growing Criminal justice agencies are going through rapid modernization, and with that comes a level of complexity that simply didn’t exist in earlier, more isolated systems. In many environments, legacy applications are still running alongside newer cloud-based platforms, which creates gaps in how data is processed and interpreted. At the same time, transaction volumes have increased significantly, and under heavy load it’s not uncommon to see partial commits, retry behavior, or subtle inconsistencies that are hard to detect. There’s also a growing expectation for near real-time synchronization across systems, even when those systems weren’t originally designed to stay perfectly in sync. As more agencies begin sharing data across jurisdictions, the number of integration points increases, and each one introduces its own risk. None of these changes are inherently problematic, but together they create conditions where data integrity issues can develop quietly without triggering any obvious system failures. These changes improve capability but also create new failure modes that traditional monitoring does not detect. System uptime alone is no longer a reliable indicator of operational health. The CJIS Security Policy reinforces this requirement by mandating that criminal justice information (CJI) remain accurate, complete, and protected from unauthorized alteration throughout its lifecycle. What Silent Data Integrity Failures Look Like Silent failures almost never show up as outages. Most of the time, everything looks fine on the surface—systems are up, jobs are running, dashboards are green. The problems usually come to light much later, often when someone is preparing for an audit, reconciling data between agencies, or digging into a case where something just doesn’t add up. In one scenario, a transaction completed successfully in the source system but never made it to a downstream platform. There were no errors, no retries flagged—just missing data. In another case, records looked perfectly valid within each system, but when compared across environments, they didn’t match. These kinds of discrepancies tend to surface during reporting or compliance checks, not during normal operations. That’s what makes them difficult to catch. From an operational standpoint, everything appears healthy. There are no alerts or obvious failures, but underneath that, the data has slowly drifted out of sync. Database Corruption: The Most Silent Failure of All Beyond synchronization gaps, database corruption represents an even more dangerous and often invisible threat. Corruption can arise from: Storage subsystem issues Hardware degradation Incomplete writes under high load Failover anomalies Legacy-to-cloud interactions Low-severity corruption may go unnoticed for weeks but eventually impacts multiple agency systems. Because corruption directly threatens the accuracy and integrity of CJI, it poses a significant CJIS compliance risk. My Implementation: Automated Corruption Alerts To deal with this, I implemented a simple automated alerting system that monitors corruption indicators and notifies me as soon as something looks off. Instead of waiting for issues to surface during audits or downstream failures, this provides an early signal that something isn’t right. In practice, it means I can react quickly, investigate the issue before it spreads, and avoid situations where bad data propagates into other systems. In CJIS environments, even a single corrupted record can have real consequences, so early visibility makes a meaningful difference. Flow Diagram to Detect Integrity Root Causes of Silent Data Drift In most cases, these data integrity issues don’t come from obvious failures—they build up during normal day-to-day operations. In high-volume systems, retries and partial commits under load can leave data in an inconsistent state without triggering any errors. During modernization or cloud migrations, subtle differences in schema behavior or transformation logic can cause data to drift between systems over time. Another common gap is monitoring. Most setups track uptime and performance, but very few validate whether the data itself remains consistent across platforms. And once data moves across multiple systems and integrations, each handoff becomes a potential point where something can go slightly wrong. None of these issues stand out individually, but together they create conditions where inconsistencies quietly accumulate. Next Steps for Agencies Criminal justice organizations don’t need to overhaul their entire technology stack to strengthen data integrity. Instead, they can take practical, incremental steps that build resilience into existing systems while preparing for future modernization. Establish a Baseline for Data Integrity Map where data originates, how it moves, and where it is stored across multiple agency systems. Implement Routine Cross-System Validation Use Azure Data Factory, Azure SQL Data Sync, and Log Analytics queries to automate comparisons between operational and reporting systems. Monitor for Corruption and Synchronization Failures Enable corruption detection and configure automated notifications—similar to the low-to-critical corruption alerts I implemented. Treat Failover and Migration as Integrity Events Use Azure SQL Failover Groups and ADF pipelines to verify data consistency before and after transitions. Strengthen Governance and Documentation Use Microsoft Purview to track lineage, schema changes, and data ownership. Build a Culture of Data Integrity Encourage teams to treat data correctness as a shared responsibility across the organization. Final Thoughts Criminal justice information systems have made significant progress in availability, scalability, and security. But as these systems become more distributed and interconnected, data integrity—including corruption detection—is emerging as one of the most critical and least visible operational risks. The challenge is no longer simply ensuring systems stay online. It is ensuring that the data moving through them remains correct, consistent, and trustworthy across every system, agency, and workflow that depends on it. In environments where data directly impacts investigations, reporting, and compliance decisions, integrity must be engineered, validated, and continuously enforced with the same rigor applied to system availability and security.
Dynamic Data Masking – What it is, What it isn’t, and How to use it effectively
In this post, we’ll explain the core purpose of Dynamic Data Masking (to ease application development), how it works, and its proper use cases – as well as its limitations. If you’re considering using Dynamic Data Masking or reviewing your data security strategy, this information will help you make informed decisions. What Dynamic Data Masking is designed for Dynamic Data Masking Dynamic Data Masking - SQL Server | Microsoft Learn is a database feature that can be used to alter how certain data elements are presented in query results for users who do not have privileged access or required permission. For example, a query on an email column may return a masked value such as jXXX@XXXX.com rather than the full address, depending on user permissions, while the original data remains unchanged in storage. Masking rules are defined within the database schema and are applied to query results for applicable users at runtime. This approach can simplify application developer’s job and reduce the need for application‑level logic that modifies how sensitive values are displayed across different application(s) or reports. DDM can help prevent accidental or casual exposure of sensitive information. How Does DDM differ from other security features? Dynamic Data Masking affects only what users see in query results—it does not protect the underlying data. Unlike encryption Always Encrypted - SQL Server | Microsoft Learn or Row‑Level security Row-Level Security - SQL Server | Microsoft Learn, DDM does not encrypt data, filter rows, or override SQL permissions. Users with elevated privileges (such as UNMASK, db_owner, or sysadmin) always see unmasked data or can modify or remove masking rules. What DDM doesn’t protect against Because Dynamic Data Masking is applied when query results are returned, there are several considerations to be aware of: Inference through queries: In some scenarios, users with database access may be able to make inferences about masked values by applying query filters or conditions that rely on underlying stored data. The database is still comparing the real values under the hood, so these queries work. It’s an expected behavior given DDM’s design. Privileged users: Users who are granted sufficient database permissions, such as the ability to alter table schemas, can directly disable or remove masking. Users with sysadmin, db_owner or CONTROL permission can view unmasked data. Thus, controlling and auditing who holds such privileges is vital. Metadata visibility: Masking rules and associated columns can be discoverable through system metadata. Data movement: Because masking is defined at the schema level in a given database instance, backups or exported datasets may contain unmasked values depending on permissions and configuration. Understanding these design characteristics is important when incorporating DDM into a broader data governance or privacy strategy. Proper use and best practices for DDM Organizations may consider using Dynamic Data Masking in scenarios where consistent display of sensitive values is needed across application(s) or reporting environments. Some implementation considerations include: Using DDM to help standardize how sensitive fields are displayed in query results and reduce developmental effort for data masking Combining DDM with other database or access‑control features as part of a layered data protection strategy Reviewing which users are granted permissions to view unmask data or alter masking configurations. Implementing auditing or monitoring database activity as part of broader governance practices Educating internal stakeholders on how masking operates at the query‑result level Testing masking configurations in non‑production environments prior to deployment Conclusion Dynamic Data Masking can be useful in scenarios where organizations want to manage how sensitive data is displayed in application outputs without modifying stored values. It is designed to operate as part of a broader data access or governance approach rather than as a standalone protection mechanism for stored data. When implemented alongside complementary database features and appropriate access controls, DDM may help support more consistent handling of sensitive values across environments.Stream data in near real time from SQL to Azure Event Hubs - Public preview
If near-real time integration is something you are looking to implement and you were looking for a simpler way to get the data out of SQL, keep reading. SQL is making it easier to integrate and Change Event Streaming is a feature continuing this trend. Modern applications and analytics platforms increasingly rely on event-driven architectures and real-time data pipelines. As the businesses speed up, real time decisioning is becoming especially important. Traditionally, capturing changes from a relational database requires complex ETL jobs, periodic polling, or third-party tools. These approaches often consume significant cycles of the data source, introduce operational overhead, and pose challenges with scalability, especially if you need one data source to feed into multiple destinations. In this context, we are happy to release Change Event Streaming ("CES") feature into Public Preview for Azure SQL Database. This feature enables you to stream row-level changes - inserts, updates, and deletes - from your database directly to Azure Event Hubs in near real time. Change Event Streaming addresses the above challenges by: Reducing latency: Changes are streamed (pushed by SQL) as they happen. This is in contrast with traditional CDC (change data capture) or CT (change tracking) based approaches, where an external component needs to poll SQL at regular intervals. Traditional approaches allow you to increase polling frequency, but it gets difficult to find a sweet spot between minimal latency and minimal overhead due to too frequent polls. Simplifying architecture: No need for Change Data Capture (CDC), Change Tracking, custom polling or external connectors - SQL streams directly to configured destination. This means simpler security profile (fewer authentication points), fewer failure points, easier monitoring, lower skill bar to deploy and run the service. No need to worry about cleanup jobs, etc. SQL keeps track of which changes are successfully received by the destination, handles the retry logic and releases log truncation point. Finally, with CES you have fewer components to procure and get approved for production use. Decoupling: The integration is done on the database level. This eliminates the problem of dual writes - the changes are streamed at transaction boundaries, once your source of truth (the database) has saved the changes. You do not need to modify your app workloads to get the data streamed - you tap right onto the data layer - this is useful if your apps are dated and do not possess real-time integration capabilities. In case of some 3rd party apps, you may not even have an option to do anything other than database level integration, and CES makes it simpler. Also, the publishing database does not concern itself with the final destination for the data - Stream the data once to the common message bus, and you can consume it by multiple downstream systems, irrespective of their number or capacity - the (number of) consumers does not affect publishing load on the SQL side. Serving consumers is handled by the message bus, Azure Event Hubs, which is purpose built for high throughput data transfers. onceptually visualizing data flow from SQL Server, with an arrow towards Azure Event Hubs, from where a number of arrows point to different final destinations. Key Scenarios for CES Event-driven microservices: They need to exchange data, typically thru a common message bus. With CES, you can have automated data publishing from each of the microservices. This allows you to trigger business processes immediately when data changes. Real-time analytics: Stream operational data into platforms like Fabric Real Time Intelligence or Azure Stream Analytics for quick insights. Breaking down the monoliths: Typical monolithic systems with complex schemas, sitting on top of a single database can be broken down one piece at a time: create a new component (typically a microservice), set up the streaming from the relevant tables on the monolith database and tap into the stream by the new components. You can then test run the components, validate the results against the original monolith, and cutover when you build the confidence that the new component is stable. Cache and search index updates: Keep distributed caches and search indexes in sync without custom triggers. Data lake ingestion: Capture changes continuously into storage for incremental processing. Data availability: This is not a scenario per se, but the amount of data you can tap into for business process mining or intelligence in general goes up whenever you plug another database into the message bus. E.g. You plug in your eCommerce system to the message bus to integrate with Shipping providers, and consequently, the same data stream is immediately available for any other systems to tap into. How It Works CES uses transaction log-based capture to stream changes with minimal impact on your workload. Events are published in a structured JSON format following the CloudEvents standard, including operation type, primary key, and before/after values. You can configure CES to target Azure Event Hubs via AMQP or Kafka protocols. For details on configuration, message format, and FAQs, see the official documentation: Feature Overview CES: Frequently Asked Questions Get Started Public preview CES is available today in public preview for Azure SQL Database and as a preview feature in SQL Server 2025. [update 20-mar-2026] Change Event Streaming is now in public preview for Azure SQL Managed instance. Read more here. Private preview CES is also available as a private preview for Azure SQL Managed Instance and Fabric SQL database: you can request to join the private preview by signing up here: https://aka.ms/sql-ces-signup We encourage you to try the feature out and start building real-time integrations on top of your existing data. We welcome your feedback—please share your experience through Azure Feedback portal or support channels. The comments below on this blog post will also be monitored, if you want to engage with us. Finally, CES team can be reached via email: sqlcesfeedback [at] microsoft [dot] com. Useful resources Free Azure SQL Database. Free Azure SQL Managed Instance.How does GitHub Copilot in SSMS 22 handle database context collection before generating a response?
Hello, I am trying to better understand the internal workflow of GitHub Copilot in SSMS 22, especially for database-specific questions. From the product descriptions, it seems that Copilot can use the context of the currently connected database, such as schema, tables, columns, and possibly other metadata, when answering questions or generating T-SQL. However, I could not find clear official documentation about the actual sequence of operations. My main questions are: Before generating a response, does Copilot first collect database context/metadata from the active connection and then send that context to the LLM as grounding information? Or does it first use the LLM to interpret the user’s request, decide what information is needed, and then retrieve database metadata before generating the final answer? In some explanations, I have seen the phrase "Core SQL Copilot Infrastructure", but I cannot find any official documentation for that term. Is this an official component name? If so, what does it specifically refer to in the SSMS Copilot architecture? When Copilot answers schema-related or data-related questions, what information is retrieved automatically from the connected database, and is any SQL executed as part of that process? Is there any official architectural documentation that explains: context collection, prompt grounding, LLM invocation order, and whether query execution can occur before the final response is generated? I am asking because I want to understand the feature from both an architecture and data governance/security perspective. Any clarification from the product team or documentation links would be greatly appreciated. Thank you.Expanding Azure Arc SQL Migration with a New Target: SQL Server on Azure Virtual Machines
Modernizing a SQL Server estate is rarely a single-step effort. It typically involves multiple phases, from discovery and assessment to migration and optimization, often spanning on-premises, hybrid, and cloud environments. SQL Server enabled by Azure Arc simplifies this process by bringing all migration steps into a single, cohesive experience in the Azure portal. With the March 2026 release, this integrated experience is extended by adding SQL Server on Azure Virtual Machines as a new migration target in Azure Arc. Arc-enabled SQL Server instances can now be migrated not only to Azure SQL Managed Instance, but also to SQL Server running on Azure infrastructure, using the same unified workflow. Expanding Choice Without Adding Complexity By introducing SQL Server on Azure Virtual Machines as a migration target, Azure Arc now supports a broader range of migration strategies while preserving a single operational model. It becomes possible to choose between Azure SQL Managed Instance and SQL Server on Azure VMs without fragmenting migration tooling or processes. The result is a flexible, scalable, and consistent migration experience that supports hybrid environments, reduces operational overhead, and enables modernization at a controlled and predictable pace. One Integrated Migration Journey A core value of SQL Server migration in Azure Arc is that the entire migration lifecycle is managed from one place. Once a SQL Server instance is enabled by Azure Arc, readiness can be assessed, a migration target selected, a migration method chosen, progress monitored, and cutover completed directly in the Azure portal. This approach removes the need for disconnected tools or custom orchestration. The only prerequisite remains unchanged: the source SQL Server needs to be enabled by Azure Arc. From there, migration is fully integrated into the Azure Arc SQL experience. A Consistent Experience Across Migration Targets The migration experience for SQL Server on Azure Virtual Machines follows the same model already available for Azure SQL Managed Instance migrations in Azure Arc. The same guided workflow, migration dashboard, and monitoring capabilities are used regardless of the selected target. This consistency is intentional. It allows teams to choose the destination that best fits their technical, operational, or regulatory requirements without having to learn a new migration process. Whether migrating to a fully managed PaaS service or to SQL Server on Azure infrastructure, the experience remains predictable and familiar. Backup Log Shipping Migration to SQL Server in Azure VM Migration to SQL Server on Azure Virtual Machines is based on backup and restore, specifically using log shipping mechanism. This is a well-established approach for online migrations that minimizes downtime while maintaining control over the cutover window. In this model, database backups need to be uploaded from the source SQL Server to Azure Blob Storage. The migration engine will restore the initial full backup followed by ongoing transaction log and diff. backups. Azure Blob Storage acts as the intermediary staging location between the source and the target. The Azure Blob Storage account and the target SQL Server running on an Azure Virtual Machine must be co-located in the same Azure region. This regional alignment is required to ensure efficient data transfer, reliable restore operations, and predictable migration performance. Within the Azure Arc migration experience, a simple and guided UX is used to select the Azure Blob Storage container that holds the backup files. Both the selected storage account and the Azure VM hosting SQL Server must reside in the same Azure region. Once the migration job is started, Azure Arc automatically restores the backup files to SQL Server on the Azure VM. As new log backups are uploaded to Blob Storage, they are continuously detected and applied to the target database, keeping it closely synchronized with the source. Controlled Cutover on Your Terms This automated restore process continues until the final cutover is initiated. When the cutover command is issued, Azure Arc applies the final backup to the target SQL Server on the Azure Virtual Machine and completes the migration. The target database is then brought online, and applications can be redirected to the new environment. This controlled cutover model allows downtime to be planned precisely, rather than being dictated by long-running restore operations. Getting started To get started, Arc enable you SQL Server. Then, in the Azure portal, navigate to your Arc enabled SQL Server and select Database migration under the Migration menu on the left. For more information, see the SQL Server migration in Azure Arc documentation.Unable to install SQL Server 2022 Express (installer glitch + SSMS error)
Hi, I recently purchased a new Lenovo laptop, and I am trying to install Microsoft SQL Server 2022 Express along with SSMS. SSMS installed successfully, but SQL Server installation fails, and sometimes the installer UI glitches or does not load properly. Because of this, I am getting connection errors in SSMS like "server not found" and "error 40". I am not very familiar with technical troubleshooting. Can someone guide me step-by-step in a simple way to install SQL Server correctly? Thank you.Zero Trust for data: Make Microsoft Entra authentication for SQL your policy baseline
A policy-driven path from enabled to enforced. Why this matters now Security and compliance programs were once built on an assumption that internal networks were inherently safer. Cloud adoption, remote work, and supply-chain compromise have steadily invalidated that model. U.S. federal guidance has now formalized this shift: Executive Order 14028 calls for modernizing cybersecurity and accelerating Zero Trust adoption, and OMB Memorandum M-22-09 sets a federal Zero Trust strategy with specific objectives and timelines. Meanwhile, attacker economics are changing. Automation and AI make reconnaissance, phishing, and credential abuse cheaper and faster. That concentrates risk on identity—the control plane that sits in front of systems, applications, and data. In Zero Trust, the question is no longer “is the network trusted,” but “is this request verified, governed by policy, and least-privilege?” Why database authentication is a first‑order Zero Trust control Databases are universally treated as crown-jewel infrastructure. Yet many data estates still rely on legacy patterns: password-based SQL authentication, long-lived secrets embedded in apps, and shared administrative accounts that persist because migration feels risky. This is exactly the kind of implicit trust Zero Trust architectures aim to remove. NIST SP 800-207 defines Zero Trust as eliminating implicit trust based solely on network location or ownership and focusing controls on protecting resources. In that model, every new database connection is not “plumbing”—it is an access decision to sensitive data. If the authentication mechanism sits outside the enterprise identity plane, governance becomes fragmented and policy enforcement becomes inconsistent. What changes when SQL uses Microsoft Entra authentication Microsoft Entra authentication enables users and applications to connect to SQL using enterprise identities, instead of usernames and passwords. Across Azure SQL and SQL Server enabled by Azure Arc, Entra-based authentication helps align database access with the same identity controls organizations use elsewhere. The security and compliance outcomes that leaders care about Reduce password and secret risk: move away from static passwords and embedded credentials. Centralize governance: bring database access under the same identity policies, access reviews, and lifecycle controls used across the enterprise. Improve auditability: tie access to enterprise identities and create a consistent control surface for reporting. Enable policy enforcement at scale: move from “configured” controls to “enforced” controls through governance and tooling. This is why Entra authentication is a high-ROI modernization step: it collapses multiple security and operational objectives into one effort (identity modernization) rather than a set of ongoing compensating programs (password rotation programs, bespoke exceptions, and perpetual secret hygiene projects). Why AI makes this a high priority decision AI accelerates both reconnaissance and credential abuse, which concentrates risk on identity. As a result, policy makers increasingly treat phishing-resistant authentication and centralized identity enforcement as foundational—not optional. A practical path: from enabled to enforced Successful security programs define a clear end state, a measurable glide path, and an enforcement model. A pragmatic approach to modernizing SQL access typically includes: Discover active usage: Identify which logins and users are actively connecting and which are no longer required. Establish Entra as the identity authority: Enable Entra authentication on SQL logical servers, starting in mixed mode to reduce disruption. Recreate principals using Entra identities: Replace SQL Authentication logins/users with Entra users, groups, service principals, and managed identities. Modernize application connectivity: Update drivers and connection patterns to use Entra-based authentication and managed identities. Validate, then enforce: Confirm the absence of password‑based SQL authentication traffic, then move to Entra‑only where available and enforce via policy. By adopting this sequencing, organizations can mitigate risks at an early stage and postpone enforcement until the validation process concludes. For a comprehensive migration strategy, refer to Securing Azure SQL Database with Microsoft Entra Password-less Authentication: Migration Guide. Choosing which projects to fund — and which ones to stop When making investment decisions, priority is given to database identity projects that can demonstrate clear risk reduction and lasting security benefits. Microsoft Entra authentication as the default for new SQL workloads, with a defined migration path for the existing workloads. Managed identities for application-to-database connectivity to eliminate stored secrets. Centralized governance for privileged database access using enterprise identity controls. At the same time, organizations should explicitly de-prioritize investments that perpetuate password risk: password rotation projects that preserve SQL Authentication, bespoke scripts maintaining shared logins, and exception processes that do not scale. Security and scale are not competing goals Security is often seen as something that slows down innovation, but database identity offers unique benefits. When enterprise identity is used for access controls, bringing in new applications and users shifts from handing out credentials to overseeing policies. Compliance reporting also becomes uniform rather than customized, making it easier to grow consistently thanks to a single control framework. Modern database authentication is not solely about mitigating risk— it establishes a scalable operational framework for secure data access. A scorecard designed for leadership readiness To elevate the conversation from implementation to governance, use outcome-based metrics: Coverage: Percentage of SQL workloads with Entra authentication enabled. Enforcement: Percentage operating in Entra-only mode after validation. Secret reduction: Applications still relying on stored database passwords. Privilege hygiene: Admin access governed through enterprise identity controls. Audit evidence: Ability to produce identity-backed access reports on demand. These map directly to Zero Trust maturity expectations and provide a defensible definition of “done.” Closing Zero Trust is an operating posture, not a single control. For most organizations, the fastest way to make that posture measurable is to standardize database access on the same identity plane used everywhere else. If you are looking for a single investment that improves security, reduces audit friction, and supports responsible AI adoption, modernizing SQL access with Microsoft Entra authentication — and driving it from enabled to enforced — is one of the most durable choices you can make. References US Government sets forth Zero Trust architecture strategy and requirements (Microsoft Security Blog) Securing Azure SQL Database with Microsoft Entra Password-less Authentication: Migration Guide (Microsoft Tech Community) OMB Memorandum M-22-09: Federal Zero Trust Strategy (White House) NIST SP 800-207: Zero Trust Architecture CISA: Zero Trust Enforce Microsoft Entra-only authentication for Azure SQL Database and Azure SQL Managed InstanceWhy Developers and DBAs love SQL’s Dynamic Data Masking (Series-Part 1)
Dynamic Data Masking (DDM) is one of those SQL features (available in SQL Server, Azure SQL DB, Azure SQL MI, SQL Database in Microsoft Fabric) that both developers and DBAs can rally behind. Why? Because it delivers a simple, built-in way to protect sensitive data—like phone numbers, emails, or IDs—without rewriting application logic or duplicating security rules across layers. With just a single line of T-SQL, you can configure masking directly at the column level, ensuring that non-privileged users see only obfuscated values while privileged users retain full access. This not only streamlines development but also supports compliance with data privacy regulations like GDPR and HIPAA, etc. by minimizing exposure to personally identifiable information (PII). In this first post of our DDM series, we’ll walk through a real-world scenario using the default masking function to show how easy it is to implement and how much development effort it can save. Scenario: Hiding customer phone numbers from support queries Imagine you have a support application where agents can look up customer profiles. They need to know if a phone number exists for the customer but shouldn’t see the actual digits for privacy. In a traditional approach, a developer might implement custom logic in the app (or a SQL view) to replace phone numbers with placeholders like “XXXX” for non-privileged users. This adds complexity and duplicate logic across the app. With DDM’s default masking, the database can handle this automatically. By applying a mask to the phone number column, any query by a non-privileged user will return a generic masked value (e.g. “XXXX”) instead of the real number. The support agent gets the information they need (that a number is on file) without revealing the actual phone number, and the developer writes zero masking code in the app. This not only simplifies the application codebase but also ensures consistent data protection across all query access paths. As Microsoft’s documentation puts it, DDM lets you control how much sensitive data to reveal “with minimal effect on the application layer” – exactly what our scenario achieves. Using the ‘Default’ Mask in T-SQL : The ‘Default’ masking function is the simplest mask: it fully replaces the actual value with a fixed default based on data type. For text data, that default is XXXX. Let’s apply this to our phone number example. The following T-SQL snippet works in Azure SQL Database, Azure SQL MI and SQL Server: SQL -- Step 1: Create the table with a default mask on the Phone column CREATE TABLE SupportCustomers ( CustomerID INT PRIMARY KEY, Name NVARCHAR(100), Phone NVARCHAR(15) MASKED WITH (FUNCTION = 'default()') -- Apply default masking ); GO -- Step 2: Insert sample data INSERT INTO SupportCustomers (CustomerID, Name, Phone) VALUES (1, 'Alice Johnson', '222-555-1234'); GO -- Step 3: Create a non-privileged user (no login for simplicity) CREATE USER SupportAgent WITHOUT LOGIN; GO -- Step 4: Grant SELECT permission on the table to the user GRANT SELECT ON SupportCustomers TO SupportAgent; GO -- Step 5: Execute a SELECT as the non-privileged user EXECUTE AS USER = 'SupportAgent'; SELECT Name, Phone FROM SupportCustomers WHERE CustomerID = 1 Alternatively, you can use Azure Portal to configure masking as shown in the following screenshot: Expected result: The query above would return Alice’s name and a masked phone number. Instead of seeing 222-555-1234, the Phone column would show XXXX. Alice’s actual number remains safely stored in the database, but it’s dynamically obscured for the support agent’s query. Meanwhile, privileged users such as administrator or db_owner which has CONTROL permission on the database or user with proper UNMASK permission would see the real phone number when running the same query. How this helps Developers : By pushing the masking logic down to the database, developers and DBAs avoid writing repetitive masking code in every app or report that touches this data. In our scenario, without DDM you might implement a check in the application like: If user_role == “Support”, then show “XXXX” for phone number, else show full phone. With DDM, such conditional code isn’t needed – the database takes care of it. This means: Less application code to write and maintain for masking Consistent masking everywhere (whether data is accessed via app, report, or ad-hoc query). Quick changes to masking rules in one place if requirements change, without hunting through application code. From a security standpoint, DDM reduces the risk of accidental data exposure and helps in compliance scenarios where personal data must be protected in lower environments or by certain roles, while reducing the developer effort drastically. In the next posts of this series, we’ll explore other masking functions (like Email, Partial, and Random etc) with different scenarios. By the end, you’ll see how each built-in mask can be applied to make data security and compliance more developer-friendly! Reference Links : Dynamic Data Masking - SQL Server | Microsoft Learn Dynamic Data Masking - Azure SQL Database & Azure SQL Managed Instance & Azure Synapse Analytics | Microsoft Learn
