The Database Application Vendor’s SQL Server Security & High Availability Checklist​

The application should support installation into an existing empty database prepared by the customer's DBA.

The DBA should be able to define the database name and file locations according to the organization's standards. The application setup should then connect to that database and create only the required database-level objects, such as schemas, tables, views, stored procedures, and functions.

This avoids the need for server-level permissions during setup and keeps physical database layout independent from application code.

This is especially important for:

• fast-growing systems where file placement and sizing matter;
• environments using Contained Availability Groups, where databases and server-level dependencies cannot be created in one uninterrupted setup sequence;
• future migration paths to Azure SQL Database.

Use CREATE ANY DATABASE as the maximum required server-level permission for database creation, if the customer DBA cannot pre-create the database (D1).

The installer should not require sysadmin or Windows administrator rights. - Requiring sysadmin for database setup is a security red flag and may require a customer-side exception, supervision, and auditing.

Do not require user or application accounts to access SQL Server data folders. - Database files are created by the SQL Server service account; granting file-system access to data folders can allow file corruption or tampering.

Note: Avoid using ##MS_DatabaseManager## as a shortcut. It has a documented unresolved privilege-escalation path. CREATE ANY DATABASE remains the appropriate minimal grant for database deployment.

Any server-level permissions required during setup must be removable after installation completes.

Elevated permissions should be temporary, installation-only, and documented.

• Clearly identify every server-level permission required during setup.
• Provide cleanup steps or scripts to remove setup-only permissions.

The application should run under a dedicated service identity - not under a shared administrator, DBA, user, or developer account.

For Windows-based deployments that connect to SQL Server, the application should ideally support Group Managed Service Accounts (gMSA).

For Azure-based deployments, it should support Managed Identities where applicable.

Do not share the same identity across unrelated applications or environments unless explicitly justified.

The application should prefer Windows Authentication for SQL Server and Managed Identity / Entra ID authentication for Azure-based deployments.

Avoid requiring SQL Authentication unless there is a clear technical reason. If SQL Authentication is required, provide a roadmap for when it can be replaced, or document the technical reason it cannot be.

Requiring SQL Authentication forces customers to enable Mixed Mode Authentication at the SQL Server instance level. That increases the attack surface for every database on the instance and may require the customer to deploy the application on a separate SQL Server instance.

If SQL Authentication is required, SQL credentials must be protected during installation, storage, and runtime use.

• Enforce strong passwords for SQL logins used by the application.
• Do not store SQL passwords in plain text configuration files.
• Encrypt connection strings or store credentials in a secure secret store.
• Support integration with customer-managed secret stores where possible, such as Azure Key Vault or an enterprise password vault.
• Do not log SQL passwords during setup, testing, troubleshooting, or connection failures.
• Limit the SQL login to the minimum required database permissions.
• Document how credentials are stored and how they can be rotated, and revoked.

When deployment imports data from the file system, use a dedicated folder (network share) and grant the least amount of privileges required.

For BULK INSERT, grant read-only access only to the SQL Server service account. The application account does not need direct file access when SQL Server performs the file read.

For exports, document which component performs the write operation, such as bcp, SSIS, SQL Server, or custom application code.

Application accounts should not require membership in db_owner and should not own the database.

Most applications should run with the read and write permissions on the database granted via the database roles:

• db_datareader
• db_datawriter

For applications that execute stored procedures broadly, grant EXECUTE permission through a dedicated database role rather than directly to the user, following the Login → User → Role → Permission (LURP) model.

Example (T-SQL):

CREATE DATABASE ROLE db_executor;
GRANT EXECUTE TO db_executor;

• Do not require db_owner for normal application operation.
• Do not make the application account the database owner.
• Grant permissions through dedicated database roles.

Note: If the application creates or modifies database objects during normal operation, it may require additional permissions such as ALTER on a schema or, in broader cases, db_ddladmin. Avoid db_ddladmin unless necessary. It is still preferable to db_owner, but it grants powerful permissions and should be treated as elevated access.

If the application has separate components, services, modules, or background jobs that access different parts of the database, permissions should be separated by schema where practical.

This lets each process access only the objects it needs.

• Design schemas around functional areas or application components where possible. Reference: Schema-design for SQL Server: recommendations for Schema design with security in mind
• Grant permissions at the schema level only when all objects in that schema share the same access pattern.

Example (T-SQL):

GRANT EXECUTE ON SCHEMA::Billing TO app_billing_role;
GRANT SELECT, INSERT, UPDATE ON SCHEMA::Orders TO app_order_role;

If the application stores sensitive data, such as PCI data, healthcare data, credentials, secrets, or regulated personal information, use data encryption.

Do not rely on customer-implemented Transparent Data Encryption as a data-protection control. - TDE protects database backup files, but it does not protect against compromised application access or against privileged users with access to the host.

