Database encryption is a recognized information security best practice and requirement for compliance with many regulatory mandates. However, several forms of data exist outside the primary MS SQL Server data store – in temporary files, Extract-Transform-Load (ETL) data, debug files, log files, and other “hidden” secondary sources – which can compromise its security even with encryption in place. Many organizations are not aware that this sensitive data is stored unencrypted on their network and the risks associated with it. Because it is easy to access, unencrypted data outside databases is commonly targeted and stolen by hackers. 

Let’s take a look at the types of files that exist outside the SQL Server database, the function they provide, their location, and the type of sensitive data they can contain. We’ll also examine which of these data sources should be encrypted and why.

Transaction Logs

In general, transaction logs contain information about every change made to the database. This includes Data Manipulation Language (DML) changes like INSERTs, UPDATEs or DELETEs, as well as Data Definition Language (DDL) or structural changes like table DROPs, CREATEs, ALTERs and so on.

Two types of transaction logs contain copies of sensitive data: Online Transaction and Backup Transaction logs.

Online Transaction logs contain current database transactions. When a transaction log fills or backed up, the information is copied to a backup file on the file system. If a database recovery is required due to a database failure or other recovery operation, the log backups are used. Transaction logs and backups contain copies of sensitive data and should be secured.

System Databases

Master Database

This database records all of the system level information for a SQL Server system. It can contain very sensitive security information about your logins and security and should be protected.

Model File

This database is used as the template for all databases that are created on the instance of SQL Server. This database does not typically contain sensitive data that needs to be protected, but can be encrypted without any impact to the system.

MSDB File

This is the database used by the SQL Server Agent for scheduling alerts and jobs, and for recording operations. msdb also contains history tables such as backup and restore, which may contain information on the backup location. It is not as critical as other system databases, but it may also need to be encrypted.

Tempdb File

This database is a workspace for holding temporary or intermediate result sets. This database is re-created every time an instance of SQL Server is started. When the server instance is shut down, any data in tempdb is deleted permanently. Temporary data could potentially contain any data that is accessed in the user tables and should be protected. 

Distribution File

These exist only if the server is configured as a replication distributor. This database stores metadata and history data, as well as transactions for all types of replication. The transactional replication will contain the same sensitive information that resides in the user databases. It should be encrypted.

Backup Files

Backups of a SQL Server database are used for recovery purposes in the event of a database failure. They contain the same sensitive information that resides inside of the database. They should be secured and encrypted.

Error Logs

SQL Server uses error logs to report errors and provide information about what the database is doing when retrieving data. Sensitive data can appear in diagnostic logs when SQL Server is reporting an error. These logs, whether instance, Agent, or other logs, should be protected.

Scripts

Various scripts might be used to execute functions against the database, either as one-time jobs or for repeating functions.  Since scripts may contain clear text passwords required to connect to the database, these should be encrypted.

Reports

Reports can contain output from a SQL script or reporting tool in a PDF, html, etc. file format. If this unstructured data originates from a production database it may contain sensitive data and should be secured.

Exports/Imports

In general, these files can be in internal-SQL Server formats or other formats used to load data into or extract data from a database. They should be encrypted.

Conventional export

These files are easily readable via both the conventional import program and any string search command; and should be secured.

Extraction, Transform and Load (ETL) files

These files can be in a variety of formats. They are typically used to extract data from a production database, transform it to meet operational needs, and load it into the target system (a data warehouse or database).  They should be protected.

Conclusion

As we’ve seen, simply encrypting the database itself is not sufficient to secure data at rest in Microsoft SQL Server instances running on Windows server platforms. Enterprises also need to consider  locations around the database where sensitive data relating to SQL Server might reside, some of which are outside the direct control of the Database Administrators. Using this article as a check list will help you identify and secure secondary SQL Server information sources that if left in clear text could result in data leaks.  

# # #

About the Author: Todd Thiemann is Senior Director of Product Marketing at Vormetric and co-chair of the Cloud Security Alliance (CSA) Solution Provider Advisory Council.