If you don’t know the lingo, you may run into trouble looking after your SQL databases
If you are new to SQL Server, then it’s a good idea to understand the basics of security that’s used to protect the database from hackers. This is one of the essential responsibilities of a database administrator.
The topic of SQL Server security is very broad, so this blog will stick to a few good practices to secure any SQL Server instances you manage.
Make a backup and encrypt it
Backups of a database are essential for any organisation. If they are not encrypted, they can be easily copied, which could lead to a data breach. A DBA can avoid this situation by creating backups using the built-in MEDIAPASSWORD feature.
E.g. BACKUP DATABASE SampleDatabase TO DISK=’C:\ SampleDatabase.BAK’ WITH MEDIAPASSWORD=’DifficultPa55W0rd’ GO
Remove unnecessary users from accessing the database backup folder
Access to the database backup folder should be restricted and permissions only granted to a select few that need access. Unauthorised access could result in backup files being copied or accidentally deleted. Systems administrators should make sure as few as possible users have access to this folder.
Trusted authentication and SQL Server security
There are two types of security schemes in SQL Server Trusted Authentication (also known as Windows Authentication) and SQL Server Security.
The latter is a standard login and password combination, whereas the former assumes the computer trying to connect to the SQL database has been checked and verified through domain authentication and that authentication is then passed on to SQL Server.
Users and applications trying to connect to SQL Server using SQL Server Security will need a login and password that has be created on the SQL Server.
Some argue that using Trusted Authentication is better from a security standpoint as this uses organisation-wide Active Directory, account, group and password policies.
Rename the System Administrator account
The System Administrator account holds the keys to the castle. Hackers are aware of this, so to prevent attacks on this account by its name, it is wise to rename it to something else. To change the name go to Object Explorer and expand Logins, then right-click System Administrator account and choose Rename from the menu.
Make the system administrator’s password difficult to guess
If you use mixed-mode authentication in SQL Server, always use a difficult to guess password for the system administrator account. It is also best practice to avoid using a system administrator account to connect web applications to SQL Server.
You should also avoid using this account when carrying out daily maintenance activities; using a Windows account with the appropriate permissions is better.
Also remember to change system administrator passwords regularly.
Switch off unused features in SQL Server
If there are features of SQL Server you know your users aren’t going to use, you can switch them off to decrease the surface area of possible attacks. Features can be disabled using the policy-based management feature.
Any failed attempts at logging into a SQL Server database should always be audited as a security best practice. Having login auditing enabled means that both failed and successful attempts will be recorded in SQL Server error logs. This means you have a record to interrogate should you become suspicious of any unwarranted activity.
Revoke guest user access
A guest user account exists by default in SQL Server database and this is a potential security risk as its enables database access to logins who don’t have associated users in the database.
Because of this, it is best to disable guest user access from all user and system databases. This means that public server role members are not able to connect to user databases on a SQL Server instance unless they have been assigned access to these databases.
Disable SQL Server Browser Service
System administrators should ensure the SQL Server Browser Service is only running on SQL Servers where multiple instances of SQL Servers are running on a single server.
SQL Server Browser Service counts SQL Server information on the network, which is a possible security hazard in a locked down infrastructure.