• Identify sensitive data fields explicitly.
• Use column-level encryption, Always Encrypted, application-level encryption, or another suitable protection mechanism where needed.
• Do not store secrets, tokens, passwords, or API keys in plain text.
• Protect encryption keys separately from the database.
• Document how encryption keys are stored, rotated, backed up, and recovered.

Triggers can become hidden execution paths. If an attacker or overly privileged user can modify a trigger, they may be able to execute code indirectly through normal application activity.

• Avoid triggers unless they are required.
• Restrict who can create, alter, or disable triggers.
• Monitor trigger definitions for unauthorized changes - signatures can be used to detect tampering; see ADD SIGNATURE (Transact-SQL).

SQL CLR should not be required unless there is a clear technical reason.

If CLR assemblies are used, they must be deployed securely and should not depend on the database TRUSTWORTHY property.

• Use signed assemblies.
• Do not require TRUSTWORTHY ON (see also O6).

The application should not require the database TRUSTWORTHY property to be set to ON.

TRUSTWORTHY ON enables elevation-of-privilege paths, especially when combined with powerful database ownership, unsafe assemblies, impersonation, or cross-database access.

• Use certificate signing or module signing instead of TRUSTWORTHY where elevated permissions are needed.
• Requiring TRUSTWORTHY may require the customer to deploy the application on a separate SQL Server instance.

The application must use safe query construction and parameter handling. Dynamic SQL should never be built by concatenating untrusted input directly into command text.

• Use parameterized queries or stored procedures with parameters.
• Validate string, numeric, date, binary, and identifier inputs.
• Do not concatenate user input into SQL command text.
• Avoid granting excessive database permissions.
• Ensure error messages do not expose SQL text, schema details, or sensitive data.

The application should not require broad access to msdb.

If the application uses SQL Server Agent jobs, Database Mail, SSIS, backup metadata, or other msdb features, the required access must be clearly documented and limited to the minimum necessary permissions.

• Use the built-in msdb fixed database role SQLAgentUserRole to execute jobs as a maximum requirement.

Ensure the application supports encrypted SQL Server connections using TLS 1.2 or later.

• Use a current Microsoft-supported SQL Server driver, such as Microsoft.Data.SqlClient for .NET, Microsoft JDBC Driver for SQL Server for Java, Microsoft ODBC Driver for SQL Server for ODBC-based applications, or Microsoft OLE DB Driver for SQL Server where OLE DB is required.
• Test connectivity in an environment where TLS 1.0 and TLS 1.1 are disabled.
• Do not use TrustServerCertificate=True as a workaround for certificate validation problems.

The application should be tested against the SQL Server high-availability patterns customers commonly use, not only against a single standalone test instance.

Always use the customer-provided stable SQL Server endpoint - listener, FCI network name, or DNS alias - and do not switch to a resolved physical node name or IP address.

• Test with Failover Cluster Instances.
• Test with Availability Groups.
• Test with Contained Availability Groups.
• Validate application behavior during planned and unplanned failover.
• Handle transient connection failures and reconnect cleanly.
• Do not cache a resolved replica, host, or IP address in a way that bypasses the listener or DNS alias.
• Document which HA technologies are tested and which are not supported.

The application should connect through a stable customer-controlled name, not directly to a physical SQL Server host or IP address.

For Availability Groups, the application must support connecting through the Availability Group listener. A DNS alias pointing to the listener or target SQL Server name gives the customer more flexibility during failover, migration, and consolidation.

• Support DNS aliases, Availability Group listeners, named instances, and explicit ports where applicable.
• Do not require direct connections to a specific replica, node, hostname, or IP address.

For SQL Server clients that support it, include MultiSubnetFailover=True in the connection string.

This improves connection behavior for Availability Groups and Failover Cluster Instances, especially during failover. It is useful even when the current deployment is not multi-subnet, and it has no adverse effect when no Availability Group is used.

Use a current Microsoft-supported SQL Server driver with validated HA behavior, such as Microsoft.Data.SqlClient, Microsoft JDBC Driver for SQL Server, or Microsoft ODBC Driver for SQL Server.

• Use a current SQL Server driver that supports MultiSubnetFailover.
• Set MultiSubnetFailover=True by default for supported drivers.

If SQL Server encryption is enforced, certificate validation must work with the exact name the application uses to connect.

A certificate can validate correctly against the server hostname but fail when the application connects through a DNS alias or Availability Group listener. The client validates the name in the connection string, not merely the server it resolves to.

• Test encrypted connections through a DNS alias or listener in a lab.
• Ensure the certificate Subject Alternative Name (SAN) includes the alias or listener name used by the application.
• Use a current supported driver, such as Microsoft.Data.SqlClient, ODBC Driver 17 or later, or JDBC Driver 6.0 or later.
• Do not resolve certificate-name problems by setting TrustServerCertificate=True or TrustServerCertificate=Yes.
• Document the expected certificate and connection-string requirements for encrypted deployments.

Note: TrustServerCertificate disables certificate validation. It should not be used as a workaround for a name mismatch.