IT Job search

SQL Server Security Audit

Recently, one of my clients requested a checklist of SQL Server Security items, they could implement as a standard procedure, across all their new installations. I sent the following to him. -Use Windows only authentication mode rather than mixed mode for database connections to limit attacks that can be carried out across the Internet. -Create custom database roles to establish more granular access controls and help keep users accountable.(This could be supplemented with auditing.) For example, an admin section of a web application may require INSERT permissions on a table, whereas the public may require a SELECT. -Limit the privileges of your SQL Server services to limit what attackers can do if they are able to compromise the system.Microsoft recommends running SQL Server Engine/MSSQLServer and SQL Server Agent Service/SQLServerAgent as a regular Windows user account with regular privileges. -An obvious one is to place your database server behind a firewall rather than risk being a victim of a direct Internet attack (Slammer worm). -Another obvious one, but nevertheless regular -- your SA account needs a strong password. Otherwise, it can be brute-forced or cracked with a dictionary attack if an attacker can gain network access. -Place your database in a separate network segment (DMZ) from your Web and application servers (if possible) to prevent a successful compromise of one host that puts SQL Server at risk. It's not impenetarble, but is another layer of security. -Enable auditing connections to SQL Server (especially failures) so you can keep track of what's going on. Ideally, look for a log management/alert system such as GFI Software Ltd.'s LANguard Security Event Log Monitoring or something similar to bring these errors to your attention rather than having to manually search for them. As an alternative , is to roll your own , there are many scripts for basic error management alerting on the web, which can be adapted . -Be careful with file and share permissions on your server to ensure that only those who need access have access (the NTFS file system is essential). -Test for and lock out null session connections to insiders and outsiders alike from making null connections to your server and gleaning usernames, security policy information and more. Here's a tip on doing this. -Don't let applications execute SQL commands directly. Otherwise your underlying database structure can be determined and commands can be run directly via SQL injection and blind SQL injection. Hopefully this audit helps not only as a preventitive but for troubleshooting

Digg This : Click to email a colleague Author details ------------------------------------------------------------------------ Jack Vamvas is a SQL Server Consultant. To contact him about this article , or any issues related to SQL Server register on www.ciquery.com and post a question. For SQL Server Consultancy of any SQL Server task - check SQL Server Specialists Make SQL Server go faster - click here