This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days. We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.
SQL Server logins are often necessary, like the sa login, but be aware they present a particular set of vulnerability issues. Their lack of multifactor authentication, their susceptibility to brute force attacks, and the dumb passwords folks give SQL Server logins make them a prime target for hackers.
The main issue with a SQL Server login is that the lack of multifactor authentication allows them to be used with near anonymity. Anyone with the login and the password can connect to your SQL Server instance, and you won’t know who is connecting. It could be you, me, or Thomas Anderson – by default there’s not really any way to know.
You can create a SQL Audit to track the login usage and collect information like the IP address of the user, the application name, and some other bits of useful information, but none of this is on by default. You have to go and set any auditing proactively if you want to know any information about who is using the login.
And yes, you can also configure the SQL Server error log to collect info about successful logins, but that is all logins, and it only includes time and IP address, so it’s not as useful. And honestly, I’m not a fan of collecting this information in the SQL Server error log files, because when I need to troubleshoot an error, I don’t want to load millions of rows of successful logins and then try to wade through that. Often times minutes or even seconds count when you are diving into the error log, and if you’ve been collecting successful logins then you might find yourself uttering some salty words.
But there’s an even bigger problem with SQL Server logins: people give them weak passwords. Microsoft defines a strong password as at least eight characters, but as computing power continues to grow, that might not be enough. One article published in 2023 estimated even the strongest 8-character password could be hacked in about 5 minutes. However, the same article estimated the strongest 12-character password would take 226 years, so it would be a good idea to start making those passwords longer.
From experience, I can tell you that a lot of folks haven’t been practicing this helpful advice. Not only are there logins with passwords that aren’t 12-characters, but some of the passwords are also begging to be hacked in about 5 seconds.
During our security checks, we all too often find vulnerable SQL Server passwords that fall into one of three categories:
- They are blank
- They are the word “password”
- They are the same as the login
Newer versions of SQL Server enforce some levels of password complexity, so how does this happen? Well, a lot of the time these logins have been migrated from older versions of SQL Server, which didn’t include these restrictions. And no one has bothered to revisit those passwords because, well, I guess it’s because of the adage “if it ain’t broken then don’t fix it”.
The hackers out there thank you for your negligence.
If you are an administrator, you can run queries using the PWDCOMPARE function that, although they won’t tell you the password for logins, will allow you to search for any logins with a password that meet a particular string of characters.
Here’s how you can find any that have the password of ‘password’. I sincerely hope you don’t have any results returned, but better to find out new then after you have been hacked.
SELECT [name]
FROM sys.sql_logins
WHERE PWDCOMPARE('password',password_hash)=1;
