SQL Server security starts from the moment a user attempts to log in. From there, every user’s login and password have to be authenticated before they’re granted access. While this principle is simple to understand, choosing the right server authentication mode can be difficult.
Luckily, selecting and implementing the authentication method right for your database and organization is relatively easy.
In this article, you will learn about:
What is SQL Server Authentication?
Whenever a user tries logging on to a website, database, or application, the connection needs to be authenticated. Authentication is essentially the program and user attempting to handshake. If the user account has the right credentials to enter, the program allows them in, authenticating them as a user that’s supposed to be here.
SQL Server authentication is no different; database users establish a connection with the database which then checks information such as the domain and instance names, port number, and the user account credentials.
SQL Server uses three kinds of authentication methods: SQL Server authentication, Windows authentication, and SQL Server + Windows (or mixed) authentication.
Authentication Modes in SQL Server
SQL Server Authentication
The most straightforward mode, SQL Server authentication only allows Active Directory or Windows users to connect to the server. When using this mode, be sure to disable the system administrator (sa) account for security purposes.
Windows Authentication
Similar to SQL Server authentication, Windows authentication also allows Active Directory and Windows users to connect. However, the key difference here is that SQL Server does not authenticate the users directly. Instead, it grants access based on the user’s Windows token, negating the need to store any user credentials on the database.
Because of its built-in security benefits such as Kerberos and keeping user credentials safely outside the database, Windows mode is the preferred authentication method for SQL Server. When using Windows authentication, you can also take advantage of other features such as Active Directory accounts, Windows groups, and easily changing and enforcing password policies.
SQL Server + Windows (Mixed) Authentication
As the name implies, mixed authentication (also known as mixed-mode) allows both SQL and Windows logins. Due to its weaker security compared to Windows authentication, this mode is most commonly used for legacy purposes and backward compatibility with older applications.
Though you should use the Windows authentication method whenever possible, mixed-mode allows you to deploy SQL Server in non-Windows environments.
SQL Server Authentication Best Practices
Regardless of which method you choose, here are a few best practices to consider when setting up SQL Server authentication.
1. Remove Unnecessary Users and Disable Unused Features
It goes without saying that you should keep your SQL Server database clean of unnecessary users and unwanted features. Aside from keeping the list of user credentials tidy and the database streamlined, you’re also closing any security loopholes that can be exploited through these features or long-forgotten user accounts. This is especially true if you neglect to remove a highly-privileged account from the database after the user leaves your organization.
2. Practice the Principle of Least Privilege
In a similar vein, all necessary users should be given only the privileges they need to get their jobs done within the database, but nothing more. This is called the principle of least privilege. For example, the brand new hire in HR doesn’t need full administrative access that could potentially delete the database. Conversely, the former database administrator that left a month ago should no longer have an active account.
By adhering to the principle of least privilege, you’ll mitigate internal risks to the database whether they’re genuine accidents or malicious intent from a disgruntled employee. With that in mind, keep user privileges updated and utilize temporary privilege escalation when someone needs access beyond their usual needs. By doing this, you’ll minimize privilege creep while keeping a better eye on who can do what – a useful tool for auditing when necessary.
3. It Is Better To Authenticate Outside of SQL Server
Since SQL Servers don’t natively have login attempt limits, they’re especially vulnerable to brute force attacks. If user credentials are stored on SQL Server (in the case of using either SQL Server or mixed authentication methods), you may be adding a risk. Instead, keep user credentials outside of SQL Server which can easily be done through Windows authentication.
4. Use Strong Passwords
Passwords and authentication go hand in hand. Without strong passwords – especially for administrator-level accounts – the best authentication methods will fail as bad actors can waltz right in.
You can avoid this by making sure all users have complex passwords. These usually contain a string of at least 15 characters with a variety of alphanumerics and symbols. These strings should also be unique and not appear in common dictionaries as hackers will often check those first.
When using Windows authentication mode, you can enforce password policies across SQL Server to ensure all users adhere to your security standards uniformly. If you’re using the other modes, however, you’ll have to make sure each login for each instance adheres to policy, a time-consuming process.
5. Perform Regular Security Audits
Not only are regular security audits required to remain in compliance with data security laws such as GDPR, CPRA/CCPA, and HIPAA, but they should be a standard practice for the sake of keeping your database and its information safe.
Be sure to keep logs of user logins, session activity, failed attempts, and weak or ineffective passwords that could pose a risk. SQL Server has a variety of monitoring tools that can streamline this process, allowing you to ensure the database’s security but to identify any potential weaknesses that should be addressed.
Microsoft SQL Server Security With Satori
Satori, The DataSecOps platform, allows companies to enforce security policies from a single location, across SQL Server, as well as their other databases, data warehouses and data lakes. Such security policies can be data masking, data localization, row-level security and more.
Learn more:
