SQL Server 2008- Audit

SQL server database auditing improves access time, saves user energy, optimize client’s opportunities, and builds customer confidence. Auditing SQL server databases depends on types of authentication [1] and roles [2] defined within database engines. Database security is important to review the security lapses and helps to improve existing login authentication policies and server roles. Login authentication can be through user’s window account, through SQL server login, or mapping through asymmetric key, third party authentication certificate or third party service provider's credential authentication. Window’s active directory groups that are set up for logging into windows/SQL server accounts, can be viewed exactly for all logins that have access to database. The xp_logininfo [3] is a Microsoft SQL server 2008 R2 stored procedure, that audits and query active directory group accessing SQL server database:

xp_logininfo [ [ @acctname = ] 'sys_name' ]

[ , [ @option = ] 'all' | 'members' ]

[ , [ @privilege = ] variable_name OUTPUT]


Most common vulnerability in MS SQL server 2008 is SQL injection, which is a method by which a hacker gains access to the database server by injecting specially formatted data through the user interface input fields. As previously thought SQL injection is a SQL server problem, but in fact, it is caused by unsafe application development procedures. Dynamic queries that requires user inputs are more vulnerable to such types of attacks. Simple attack methods include text messages, while advance attack methods follow binary and ASCII coding.

Text attack starts with an apostrophe (') to end the string column (usually username) check, continues with malicious SQL, and then ends with the SQL comment mark (--) in order to comment out the full original SQL that was intended to be submitted.

First line of defense is to use dual login: One for window and other for SQL server. Second line of defense: Keep the error messages hidden from end user. Malicious users can extract client’s data during dynamic execution of SQL code which requires user interaction of submitting data such as user id and password by string concatenation, and then submitting it for execution. Stored procedure use in SQL server and parameterizing it for master access to the user is also susceptible to SQL injection attack. For further reading, I would suggest useful links that are helpful for users and clients [4 -7]

Note: All of these resources were accessed on October 2, 2011.

[1] http://technet.microsoft.com/en-us/library/ms144284.aspx

[2] http://msdn.microsoft.com/en-us/library/ms188659.aspx

[3] http://msdn.microsoft.com/en-us/library/ms190369.aspx

[4] http://weblogs.sqlteam.com/mladenp/archive/2011/02/16/sql-server-sql-injection-from-start-to-end.aspx

[5] http://en.wikipedia.org/wiki/SQL_injection

[6] http://searchsqlserver.techtarget.com/tip/Secure-SQL-Server-from-SQL-injection-attacks

[7] http://hungryhackers.blogspot.com/2008/01/top-10-tricks-to-exploit-sql-server.html