THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Case Study: Secure Log Shipping via SSL FTP

Today I’m putting up sort of an oddball solution I build a couple of months ago. We had the need to provide a reporting copy of some production databases for analysts to do ad-hoc reporting. The trick was that we needed to move the databases from a less secure location into a more secure location, with an untrusted domain boundary and a firewall in between. Log shipping to Standby Mode databases fit the bill from a business perspective, but doing it securely through the firewall was a bit of a stunt.

It’s worth noting that log shipping to standby isn’t always a good solution to this problem – because the users of the reporting database have to be “kicked off” each time a log backup is restored, you can’t really get close to real-time reporting with this design. In our case, however, day-old data is more than sufficient, so we just stage the log backup files during the day, and then restore them all at night to bring the standby databases up to some point in time a few hours behind production.

For this solution we settled on a secure file transfer using SSL + FTP (aka. SFTP). This basically makes an SSL connection to an FTP server, and then uses that secure channel to transmit the files using FTP. Other methods like Secure Copy (SCP) could also be made to work, I think. The trick was to locate the details about how to do an SFTP upload with PowerShell, and then to devise a way that the upload could incrementally push only new log backup files and not repeatedly upload the same files. We also use “vanilla” log shipping on the system in question for disaster recovery, so we needed to tap into that set of files, without disrupting it.

Overall, the system looked like this:

  1. Our source SQL Server system already has an out-of-the-box log shipping setup, so there is an existing file share that contains a “sliding window” of log backup files using Microsoft’s standard conventions.
  2. We have a Linux FTP server on the other side of a firewall from this source system. Windows file sharing, however, is blocked, so moving the log backup files with the usual method is not possible.
  3. Behind that FTP server we have some automation that can move files across the network to the destination SQL server we’ll use for our reporting function.

Prereq’s

Before our SSL setup would work, we needed to have a certificate arrangement in place where the source servers would trust the FTP server. We have internal certificates for this type of function, so the first step was to export a root certificate for the FTP server and add that to the source servers’ cert stores. This is a requirement because, as will become clear below, we are using the stock .NET framework FTP client classes, and there isn’t a safe way to force those to trust a server without a valid cert setup.

Second, being a PowerShell rank amateur, I had to do some research into how exactly one would use it to invoke FTP client classes. I found a few articles were very helpful on this topic:

http://msdn.microsoft.com/en-us/library/ms229715(v=vs.90).aspx

http://sharpertutorials.com/ultimate-guide-ftp/

http://stackoverflow.com/questions/265339/whats-the-best-way-to-automate-secure-ftp-in-powershell

Provided there’s appropriate cert/trust set up between the machines, automating SFTP doesn’t require any software purchase, because the .NET framework has quite suitable classes built right in. Accessing those from PowerShell is pretty easy – the last article shows how to port some of the C# examples from the first two over to PowerShell.

Sending a File

Getting one file across is a good starting point. The basic process is encapsulated in the following function – assuming a few variables are available to the function, such as  the FTP site address, username and password, and so on, this function will open a file and transmit it to an FTP site destination. Enabling SSL support is as simple as setting the “.EnableSSL” property for the ftp request object:

function sendFile ( $fullSourcePath ) {

    # Function uploads one file via SFTP:

    $fileToUpload = Split-Path $fullSourcePath -leaf 
    $fromPath = Split-Path $fullSourcePath -parent
    $buffersize = 1024 * 1024 

    # Create an FTPWebRequest object to handle the connection 
    # to the ftp server 
    Write-Host Connecting to FTP Server
    $ftpRequest = [System.Net.FtpWebRequest]::create( `
            [IO.Path]::Combine( $ftpFullPath, $fileToUpload )  )

    # Set the request's network credentials, settings  

    $ftpRequest.Credentials = $SFTPCredential.GetNetworkCredential() 
    $ftpRequest.Method = [System.Net.WebRequestMethods+Ftp]::UploadFile 
    $ftpRequest.UseBinary = $true 
    $ftpRequest.KeepAlive = $false 
    $ftpRequest.UsePassive = $false 
    $ftpRequest.EnableSsl = $true 

    # Get an upload stream from the FTP server
    Write-Host Get Server Request Stream
    $requestStream = $ftpRequest.GetRequestStream() 

    # Create a stream to read from the source file, and a 
    # buffer to stage the data
    Write-Host Get File Source Stream
    $sourceStream = New-Object IO.FileStream( $fullSourcePath, `
                                           [IO.FileMode]::Open, `
                                           [IO.FileAccess]::Read )
    [byte[]]$readBuffer = New-Object byte[] $buffersize  

    # Transfer the file data to the destination via the buffer
    Write-Host Transfer the file
    
    do{
        write-Host Transferring file data ...
        $readLength = $sourceStream.Read( $readBuffer, 0, $buffersize )
        $requestStream.Write( $readBuffer, 0, $readLength )
    } while ( $readLength -ne 0 )

    Write-Host Close Streams
    $requestStream.Close()
    $sourceStream.Close()

    Write-Host Get Response
    $ftpResponse = $ftpRequest.GetResponse()  
    $ftpResponse.Close()
}

Storing Credentials

Because this will be a fully automated process, and isn’t within the realm of Windows Auth, I also needed to store the FTP site user credentials in some secure fashion. PowerShell has a pretty neat pair of command-lets that can take a string and store it, in a file, as a value encrypted by the current Windows user key. Those are, essentially:

a. To store a password as an encrypted value in a file:

> $newPassword = read-host –assecurestring
  <type the password to encrypt>
> ConvertFrom-SecureString $newPassword | Set-Content 'someFile.txt'

b. To read the file, decrypt the value, and then make it usable in a “network credential” object:

> $credential = New-Object System.Management.Automation.PsCredential( `
  'UserName', ( Get-Content 'someFile.txt' | ConvertTo-SecureString ) )
> $credential.GetNetworkCredential()

See http://www.leeholmes.com/blog/2008/06/04/importing-and-exporting-credentials-in-powershell/ for more background and details.

Remember, though, that only the user who encrypts the value can decrypt it, because this encryption method uses the current Window’s user’s key.

Now, armed with a method to upload files, and to keep the required credentials, the next problem was how to automate the process, making sure we didn’t repeatedly upload the same log backup files. The sending server would have no knowledge of the list of files on the destination server, so essentially we have to track which files were successfully transmitted in each pass of the upload script, and then send only the net-new files from a folder.

Tracking Sent Files

From the point of view of our source server, these log backup files go into a “black hole” – there’s no visibility to the destination server to see which files were sent already. So we wrapped the send function in some added code that uses a small text file to keep track of each file that was successfully transmitted. The basic idea is this, in pseudocode:

  1. Set parameters such as file locations, ftp server URL, credentials.
  2. If there is a text file available, containing a list of the log backup files that have already been sent, read it and put the list of files into a hash table (prevents uploading the same files twice).
  3. Create an empty list in memory for new, pending files to send.
  4. For each .TRN file currently present in the transaction log backup folder, matching some naming pattern:
    1. If the file is on the list from #1, then note that it was already sent.
    2. If the file is not on the list, then add it to the list of pending files to send.
  5. Replace the list from #2, on disk, with the new list from #4.1. This is the new sent file list. (Performing this exchange automatically discards the oldest file names from the list, preventing the need to create a process to circle back and delete the oldest file names from the list.)
  6. For each file on the list of files to send:
    1. Upload the file by SFTP (function above).
    2. Add the name of the file, after successful upload, to the new sent file list.

The full text of the script, including these details, is at the end of this post. The script runs as a scheduled task under a service account. (Worth noting: when encrypting the FTP password for this process, one has to use Run As, run a PowerShell session as the service account, and encrypt the password using that account’s key, so that the scheduler can decrypt the data later.)

Half a Log-Shipping Config

So, with this process in place, we should have files moving over the network and arriving at the destination server. The next piece is to enable the automated restores at the reporting server. It’s not possible to use the log-shipping GUI to set this up, because there’s no access to the source server. You can, however, use the log shipping stored procs to set up just the receiving half of a stock log-shipping config, like:

declare @copy_job uniqueidentifier,
       @restore_job uniqueidentifier,
       @secondary uniqueidentifier
      
exec sp_add_log_shipping_secondary_primary
       @primary_server = 'someServer.abc.com',
       @primary_database = 'someDB',
       @backup_source_directory = 'G:\SQLBackup\LogShipping' ,
       @backup_destination_directory = 'G:\SQLBackup\LogShipping',
       @copy_job_name = 'someDB Log Copy',
       @restore_job_name = 'someDB Log Restore',
       @file_retention_period = 14420 ,
       @copy_job_id = @copy_job OUTPUT ,
       @restore_job_id = @restore_job OUTPUT ,
       @secondary_id = @secondary OUTPUT
select @copy_job CopyJob, @restore_job RestoreJob, @secondary Secondary
 
exec sp_add_log_shipping_secondary_database
       @secondary_database =  'someDB',
       @primary_server = 'someServer.abc.com',
       @primary_database = 'someDB',
       @restore_all = 1,
       @restore_mode = 1,
       @disconnect_users = 1,
       @restore_threshold = 4320 – Minutes

This type of script will create the destination log shipping config only, and will make disabled, unscheduled jobs for restore and copy. Based on those results, I enabled the restore job and scheduled it to run only at night (to catch the reporting databases up to production during off-hours). The FTP upload, together with the automation we have to move the files from the FTP server to the destination SQL Server, makes the copy job irrelevant, so I just left it disabled.

Last Details: Use the .WRK Extension, Agent Proxy issue

In testing this, I did find one bug in my crazy scheme: the destination server would try to open a .TRN file before that file had finished copying into the destination folder. To solve that issue, I just mimicked what SQL Server does natively: we adjusted the automation between the FTP server and the destination server to rename the files with a .WRK extension, then change the names back when delivery of each file is complete, so that SQL Server can open them safely.

Finally, while I had originally intended to schedule the upload script with SQL Agent, there’s some conflict, apparently, between using a SQL Agent proxy account and the PowerShell secure string methods. I had to fall back on the (less desirable) Windows Scheduled Tasks to get the password decryption to work as designed.

Final Script

Disclaimer: This is a crazy idea, and I am bad at PowerShell. If it helps you out, that’s great – but please read and understand the script, and test carefully before attempting to deploy in your environment. Also, sadly, this is a PowerShell 1.0 script, and therefor lacks Try/Catch, in favor of Trap. If you have PowerShell 2 as an option, it’s probably advisable to fix my questionable error handling:

# Script to incrementally upload new transaction log
# files via SFTP for log shipping to a remote site

$ErrorActionPreference = "Stop"

# Set file locations, parameters here:

    # FTP Site location and credentials:

    $ftpFullPath = "ftp://yourserver.com/someDirectory/"

    $userName = "ftpuser"

    # Load the FTP password from an encrypted file:
    $password = ConvertTo-SecureString ( `
                Get-Content D:\someFolder\passwordfile.txt )

    $SFTPCredential = New-Object System.Management.Automation.PsCredential( `
                                  $userName, `
                                  $password )

    # Location and file name pattern for source files to copy:

    $logBackupPath = '\\mysqlserver\logshippingshare'
    $logBackupNameRegex = '(^database_a|^database_b|^database_c).*\.trn'

    # Name of the file to track sent file information
    $fileListFileName = '_FTP_Transferred_Files'
    
function sendFile ( $fullSourcePath ) {

    # Function uploads one file via SFTP to location set above:

    $fileToUpload = Split-Path $fullSourcePath -leaf 
    $fromPath = Split-Path $fullSourcePath -parent
    $buffersize = 1024 * 1024 

    # Create an FTPWebRequest object to handle the connection 
    # to the ftp server 
    Write-Host Connecting to FTP Server
    $ftpRequest = [System.Net.FtpWebRequest]::create( `
            [IO.Path]::Combine( $ftpFullPath, $fileToUpload )  )

    # Set the request's network credentials, settings  

    $ftpRequest.Credentials = $SFTPCredential.GetNetworkCredential() 
    $ftpRequest.Method = [System.Net.WebRequestMethods+Ftp]::UploadFile 
    $ftpRequest.UseBinary = $true 
    $ftpRequest.KeepAlive = $false 
    $ftpRequest.UsePassive = $false 
    $ftpRequest.EnableSsl = $true 

    # Get an upload stream from the FTP server
    Write-Host Get Server Request Stream
    $requestStream = $ftpRequest.GetRequestStream() 

    # Create a stream to read from the source file, and a 
    # buffer to stage the data
    Write-Host Get File Source Stream
    $sourceStream = New-Object IO.FileStream( $fullSourcePath, `
                                           [IO.FileMode]::Open, `
                                           [IO.FileAccess]::Read )
    [byte[]]$readBuffer = New-Object byte[] $buffersize  

    # Transfer the file data to the destination via the buffer
    Write-Host Transfer the file
    
    do{
        write-Host Transferring file data ...
        $readLength = $sourceStream.Read( $readBuffer, 0, $buffersize )
        $requestStream.Write( $readBuffer, 0, $readLength )
    } while ( $readLength -ne 0 )

    Write-Host Close Streams
    $requestStream.Close()
    $sourceStream.Close()

    Write-Host Get Response
    $ftpResponse = $ftpRequest.GetResponse()  
    $ftpResponse.Close()
}

