Today, SearchDatabase.com offers the second part of a top 10 list of SQL Server security tips, courtesy of expert Greg Robidoux. Robidoux is chairman of the Professional Association for SQL Server DBA Special Interest Group (PASS DBA SIG) and a SearchDatabase.com expert.
6. Avoid dynamic SQL.
From a security standpoint, dynamic SQL creates the opportunity for SQL Injection. SQL Injection allows hackers to embed additional SQL statements into the statement that the user is to run. When code is assembled dynamically, either in an application or in a stored procedure, there is a chance of embedding additional statements that could be harmful to the system. Predefine the SQL statements that are going to run on the system instead of dynamically building the statement both in embedded SQL and in the stored procedures. Also, make sure to validate the data being passed before executing the statements.
7. Remove XPs that are not used.
SQL Server comes bundled with a load of extended stored procedures for accessing information outside of SQL Server. The reason for this is to assist in the administering of SQL Server but, unfortunately, these procedures also create security risks. Review the XPs that are installed, and remove XPs that will never be used.
8. Use stored procedures to manipulate data.
Using stored procedures to manipulate data gives you control over how the data is being updated, deleted and inserted. With rights given directly to tables, you give up control over how the data will be changed and possibly create a situation in which mass updates are performed. Try to create stored procedures for all of your data updates, inserts and deletes. In addition to securing your data, this should also help with overall system performance, if the stored procedures are well written.
9. Audit logins.
Do you know who is accessing your databases or, better yet, who is trying to access your databases? Auditing logins is a good, simple measure that allows you to see who is attempting to access your database server. At a minimum, you should change the security settings to capture failed logins for all of your database servers. This can easily be done through Enterprise Manager on the security tab of the server properties window.
10. Safeguard database backups.
Native backup files are stored in clear text, so anyone that has access to one of your backup files could open the file using a text editor and read the contents of the data. The data is not that easy to read in all cases, but stored procedures are as clear as day. Even if you use passwords for your database backups, the files are still readable. It just means you need the password to perform the restore. Make sure drives that backups are written to are not accessible by anyone other than DBAs and your tape backup administrator. If possible, encrypt backups using third-party tools. Lastly, make sure you have good practices for transporting and securing backup files.
FOR MORE INFORMATION:
Read part 1 of this Top 10 list on SearchDatabase.com
Check out our Featured Topic: Your SQL questions answered.
To provide feedback on this article, contact Robert Westervelt.