{"id":7116,"date":"2025-12-16T16:40:30","date_gmt":"2025-12-16T21:40:30","guid":{"rendered":"https:\/\/andreas-wolter.com\/?p=7116"},"modified":"2026-04-07T16:27:28","modified_gmt":"2026-04-07T21:27:28","slug":"202512_database_application_vendor_sqlserver_security_availability_checklist_sarpedonqualitylab","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/202512_database_application_vendor_sqlserver_security_availability_checklist_sarpedonqualitylab\/","title":{"rendered":"The Database Application Vendor&#8217;s SQL Server Security &#038; High Availability Checklist &#8211; v1"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-m0cxh8ps-477f40bdee455d44b5dfc84a57e6bfcc\">\n#top .av-special-heading.av-m0cxh8ps-477f40bdee455d44b5dfc84a57e6bfcc{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-m0cxh8ps-477f40bdee455d44b5dfc84a57e6bfcc .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-m0cxh8ps-477f40bdee455d44b5dfc84a57e6bfcc .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-m0cxh8ps-477f40bdee455d44b5dfc84a57e6bfcc 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\"  >The Database Application Vendor\u2019s SQL Server Security <span class='special_amp'>&amp;<\/span> High Availability Checklist by Sarpedon Quality Lab<\/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=\"aligncenter size-full wp-image-7117\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/12\/2025-12_Security_Checklist_Image.jpg\" alt=\"Server Security &#038; High Availability Checklist\" width=\"1024\" height=\"1024\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/12\/2025-12_Security_Checklist_Image.jpg 1024w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/12\/2025-12_Security_Checklist_Image-300x300.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/12\/2025-12_Security_Checklist_Image-80x80.jpg 80w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/12\/2025-12_Security_Checklist_Image-768x768.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/12\/2025-12_Security_Checklist_Image-36x36.jpg 36w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/12\/2025-12_Security_Checklist_Image-180x180.jpg 180w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/12\/2025-12_Security_Checklist_Image-705x705.jpg 705w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/p>\n<p>As a database application vendor, the security and reliability of your software are core competitive advantages. To help reaching this goal, we have created a checklist which is meant to serve as a blueprint for designing data applications that are <strong>secure by default<\/strong> and <strong>resilient to failure<\/strong>, ensuring your customers can trust your product in demanding enterprise environments.<\/p>\n<p>This is <strong>Version 1.0, 2025\/12\/16<\/strong><\/p>\n<p>Please visit <a href=\"https:\/\/andreas-wolter.com\/en\/2026_sqlserverdatabaseapplicationsecurityandhighavailabilitychecklist_v2\/\">The SQL Server Database Application Security &#038; High Availability Checklist &#8211;<strong> Version 2<\/strong><\/a>\u00a0for the current version.<\/p>\n<p>This list is structured into 3 buckets:<\/p>\n<ol>\n<li>Deployment Security<\/li>\n<li>Operational Security<\/li>\n<li>High Availability &#038; Connection Resilience<\/li>\n<\/ol>\n<h2>Deployment Security<\/h2>\n<table>\n<tbody>\n<tr>\n<td width=\"151\"><strong>Checklist item<\/strong><\/td>\n<td width=\"409\"><strong>Details<\/strong><\/td>\n<\/tr>\n<tr>\n<td width=\"151\"><strong>Can the database be pre-deployed by the DBA?<\/strong><\/td>\n<td width=\"409\">Ideally, the DBA has the option to prepare an empty database and the application setup can be pointed to the database and start preparing the database-schema with all objects from this point on.<br \/>\nThis has 4 advantages:1.\u00a0\u00a0\u00a0\u00a0\u00a0 Avoids handing over server-level permissions even temporarily.<\/p>\n<p>2.\u00a0\u00a0\u00a0\u00a0\u00a0 This will simplify the workflow when moving the database to an <strong>Azure SQL database<\/strong> environment.<\/p>\n<p>3.\u00a0\u00a0\u00a0\u00a0\u00a0 For Contained Availability Groups, this is the most efficient setup-workflow today (also see here my remarks on <strong><em>Deployment<\/em><\/strong>: <a href=\"https:\/\/andreas-wolter.com\/en\/2504_sqlserver_contained_availability_groups\/\">Why you should use SQL Server contained availability groups to save time \u2013 and why consultants may not tell you about them<\/a> )<\/p>\n<p>4.\u00a0\u00a0\u00a0\u00a0\u00a0 The DBA can optimize file-placement if needed for fast-growing system. The physical layout of your database should be transparent to the application and database-code.<\/td>\n<\/tr>\n<tr>\n<td width=\"151\"><strong>Permissions for database-deployment<\/strong><\/td>\n<td width=\"409\">The database-creation part of the setup usually should not require more than the<strong><em> CREATE ANY DATABASE<\/em><\/strong> permission. \u2013 <strong>sysadmin should not be required!<\/strong><\/p>\n<p>&#8211;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If SQL Agent jobs are involved, use the msdb roles (<a href=\"https:\/\/learn.microsoft.com\/en-us\/ssms\/agent\/sql-server-agent-fixed-database-roles\" target=\"_blank\" rel=\"noopener\">SQL Server Agent fixed database roles<\/a>)<\/p>\n<p>&#8211;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Other server level objects, if required, usually have distinct permissions as well.<\/td>\n<\/tr>\n<tr>\n<td width=\"151\"><strong>Remove server-level permissions after setup<\/strong><\/td>\n<td width=\"409\">If your application did require server-level permissions for setup, make sure that they can be revoked. Also, again, for an Azure SQL database, this would be a requirement anyway.<\/td>\n<\/tr>\n<tr>\n<td width=\"151\"><strong>Dedicated secure application identity<\/strong><\/td>\n<td width=\"409\">Allow the use of <strong>Group Managed Service Accounts<\/strong> (gMSA) for the Application service account, especially if it connects to database.<\/td>\n<\/tr>\n<tr>\n<td width=\"151\"><strong>Secure Credentials<\/strong><\/td>\n<td width=\"409\">If SQL Server Authentication is required, ensure the setup enforces strong password policies and that <strong>connection strings<\/strong> are <strong>encrypted<\/strong> and <strong>never stored in plain text<\/strong> in config files.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>Data Protection &#038; Code Integrity<\/h2>\n<p>These are the operational standards that protect the database itself, addressing the most common application-level security-risks.<\/p>\n<table>\n<tbody>\n<tr>\n<td width=\"151\"><strong>Checklist item<\/strong><\/td>\n<td width=\"409\"><strong>Details<\/strong><\/td>\n<\/tr>\n<tr>\n<td width=\"151\"><strong>Permissions \u2013 Comply with the Principle of Least Privilege<\/strong><\/td>\n<td width=\"409\">Do not require membership in db_owner-role or being the owner of the database<\/p>\n<p>Most applications do not require more than membership in the following database roles: db_datareader, db_datawriter, and the EXECUTE permission on the database, or a database role with the EXECUTE-permission. This is the simple code for that:<\/p>\n<p>CREATE DATABASE ROLE db_executor<\/p>\n<p>GRANT EXECUTE to db_executor<\/p>\n<p>Applications which create database objects during standard-use, will need also membership in the ddl_admin database role. However, the implied permissions allow for certain attacks from inside the database, so avoid it if not needed. It\u2019s still better that db_owner though!<\/td>\n<\/tr>\n<tr>\n<td width=\"151\"><strong>Schema-level permissions<\/strong><\/td>\n<td width=\"409\">If your database supports multiple different processes, each accessing different areas (objects) of the database, you can further lock down permissions by using schema-level permissions, if your database-schema is developed with those processes in mind. Read here for further details:<\/p>\n<p><a href=\"https:\/\/andreas-wolter.com\/en\/schema-design-for-sql-server-recommendations-for-schema-design-with-security-in-mind\/\">Schema-design for SQL Server: recommendations for Schema design with security in mind<\/a><\/td>\n<\/tr>\n<tr>\n<td width=\"151\"><strong>Encrypt sensitive data<\/strong><\/td>\n<td width=\"409\">If your application stores sensitive data (PCI, HealthCare, PII), <strong>use Data Encryption<\/strong> to make sure no unauthorized user can read the data. (Do not confuse with TDE at the file-level: <a href=\"https:\/\/andreas-wolter.com\/en\/protecting-database-data-at-rest-tde-backupencryption-alwaysencrypted\/\">Protecting database data at rest: Transparent Data Encryption, Backup Encryption or Always Encrypted<\/a> ) This can save millions!<\/td>\n<\/tr>\n<tr>\n<td width=\"151\"><strong>Avoid triggers<\/strong><\/td>\n<td width=\"409\">If the application requires triggers, <strong>sign triggers<\/strong> with a certificate (<a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/statements\/add-signature-transact-sql?view=sql-server-ver17\" target=\"_blank\" rel=\"noopener\">ADD SIGNATURE (Transact-SQL)<\/a> so you can ensure to detect any tampering.<\/td>\n<\/tr>\n<tr>\n<td width=\"151\"><strong>Avoid CLR<\/strong><\/td>\n<td width=\"409\">When using CLR-assemblies, use certificate-signed assemblies. <strong>Do not rely on the Trustworthy-database property<\/strong>. (This is a high EoP risk and will not allow the database being co-hosted with other databases on the same system.)<\/td>\n<\/tr>\n<tr>\n<td width=\"151\"><strong>Never construct dynamic SQL directly from user input and validate parameters<\/strong><\/td>\n<td width=\"409\">To <strong>prevent<\/strong> <strong>SQL Injection<\/strong>, validate string- and binary-parameters. Never construct dynamic SQL directly from user input.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>High Availability (HA) &#038; Connection Resilience<\/h2>\n<table>\n<tbody>\n<tr>\n<td width=\"189\"><strong>Checklist item<\/strong><\/td>\n<td width=\"409\"><strong>Details<\/strong><\/td>\n<\/tr>\n<tr>\n<td width=\"189\"><strong>Test application for common High Availability Technologies<\/strong><\/td>\n<td width=\"409\">Understand the difference between (1) <strong><em>Failover Clustering<\/em><\/strong>, (2) <strong><em>Availability Groups<\/em><\/strong> and the new (3) <strong><em>Contained Availability Groups<\/em><\/strong> and test their behavior.<\/td>\n<\/tr>\n<tr>\n<td width=\"189\"><strong>Allow using DNS Alias instead of Hostnames or IPs<\/strong><\/td>\n<td width=\"409\">To support Availability Groups, applications must connect using the Availability Group Listener Name, not a direct replica name. And to simplify Migrations, using a DNS Alias avoids further changes to the connection strings, which is why <strong><em>Sarpedon Quality Lab<\/em><\/strong> recommends using DNS Aliases (CNAME) in general.<\/td>\n<\/tr>\n<tr>\n<td width=\"189\"><strong>Support MultiSubnetFailover=True in Connection String<\/strong><\/td>\n<td width=\"409\">Include <strong><em>MultiSubnetFailover=True <\/em><\/strong>in your connection strings. This ensures <strong>fast reconnection<\/strong> during a failover, <u>not only across different subnets<\/u>.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>By adopting these standards, you are not just ensuring compliance; you are guaranteeing that your application is secure by design, a critical assurance that builds confidence with every enterprise customer.<\/p>\n<p>We hope that our checklist answers some of the common questions and is useful as a reference by both DBA\u2019s and Application Developers.<\/p>\n<p>Any feedback and input on refining this checklist is welcome!<\/p>\n<p>Happy securing<\/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\/202512_database_application_vendor_sqlserver_security_availability_checklist_sarpedonqualitylab\/&#038;t=The%20Database%20Application%20Vendor%E2%80%99s%20SQL%20Server%20Security%20%26%20High%20Availability%20Checklist%20%E2%80%93%20v1\" 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=The%20Database%20Application%20Vendor%E2%80%99s%20SQL%20Server%20Security%20%26%20High%20Availability%20Checklist%20%E2%80%93%20v1&#038;url=https:\/\/andreas-wolter.com\/en\/?p=7116\" 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=The%20Database%20Application%20Vendor%E2%80%99s%20SQL%20Server%20Security%20%26%20High%20Availability%20Checklist%20%E2%80%93%20v1&#038;url=https:\/\/andreas-wolter.com\/en\/202512_database_application_vendor_sqlserver_security_availability_checklist_sarpedonqualitylab\/\" 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":7117,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[57],"tags":[206],"class_list":["post-7116","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\/7116","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=7116"}],"version-history":[{"count":8,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/7116\/revisions"}],"predecessor-version":[{"id":7120,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/7116\/revisions\/7120"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/7117"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=7116"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=7116"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=7116"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}