Below is a list of some of the most important best practices for database server security, database security, and data security:
Database Server Security
- Use Windows account authentication for connections to a SQL Server. It is much more secure than SQL Server authentication.
- Do not use SQL Server authentication except where absolutely necessary. Normally, it is not necessary. Its security is weaker than Windows account authentication and more easily hacked.
- Do not allow anyone to log on to a database server with the “sa” account. Give the “sa” account a long and complex password, lock it in a safe, and never use it except in case of an emergency.
- Passwords should now be at least 13 characters long. Recent advances in hacking technology have made brute force attacks viable for shorter passwords. The longer the password, the longer time it will take to hack it using brute force.
- Passwords should not contain any dictionary words or proper names, and should contain at least one of each of the following: alpha lower case, alpha upper case, numeric, and special characters.
- Enforce SQL Server’s password policy including expirations.
- Do not give SQL Server Administrator permissions to Domain Administrators. Strict separation of administrator permissions is a best practice.
- Do not give SQL Server Administrator permissions to Windows Server Administrators. Strict separation of administrator permissions is a best practice.
- Each SQL Server Administrator should have his/her own, individual account.
- No individuals that directly login to a SQL Server should ever use the same account.
- Strictly limit the individuals that can directly login to a SQL Server. There are very few good reasons to allow direct user access to either the server or a database.
- Do not give more access to databases and database objects in a SQL Server than is necessary.
- When direct user access is necessary, give strict read access, as little permission as possible. Do not give DDL view access to anyone who does not have a need to know the structure of a database. Knowledge of database structures can be used to hack a database.
- Use views and stored procedures to give access to data. Both hide database structural information.
- Do not allow users, software developers, even managers, to write ad-hoc SQL queries. Incorrectly written, they have the potential to devastate a production database server environment, even to destroy or delete an entire database.
- What follows is a very safe security solution for database access strictly through stored procedures. It is an excellent way to prevent SQL Injection attacks against databases. It is ideal for application software implementations. It involves only 3 security elements: a server login, a user account for each accessed database, and stored procedures created by a system administrator who grants permission to the database user account(s) to execute the stored procedures:
- Create a server principal (a server login) for the application software that is not mapped to any database(s). It must only be able to connect to the server (it must be in the “public” role).
- Create a database principal (a database user account) on each database, that references the server login, and that will be used to execute the stored procedures supporting the application software.
- Have a SQL Server system administrator create the stored procedures and explicitly grant the database user account permission to execute the stored procedures.
- Users executing the application software will execute the stored procedures which have been given the necessary and appropriate access to the referenced database(s).
- At the database level, never assign schema and/or database object ownership directly to a user account. Always assign schema and database object ownership to a role and assign membership in the role to users needing ownership. Remember, ownership implies the ability to do anything with the object owned. Ownership should be granted very sparingly and only for very good reasons.
- Do not give a database’s “Guest” account any permissions.
- Whole databases can be encrypted but this is inefficient and consumes server resources.
- Chose only those columns that contain sensitive data and encrypt them explicitly.
- Never allow anyone but a database administrator to directly modify data in a database. All other users should use a stored procedure for data modifications.
If you need help with performance tuning your SQL Server, contact me to schedule a free consultation.