# Hash table and array used to track sent files vs. files queued to send:

$filesAlreadySent = @{} 
$filesToSend = @()

$fileListFullPath = ( `
    join-path $logBackupPath ( $fileListFileName + ".txt" ) )
$tmpListFullPath = ( `
    join-path $logBackupPath ( $fileListFileName + ".tmp" ) )

# Check for / delete file list temp file, if there's one left from 
# a failed prior run. Prevents appending to an invalid/existing list:

if( Test-Path $tmpListFullPath ) {
    Remove-Item $tmpListFullPath
} 

New-Item $tmpListFullPath -type file

# Read the list of already-sent files from text file into the 
# file list hash table

Write-Host Reading list of sent files

if( Test-Path $fileListFullPath ) {
    Get-Content $fileListFullPath  `
    | Where-Object { $_.length -gt 0 } `
    | foreach {
        $filesAlreadySent.Add( $_.TrimEnd(), 1 )
    }
} 

# Walk all the files currently in the source folder, and transmit 
# any new ones added since the last run

Write-Host Walking all current log backup files

Get-Childitem $logBackupPath `
| Where-Object { $_.Name -match $logBackupNameRegex } `
| foreach { 

    if( $filesAlreadySent.contains( $_.name ) ) { 

        # File was already sent
        Write-Host "File was already transmitted:" $_.name

        # but retain that fact for the next run:
        $_.name | out-file $tmpListFullPath -append 

    } else { 
        # File needs to be transmitted
        $filesToSend = $filesToSend + $_.fullname 
    } 
}

# Exchange the new/temporary sent file list for the old one

if( Test-Path $fileListFullPath ) {
    Remove-Item $fileListFullPath
}
Rename-Item $tmpListFullPath ( $fileListFileName + ".txt" )

# Send the pending files and append them to the sent file list

foreach( $filename in $filesToSend ) {

    Write-Host "Sending File:" $filename

    &{ # Try to upload the file

        $file = get-item $filename
        sendFile( $file.FullName )

        # if successful, append the current file to the sent file list
        $file.name | out-file $fileListFullPath -append 
    }

    trap {
        Write-Host "There was an error sending file $filename"
        Write-Host "Skipping File"
        Write-Host "Error was:" $_.Exception.GetType().FullName $_.Exception.Message
        continue
    }
}

Write-Host File Transfer Complete
Published Thursday, May 19, 2011 6:12 PM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

HJ Meulekamp said:

Thanks Merill. I sort of used your post to come up with a different way to do the logshipping. I used the same ideas but reversed it. Have SQL server do its thing on the source side and PowerShell do the restoring and tracking of which files to restore. I described it at: http://www.serverside-developer.com/2012/09/automating-sql-log-shipping-over-sftp.html

September 16, 2012 12:49 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement