Availability group seeding mode
Issue: When a database is added to an Always On Availability Group, the secondary replicas need an initial copy of the database before synchronization can begin. SQL Server supports two seeding modes for this initial copy: MANUAL, where the administrator restores a backup of the primary on each secondary, and AUTOMATIC, where SQL Server streams the database directly from the primary to the secondary over the AG endpoint.
The seeding mode is set per replica using the SEEDING_MODE option in CREATE AVAILABILITY GROUP or ALTER AVAILABILITY GROUP. Once a replica has been fully seeded and is synchronizing normally, the seeding mode setting continues to apply for any new databases added to the AG in the future.
This finding identifies replicas that are currently configured with a seeding mode of AUTOMATIC. The setting is often left in place after initial seeding without being reviewed for whether it should remain.
Problem: Automatic seeding is a good option for the initial copy of a database to a secondary, particularly when paired with trace flag 9567 (often used for compression) to reduce the network and time impact of seeding large databases. The mechanism handles the file copy and synchronization handoff automatically, removing several manual steps from the deployment process.
After the replica is fully seeded and synchronizing, however, the automatic seeding mode continues to apply to any new database added to the AG. This means the next database added is automatically streamed to every secondary configured for automatic seeding, which may not be the desired behavior. In environments where new databases are added regularly, this can produce unexpected resource usage on the primary and secondaries during the seeding process.
Automatic seeding also adds a small amount of ongoing overhead to maintain the readiness state on the primary, since SQL Server must be prepared to seed at any time. The cost is small but real, and it provides no benefit once existing replicas are fully synchronized and stable.
Learn More...Endpoint not started
Issue: Always On Availability Groups communicate between replicas through a TCP database mirroring endpoint, typically named Hadr_endpoint and configured during AG creation to listen on a specific port (5022 by default). Each replica in an AG has its own endpoint, and the endpoints handle the log record streaming, health detection, and synchronization traffic that keep the AG running.
The endpoint has a state recorded in sys.tcp_endpoints and can be either STARTED (accepting connections) or STOPPED (not accepting connections). The normal operating state is STARTED, and the endpoint must be running for the local replica to participate in the AG.
This finding identifies replicas where the AG endpoint is currently in the STOPPED state. The condition can result from manual administrative action, certain failure scenarios, or configuration changes that left the endpoint in an unintended state.
Problem: A stopped endpoint cannot send or receive AG traffic, which means the local replica cannot synchronize with its partners. If the affected replica is the primary, secondaries cannot receive log records and fall behind, causing the primary's transaction log to grow because records cannot be truncated until they are delivered. If the affected replica is a secondary, that secondary cannot receive updates and falls out of synchronization, eliminating its usefulness for failover or backup offload.
The condition also affects automatic failover. If the primary fails while a secondary's endpoint is stopped, the cluster may not be able to fail over to that secondary because health detection and the synchronization handshake depend on endpoint connectivity. This can turn a routine failover into an extended outage that requires manual intervention to restore the AG.
A stopped endpoint can be hard to spot because the AG dashboard and standard monitoring may continue to show partial information. The synchronization state shows as not synchronizing or in an error state, but the underlying cause (the endpoint itself) is not always obvious without specifically checking endpoint status.
Endpoints can also be stopped silently as a side effect of other administrative actions. Certain SQL Server upgrades, security configuration changes, or scripts that drop and recreate endpoints can leave the endpoint in a stopped state until someone explicitly starts it. The AG may have been working perfectly before the change and stops working immediately afterward, with the cause not obvious to anyone troubleshooting the resulting symptoms.
Learn More...Cluster quorum members
Issue: SQL Server Always On Availability Groups and Failover Cluster Instances rely on Windows Server Failover Clustering (WSFC) for high availability coordination. The cluster maintains a list of members (nodes) and tracks their current state, and it uses a quorum model to determine whether enough of the cluster is online to safely make decisions about resource ownership and failover.
Quorum is calculated from votes assigned to cluster members and, depending on the quorum model, a witness (file share, disk, or cloud witness). The cluster must have a majority of votes online and reachable to maintain quorum and continue operating; if quorum is lost, the cluster shuts down its resources to prevent split brain scenarios.
This finding identifies clusters where one or more members are currently offline, where vote configuration is out of balance, or where the remaining online members do not constitute a healthy quorum margin.
Problem: A cluster running with reduced membership has reduced resilience to additional failures. A two-node cluster with one node offline has no remaining redundancy: the loss of the surviving node takes the cluster fully offline, which means any AG or FCI hosted on the cluster also goes offline, taking applications with it.
Quorum problems can also cause unexpected behavior even when the visible members appear healthy. If the vote configuration is misaligned (for example, two votes online and two offline with no witness to break ties), a brief network partition can split the cluster into two halves that each believe they should host resources, producing the split brain condition that quorum exists to prevent.
The SQL Server side experience of quorum loss is often confusing. AGs and FCIs become unavailable suddenly, sometimes with limited error messaging at the SQL Server level, and the actual cause sits at the Windows clustering layer rather than in SQL Server itself. Diagnosing the issue requires looking at cluster events, which may be unfamiliar territory for teams focused on the database engine.
The condition often persists because clusters are designed to keep running even with reduced membership, so the immediate symptoms of an offline node are limited until additional failures occur. Without monitoring specifically focused on cluster health, an offline node can remain undetected for days or longer, leaving the cluster in a fragile state that no one knows about.
Learn More...Availability group ownership
Issue: Always On Availability Groups have an owner, recorded internally and visible through sys.availability_groups_cluster joined with related views, that represents the SQL Server login responsible for the AG. The owner is typically set to whichever login created the AG, which is often the administrator who performed the initial deployment.
This finding identifies AGs whose owners are individual user logins rather than a system or service principal such as sa or a dedicated owner account. The condition is most often the result of AGs being created ad hoc by an individual administrator without consideration of long-term ownership.
The owner of an AG affects how the AG is treated during certain operations and what happens when the owning login changes or is removed.
Problem: When an AG is owned by an individual user login, the AG becomes tied to that person's account lifecycle. If the user leaves the organization, changes roles, or has their login disabled or dropped, certain AG operations can produce errors or unexpected behavior, particularly during failover, replica changes, and configuration updates.
User owned AGs also complicate audit and review. The presence of AGs owned by former employees or by personal accounts produces audit findings and indicates that AG ownership is not being managed deliberately. This is particularly visible during compliance reviews that examine ownership of critical infrastructure components.
Learn More...Service account permissions
Issue: SQL Server runs as a Windows service under a configured service account, which provides the security context for the engine to interact with the operating system, the file system, and the network. The choice of service account affects what the engine can do at the operating system level and which permissions are needed for various features.
This finding identifies instances where the SQL Server service account has elevated permissions beyond what is required to run SQL Server, such as membership in the local Administrators group on the host or membership in the sysadmin fixed server role.
The condition is sometimes the result of installing SQL Server with a privileged account out of expedience, particularly during testing or initial deployment, and not being changed later. Generally speaking, the permissions required of the service account are added during installation or by adding the account in the SQL Server Configuration Manager.
Problem: A SQL Server service account with elevated permissions expands the impact of any compromise of the SQL Server process. If an attacker exploits a vulnerability in the engine or extracts the service account credentials through other means, the attacker inherits whatever permissions the service account holds. A service account in the local Administrators group provides full operating system control, while one with sysadmin membership through a login provides full database engine control.
The principle of least privilege applies as much to service accounts as to user accounts. SQL Server itself does not require its service account to be a local administrator or to have an explicit sysadmin login on the instance, since the engine grants the service account the necessary internal access automatically. Granting additional permissions provides little to no operational benefit but creates a larger attack surface for the same workload.
Elevated service accounts also complicate audit and compliance review. Most regulatory frameworks expect service accounts to follow least privilege, and a SQL Server service account in a privileged group is typically flagged during audits even when no actual misuse has occurred. Justifying the configuration after the fact may be harder than maintaining a least-privilege baseline from the start.
Learn More...sa login renamed
Issue: The sa login is the default system administrator account created during SQL Server installation, with a fixed and well-known name that is the primary target of brute-force authentication attacks against any SQL Server instance reachable on the network. Beginning with SQL Server 2005, the sa login can be renamed, which is often thought to remove the well-known name as an attack vector while preserving the underlying account and its security context.
This finding identifies instances where the sa login has been renamed to something other than sa. The renamed login retains all the original properties of sa, including its membership in the sysadmin role and its underlying SID. Only the name has changed.
Problem: Renaming the sa login is a defense-in-depth measure can meaningfully reduces the success rate of automated authentication attacks. Brute-force tools and credential-stuffing scripts that target SQL Server typically try the literal name sa first and most often, since it is the only universally guaranteed login name across SQL Server installations. Removing the well-known name as a target deflects much of this opportunistic attack traffic.
However, the protection is not absolute. An attacker who specifically targets your environment can discover the renamed account through enumeration of sys.server_principals, which the public role has access to by default. The rename is most effective against generic, automated attacks rather than targeted ones, and it is best paired with other controls including disabling the account, enforcing strong password policies, and limiting network exposure.
The condition is worth noting primarily because the team needs to be aware of the rename and to know the current name of the account. Operational tasks that historically used sa (typically as a dedicated job owner, database owner, or troubleshooting account) need to use the renamed login, and documentation that references sa by name needs to be updated to reflect the actual account name.
Unfortunately, the rename can also surprise team members who are not aware of it, particularly during incident response or troubleshooting when references to the standard sa name fail because the account no longer exists under that name. Without clear documentation, time can be lost trying to use a login name that does not work, and confusion can lead to additional configuration changes that should not have been needed.
Learn More...Contained database
Issue: Contained databases are a SQL Server feature that allows a database to be more independent of the SQL Server instance hosting it. In a partially contained database, users can be created and authenticated at the database level rather than through server level logins, which means the database can be moved or restored to another instance without requiring login synchronization or recreation.
This finding identifies instances where one or more databases are configured for partial containment. The feature is sometimes enabled intentionally to support portability or specific application requirements, and other times appears as a side effect of database creation by tools that default to containment.
Problem: Contained database users authenticate directly against the database without requiring a corresponding server level login. This is convenient for portability, but it also means that anyone with a contained user account can connect to the SQL Server instance and gain access to the contained database without ever appearing in the server level security configuration.
This changes the security review model. Reviewing logins at the server level no longer provides a complete picture of who can connect to the instance, since contained users connect through the same network endpoints but are not visible in sys.server_principals. Reviews must now extend into each contained database to understand the full set of authenticated users.
Contained databases also have a privilege escalation consideration. A user with db_owner rights in a contained database can create new contained users, including users with strong passwords and broad rights inside that database. While the access remains scoped to the contained database, the ability to create authenticated principals without server level approval is a meaningful shift in the security model that deserves careful permission management on the db_owner role.
Containment also affects features that rely on server level objects, including SQL Agent jobs, server level triggers, and certain replication configurations. Some of these continue to work normally, while others may produce unexpected behavior when the database is moved between instances. Teams should verify that any external dependencies are documented and tested before relying on portability.
Learn More...Remote access
Issue: The remote access server configuration option controls whether stored procedures can be executed from remote servers using the four-part naming convention (servername.database.schema.procedure). It is a legacy setting from older versions of SQL Server, predating the modern linked server architecture, and is enabled by default with a value of 1.
Microsoft has marked this feature as deprecated and has stated that it may be removed from a future release of SQL Server. Despite the deprecation, it remains enabled by default in current versions and is still in use by certain operational features, most notably the log shipping status report in SQL Server Management Studio.
This finding identifies instances where the remote access configuration is currently enabled.
Problem: The remote access feature predates and overlaps with the linked server functionality that has replaced most of its original use cases. Modern distributed query patterns use linked servers, which provide better security mappings, more granular configuration, and clearer audit trails than the older remote access mechanism. Most environments that have remote access enabled are not actively using the feature and could disable it without operational impact.
Leaving the feature enabled when it is not needed is a small surface-area concern. The feature provides a connectivity path that, like other legacy connectivity features, could potentially be misused if combined with other misconfigurations. While the practical risk is low, the principle of disabling unused features applies.
The main complication with disabling remote access is the dependency in SQL Server Management Studio's log shipping status report. If your environment relies on the SSMS-based log shipping report for monitoring or troubleshooting, the report will not function correctly with remote access disabled. Other log shipping monitoring approaches (custom queries, third-party monitoring tools) are not affected.
The deprecation also matters for long-term planning. Although Microsoft has not announced a specific removal timeline, features marked as deprecated are eventually removed, and any environment that genuinely depends on this functionality will need to migrate before that happens. Evaluating the dependency now provides time to plan the migration on your schedule rather than under deadline pressure.
Learn More...Force encryption
Issue: SQL Server supports encrypting client connections using TLS, which protects data in motion between clients and the Database Engine. The Force Encryption setting in SQL Server Configuration Manager (instance protocol properties, Flags tab) determines whether the server encrypts all client/server communication or allows clients to connect without encryption unless they request it.
When Force Encryption is enabled, SQL Server encrypts all client/server communication and clients must be able to establish TLS and trust the server certificate chain (or otherwise be configured appropriately). If a CA‑issued certificate is not provisioned, SQL Server can generate a self‑signed certificate at startup, but this does not provide strong authentication and is not recommended for production.
This finding identifies instances where Force Encryption is enabled. This is not a problem by itself, but it warrants review to confirm the certificate configuration is correct (meets SQL Server certificate requirements, is trusted by clients, and is within its validity period) to avoid unexpected connectivity issues and reduce MitM risk.
Problem: Force Encryption meaningfully strengthens in‑transit security by ensuring all client/server communication is encrypted (and the login process is always encrypted regardless). The setting is commonly used where connections traverse untrusted networks or where policies require encryption in motion, because SQL Server uses TLS to encrypt network traffic between client applications and the Database Engine.
The main consideration is the server certificate. SQL Server TLS certificates must meet specific requirements (including Server Authentication EKU, appropriate name/SAN coverage for how clients connect, and service account access to the certificate/private key), and clients must be able to trust the issuing CA for full validation.
Certificate expiration is a common operational risk: TLS requires the certificate to be within its validity period, and an expired or invalid certificate can cause connection failures when encryption is required (and in some misconfiguration cases can prevent SQL Server from starting). Plan certificate renewal and monitoring accordingly, and be aware that changes often require a SQL Server restart to fully apply.
Client compatibility is another consideration. TLS negotiation requires matching supported protocol versions/ciphers between client and server; disabling older TLS versions for security can break legacy clients that haven’t been updated.
Learn More...Extended protection
Issue: Extended Protection is a SQL Server security feature that helps protect the engine against man-in-the-middle (MitM) attacks, particularly those that exploit authentication relay or spoofing vulnerabilities. When enabled, it requires channel binding tokens (CBTs) during the authentication handshake, which tie the authentication session to the underlying transport channel and prevent an attacker from relaying authentication credentials between systems.
The feature is controlled by the extended protection server configuration option, which can be set to Off, Allowed, or Required. The setting is typically configured during initial deployment for environments with strict security requirements and works best when paired with TLS encryption and Windows authentication.
This finding identifies instances where Extended Protection is currently enabled. It is not necessarily a problem, but it is worth noting in case the team is unaware that the feature is active or unaware of the implications it has for client compatibility.
Problem: Extended Protection meaningfully strengthens the authentication path against relay and spoofing attacks. In environments where these threats are part of the threat model, particularly those with significant Windows authentication usage, the feature provides a real security benefit by ensuring that credentials presented during authentication cannot be reused on a different channel by an attacker positioned between the client and the server.
The main consideration is client compatibility. Older client libraries, drivers, and applications that do not support channel binding tokens can fail to authenticate when Extended Protection is set to Required. This can produce sudden authentication failures after enabling the feature, particularly for legacy applications that were never updated to support modern authentication mechanisms.
The feature provides the strongest protection when paired with TLS encryption on the SQL Server connection. Without TLS, Extended Protection falls back to service binding, which helps against luring but does not address spoofing as strongly as channel binding. Most organizations that enable Extended Protection also configure SQL Server to require encrypted connections, and the two settings are typically reviewed together.
Extended Protection primarily benefits Windows authentication. SQL authentication does not gain the same level of protection from the feature, since the credential is sent over the connection rather than negotiated through a Windows authentication exchange. Environments that rely heavily on SQL logins should consider whether the operational cost of Extended Protection is justified by the available benefit, or whether broader changes to encryption and authentication strategy would produce more meaningful security gains.
Learn More...About sp_checks
This page contains a list of SQL Server configuration checks performed by Straight Path's suite of sp_check tools. For more details about our free tools, select one from the following list: