Sample Code on SQL Server Security

I spoke on SQL Server User Group Indonesia monthly meeting last month, discussed about a developer perspectives of SQL Server Security. I covered most of the session with some SQL Injection techniques, completed with real sample code in web application.

I started with the awareness that network administrator is not the only person who’s responsible for computer security. Networking guys are only responsible at the network (firewall) and host (OS) level. At the application level, is a developer responsiblity for the software security. Ensure that no single line of code contains a hole, something that can be utilized by an attacker to compromise the system.

These are the summarry for SQL Injection countermeasures:

  • Never use user input as a string concatenation element
  • Avoid string concatenation in SQL statement
  • Use Parameter collection of ADO.NET Command object for parameter parsing
  • Use stored procedure as possible
  • Validate user input, never trust them
  • Client side validation is not enough (java script can be removed)
  • Utilize built in database constraint
  • Change the sa password
  • Remove built in administrator

Some configurations for locking down SQL Server from any vulnerabilities:

  • Disable xp_cmdshell (default ON in SQL2000)
  • Disable OPENROWSET and OPENDATASOURCE (default ON in SQL2000)
  • Don’t enable SQL CLR if not needed
  • Minimize protocol used
  • Disable remote access if not needed
  • All setting can be modified using sp_configure
  • SQL authentication: create user mapping, don’t use the real SQL user
  • Use Application Role if possible

Download the presentation here.
Download the sample code here.