Welcome to Myths and Misconceptions Month, or “Mythconceptions Month,” as we have lovingly referred to it in our internal discussions preparing this month’s content. We will have posts about the major Myths and Misconceptions we met as SQL Server DBA consultants. We hope you learn a lot from it!
On to the content!
Many companies with SQL Servers have a false sense of security, despite having huge security issues within their environment. I will go through a few of the most critical and common issues we see below, along with a brief blurb about each:
- Out of support – Running out of support versions of SQL server is extremely risky, especially since security updates are not provided after extended support ends. This means, anything older than 2014 is no longer supported at all, and anything older than 2017 is no longer receiving any CU updates! Refer to this reference for a complete overview: https://learn.microsoft.com/en-us/sql/sql-server/end-of-support/sql-server-end-of-support-overview?view=sql-server-ver16#lifecycle-dates
- There are security updates available that have not been applied – Security updates are released to fix security holes in the software. Without implementing these updates, your server is open to the malicious actor community via a known vulnerability in the software. Applying these updates is simple and worthwhile.
- The SA account is enabled for connection – Some even have this account being used for application access or as a shared account for admin purposes. This is a dangerous practice. We recommend disabling this account for connection and only using it for object ownership. Every SQL Server has an SA account, malicious actors who get this account’s credentials can do anything on the server.
- Blank or Weak Passwords – SQL Server Auth accounts can be created with extremely weak or even blank passwords. Dictionary attacks will sniff out these weak or easily guessed or frequently used passwords quickly, which will give even further traction to a malicious actor should they get in!
- Security and Sysadmins – these are the most privileged users besides the SA account. Regular review and limiting the membership of these roles is recommended for a secure environment.
- Users with “Control server” permission – this permission is explicitly granted outside of a role. This gives the same privilege as the sysadmin role except this can be restricted through DENYs. This is an atypical permission to find granted, and it should be reviewed to determine if there is a real need for it to be granted.
- CLR Enabled – Enabling CLR assemblies to run should be done with the Microsoft CLR security model in mind. Specifically, not running processes that affect the overall stability of SQL server, gaining unauthorized access to user data or other code in the database, resources outside of the server, or unauthorized local system resources. https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/security/clr-integration-security?view=sql-server-ver16
- XP_CMDSHELL is enabled – this allows the execution of operating system commands in the context of the SQL Server account, which some have set as a local administrator. This reduces the barrier between SQL and the OS.
- Cross-Database ownership chaining – This allows database owners and members of the db_ddladmin and db_owners database roles to create objects that are owned by other users. Since enabling this setting allows certain users to create objects can potentially target objects in other databases, this configuration option should be disabled if possible.
- Jobs, Databases, or endpoints owned by users. If their login is disabled or not available due to Active Directory problems, the job will stop working, or database/ endpoint will not behave as expected.
- Jobs or stored procedures running on SQL server startup – Verify you and your team know exactly what this job is doing, because it could pose a security risk if someone created it as a “timebomb”.
- Linked servers configured – Check its security configuration to make sure it isn’t connecting with a sysadmin-level login, where any user who queries it would have admin-level permissions.
- Failing connections – checking the error log to verify failed connections are benign. Meaning they are from expected logins, expected IP addresses, expected times, and in reasonable numbers.
- Elevated database permissions – reviewing those with ‘db_owner’ and other admin level permissions regularly, or creating AD groups to manage permission groups, and then regularly auditing who is in those groups.
- Explicit permissions granted to the public or other built-in roles. This is not recommended, as it can result in permission creep, especially if you are a team of DBAs and it was added without team knowledge.
- Databases aren’t encrypted at rest – What happens if someone steals your hard drive? Can they restore your database and have all your data? Upgrading to a SQL version that has Transparent Data Encryption (TDE) helps to protect the physical files where they are and requires an encryption key for restores.
- Connections to SQL Server aren’t encrypted. Forcing connections to be encrypted is a way to help ensure that there isn’t any sniffable traffic to the SQL server.
- Sensitive data isn’t classified and audited – Does the DBA need to be able to see the PII or health information in the database? Have you considered using Always Encrypted so that only users on the application side with the correct permissions can access sensitive data? If not, why not? If you have, consider this question: How can we be protected from a disgruntled employee? What if the super user’s account gets compromised? Separation of duties is needed here.
Lastly, a major part of disaster recovery is the recovery. And a major security issue is Ransomware. So here is our PSA:
- First, you should be backing up regularly, we recommend a weekly full, a daily diff, and 15 minute log backups for mission critical databases (FULL recovery), and for less important databases, weekly full and daily diff (SIMPLE recovery).
- Second, you need to regularly test these backups in a full restore, point in time restore, and more. Get it to the point where you can hit your RTO and RPO, most can’t with a regular full backup and recovery process which is where DR technologies like Log shipping can come in handy.
- Third, please ensure that you have backups heading off site and off network. We recommend copying to a cloud bucket or container of some sort as a long-term off-site storage solution. With the ease of robocoy, azcopy or the many other methods, it can be as simple as an extra step on every SQL backup job to call a powershell or commandshell scriptto begin the copy.
Ransomware targets backup files first and either encrypts or deletes them so that they can remove your ability to say no to paying the ransom cost. By taking these backups off-site, and having a secondary environment plan, meaning you would either spin up in a cloud service or having a secondary datacenter, you would be leaps and bounds ahead of most. If you have already checked all of the boxes on this list, congratulations! You are doing very well. However, don’t lose your vigilance since there will always be someone trying to get in, nothing is ever 100% secure, and this list isn’t 100% comprehensive. If you haven’t checked all of these boxes don’t be afraid to get started now. If you would like expert assistance, please let us know and we would be glad to help!