[This tip was originally published on our sister site SearchWindowsManageability.com.]
Do you ever lock the front door and leave the back door wide open? That kind of half-safe security practice is all too common among database administrators, said database security expert Aaron C. Newman. In many cases, DBAs get lucky and hackers overlook their oversights. Too often, their luck runs out.
Thanks to Microsoft's notoriety and the widespread use of Microsoft SQL Server, the database is a favorite target of hackers. That's why leaving the security of Microsoft SQL Server to chance is a bad idea, said Newman, who is co-author of Oracle Security Handbook as well as CTO and co-founder of Application Security Inc. New York-based Application Security is a security solutions and services provider.
Newman has seen bundles of SQL Server database security blunders in his field work for Application Security. In an interview with SearchWindowsManageability.com, he lists the 10 most common mistakes and offers tips for avoiding them.
Blunder #1: Only using standard security measures
"Some people just use the SQL Server standard security capabilities, wherein you simply set up a login ID and a password that the database maintains," said Newman. That's a mistake. The most important security process for SQL Server is integrating the server's standard security functions with Windows account security measures. In fact, he said, Microsoft strongly encourages SQL Server users to integrate SQL Server security with Windows account security because SQL Server doesn't manage passwords that well.
There are a lot of vulnerabilities associated with using only SQL Server security options. If you do use standard security, passwords end up written in log files or somewhere in the registry. That can lead to compromised security. Also, there is no facility for expiring passwords or requiring strong passwords. Unfortunately, using integrated security may not be an option if your users do not belong to the domain or must access the database through a firewall.
Blunder #2: Leaving default public permissions 'on'
By default, SQL Server allows all users to select from tables such as "syslogins," which contain sensitive information such as the list of logins. By default, anyone could select from that table, and that's not really a good idea. It gives others a list of login names they could use to hack your database. SQL Server's system tables have public permissions, and it's a good idea to remove them to prevent people from getting that sensitive information.
Blunder #3: Setting an easy-to-guess password for the system administrator login
The system administrator (SA) login is your main account on the server. "That's a powerful login and a common target. A SQL Server administrator needs to set a very strong password on it," said Newman. "Even better, lock that account and create secondary accounts for system administration, and use them instead of the SA login." If every database administrator uses the SA login, it's difficult to create an audit trail and know who did what. It's better to add users who have SA privileges, so you can track their actions individually.
Blunder #4: Allowing guest users in databases
SQL Server typically has multiple databases within it. If a database has a user name's guest in it, logins with access to other databases will be allowed to access this database as the guest user without official access permissions.
"It's a good idea never to allow guest users in your database unless that's what you intended," said Newman.
Some databases within SQL Server -- such as the MSDB database -- come by default with a guest user account. "It's a good idea to remove that guest user," said Newman.
The database administrator can't remove the guest user from some SQL Server databases, including master and tempDB, because of the nature of the databases. "If you can remove the guest user, however, you should, especially when sensitive information is at risk," Newman said.
Now, some databases administrators intentionally set up guest user accounts because they want to broaden users' accessibility to various databases within SQL Server. That's an option, but it's a risky one, Newman said.
Blunder #5: Failing to enforce strong password policies
Strong passwords contain at least eight characters and both letters and numbers. Additionally, they include both upper- and lower-case characters. Require people to change their passwords every 90 days.
"If you are using standard security, this is hard to enforce," said Newman. Administrators can enforce these policies by educating users.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free SQL Server tips and scripts.
- Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
- Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.