{"id":6969,"date":"2025-08-07T06:45:36","date_gmt":"2025-08-07T11:45:36","guid":{"rendered":"https:\/\/andreas-wolter.com\/?p=6969"},"modified":"2025-08-19T14:50:58","modified_gmt":"2025-08-19T19:50:58","slug":"202508_evading_dataaccess_auditing_sqlserver","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/202508_evading_dataaccess_auditing_sqlserver\/","title":{"rendered":"Evading Data Access Auditing in Microsoft SQL Server \u2013 and how to close the gaps"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-m0cxh8ps-0c1777c3fe6ea8ed5546b30227f87fc0\">\n#top .av-special-heading.av-m0cxh8ps-0c1777c3fe6ea8ed5546b30227f87fc0{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-m0cxh8ps-0c1777c3fe6ea8ed5546b30227f87fc0 .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-m0cxh8ps-0c1777c3fe6ea8ed5546b30227f87fc0 .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-m0cxh8ps-0c1777c3fe6ea8ed5546b30227f87fc0 av-special-heading-h3 blockquote modern-quote  avia-builder-el-0  el_before_av_textblock  avia-builder-el-first '><h3 class='av-special-heading-tag'  itemprop=\"headline\"  >Evading Data Access Auditing in Microsoft SQL Server \u2013 and how to close the gaps<\/h3><div class=\"special-heading-border\"><div class=\"special-heading-inner-border\"><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-m0cxgkjy-c935304b4106b45214698f40e83a9894 '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>In this article I want to point out some important evasion-methods for anyone concerned about Auditing data access.<\/p>\n<p>In former articles I have already written about the importance of Auditing (<a href=\"https:\/\/andreas-wolter.com\/en\/202109_security_concept_audit_trail\/\">Security concept: Audit Trail<\/a>) and what constitutes a minimal Audit every database system should have in place (<a href=\"https:\/\/andreas-wolter.com\/en\/202507_recommended_security_auditing_databases_sql_server\/\">Recommendation for Security Auditing for databases \u2013 with example for Microsoft SQL Server<\/a>).<\/p>\n<p>But if you are storing sensitive data as in finance and healthcare (not an exclusive list!), you know that you can\u2019t stop at the basics. You need to specifically protect against Data Exfiltration attacks. And that is where <strong>Data Access Auditing<\/strong> comes in: logging and monitoring access to sensitive data. That can be <strong>to detect internal actors as well as external threat actors<\/strong>: An internal actor who is attempting to read data that he has no business reason to read, or an external actor who is preparing or conducting a large data exfiltration attack.<\/p>\n<p style=\"padding-left: 40px;\"><strong><em>How do I know if we have sensitive data, and where it is stored?<br \/>\nData discovery<\/em><\/strong> is really a subject on its own, and goes beyond what I can cover in this article.<br \/>\nIf you do not know if or where you have stored sensitive data, I urge you to consider investing in a solution that helps you identify such data in your database-environment.<br \/>\nEven a low-cost solution can make a big difference vs no discovery-attempt at all. Not every environment needs a full-fledged <strong><em>Data Governance<\/em><\/strong> solution, but at a certain size there is really no way around it if you want to safeguard your data with confidence.<\/p>\n<p style=\"padding-left: 40px;\">In the SQL Server world, we have:<\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li>built-in <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/security\/sql-data-discovery-and-classification?view=sql-server-ver17\" target=\"_blank\" rel=\"noopener\"><strong>SQL Data Discovery and Classification<\/strong><\/a> which looks for table and column names in databases<\/li>\n<li><strong>Microsoft Purview<\/strong> (which I had the honor to help integrate into the SQL Engine in my role at Microsoft), which uses a combination of metadata scanning, data classification, and pattern-based content analysis: <a href=\"https:\/\/learn.microsoft.com\/en-us\/purview\/unified-catalog\" target=\"_blank\" rel=\"noopener\">Learn about Microsoft Purview Unified Catalog<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>Once you know what data to specifically monitor for access, in Microsoft SQL Server you create a so-called Audit and then an Audit Specification.<\/p>\n<ul>\n<li>There are tons of How-To\u2019s you can find on the internet that cover the basic approach. I.e. <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/security\/auditing\/create-a-server-audit-and-database-audit-specification?view=sql-server-ver17\" target=\"_blank\" rel=\"noopener\">Create a server audit and database audit specification<\/a> Here I will focus on the shortcomings.<\/li>\n<\/ul>\n<h2><\/h2>\n<h2>The basic security Audit Specification for read access to data<\/h2>\n<p>For this article I will use the database AdventureWorks2022 samples database which contains tables with sensitive data such as CreditCard details.<\/p>\n<p style=\"padding-left: 40px;\"><strong><em>Important note on CreditCard numbers in Plaintext<\/em><\/strong><br \/>\nIn fact, CreditCard information is so sensitive that it should never ever be stored in plain text. I apologize for using this bad example and hope someone at Microsoft feels responsible and empowered by leadership to fix this, so it stops being a pattern.)<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-6971 alignnone\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_TableStructure.jpg\" alt=\"visual table definition\" width=\"800\" height=\"404\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_TableStructure.jpg 800w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_TableStructure-300x152.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_TableStructure-768x388.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_TableStructure-705x356.jpg 705w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><\/p>\n<p>I repeat: Do not store CreditCard numbers (the PAN) in plaintext, ever! This is a violation of the PCI-DSS compliance standard which is applicable to all organizations that store cardholder data.<\/p>\n<p>For this article, we will define the following rule for Auditing:<\/p>\n<p><strong>Example requirement: Audit (Log and review) every read-access to tables in the Sales schema.<\/strong><\/p>\n<p>We keep it that simple.<\/p>\n<p>Based on that we can create an Audit at the server level:<\/p>\n<p>CREATE SERVER AUDIT [Audit_SensitiveObjects]<br \/>\nTO FILE<\/p>\n<p>(\u00a0\u00a0\u00a0\u00a0\u00a0 FILEPATH = N&#8217;D:\\SQLData\\Audits\\&#8217;<\/p>\n<p>)<br \/>\nGO<\/p>\n<p>Nothing special about this Audit definition. The focus is on the Specification here.<\/p>\n<p>Then we create the Audit Specification which logs to the Audit above:<\/p>\n<p>CREATE DATABASE AUDIT SPECIFICATION [AuditSpec_Select]<br \/>\nFOR SERVER AUDIT [Audit_SensitiveObjects]<br \/>\nADD (SELECT ON SCHEMA::[Sales] BY [public])<\/p>\n<p>In a nutshell: we create an Audit Specification that captures every SELECT-command that touches any object within the schema Sales. Also no tricks here, just as documented (<a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/security\/auditing\/sql-server-audit-action-groups-and-actions?view=sql-server-ver17#database-level-audit-actions\" target=\"_blank\" rel=\"noopener\">Database-level audit actions<\/a>) .<\/p>\n<ul>\n<li>As you can see, we are auditing every user, no exception. (In SQL Server every database user is member of the database role <em>public<\/em> and cannot be removed. Comparable to the <em>Everyone<\/em>-Group in Windows.)<\/li>\n<\/ul>\n<h2>Testing the basic Audit-setup<\/h2>\n<p>Let\u2019s test if the Audit works as expected. We will utilize a User named \u201cSneaky\u201d for the tests in this article and run a query that selects from our CreditCard table:<\/p>\n<p>SELECT TOP (10) *<\/p>\n<p>FROM [Sales].CreditCard<\/p>\n<p>And we see the following in the Audit:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-6973 alignnone\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_Log1.jpg\" alt=\"\" width=\"915\" height=\"88\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_Log1.jpg 915w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_Log1-300x29.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_Log1-768x74.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_Log1-705x68.jpg 705w\" sizes=\"auto, (max-width: 915px) 100vw, 915px\" \/><\/p>\n<p>(ignore the first row which just states that the Audit had been started)<\/p>\n<p>All there as expected.<\/p>\n<h2>First attempt to evade auditing via secondary object<\/h2>\n<p>Now User Sneaky has the idea that instead of selecting from the table directly, he could try <strong>utilizing a stored procedure that resides in a different schema<\/strong>, and call that instead running the SELECT directly, hoping to evade the Auditing.<br \/>\nAfter creating the stored procedure, the User Sneaky can execute it and the rows from the table are returned as shown below:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-6975 alignnone\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Procedure.jpg\" alt=\"proc code with select\" width=\"337\" height=\"435\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Procedure.jpg 337w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Procedure-232x300.jpg 232w\" sizes=\"auto, (max-width: 337px) 100vw, 337px\" \/><\/p>\n<p>Did SQL Auditing catch this?<\/p>\n<p><strong>Yes<\/strong>, it did. And not only that, but it also tells us that the access happened through nested calls (A procedure running the select)<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-large wp-image-6977 alignnone\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_NestedCalls-1030x94.jpg\" alt=\"audit entry with tsql stack\" width=\"1030\" height=\"94\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_NestedCalls-1030x94.jpg 1030w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_NestedCalls-300x27.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_NestedCalls-768x70.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_NestedCalls-705x64.jpg 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_NestedCalls.jpg 1079w\" sizes=\"auto, (max-width: 1030px) 100vw, 1030px\" \/><\/p>\n<p>As I have shown in my session \u201cSecuring SQL Server from Inside-Attacks\u201d at PASS Summit 2009, when Auditing was freshly released with SQL Server 2008, one of the main advantages of Auditing over the formerly available SQL Trace method is, that it captures based on the accessed objects, no matter where the statement comes from.<\/p>\n<p>In the log you can see that the Audit was triggered by the sql statement that selected from the CreditCard table, but to help us understand the access-path, we can see the name of the stored procedure that was executed in the column <em>additional_info<\/em>.<\/p>\n<p>Takeaway:<\/p>\n<blockquote><p><strong>No matter behind how many nesting levels of views or procedures a user tries to hide the access to the audited table, auditing will capture the access-attempt.<\/strong><\/p><\/blockquote>\n<p>&#8211; Including the tsql stack of objects that lead to it.<br \/>\nThat is awesome and <strong>an important requirement for a security control: to be enforced at every access point.<\/strong><\/p>\n<p style=\"padding-left: 40px;\"><em>Note on Permissions<\/em><br \/>\nIf you know me, you know I take the topic of permissions very seriously. However, this article isn\u2019t about permissions per se. The principles I\u2019m discussing here apply to any user, regardless of their permission level. <strong>Even a sysadmin should be subject to auditing.<\/strong> In this specific example, the user might be a developer or support engineer with DDL permissions in addition to SELECT. But ultimately, it doesn&#8217;t matter &#8211; if the rule is \u201cwe shall audit,\u201d then we shall audit. \ud83d\ude09<\/p>\n<p>Alright, so we got the basics covered.<br \/>\nLet\u2019s look at some other ways to get to the data.<\/p>\n<h2>Evading auditing using DBCC SHOWSTATISTICS to read data from the Statistics objects<\/h2>\n<p>If you look back at the table definition above, you will notice several statistics objects. <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/statistics\/statistics?view=sql-server-ver17\" target=\"_blank\" rel=\"noopener\">Statistics<\/a> contain samples of the actual data of an index or column.<\/p>\n<p>The Sales.CreditCard-table in the AdventureWorks database has a predefined statistics object named AK_CreditCard_CardNumber. Any user who has SELECT access to the table can also read statistics (which is kind of important for the Query Analyzer to choose a good query plan)<\/p>\n<p>Since our User Sneaky has the SELECT-permission on the table but wants to avoid being audited, he could try reading the data that\u2019s available in the statistics. Like so:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-6979 alignnone\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_DBCC_SHOWSTATISTICS.jpg\" alt=\"DBCC SHOWSTATISTICS result\" width=\"592\" height=\"246\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_DBCC_SHOWSTATISTICS.jpg 592w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_DBCC_SHOWSTATISTICS-300x125.jpg 300w\" sizes=\"auto, (max-width: 592px) 100vw, 592px\" \/><\/p>\n<p>Marked in red are the CreditCard numbers, which are in the first column of the statistics object.<\/p>\n<p><strong>Does the SELECT-Action in SQL Auditing capture the access to the CreditCard numbers via the statistics histogram using DBCC_SHOWSTATISTICS?:<\/strong><\/p>\n<p><span style=\"color: #ff0000;\"><strong>No!<\/strong><\/span><\/p>\n<p>The Audit log contains no new entry.<\/p>\n<p>This histogram contains only 153 rows, but still, these are legitimate CreditCard numbers.<br \/>\nSince User Sneaky is just that: sneaky, and is a power-user with DDL-permissions, he can now carefully craft further statistics to get more than 153 CreditCard numbers. He could create multiple filtered statistics for example, like so, essentially dividing them up into buckets of 100:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-6981 alignnone\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_CreatingFilteredStatistics.jpg\" alt=\"Creating filtered statistics\" width=\"574\" height=\"496\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_CreatingFilteredStatistics.jpg 574w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_CreatingFilteredStatistics-300x259.jpg 300w\" sizes=\"auto, (max-width: 574px) 100vw, 574px\" \/><\/p>\n<p>And so on.<\/p>\n<p>None of that is Audited under the SELECT-event, even though it clearly exposes data!<\/p>\n<p style=\"padding-left: 40px;\"><em>Background to understand Auditing in Microsoft SQL Server<\/em><br \/>\nHistorically SQL Server Auditing was designed to Audit based on permission-checks. This is why instead of an Audit Group \u201cRead data\u201d or something like that, it is \u201cSELECT\u201d. Because there is a permission SELECT that one can have on a table and the audit will trigger when it is checked. Of course, several adjustments had to be made to make Audit also log access by sysadmins which do not trigger permission checks. But this legacy is probably the reason why there are some non-intuitive Audit groups that are very \u201cactivity-based\u201d rather than \u201cresult-based\u201d. (\u201cSELECT from objects\u201d vs \u201cRead data\u201d).<\/p>\n<h2>Solution: How to Audit DBCC-access to Statistics objects<\/h2>\n<p>Now in this case, User Sneaky used a DBCC-command, and if you are somewhat familiar with the Audit Groups of SQL Server, you may have seen that there is a separate Audit Action Group for just that: DBCC_GROUP. Let\u2019s add that to the Audit Specification:<\/p>\n<p>ALTER DATABASE AUDIT SPECIFICATION [AuditSpec_Select]<\/p>\n<p>ADD (DBCC_GROUP)<\/p>\n<p>And after running the DBCC_SHOWSTATISTICS again, we see the following in the Audit log:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-6983 alignnone\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_Log2.jpg\" alt=\"Audit entry with DBCC\" width=\"790\" height=\"122\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_Log2.jpg 790w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_Log2-300x46.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_Log2-768x119.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_Log2-705x109.jpg 705w\" sizes=\"auto, (max-width: 790px) 100vw, 790px\" \/><\/p>\n<p>Alright, this works as expected.<\/p>\n<p style=\"padding-left: 40px;\"><em>Important note on DBCCs:<\/em><br \/>\nIt is not by chance that <strong>the DBCC_GROUP Audit Action group is one of the essential action groups which I recommend every system to monitor<\/strong> (see <a href=\"https:\/\/andreas-wolter.com\/en\/202507_recommended_security_auditing_databases_sql_server\/\">Recommendation for Security Auditing for databases \u2013 with example for Microsoft SQL Server<\/a> ). Besides DBCC_SHOWSTATS there are other DBCC commands which sysadmins could use for malicious purposes, among them DBCC PAGE, which allows reading the raw 8-KB pages which SQL Server uses to store data. So, make sure to monitor those commands specifically.<\/p>\n<p>Alright, so we can capture all access variations?! Well, not so fast.<\/p>\n<h2>Evading auditing using Statistics objects and system function<\/h2>\n<p>User Sneaky knows SQL Server in and out. He also knows that since SQL Server version 2016 there is a Dynamic Management Function (DMF) which can be used instead of the DBCC command: <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/system-dynamic-management-views\/sys-dm-db-stats-histogram-transact-sql?view=sql-server-ver17\" target=\"_blank\" rel=\"noopener\">sys.dm_db_stats_histogram<\/a>.<\/p>\n<p>So, what happens if he uses this query instead?:<\/p>\n<p>SELECT range_high_key, equal_rows, distinct_range_rows<br \/>\nFROM sys.dm_db_stats_histogram(OBJECT_ID(&#8216;Sales.CreditCard&#8217;), 5);<\/p>\n<p>Note the 5 is the statistics object Id, which can be obtained by querying sys.stats. The results are essentially the same as for the DBCC command.<\/p>\n<p><strong>Does the SELECT-Action in SQL Auditing capture the access to the CreditCard numbers via the statistics histogram using the DMF sys.dm_db_stats_histogram?:<\/strong><\/p>\n<p><span style=\"color: #ff0000;\"><strong>No!<\/strong><\/span><\/p>\n<p>The Audit log contains no new entry.<\/p>\n<h2>Solution: How to Audit access to Statistics objects via DMF<\/h2>\n<p>As you may realize, I won\u2019t just publish security bugs without showing a possible solution. So, what can we do here?<\/p>\n<p>1<sup>st<\/sup> attempt:<\/p>\n<p>Searching the Audit Action Groups brings up SCHEMA_OBJECT_ACCESS_GROUP. From the name and description this one might capture that \u2013 and more. Let\u2019s try:<\/p>\n<p>ALTER DATABASE AUDIT SPECIFICATION [AuditSpec_Select]<br \/>\nADD (SCHEMA_OBJECT_ACCESS_GROUP)<\/p>\n<p>Reading the histogram via the DMF again and checking the Audit log <u>still yields no results<\/u>. It captures a lot of other stuff that I have no interest in though, so I let\u2019s drop that Action Group again.<\/p>\n<p>ALTER DATABASE AUDIT SPECIFICATION [AuditSpec_Select]<\/p>\n<p>DROP (SCHEMA_OBJECT_ACCESS_GROUP)<\/p>\n<p>2<sup>nd<\/sup> attempt:<\/p>\n<p>There is no distinct Audit Group for system views. But we can define Audits on individual objects, if we know their names. Almost the same way as I am auditing the whole schema <em>sales<\/em>, I can monitor the schema <em>sys<\/em>, or even the individual DMVs and DMFs within the sys-schema.<\/p>\n<p>There\u2019s one peculiarity, though: those objects don\u2019t actually reside in the user database, but in a hidden system database called <em>mssqlsystemresource<\/em>, which isn\u2019t directly accessible. When you try to Audit dynamic system objects from within a user database you get this error:<\/p>\n<p style=\"padding-left: 40px;\"><span style=\"color: #ff0000;\"><em>Error 33099: You cannot add server-scoped catalog views, system stored procedures, or extended stored procedures to a database audit specification in a user database. Instead add them to a database audit specification in the master database.<\/em><\/span><\/p>\n<p>We need to create the following database audit specification in the master database:<\/p>\n<p>CREATE DATABASE AUDIT SPECIFICATION [AuditSpec_Select_dm_db_stats_histogram]<br \/>\nFOR SERVER AUDIT [Audit_SensitiveObjects]<br \/>\nADD (SELECT ON OBJECT::[sys].[dm_db_stats_histogram] BY [public])<\/p>\n<p>Once this is in place, access to the DMF <em>sys.dm_db_stats_histogram<\/em> is logged:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-6985 alignnone\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_Log3.jpg\" alt=\"Auditing DMF call\" width=\"590\" height=\"55\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_Log3.jpg 590w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_Log3-300x28.jpg 300w\" sizes=\"auto, (max-width: 590px) 100vw, 590px\" \/><\/p>\n<ul>\n<li>Notice the database user being <em>guest<\/em>, since we do not have an explicit user in master. But when Auditing SQL Server, you should always look at the column <em>session_server_principal_name<\/em><\/li>\n<\/ul>\n<p>3<sup>rd<\/sup> attempt, alternative:<\/p>\n<p>The alternative would be to use the BATCH_COMPLETED_GROUP, which captures every individual statement that is run against the database. Like so:<\/p>\n<p>ALTER DATABASE AUDIT SPECIFICATION [AuditSpec_Select]<br \/>\nADD (BATCH_COMPLETED_GROUP)<\/p>\n<p>Which logs all the mentioned read attempt methods shown and more:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-6987 alignnone\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_Log4.jpg\" alt=\"Audit entries Batch Completed\" width=\"602\" height=\"107\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_Log4.jpg 602w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_Log4-300x53.jpg 300w\" sizes=\"auto, (max-width: 602px) 100vw, 602px\" \/><\/p>\n<p>However, due to the large volume of data generated by BATCH_COMPLETED &#8211; I&#8217;ve seen it reach up to a terabyte per day on a busy system &#8211; it\u2019s usually considered a last resort. Additionally, the 3,000-character limit on filters is quickly exhausted once you realize how much filtering is actually needed.<\/p>\n<p>In my next article, I&#8217;ll present a more elegant and efficient solution that can be used in SQL Server 2022. > <a href=\"https:\/\/andreas-wolter.com\/en\/202508_dataaccess_auditing_data_classification_sqlserver\/\">How to Use Data Classification to Audit specific Data Access in SQL Server<\/a><\/p>\n<h2>Summary<\/h2>\n<p>In sum: Auditing access to data isn\u2019t quite as trivial as the Audit Groups may make us believe:<br \/>\nThe SELECT-Action captures specifically only Select-commands run against a given object (can be the whole database, schema or individual objects such as tables or views.<\/p>\n<p>However, depending on the environment, one needs to consider potential indirect attacks or even side-channels. Most database systems use different object types to help optimize workloads that may contain actual data. Such as Indexes, Statistics and Query Plans with parameter values or other system functions that may return data from a running session.<\/p>\n<p>Therefore, it may be required to capture additional Events, such as in this case DBCC-commands. And if nothing else helps, collecting every single statement may be required as last resort. In such a case it is critical to consider the overhead on the system as well as consider ow to handle the large amount of log-data.<br \/>\nIn a system with 10 queries per minutes this may be easy, but when you have thousands of statements per minute the performance-impact will start to be felt. And the Audit logs will quickly take up many gigabytes if not terabytes.<\/p>\n<p>If you have such a critical system to Audit, don\u2019t shy away from seeking help from professionals. At <a href=\"https:\/\/www.sarpedonqualitylab.us\/\" target=\"_blank\" rel=\"noopener\">Sarpedon Quality Lab<\/a> we know SQL Server in and out and have experience with collecting and parsing Audit information.<\/p>\n<h2>Resources on Auditing for Microsoft SQL<\/h2>\n<p>Lastly, here are some resources on data access Auditing support in Microsoft SQL offerings:<\/p>\n<ul>\n<li>SQL Server and Managed Instance: <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/security\/auditing\/sql-server-audit-database-engine?view=sql-server-ver17\" target=\"_blank\" rel=\"noopener\">SQL Server Audit<\/a><\/li>\n<li>Azure SQL: <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/azure-sql\/database\/auditing-overview?view=azuresql\" target=\"_blank\" rel=\"noopener\">Auditing for Azure SQL Database and Azure Synapse Analytics<\/a><\/li>\n<li>SQL database in Fabric: at the time of this article is still in Preview and does not support Auditing: <a href=\"https:\/\/learn.microsoft.com\/en-us\/fabric\/database\/sql\/feature-comparison-sql-database-fabric\" target=\"_blank\" rel=\"noopener\">Features comparison: Azure SQL Database and SQL database in Microsoft Fabric (preview)<\/a><\/li>\n<li>Fabric Warehouse: Auditing is in Preview: <a href=\"https:\/\/learn.microsoft.com\/en-us\/fabric\/data-warehouse\/sql-audit-logs\" target=\"_blank\" rel=\"noopener\">SQL audit logs in Fabric Data Warehouse (Preview)<\/a> and just received a refreshed granular GUI: <a href=\"https:\/\/blog.fabric.microsoft.com\/en-us\/blog\/experience-the-new-visual-sql-audit-logs-configuration-in-fabric-warehouse\/\" target=\"_blank\" rel=\"noopener\">Experience the New Visual SQL Audit Logs Configuration in Fabric Warehouse<\/a><\/li>\n<li>Article by Emad Al-Mousa describing accessing data via statistics to circumvent auditing: <a href=\"https:\/\/databasesecurityninja.wordpress.com\/2020\/10\/22\/bypassing-data-classification-and-auditing-feature-in-sql-server-2019\/\" target=\"_blank\" rel=\"noopener\">bypassing Data Classification and Auditing feature in SQL Server 2019<\/a><\/li>\n<\/ul>\n<p>Happy Auditing<\/p>\n<p>Andreas<\/p>\n<p>Thank you for reviewing: Sravani Saluru, Senior Product Manager at Microsoft Azure Data and responsible for Auditing in Azure SQL, Fabric SQL and SQL Server.<\/p>\n<\/div><\/section>\r\n\r\n<div  class='hr av-baku8u-c77559299fb7cb036a9bcb2d27e7c839 hr-default  avia-builder-el-2  el_after_av_textblock  el_before_av_social_share '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<div  class='av-social-sharing-box av-5n5vpa-78ffdd9d224b4a246af65bdc00dce900 av-social-sharing-box-default  avia-builder-el-3  el_after_av_hr  el_before_av_hr  av-social-sharing-box-fullwidth'><div class=\"av-share-box\"><h5 class='av-share-link-description av-no-toc '>Share article<\/h5><ul class=\"av-share-box-list noLightbox\"><li class='av-share-link av-social-link-facebook' ><a target=\"_blank\" aria-label=\"Share on Facebook\" href=\"https:\/\/www.facebook.com\/sharer.php?u=https:\/\/andreas-wolter.com\/en\/202508_evading_dataaccess_auditing_sqlserver\/&#038;t=Evading%20Data%20Access%20Auditing%20in%20Microsoft%20SQL%20Server%20%E2%80%93%20and%20how%20to%20close%20the%20gaps\" aria-hidden=\"false\" data-av_icon=\"\ue8f3\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Facebook\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Facebook<\/span><\/a><\/li><li class='av-share-link av-social-link-twitter' ><a target=\"_blank\" aria-label=\"Share on Twitter\" href=\"https:\/\/twitter.com\/share?text=Evading%20Data%20Access%20Auditing%20in%20Microsoft%20SQL%20Server%20%E2%80%93%20and%20how%20to%20close%20the%20gaps&#038;url=https:\/\/andreas-wolter.com\/en\/?p=6969\" aria-hidden=\"false\" data-av_icon=\"\ue8f1\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Twitter\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Twitter<\/span><\/a><\/li><li class='av-share-link av-social-link-linkedin' ><a target=\"_blank\" aria-label=\"Share on LinkedIn\" href=\"https:\/\/linkedin.com\/shareArticle?mini=true&#038;title=Evading%20Data%20Access%20Auditing%20in%20Microsoft%20SQL%20Server%20%E2%80%93%20and%20how%20to%20close%20the%20gaps&#038;url=https:\/\/andreas-wolter.com\/en\/202508_evading_dataaccess_auditing_sqlserver\/\" aria-hidden=\"false\" data-av_icon=\"\ue8fc\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on LinkedIn\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on LinkedIn<\/span><\/a><\/li><\/ul><\/div><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-4ofg9q-c2108540b480aba02923089240a3a176\">\n#top .hr.hr-invisible.av-4ofg9q-c2108540b480aba02923089240a3a176{\nheight:50px;\n}\n<\/style>\n<div  class='hr av-4ofg9q-c2108540b480aba02923089240a3a176 hr-invisible  avia-builder-el-4  el_after_av_social_share  el_before_av_comments_list '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<div  class='av-buildercomment av-284ftq-f5a1564cd6b8ffad6ce835e2d40de4b7  av-blog-meta-author-disabled av-blog-meta-html-info-disabled'><\/div>","protected":false},"excerpt":{"rendered":"","protected":false},"author":4,"featured_media":6990,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[57],"tags":[206],"class_list":["post-6969","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-security-en","tag-sql-security"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/6969","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/comments?post=6969"}],"version-history":[{"count":4,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/6969\/revisions"}],"predecessor-version":[{"id":6992,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/6969\/revisions\/6992"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/6990"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=6969"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=6969"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=6969"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}