A security breach is never pretty. When a hacker gets into a database, however, things get downright ugly.
As CTO and co-founder of New York-based security solutions provider Application Security Inc., Aaron C. Newman has seen too many ugly security scenes involving Microsoft SQL Server. He'd like to save himself and others from witnessing such events. So, in part one of "Top 10 SQL Server security blunders," he discussed five common SQL Server security management mistakes. In this installment, he rounds out his top 10 list.
Blunder #6: Allowing users to run ad hoc queries
Nowadays, many businesses run a three-tier architecture wherein people access the SQL Server database through a Web application. Not all of these companies limit database access to users of the Web application. That's too bad, said Newman. Setting up a middle tier -- such as a Web application -- for accessing a database is a good safety measure. That way, risk is shifted from SQL Server to a Web server.
"If users can access the database directly, they can run ad hoc queries," said Newman. "They have the flexibility to try to get around your security measures." Also, when users have the ability to run any queries they want, problems like buffer overflows occur more frequently.
Blunder #7: Putting security patch implementation on the back burner
Patch maintenance is a lousy job, but someone has to do it. There's just no better mouse -- er, hacker trap. It's very important to keep up with patches, hotfixes and service packs.
"SQL Server has been getting hit with a lot of buffer overflows and other types of security attacks," said Newman. "Hackers often use a vulnerability that can only be closed by a patch."
In fact, the release of a patch often alerts hackers to SQL Server security vulnerabilities. They'll quickly cast about for IT shops that haven't put the patch in place. Sometimes they get lucky. Why give them the winning network Blotto ticket? This leads us to…
Blunder #8: Letting hackers reveal your security vulnerabilities
Why wait for an attack? Why not try to break in yourself? If you don't have strong hacker chops, a security consultant or your company's audit department can check to see if you've locked everything down. Doing a 99.9% job of securing a system isn't good enough, said Newman. Just one oversight, just one open door to your database is all the opportunity a hacker needs. "It's really a challenge to lock everything down," he said.
Blunder #9: Installing every tool available and leaving all tools and features 'on' all the time
"The more software you have running and the more extra features you have, the more vulnerable SQL Server is going to be," Newman said. It's very important to turn off features that are not in use and install only the features you need.
For example, an administrator should remove access to or delete any extended stored procedure that's not in use. SQL Server is shipped with about 200 extended stored procedures inside. These are ways to extend or call out to C++ modules or any other language you write code in. "They're a really powerful feature in SQL Server," said Newman. "As with most things that are powerful, there's also a risk that they can be used maliciously. That's where a lot of buffer overflows have been found."
In particular, be sure to shut off SQL Agent, a scheduling and task management engine for SQL Server, when it's not in use. It's often left on because it's a frequently used tool. For that reason, it's a popular entry point for hackers.
Blunder #10: Creating links to other databases that any Tom, Dick or Harriet can use
When SQL Server is running in a heterogeneous environment, it can often be linked to other types of databases (from Sybase, Oracle, and so forth). Usually, the SQL Server administrator will have to create links to those other databases. In doing so, the admin must create a login name and password for administrators who are authorized to connect to those other systems. "Make sure non-administrators can't see the tables that contain the user names and passwords that permit admins to connect to those other systems," said Newman.
One good way to secure access between linked databases is to use Windows' integrated security features. When connecting to an Oracle database on another system, for example, set up SQL Server to run under a Windows NT account that can connect to that Oracle database. Using this method eliminates the need to store a user name and password.
Sometimes, another database running on a Unix platform won't accept Windows NT authentication. When using Windows NT authentication isn't possible, Newman said, it's doubly important to lock down the tables where user names and passwords are stored.
10 ways to step up database security
In this day and age, there's no one-step way to secure SQL Server. By turning Newman's Top 10 list of worst practices 180 degrees, however, database admins can take 10 steps in the right direction.
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.