Endpoint ownership
Issue: SQL Server endpoints are the network-facing entry points used by features such as database mirroring, Always On Availability Groups, Service Broker, and SOAP (in older versions). Each endpoint has an owner, recorded in sys.endpoints and visible by joining to sys.server_principals on the principal_id column.
The owner is typically set to whichever login created the endpoint, which is often the administrator who performed the initial deployment of the AG, mirroring session, or other feature that depends on the endpoint. The owner controls the endpoint and is the principal under which certain endpoint operations are evaluated.
This finding identifies endpoints 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 endpoints being created ad hoc during initial setup without consideration of long-term ownership.
Problem: When an endpoint is owned by an individual user login, the endpoint becomes tied to that person's account lifecycle. If the user leaves the organization, changes roles, or has their login disabled or dropped, certain endpoint operations and ownership-dependent behaviors can produce errors or unexpected results, particularly during configuration changes or troubleshooting.
User-owned endpoints also complicate audit and review. The presence of endpoints owned by former employees or by personal accounts produces audit findings and indicates that endpoint ownership is not being managed deliberately. Since endpoints are part of the infrastructure that connects replicas and instances, ownership clarity is particularly important for understanding the security model of the connected systems.
Ownership of an endpoint is rarely visible in normal monitoring, so the condition often persists undetected. Endpoints continue to accept connections and pass traffic normally under their original owner long after the owning user has moved on, and the issue surfaces only during audits, security reviews, or when the owning account is finally removed.
Learn More...Linked server
Issue: A linked server is a SQL Server configuration object that defines a connection to another data source, allowing distributed queries, remote stored procedure calls, and cross-server joins to be executed against the remote source as if it were local. Linked servers can target other SQL Server instances, other database platforms through OLE DB providers, files, and various external systems.
Each linked server has a security context configuration that determines how queries against it are authenticated. Options include impersonating the calling user (mapped through Windows authentication), using a fixed remote login and password, mapping specific local logins to specific remote logins, or making no connection at all for unmapped logins.
This finding identifies instances where one or more linked servers are configured.
Problem: The most significant concern with linked servers is the security context used for the remote connection. When a linked server is configured to connect to the remote system using a fixed login that has elevated privileges, any local user who can use the linked server inherits those privileges on the remote system, regardless of what their own permissions would normally allow.
The most extreme case is a linked server configured to connect using sa or another sysadmin-equivalent account on the remote system. In this configuration, any user able to query through the linked server effectively has full administrative control over the remote instance, even if they have no direct access to it. This is a privilege escalation path that bypasses the normal permission model entirely. sp_CheckSecurity classifies this as level 1, "High - action required," because the consequences of leaving such a configuration in place are severe.
Linked servers are also a known SQL injection amplifier. An injection vulnerability in an application connecting through a linked server can extend the attack to the remote system, potentially exfiltrating data, executing commands, or pivoting to additional systems through chained linked servers. The blast radius of any single compromise grows with each linked server that carries elevated remote credentials.
The condition is also worth attention because linked server configurations are often deployed once and rarely revisited. Credentials configured years ago for a since-retired use case may still be active, with the linked server providing an attack path that no current team member is aware of. Reviewing existing linked servers periodically catches these forgotten configurations before they are exploited.
Learn More...Database backup certificate expiration date
Issue: SQL Server supports backup encryption, available since SQL Server 2014, which protects backup files using a server certificate or asymmetric key along with a chosen encryption algorithm. The certificate used for backup encryption is created in the master database and is referenced when backups are taken with the WITH ENCRYPTION clause.
Like all SQL Server certificates, backup encryption certificates have an expiry_date property that records when the certificate is considered expired. The expiration date is set at creation time and defaults to one year from creation if not specified explicitly.
This finding identifies instances where one or more certificates being used for backup encryption have passed their expiration date.
Problem: For backup encryption specifically, SQL Server does check certificate expiration at backup time. When a new backup is attempted using an expired certificate, the engine raises the error "The certificate specified for backup encryption has expired" and the backup fails. This is different from TDE certificate behavior, where expired certificates continue to function with only a warning.
Restore operations are not affected by certificate expiration. An expired certificate can still be used to decrypt and restore backups that were taken under it, which is essential for recovering data after the certificate has aged out. The expiration only blocks new backups, not access to existing ones.
The most common operational impact is silent backup failure. Scheduled backup jobs configured to use an expired certificate begin failing immediately after the expiration date, and unless monitoring catches the failures promptly, the database can go without successful encrypted backups for an extended period. This produces a recovery gap that may not be visible until a restore is needed.
The bigger long-term risk with backup encryption certificates is the same as with TDE: certificate management discipline. The expired certificate must still be retained as long as any backup taken under it exists, since dropping it makes those backups permanently unreadable. Organizations sometimes assume that an expired certificate has been rotated and the old one can be removed, when in fact it is still required for restore.
Learn More...Missing database backup certificate backup
Issue: SQL Server backup encryption protects backup files using a server certificate or asymmetric key, with the certificate created in the master database and referenced when backups are taken with the WITH ENCRYPTION clause. The certificate must be backed up using BACKUP CERTIFICATE, which produces both a certificate file and a private key file protected by a password.
This finding indicates that one or more certificates currently being used for backup encryption have not had their private key backed up in the last 90 days, based on this column. The 90-day threshold reflects a common practice of confirming certificate backups on a quarterly cadence at minimum, ensuring that a current, verified backup of every active certificate is always available.
Problem: Without a current backup of the certificate and private key, encrypted backups can only be restored on an instance where that certificate is already present (typically the source instance) and cannot be restored elsewhere. If the source instance is lost (hardware failure, ransomware, accidental drop of the master database), every encrypted backup taken under that certificate becomes permanently unreadable.
This is one of the most common causes of unrecoverable data loss in environments using backup encryption. The encryption itself works as intended, the backups complete successfully, the recovery procedures look correct on paper, and the missing certificate turns a routine restore into a permanent loss event.
A stale certificate backup is similarly risky if the certificate has been rotated or recreated since the last backup. The current certificate in use no longer matches the backed up version, so a recovery using the old backup file will fail to decrypt newer encrypted backups even though a backup file exists. Without periodic verification, the team cannot tell whether the current backup file actually matches the active certificate. The 90-day threshold is not a hard requirement but reflects the reality that certificate state can change without anyone realizing, and a regular verified backup is the only reliable way to confirm the protection is still effective. Without this discipline, the certificate backup quietly drifts out of date until it is needed for recovery, by which point it is too late.
Learn More...TDE certificate expiration date
Issue: SQL Server certificates, including those used to protect TDE database encryption keys, have an expiry_date property recorded in sys.certificates. The expiration date is set when the certificate is created and defaults to one year from creation if not specified explicitly, although administrators can set any future date or use the maximum supported value to effectively avoid expiration.
This finding identifies TDE certificates that have an expiration date set. It is not necessarily a problem, but it is worth noting in case the team is unaware of the expiration date or unaware of how SQL Server handles certificate expiration for TDE.
The behavior of expired TDE certificates is an area where reasonable assumptions often differ from actual SQL Server behavior, which is the main reason this condition is worth surfacing.
Problem: Unlike certificates used for SSL or TLS, an expired TDE certificate does not stop working. SQL Server continues to use the certificate to protect the database encryption key and to decrypt data even after the expiration date has passed, so encrypted databases continue to function and backups can still be taken and restored without interruption.
Despite this, an expired certificate is still a meaningful operational issue. Some monitoring tools and audit processes flag expired certificates as findings, and certain restore scenarios on different SQL Server versions or in specific configurations can produce warnings that confuse the team during recovery. Expiration is also a useful trigger to review the certificate's age, the strength of its key, and whether rotation is appropriate.
The bigger risk with TDE certificates is not expiration itself but rather inconsistent assumptions about expiration. Teams sometimes assume the certificate will need to be replaced before the expiration date and plan a rotation that, if mishandled, leaves the new certificate without a backup or fails to protect newer data correctly. The rotation process is where most TDE certificate problems actually occur.
Some organizations also have policies that require certificate rotation on a defined schedule for security reasons, regardless of whether SQL Server itself enforces expiration. Long lived certificates increase the impact of a potential certificate compromise, so rotating them periodically is a reasonable security practice even though SQL Server does not require it.
Learn More...No recent TDE certificate backup
Issue: Transparent Data Encryption (TDE) protects database files, log files, and backups by encrypting them with a database encryption key, which is itself protected by a certificate or asymmetric key in the master database. The certificate must be backed up using BACKUP CERTIFICATE, with both the certificate file and the private key file written to disk and protected by a password.
This finding indicates that one or more TDE certificates currently in use have not been backed up in the last 90 days, based on the pvt_key_last_backup_date column in sys.certificates. The threshold reflects a common practice of confirming certificate backups on a quarterly cadence at minimum.
A missing or stale certificate backup is one of the most consequential gaps in a TDE configuration, since it directly affects whether encrypted data can be recovered.
Problem: Without a current backup of the TDE certificate and its private key, encrypted databases and encrypted backups cannot be restored on any instance other than the one that originally created them. If the source instance is lost (hardware failure, ransomware, accidental drop of the master database), every encrypted database and every encrypted backup becomes permanently unreadable.
This is one of the most common causes of unrecoverable data loss in TDE environments. The encryption itself works as intended, the backups appear to complete successfully, and recovery procedures look sound on paper, but the missing certificate turns a routine restore into a permanent loss event.
A stale certificate backup is similarly risky if the certificate has been rotated, recreated, or modified since the last backup. The current certificate in use no longer matches the backed up version, so a recovery using the old backup will fail to decrypt newer encrypted data even though a backup exists.
The 90-day threshold is not a hard requirement but reflects the reality that certificate state can change without anyone realizing, and a regular verified backup is the only reliable way to confirm the protection is still effective.
Learn More...Unencrypted databases
Issue: Transparent Data Encryption (TDE) protects SQL Server databases at rest by encrypting database files, log files, and backups so that the underlying file contents are unreadable without the protecting certificate or key. When TDE is not enabled, the database files and backups are stored in clear form, meaning anyone with file system access to the SQL Server data, log, or backup locations can potentially read the data.
This finding identifies one or more databases on the instance that are not currently encrypted with TDE. It is not necessarily a problem, but it is worth noting in case the team is unaware that some databases are unencrypted or has not consciously decided whether TDE is appropriate for them.
The decision to use TDE is usually driven by compliance requirements, the sensitivity of the data, and the threat model for the storage environment.
Problem: Without TDE, an attacker who obtains a copy of a database file or a backup file has direct access to the data inside it. This is particularly relevant in environments where backups are stored on shared storage, copied to offsite locations, or transmitted across networks where they could be intercepted.
Many compliance frameworks, including PCI DSS, HIPAA, GDPR, and various industry specific regulations, require encryption at rest for sensitive data. An unencrypted database that holds regulated data may violate these requirements directly, even if other security controls are strong.
Encryption decisions are sometimes made inconsistently across an environment. A common pattern is for production databases to be encrypted while development, test, or reporting copies are not, even though those copies often contain the same sensitive data. The unencrypted copies become the weakest link, since attackers will target the easiest path to the data.
Note that TDE is one of several available encryption options and protects only data at rest. It does not encrypt data in motion (which requires TLS for client connections), does not protect against authorized users reading the data through SQL Server, and does not replace column level encryption, Always Encrypted, or application-level encryption for protecting specific sensitive fields from privileged database users.
Learn More...Encrypted databases
Issue: QL Server's Transparent Data Encryption (TDE) protects data at rest by encrypting database files, log files, and backups using a database encryption key (DEK), which is itself protected by a certificate or asymmetric key stored in the master database. The encryption is transparent to applications, since SQL Server handles encryption and decryption automatically as data is read and written.
This finding identifies one or more databases on the instance that are currently encrypted with TDE. It is not necessarily a problem, but it is worth noting in case the team is unaware that TDE is in use, or unaware of the operational considerations that come with it.
TDE is commonly enabled to meet compliance requirements (PCI DSS, HIPAA, GDPR), to protect data on shared or cloud storage, or as part of a defense in depth strategy against unauthorized access to physical files.
Problem: TDE adds CPU overhead because every page must be encrypted on write and decrypted on read. The cost is usually small for most workloads but can become measurable on systems with very high I/O rates, particularly when combined with other CPU intensive features.
Enabling TDE on any user database also automatically encrypts tempdb, which affects the entire instance regardless of which other databases are encrypted. Once tempdb is encrypted, it remains so until TDE is removed from every user database on the instance, which makes the decision effectively instance wide rather than database specific.
The most critical operational concern with TDE is certificate management. The certificate that protects the database encryption keys must be backed up and stored securely in a location separate from the database backups, since the loss of the certificate makes every encrypted database and every encrypted backup permanently unrecoverable. This single point of failure is by far the most common cause of unrecoverable TDE data loss.
TDE also interacts with several other features in ways that need to be planned. For instance, encrypted databases cannot be restored to instances that do not have the matching certificate, and Always On Availability Group failovers require certificates and keys to be present on every replica.
Learn More...SQL Agent jobs that run at startup
Issue: SQL Server Agent supports a schedule type called "Start automatically when SQL Server Agent starts," which causes a job to run every time the SQL Server Agent service starts. These jobs execute as soon as Agent comes online after a service restart, host reboot, or failover, with no further trigger required.
This finding identifies SQL Server Agent jobs configured with this schedule type. The feature has legitimate uses for routine startup activity, but it warrants review since startup jobs run automatically and can have significant impact during service recovery.
Common legitimate uses include initializing application state, sending notifications that the instance has restarted, refreshing cached data, or kicking off ongoing processes that need to resume after downtime.
Problem: Startup jobs execute under the security context of their owner, which means a job owned by a privileged account runs with that account's permissions every time Agent starts. This combines with the user owned job concern noted elsewhere: a startup job owned by a former employee, an sa like account, or a sysadmin user effectively grants those credentials to whoever can modify the job.
Startup jobs also affect service recovery time. A job that runs heavy work, holds locks, or blocks on external resources at startup can delay normal operations during exactly the window when the team is trying to restore service. In environments with strict recovery objectives, an unexpected startup job can be the difference between meeting and missing the target.
Like startup stored procedures, startup jobs are a persistence mechanism that an attacker with sufficient privileges can use to ensure malicious code runs after every restart. The job is visible in the standard Agent job list, but its startup schedule is easy to miss without specifically looking for it, and the job appears unremarkable next to legitimate scheduled jobs.
Forgotten startup jobs also accumulate over time. A job created to address a one-time recovery scenario or a temporary requirement may continue running at every startup long after the original need has passed, consuming resources and creating dependencies the team is no longer aware of.
Learn More...Stored procedures that run at startup
Issue: SQL Server allows stored procedures in the master database to be marked to run automatically every time the SQL Server service starts. This is configured by setting the startup option on a procedure using sp_procoption, and the procedures execute under the sa security context as part of the service startup sequence.
This finding identifies instances where one or more stored procedures are configured to run at startup. The feature has legitimate uses for instance level initialization tasks, but it is also a known persistence and privilege escalation mechanism that warrants review whenever startup procedures exist.
Common legitimate uses include performing routine warmup activity or initializing application specific state. Less legitimate uses include attacker installed code that ensures malicious activity resumes after every restart.
Problem: Startup procedures execute with full sysadmin privileges as sa, regardless of who created them or what permissions the original creator had. Any code in a startup procedure can read, modify, or drop any object on the instance, change configuration, create logins, and execute operating system commands through features such as xp_cmdshell if enabled.
Because startup procedures run automatically and under elevated privilege, they are an attractive persistence mechanism for attackers who have gained sysadmin access at any point in the past. A malicious startup procedure can reestablish backdoor accounts, disable audit configuration, or fetch and execute additional code on every service restart, even if the original compromise has otherwise been remediated.
The feature is also easy to overlook during security reviews. Startup procedures are not visible in the normal job and schedule views that administrators check regularly, and they require a specific query against sys.procedures and OBJECTPROPERTY to enumerate. An attacker who knows about the feature can rely on it remaining undetected longer than other persistence mechanisms.
Even when startup procedures are legitimate, they introduce a startup dependency that can affect service availability. A procedure that errors, blocks, or hangs at startup can delay or prevent SQL Server from coming online, turning what should be a routine restart into an extended outage that requires manual intervention to resolve.
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: