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

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Securing Log Shipping

There was a post recently where someone was asking about securing log shipping to comply with HIPAA requirements. It struck me at first that really log shipping isn’t a SQL Server operation, it’s a Windows operation. The ongoing process of log shipping is:

  • Perform a SQL Server log backup
  • File sits on the local Windows file system for some xxx time
  • Copy the log file to the remote server
  • The file sits on the remote Windows file system for some yyy time
  • Restore the log backup to the SQL Server instance

Of these, they break down in terms of security and possible issues in this way.

  • Perform a SQL Server log backup (SQL Server process, secure)
  • File sits on the local Windows file system for some xxx time (Windows security needed)
  • Copy the log file to the remote server (Standard SMB copy, Windows security needed)
  • The file sits on the remote Windows file system for some yyy time (Windows security needed)
  • Restore the log backup to the SQL Server instance (SQL Server process, secure)
  • The only real places to provide security are from Windows, where you need to do the following:

    • Assign tight Windows security to a limited access group, preferably only service accounts for SQL Server.
    • Ensure network communications between the two Windows servers is secure with something like IPSec.

    This means to me that this is primarily a Windows issue, and that the DBA needs to work with the Windows admin to implement an IPSec policy or setup a secure tunnel. Here’s one link I found, but not being a strong Windows admin, I’m not sure if it’s the best one. The idea would be that you would want to secure the network traffic to prevent a third party from copying the log backup somehow.

    Is it a big deal? I’m not sure about that. I think this is an item that auditors might be concerned about more than it is a security risk. After all, even if a hacker acquired an old full backup and a log backup, they wouldn’t necessarily be able to restore the log backup since the LSNs would be out of order. And while a log backup might contain sensitive information, an attacker couldn’t guarantee that any particular log backup would have sensitive information. Only if that information were edited would it be included in a log backup.

    The big security downside is that whatever channel or method you use here would likely be the same one used for initialize things and include a full backup somewhere. So it’s a good idea to provide tight security, and not just set up shares that Everyone can access.

    Comments

    No comments.

    Leave a Comment

    Please register or log in to leave a comment.