Security Recommended Practises

Administrative

Never run SQL server service as localSystem or administrator
Apply latest service packs and security patches
Always set strong password for SA
Do not change default permissons on xp_cmdshell
Do not set the proxy account to be an administrator
Default audit is none...change to at least Failed Audit
Run MBSA tool on a regular basis (http://www.microsoft.com/technet/security/tools/Tools/mbsahome.asp)
When changing service accounts always go use the Enterprise Manager tool
Install a SSL certificate to secure authentication

 

Developer / Application

Avoid SQL Injection attacks

SQL Injection attacks

Class of attack where attacker can insert or manipulate queries made by application to backend

Example

Application code
var ShipCity;
ShipCity = Request.form("ShipCity");
var sql = "SELECT * FROM OrdersTable WHERE ShipCity = '" + ShipCity + "'";

Good User
Inputs Miami in the form
Query to backend is:
SELECT * FROM OrdersTable WHERE ShipCity = 'Miami'

Malicious User
Inputs the following in the form:
Redmond' DROP TABLE OrderTable --
Query to the backend is:
SELECT * FROM OrdersTable WHERE ShipCity = 'Miami'
DROP TABLE OrdersTable --'

Mitigate SQL injection Attacks

  • Validate all User Input
    - Explicitly reject all input that is invalid
    - Define (if possible) the set of valid input and accept only those
  • Prune all error messages flowing back to the client
    - Error messages can be a useful tool to attackers
  • Run applications in minimally privileged contexts
  • Use Integrated Security to connect to back end...Avoid storing passwords