Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Securing SQL Server: Vulnerabilities You Might Not Have Considered

By Ashvin Kamaraju,

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. 

Total article views: 5792 | Views in the last 30 days: 2
 
Related Articles
BLOG

Understanding Contained Database in SQL Server 2012

SQL Server 2012 supports contained databases and partially contained databases, which provide a high...

BLOG

Contained Databases – Server Setting Matters

In doing some additional testing on contained databases, I decided to create a new database on a new...

BLOG

Denali — Day 7: Contained Database

  Denali – Day 7 : Contained Database Contained database is another new feature for sql server...

BLOG

Cross-database Queries by Contained Users

Cross-database Queries by Contained Users http://www.necromaticmedia.com/2007/11/containment/ ...

BLOG

Enabling Contained Databases in SQL Server 2012

One of the new features in SQL Server 2012 is the Partially Contained Database feature. I gave a tal...

Tags
encryption    
security    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones