SQL Server Security

Secure Installation:

1.Review installation requirements, system configuration checks, and security considerations for a SQL Server installation.

2. SQL Server Setup installs the following software components required by the product

a. SQL Server Native Client

b. SQL Server Setup support files

3.Physical security protect all related systems, media, backups, etc

4. Never place database unprotected on public net

a. Or on unprotected private net

b. Firewall protected

c. S/W mediating database access

5. Install on NTFS file system (Do not use FAT32 file system)

a. This allows securing the files appropriately

6. Do not install on a domain controller

7. Latest code is most secure code

a. Apply latest service packs and security patches

8. SQL Server utilities to configure SQL Server

Configuration Options:

1. Authentication mode

a. Use Integrated Security

More secure protocols (Kerberos and NTLM)

Kerberos allows for delegation

Allows for password policy enforcements

Typically does not require application to store passwords

b. If using Mixed mode (Standard SQL Authentication)

Use SSL to encrypt network traffic

Use strong passwords

Never use blank passwords

2. Login auditing

a. Audit failed login attempts at the very least

3. Disallow ad hoc queries

4. Choose static ports for named instances avoid opening UDP1434 at firewall

a. Media security including backups

b. Assume damage possible and have aggressive backup policy

c. Test disaster recovery system

5. Turn on appropriate level of auditing

a. Track critical user actions at a minimum (i.e: sysadmin actions, server role membership changes, password changes, login-related activity)

b. Keep overhead minimum

6. Encryption options

a. Protect sensitive data over the wire use SSL, IPSEC, VPN, etc.

b. File-level encryption

7. SQL supports Encrypted File System

Mid Tier to Database Connection:

SQL Security

1. End users authenticate at application level

a. Database trusts application to authenticate users

2. Connection to database using standard SQL login

a. Use low-privileged login account

b. Use strong passwords

c. Leverage SSL to protect authentication over the wire

3. Secure mid tier credentials data protection APIs

a. Encrypted using service’s credentials

b. Only same service account can decrypt

4. Disadvantages

a. Credentials storage required

b. Standard SQL authentication weaker than Windows authentication

5. Advantages

a. Works across firewalls and nontrusted domains

b. Connection pooling possible

Integrated Security

1. Run ASP.NET as low-privileged account

2. End users authenticate at application level

3. Database trusts application to authenticate users

4. Connection to database in context of ASP.NET account

a. Recommend low-privileged domain account

b. Alternatively, local Windows account on SQL Server box with same username and password

c. Useful if connection made across nontrusted domain

d. Account has only necessary runtime permissions in SQL

e. Is not a high-privileged account; not a sysadmin

5. Advantages

a. No storage of credentials needed

b. No need to pass credentials over the wire to SQL

c. Running as low-privileged account, minimizes potential damage from compromise

d. Connection pooling possible as single account is used

Preventing SQL Injection:

Attacker allowed sending SQL queries to backed end data store

Example:

Application Code:

var shipcity;

ShipCity = Request.form (“Shipcity”)

var sql = “SELECT * FROM OrdersTable

WHERE ShipCity = “’ + Shipcity + “’”;

Normal user: Inputs REDMOND in the form query to back-end is:

SELECT * FROM ORDERSTABLE WHERE SHIPCITY = ‘REDMOND’

Malicious user: Inputs REDMOND’ DROP TABLE ORDERTABLE – in the form  

Query to the back-end is:

SELECT * FROM ORDERSTABLE WHERE SHIPCITY = ‘REDMOND’ 

DROP TABLE ORDERSTABLE—’

SQL injection

1. Why SQL injection works?

a. Connection made in context of higher-privileged account

b. Application accepts arbitrary user input

2. Mitigating SQL injection

a. Validate all user input

b. Define set of valid input, accept only that

c. Reject all invalid input

d. Avoid using dynamic SQL in stored procedure

e. Run applications in minimally privileged contexts

f. Never run as sysadmin

Tips for App Dev Teams

1. Understanding various security issues

a. Different threat vectors, attack scenarios

b. Awareness of issues such as SQL injection, cross-site scripting, buffer-overflow attacks

2. Construct threat analysis for each S/W component

a. Enumerate component boundaries

b. Analyze component data flow, interfaces and interactions

 - Can it be compromised?

 - What data flows in and out?

c. Compromise could be through different kinds of threats

Escalation of privileges, tampering of data, spoofing, information disclosure, code injection

3. Code Review

 a. Develop Code review checklists

 b. Guideline for common security issues

 c. Directed code reviews — based on threat analysis

4. Generic file reviews — top-down approach.

About Author:

Amit Gupta is technology lead in Systems Plus Pvt. Ltd and keen to resolve challenges using his technical skills. He actively contributes to technology and can be contacted at: amit.gupta@spluspl.com