{"id":6996,"date":"2025-08-19T06:06:01","date_gmt":"2025-08-19T11:06:01","guid":{"rendered":"https:\/\/andreas-wolter.com\/?p=6996"},"modified":"2025-08-18T23:23:15","modified_gmt":"2025-08-19T04:23:15","slug":"202508_dataaccess_auditing_data_classification_sqlserver","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/202508_dataaccess_auditing_data_classification_sqlserver\/","title":{"rendered":"How to Use Data Classification to Audit specific Data Access in SQL Server"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-m0cxh8ps-50f50578d9220bae11ba06c01b6ea237\">\n#top .av-special-heading.av-m0cxh8ps-50f50578d9220bae11ba06c01b6ea237{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-m0cxh8ps-50f50578d9220bae11ba06c01b6ea237 .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-m0cxh8ps-50f50578d9220bae11ba06c01b6ea237 .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-m0cxh8ps-50f50578d9220bae11ba06c01b6ea237 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\"  >How to Use Data Classification to Audit specific Data Access in Microsoft SQL Server<\/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 share a targeted approach to audit access to specific objects within a database in Microsoft SQL Server.<\/p>\n<ul>\n<li>In my last article, <a href=\"https:\/\/andreas-wolter.com\/en\/202508_evading_dataaccess_auditing_sqlserver\/\">Evading Data Access Auditing in Microsoft SQL Server \u2013 and how to close the gaps<\/a>, I showed multiple approaches to gain access to a chunk of sensitive data using the statistics object in SQL Server. The hardest one to capture is access to data that is exposed via the dynamic management function (DMF) <strong><em>dm_db_stats_histogram<\/em><\/strong>. This requires an additional Audit Specification in the master database for this system object. In the end we required 3 different Audit Action Groups to cover all the methods used to read data from our example table.<\/li>\n<\/ul>\n<p>In the former article I did also mention <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><em>SQL Data Discovery and Classification<\/em><\/strong><\/a>, in the context of discovering sensitive data.<\/p>\n<p>Microsoft SQL Server\u2019s Data Classification doesn\u2019t enforce access control on its own, so one can argue it isn\u2019t a classic security control. However, starting with SQL Server 2022, it gains a powerful new capability: auditing access to sensitive data.<br \/>\nIt introduces the new Action group, <strong><em>SENSITIVE_BATCH_COMPLETED_GROUP <\/em><\/strong>which gets triggered any time a query in SQL Server touches data that has been classified.<\/p>\n<p style=\"padding-left: 40px;\"><em>Background<\/em><br \/>\nThe classification metadata is part of the Tabular Data Stream (TDS) protocol, and therefore this information can be used not just in the underlying table data, but also in the query execution phase and even in the client when receiving the result-set.<\/p>\n<p>And as it turns out, this matches our scenario perfectly.<\/p>\n<p>What we need to do is of course first classify the columns that we want to monitor access to. So, let\u2019s do that with our CreditCard table columns:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-6997\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_DataClassification.jpg\" alt=\"classifying table columns in SQL Server\" width=\"670\" height=\"354\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_DataClassification.jpg 670w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_DataClassification-300x159.jpg 300w\" sizes=\"auto, (max-width: 670px) 100vw, 670px\" \/><\/p>\n<p>Then we include the new action group in the Audit Specification:<\/p>\n<p>ALTER DATABASE AUDIT SPECIFICATION [AuditSpec_Select]<br \/>\nADD (SENSITIVE_BATCH_COMPLETED_GROUP)<\/p>\n<p>Now User Sneaky will again access the CreditCard numbers via the statistics using the Dynamic Management Function (DMF), which proved the hardest one to capture:<\/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>Checking the Audit log:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-6999\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_SBC_Log5-1030x46.jpg\" alt=\"audit log access to classified data\" width=\"1030\" height=\"46\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_SBC_Log5-1030x46.jpg 1030w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_SBC_Log5-300x13.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_SBC_Log5-768x34.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_SBC_Log5-705x32.jpg 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_SBC_Log5.jpg 1447w\" sizes=\"auto, (max-width: 1030px) 100vw, 1030px\" \/><\/p>\n<p>Gottcha!<\/p>\n<p>So instead of requiring a second Audit in master-database to capture access via system objects, the SENSITIVE_BATCH_COMPLETED_GROUP gets triggered no matter where the call came from. Simply because it touches classified data and this metadata is inherited by the statistics object!<\/p>\n<p>In Fact, the SENSITIVE_BATCH_COMPLETED_GROUP-Audit Action group captures all the 3 described methods to access the data: SELECT from table, DBCC SHOWSTATISTICS, SELECT from Dynamic Management Function, as we can see here:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7001\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_SBC_Log6.jpg\" alt=\"\" width=\"880\" height=\"98\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_SBC_Log6.jpg 880w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_SBC_Log6-300x33.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_SBC_Log6-768x86.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/08\/2508_Auditing_Audit_SBC_Log6-705x79.jpg 705w\" sizes=\"auto, (max-width: 880px) 100vw, 880px\" \/><\/p>\n<p>Neat, isn&#8217;t it?<\/p>\n<p>As we can see, if we have scenarios where we just need to audit very specific data access, Data Classification is of immense help.<\/p>\n<p>Of course, you need to make sure that nobody drops or in general manipulates the Data Classification itself. To audit ADD \/ DROP SENSITIVITY CLASSIFICATION you need add the SENSITIVITY_CLASSIFICATION_CHANGE_GROUP.<\/p>\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_dataaccess_auditing_data_classification_sqlserver\/&#038;t=How%20to%20Use%20Data%20Classification%20to%20Audit%20specific%20Data%20Access%20in%20SQL%20Server\" 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=How%20to%20Use%20Data%20Classification%20to%20Audit%20specific%20Data%20Access%20in%20SQL%20Server&#038;url=https:\/\/andreas-wolter.com\/en\/?p=6996\" 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=How%20to%20Use%20Data%20Classification%20to%20Audit%20specific%20Data%20Access%20in%20SQL%20Server&#038;url=https:\/\/andreas-wolter.com\/en\/202508_dataaccess_auditing_data_classification_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":6997,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[57],"tags":[380,381,206],"class_list":["post-6996","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-security-en","tag-auditing","tag-data-classification","tag-sql-security"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/6996","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=6996"}],"version-history":[{"count":2,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/6996\/revisions"}],"predecessor-version":[{"id":7005,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/6996\/revisions\/7005"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/6997"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=6996"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=6996"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=6996"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}