For your SQL Servers to work at their fullest potential, you must tweak a thing or two. However, small mistakes in settings tend to snowball over time.
This blog post highlights security best practices intended to secure corporate data and performance best practices to help you boost SQL Server performance with the help of SQLDocKit.
With the latest released SQLDocKit version, we’ve added more best practice reports so you don’t have to wonder if you have everything set correctly.
SQL Server security and configuration best practices
The new SQL Server best practices listed below are intended to help you prevent certain problems from occurring, warn you if you have misconfigured some settings, or left the default settings.
It’s about time you go over those best practices that you have lined up on various sticky notes all over your desk.
You’ve been planning to get to those for years but never actually got around to it, right?
You can’t have just any user logging on to your servers and, since you can’t delete this guest user, you have to prevent them from connecting to a server that they have no need to access.
Run REVOKE CONNECT FROM GUEST within any database other than the master or tempDB database to disable the guest user.
Generally, the guest user isn’t a problem (it’s quite useful because you don’t have to assign permissions individually to each new user), it’s the mistake of giving a guest user admin permissions.
Public Role Not Granted Server Permissions
By default, all users on an SQL Server are added to a Public server role. If a DBA by accident grants them server permissions then you’ve got yourself a security problem once again – anyone can do what they please on that server.
SQLDocKit can check whether the public server role has server permissions. If it has, you need to tend to it ASAP.
SQL Server User Password Policy and SQL Server Users with The Simple Password
SQL Servers have two login types: Windows Authentication and, for a lot of SQL Servers, SQL Server Authentication turned on as well.
If a user doesn’t have a domain account, they can only log in with the SQL Server credentials and you have to be careful with that.
SQLDocKit checks for enabled settings, such as the authentication modes in your environment, if have password policies been set properly, the password expiration, and password complexity policies.
For example, you might want to avoid using the following when creating new passwords; PASSWORD, SA, DEV, TEST, and ADMIN.
Also, avoid creating a matching username and password. This includes blank or empty string passwords.
If these few policies haven’t been enabled, it’s bad practice.
Mistakes like this often happen when you’re in a hurry to create credentials for a new employee and you think you’re going to get back to sorting it out then you forget.
It happens, I’m not judging.
Auto Close On
Continuous opening and closing of the database results in performance problems and timeouts. The recommended setting is to disable the AUTO_CLOSE option.
There’s a bit more info on that here.
Auto Shrink On
It’s best to configure the database files for sizes required by application and average maintenance usage. Most of the time it’s okay to disable the AUTO_SHRINK option for managing database file sizes.
Refer to this article when setting the AUTO_SHRINK database option.
Virtual Log File Count
The problem occurs when Virtual Log Files pile up in the Transaction Log and the Log File Growth hasn’t been set up properly. This can lead up to thousands of Virtual Log Files that slow down the database recovery time.
Follow these guidelines to prevent VLFs from growing uncontrollably.
Active Directory Valid Logins
In the corporate world, when people come and go, their accounts should be disabled in the Active Directory and on servers. SQLDocKit checks if accounts on the SQL Server exist in the AD.
DBAs rarely know if a system admin has deleted or disabled a user when they left the company. Former employee accounts should be disabled for security reasons. Access to servers should be reserved for people who still have business on those servers.
SQL Server Agent Account
Microsoft advises to avoid assigning full permissions to accounts running the SQL services – less is always more and your guideline should be the principle of least privilege.
SQLDocKit checks whether the SQL Agent account on the server has administrator privileges.
SQL Server Job Owner
As I’ve mentioned before, employees come and go, there’s nothing you can do about it.
However, when it comes to SQL Server maintenance, you can use SQLDocKit to automatically match the SQL Server Agent Job owners with a login to validate which jobs do not match that owner.
Here’s the thing:
When that employee leaves, their account will be disabled which means it will no longer have any privileges and scheduled jobs will not be executed.
More about SQLDocKit
Without a third-party tool, you would have to check all these settings and policies manually.
Luckily, there’s SQLDocKit! Check out the SQLDocKit Best Practices Library to see a full list of settings and policies that SQLDocKit can help you with.
Make security a priority, download SQLDocKit today for a free 30-day trial.