The three pillars of security according to Microsoft are:
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.
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.
Why we should not give 'sa' privileges
- Encrypt and Backup SQL Server files in a secure location
- Use Microsoft's Baseline Security Analyzer frequently
- Update SQL Server service packs and patches
- Check for weak passwords in SQL Server accounts
- Give access to only trusted clients
- Use Windows Only authentication wherever possible
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.
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.
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.