Database Security 5 Methods for Backing up SQL Server Databases
Hardly a day goes by without information about a new cyber attack or a data security breach making the rounds. “YADB” (Yet Another Data Breach) has become a hashtag and buzzword.
Every database is vulnerable to security incidents. No platform is more at risk than others. This article focuses on SQL servers, but the information applies to a wide variety of platforms.
The reasons for security breaches and data theft are manifold. Sometimes someone has forgotten a laptop on the bus, sometimes a publicly accessible website is not protected from attacks by injecting SQL commands. But we shouldn’t make it too easy for hackers. With the following measures, we can better protect our data and database servers and reduce the risk of a data security breach.
Monitoring configuration
The very first point on any security checklist is to configure monitoring. In the past, monitoring of database servers was limited to performance monitoring. Nowadays you need more. Safety must also be monitored. The simplest and most frequently used security monitoring is to keep an eye on failed login attempts. In addition, you should monitor configuration changes at the database level, in server instances, and in the server operating system. The configuration should include monitoring password changes, server role and database role membership changes, and schema changes.
Software installation
When installing SQL Server, one should not install unnecessary functions. In other words: do not install reporting Services “just in case”, because someone might use it at some point. Any software and additional components of SQL Server that you install must be managed and patched regularly. This means that any software installed on the server is an additional security risk. In addition, SQL Server Management Studio does not have to be installed for each instance. It will hardly ever happen that you or a developer access a server via RDP. And even then, you should consider using a jump host instead.
Access control
Access to the database server should be limited to those people who really need it and limited to the specific actions that they need for their tasks. Ideally, only Windows authentication is used together with Active Directory (AD) groups. This limits the number of individual, manually created Windows user credentials. Another reason to avoid creating Windows user credentials is cleanup: when the appropriate people leave the company, the credentials must be removed manually. If you use AD groups, you don’t have to manually remove users that no longer exist.
Security-related tasks should be handled by a special security team responsible for assigning Windows users to AD groups. The database administrator is then responsible for assigning database-level permissions to these AD group credentials. If SQL authentication is required, deactivate or rename the “sa” account.
Protection against the injection of SQL commands
The injection of SQL commands is a common form of data theft, usually through web attacks. It is quite possible to detect and prevent attacks by inserting SQL commands. Regular penetration testing with tools like sqlmap can detect suspicious code. If the web server is configured to log all requests, the logs can be searched for evidence of a query to inject SQL commands. An injection of SQL commands can also be detected if an attacker has made changes to the schema, for example by removing a table.
Preventing the injection of SQL commands is not difficult. Instead of using dynamic SQL, one should use stored procedures or prepared statements and take care to clean up all inputs. It should be prevented that error messages are returned to a client, as they could provide the attackers with additional information about their own system. Another best practice is to use the EXECUTE AS function within SQL Server to execute statements with an account with lower permissions.
Lastly, the use of advanced stored procedures, which attackers could execute by injecting SQL commands, should be removed or deactivated.
Backing up Backups
The last step is to back up the backups. Without a good backup, recovery after a security incident becomes difficult. The 3-2-1-1 rule applies to backups: three backups on two different media types, one of which is immutable and the other is kept externally. The recovery process should be tested frequently. A backup alone is useless if it cannot be restored. The database backups of previous backups should not be overwritten. Otherwise, you will no longer have a working backup if the current backup fails. This situation should be avoided at all costs.
In addition, the use of Transparent Data Encryption (TDE) should be considered: this protects not only the database files, but also the database backups. Backups can be both compressed and encrypted while TDE is enabled. You don’t necessarily need all three, but you should consider combining compression with encryption in one way or another.
Conclusion
Data is the most valuable asset of any company. Every database is vulnerable to security incidents. No platform is more at risk than others. This article focuses on SQL servers, but the information applies to a wide variety of platforms. Safety is a shared responsibility. As a data professional, you should take care to secure the database server and reduce the risk of data loss.
