SQL server security

The three pillars of security according to Microsoft are:

  1. Reliability
  2. Confidentiality
  3. Reliability


Most, if not all companies keep their SQL Server inside a firewall. However, SQL Server database can still be attacked internally. So, all CIO's ensure that SQL Server is secured.

We know that Microsoft supports two modes of authentication.

Windows Only

Only Windows accounts can access the server

Windows and SQL Server

Both Windows accounts and accounts created within SQL Server can access the server. If you are sure that an attack is impossible, you can use Windows Only authentication. However, SQL Server 2005 has grown leaps and bounds and there is increased security of standard SQL Server logins. This has been achieved by including password complexity and timeouts.

Tips for keeping SQL Server secure:

The following six tips can be used to keep your sql server secure.
  1. Encrypt and Backup SQL Server files in a secure location
  2. Use Microsoft's Baseline Security Analyzer frequently
  3. Update SQL Server service packs and patches
  4. Check for weak passwords in SQL Server accounts
  5. Give access to only trusted clients
  6. Use Windows Only authentication wherever possible
Why we should not give 'sa' privileges

Ability to read, write, and mutilate all data stored on the SQL Server databases

Why we should not give "db_owner" privileges

Ability to drop tables, create new objects, and generally take total control of the affected database.

New features in SQL Server 2005 security

Surface Area Configuration

The first tool you run on installation is the SQL Server Surface Area Configuration Tool, with a link to configure services and protocols.

Default Off

To reduce unauthorized access after initial installation, a number of services have been turned off or set for manual start-up so no inadvertent access is granted.

Data and Native Encryption

SQL Server 2005 provides plenty of new features for securing the database. Database administrators can allow developers focus on the database details, as long as the developer works within the specified constraints. SQL Server 2005 supports encryption capabilities within the database itself.

Granular Permissions

Built on the principle of least privileges SQL Server permissions are more granular now to restrict the scope of rights.

User and Schema Separation

The standard link connecting users and the database objects they own is now dropped.