{"id":6672,"date":"2025-02-10T19:53:04","date_gmt":"2025-02-10T18:53:04","guid":{"rendered":"https:\/\/andreas-wolter.com\/?p=6672"},"modified":"2025-08-06T22:01:06","modified_gmt":"2025-08-07T03:01:06","slug":"2502-sql-auditing-missing-permission-changes-security-bug","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/2502-sql-auditing-missing-permission-changes-security-bug\/","title":{"rendered":"SQL Server security admins, attention: Auditing is missing attempts to change permissions, leading to repudiation and miss elevation attempts"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-m0cxh8ps-496fc890dc2959b37df66d0349167231\">\n#top .av-special-heading.av-m0cxh8ps-496fc890dc2959b37df66d0349167231{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-m0cxh8ps-496fc890dc2959b37df66d0349167231 .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-m0cxh8ps-496fc890dc2959b37df66d0349167231 .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-m0cxh8ps-496fc890dc2959b37df66d0349167231 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\"  >SQL Server security admins, attention: Auditing is missing attempts to change permissions, leading to repudiation and miss elevation attempts<\/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><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-6676 size-full\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/02\/2502-SQL-Audit-bug-permissions-1.jpg\" alt=\"SQL Audit bug permissions\" width=\"576\" height=\"189\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/02\/2502-SQL-Audit-bug-permissions-1.jpg 576w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/02\/2502-SQL-Audit-bug-permissions-1-300x98.jpg 300w\" sizes=\"auto, (max-width: 576px) 100vw, 576px\" \/><\/p>\n<p>Auditing is one if not the most crucial security control (read more about it here: <a href=\"https:\/\/techcommunity.microsoft.com\/blog\/azuresqlblog\/security-concept-audit-trail\/2783486\" target=\"_blank\" rel=\"noopener\">Security concept: Audit Trail<\/a>): without it it\u2019s hard to figure out exactly how a breach happened, when it started, sometimes even if it happened at all and last but not least: \u201cwhodunnit\u201d \u2013 in technical terms: <strong>non-repudiation<\/strong>. Unless the breach is at a stage that users notice, only Auditing can tell you that a breach happened.<\/p>\n<p style=\"padding-left: 40px;\"><em>Advice<\/em><br \/>\nAll SQL Server installations should contain at least a basic security audit.<\/p>\n<p>When running some tests in November last year I noticed that some events were not captured on a new SQL Server 2022 system. We (<a href=\"https:\/\/www.sarpedonqualitylab.us\/\" target=\"_blank\" rel=\"noopener\">Sarpedon Quality Lab LLC<\/a>, USA and <a href=\"https:\/\/www.sarpedonqualitylab.com\/\" target=\"_blank\" rel=\"noopener\">Sarpedon Quality Lab GmbH<\/a>, Germany) quickly ran more tests and compared different systems with a definitive result:<\/p>\n<p><strong>SQL Auditing fails to capture the<\/strong> <strong>SERVER_OBJECT_PERMISSION_CHANGE_GROUP<\/strong> Audit Action group<\/p>\n<p>Affected:<strong> SQL Server 2022 <\/strong>(including CU 17) and<strong> Azure SQL Managed Instance<\/strong>. No fix till today.<\/p>\n<p>This <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/security\/auditing\/sql-server-audit-action-groups-and-actions?view=sql-server-ver16\" target=\"_blank\" rel=\"noopener\">server-level audit action group<\/a> is being monitored on our customer\u2019s servers, because <strong>events in this category can directly impact the security of the SQL Server Instance<\/strong>.<\/p>\n<p>The first to inform (besides our customers) was the security team at Microsoft Data of course and also wasted many hours with the Microsoft Support system just to get this rolling as a ticket.<\/p>\n<p>Given the timeline and importance of this event, I want to raise awareness of this auditing-gap, hoping it gets fixed soon.<\/p>\n<h2>Recommended temporary workaround<\/h2>\n<p>There is no practical workaround for Auditing. Auditing every <em>batch_completed<\/em> is likely going to impact not only the system itself but also overload the audit and processing thereof.<\/p>\n<p>Instead I recommend to <strong>keep track of server object permissions by taking scheduled snapshots of the system table <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/system-catalog-views\/sys-server-permissions-transact-sql?view=sql-server-ver16\" target=\"_blank\" rel=\"noopener\">sys.server_permissions<\/a><\/strong> and compare it over time for changes. Changes of server-level permissions should be the exception and always have a proper explanation. This way one would at least capture permissions that are changes and left in place. What it can miss are changes to permissions and then reverting the change quickly again. Therefore, <strong>make sure to audit other events like impersonation or role-membership changes<\/strong>.<\/p>\n<h2>Code to test<\/h2>\n<p>And here is the code to test this event on your system:<\/p>\n<p>Set up an Audit including the event \u201cSERVER_OBJECT_PERMISSION_CHANGE_GROUP\u201d:<\/p>\n<p>USE [master]<\/p>\n<p>GO<\/p>\n<p>CREATE SERVER AUDIT SARPEDON_SecurityAuditToFile<\/p>\n<p>TO FILE &#8212; configure according to your needs<\/p>\n<p>(\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FILEPATH\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = N&#8217;D:\\SQLData\\Audits&#8217;<\/p>\n<p>,\u00a0\u00a0\u00a0\u00a0\u00a0 MAXSIZE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 100 MB<\/p>\n<p>,\u00a0\u00a0\u00a0\u00a0\u00a0 MAX_ROLLOVER_FILES\u00a0 = 100<\/p>\n<p>,\u00a0\u00a0\u00a0\u00a0\u00a0 RESERVE_DISK_SPACE\u00a0 = OFF<\/p>\n<p>) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)<\/p>\n<p>GO<\/p>\n<p>CREATE SERVER AUDIT SPECIFICATION SARPEDON_SecurityAuditSpec<\/p>\n<p>FOR SERVER AUDIT SARPEDON_SecurityAuditToFile<\/p>\n<p>ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),<\/p>\n<p>&#8212; &#8230; in addition to many others<\/p>\n<p>GO<\/p>\n<p>ALTER SERVER AUDIT SARPEDON_SecurityAuditToFile<\/p>\n<p>WITH (STATE = ON)<\/p>\n<p>GO<\/p>\n<p>ALTER SERVER AUDIT SPECIFICATION SARPEDON_SecurityAuditSpec<\/p>\n<p>WITH (STATE = ON)<\/p>\n<p>Test: if the system is affected by the bug, it will NOT capture any of the following event types:<\/p>\n<p>&#8212; replace &#8220;MALICIOUS&#8221; with a Test Login of your choice<\/p>\n<p>GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO MALICIOUS;<\/p>\n<p>GO<\/p>\n<p>GRANT ALTER ON ENDPOINT::[TSQL Default TCP] TO MALICIOUS<\/p>\n<p>GO<\/p>\n<p>GRANT IMPERSONATE ON LOGIN::sa TO MALICIOUS<\/p>\n<p>GO<\/p>\n<p>GRANT ALTER ON LOGIN::sa TO MALICIOUS<\/p>\n<p>GO<\/p>\n<p>&#8212; [role_SecurityAdmins] is a custom server role<\/p>\n<p>GRANT ALTER ON SERVER ROLE::[role_SecurityAdmins] TO MALICIOUS<\/p>\n<p>GO<\/p>\n<h2>Call to action<\/h2>\n<p>Please upvote the official bug-report:<br \/>\n<a href=\"https:\/\/feedback.azure.com\/d365community\/idea\/e1cb8c42-70e3-ef11-b542-00224854717c\" target=\"_blank\" rel=\"noopener\">Security-bug: SQL Auditing SERVER_OBJECT_PERMISSION_CHANGE_GROUP does not capture anything<\/a><\/p>\n<p>And while you\u2019re there, why not also vote for these other Auditing related suggestions:<\/p>\n<ol>\n<li><a href=\"https:\/\/feedback.azure.com\/d365community\/idea\/1358762b-64e3-ef11-b542-00224854717c\" target=\"_blank\" rel=\"noopener\">Having the same Audit name on server and inside contained AG will lead to error when trying to start it (but not when creating it)<\/a><\/li>\n<li><a href=\"https:\/\/feedback.azure.com\/d365community\/idea\/e39b989e-eeb6-ee11-92bc-0022484c4141\" target=\"_blank\" rel=\"noopener\">Implicit addition of database user is not audited<\/a><\/li>\n<li><a href=\"https:\/\/feedback.azure.com\/d365community\/idea\/99ffe952-e6a8-ee11-92bc-000d3a037f01\" target=\"_blank\" rel=\"noopener\">When adding an Entra ID admin for an ARC-enabled SQL Server this needs to be auditable in the instance itself<\/a><\/li>\n<li><a href=\"https:\/\/feedback.azure.com\/d365community\/idea\/57ca4365-71e3-ef11-b542-00224854717c\" target=\"_blank\" rel=\"noopener\">RESTORE VERIFYONLY should not be captured under CREATE DATABASE Audit Action group<\/a><\/li>\n<li><a href=\"https:\/\/feedback.azure.com\/d365community\/idea\/4a378000-82bd-ef11-95f5-000d3a7d9e49\" target=\"_blank\" rel=\"noopener\">Document ALL_AUDIT_SPECIFICATIONS_AND_ACTIONS wait type<\/a><\/li>\n<\/ol>\n<p>Thank you<\/p>\n<p>Happy Auditing<\/p>\n<p>Andreas<\/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\/2502-sql-auditing-missing-permission-changes-security-bug\/&#038;t=SQL%20Server%20security%20admins%2C%20attention%3A%20Auditing%20is%20missing%20attempts%20to%20change%20permissions%2C%20leading%20to%20repudiation%20and%20miss%20elevation%20attempts\" 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=SQL%20Server%20security%20admins%2C%20attention%3A%20Auditing%20is%20missing%20attempts%20to%20change%20permissions%2C%20leading%20to%20repudiation%20and%20miss%20elevation%20attempts&#038;url=https:\/\/andreas-wolter.com\/en\/?p=6672\" 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=SQL%20Server%20security%20admins%2C%20attention%3A%20Auditing%20is%20missing%20attempts%20to%20change%20permissions%2C%20leading%20to%20repudiation%20and%20miss%20elevation%20attempts&#038;url=https:\/\/andreas-wolter.com\/en\/2502-sql-auditing-missing-permission-changes-security-bug\/\" 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":6676,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[57],"tags":[380,27],"class_list":["post-6672","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-security-en","tag-auditing","tag-security-en"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/6672","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=6672"}],"version-history":[{"count":4,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/6672\/revisions"}],"predecessor-version":[{"id":6994,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/6672\/revisions\/6994"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/6676"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=6672"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=6672"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=6672"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}