Sql-server – Scripting a Backup Process and Backup File Ownership

Background

I'm writing a Powershell script to

• Backup a database to a file on disk
• Compress and encrypt the file (7-Zip/AES256)
• Copy the encrypted backup to Amazon S3
• Delete the backup file

The script is supposed to run under a Windows account with the minimum privilege set to do accomplish those tasks, and it works great with SQL Server Express 2008R2.

However, with SQL Server 2008R2 Web Edition, the backup file is written such that only Administrators have full control of the file. While my less-privileged user can read the file, it cannot delete the file.

Question

Is it possible to configure SQL Server to write the backup file such that ordinary Users can delete it?

UPDATE

Here is the script as requested in a comment. I could not find a functional S3 console app for Windows, so wrote a basic one. Source is included below. These files are released into the public domain without any warrantee.

S3EncryptedBackup.ps1

#param ($SERVER = $(throw "Usage: S3EncryptedBackup.cmd -SERVER NameOfSqlServerInstance -DB NameOfDbToBackup"), 
#       $DB = $(throw "Usage: S3EncryptedBackup.cmd -SERVER NameOfSqlServerInstance -DB NameOfDbToBackup"),
#       $BACKUPFOLDER = $(throw "Usage: S3EncryptedBackup.cmd -SERVER NameOfSqlServerInstance -DB NameOfDbToBackup"),
#       $BUCKET = $(throw "Usage: S3EncryptedBackup.cmd -SERVER NameOfSqlServerInstance -DB NameOfDbToBackup"))

### DEV: Set these locally.  For prod, require as parameter
$SERVER=".\YOUR_SERVER_HERE"
$DB="YOUR_DB_NAME_HERE"
$BACKUPFOLDER="YOUR_BACKUP_FOLDER_HERE"
$BUCKET="YOUR_S3_BUCKET_HERE"

### EDIT System Configuration
$S3='E:\Software\S3Backup\S3Backup.exe'
$SEVEN="`"C:\Program Files (x86)\7-Zip\7z.exe`"" # Edit if 7-Zip installation differs

### For AES Encryption:
$AES_KEY="YOUR_ENCRYPTION_KEY_HERE"

### Edit the environment variables below only if the AWS keys change
$AWS_ACCESS_KEY_ID="YOUR_AWS_KEY_HERE"
$AWS_SECRET_ACCESS_KEY="YOUR_AWS_SECRET_HERE"

### Create the DB Backup

$BACKUP_FILE=$BACKUPFOLDER + "\" + $DB + "_" + (get-date -F yyyyMMdd) + ".bak"
$BACKUP_CMD="BACKUP DATABASE [" + $DB + "] TO  DISK = N'" + $BACKUP_FILE + "' WITH NOFORMAT, INIT,  NAME = N'" + $DB + "-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"
$BACKUP_7Z="$BACKUP_FILE.7z"

Remove-Item -Path $BACKUP_FILE -Include *.bak
Remove-Item -Path $BACKUP_7Z -Include *.7z

Invoke-Expression "SQLCMD.exe -S $SERVER -Q `"$BACKUP_CMD`""

if ($LastExitCode -ne 0) 
{
    throw "SQL Backup failed with exit code $LastExitCode."
}

### 7-Zip the file with AES Encryption
$SEVEN_ARGS=@("a", "$BACKUP_7Z", "$BACKUP_FILE", "-p$AES_KEY", "-mx3")

Start-Process $SEVEN -ArgumentList $SEVEN_ARGS -NoNewWindow -Wait

### Transfer to S3

# Uses our own S3Backup.exe because S3.exe from http://s3.codeplex.com/ crashed and is no longer supported

echo "Start S3 Upload to $BUCKET of $BACKUP_7Z"

$S3_PUT_ARGS=@("-Bucket:$BUCKET", "-Path:$BACKUP_7Z", "-Key:AWS_ACCESS_KEY_ID", "-Secret:$AWS_SECRET_ACCESS_KEY")

Start-Process $S3 -ArgumentList $S3_PUT_ARGS -NoNewWindow -Wait

if ($LastExitCode -ne 0) 
{
    throw "S3 Command failed with exit code $LastExitCode."
}

echo "Done!"

Remove-Item -Path $BACKUP_FILE -Include *.bak
Remove-Item -Path $BACKUP_7Z -Include *.7z

S3Backup.cs

I did not include my library for parsing command line parameters. You'll have to rewrite that part, which should be quite straightforward. If your AWS instance is using IAM to manage keys, you do not have to pass them in as parameters.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Amazon.Runtime; // Use NuGet to reference the AWS SDK 
using Amazon.S3;
using Amazon.S3.Model;
using Amazon.S3.Transfer;

namespace Tools
    class Program
    {
        static void Main(string[] args)
        {
            Args parsedArgs = new Args(args);

            string bucket = parsedArgs.Get("Bucket");

            if (bucket == null || bucket == string.Empty)
            {
                Console.WriteLine("Backup bucket must be specified as -Bucket:MyBucketName");
                Environment.Exit(1);
            }

            string path = parsedArgs.Get("Path");
            if (path == null || path == string.Empty)
            {
                Console.WriteLine(@"Backup path (directory or file) must exist and be specified as  -Path:D:\My\Path or -Path:D:\My\Path\MyFile.ext");
                Environment.Exit(2);
            }

            bool fileMode;

            if (Directory.Exists(path))
            {
                fileMode = false;
            }
            else if (File.Exists(path))
            {
                fileMode = true;
            }
            else
            {
                fileMode = false; // Compiler wants initialized value
                Console.WriteLine("Backup file does not exist.");
                Environment.Exit(3);
            }

            string key = parsedArgs.Get("Key");
            string secret = parsedArgs.Get("Secret");

            bool keyParmsOK = ((key == null && secret == null) || (key != null && secret != null));
            if (!keyParmsOK)
            {
                Console.WriteLine("If the parameters Key or Secret are specified, both must be specified.");
                Environment.Exit(4);
            }

            Transfer(fileMode, path, bucket, key, secret);

            Environment.Exit(0);
        }

        private static void Transfer(bool fileMode, string path, string bucket, string key, string secret)
        {
            AmazonS3Client s3 = null;
            try
            {
                if (key == null)
                {
                    s3 = new AmazonS3Client();
                }
                else
                {
                    AWSCredentials cred = new BasicAWSCredentials(key, secret, true);
                    s3 = new AmazonS3Client(cred);
                }

                using (TransferUtility transfer = new TransferUtility(s3))
                {
                    try
                    {                        
                        var listResult = s3.ListBuckets();
                        var exists = (from b in listResult.Buckets where b.BucketName == bucket select b).Any();
                        if (!exists)
                        {
                            s3.PutBucket(new PutBucketRequest() { BucketName = bucket, BucketRegion = S3Region.US });
                        }
                        if (fileMode)
                        {
                            transfer.Upload(path, bucket);
                        }
                        else
                        {
                            transfer.UploadDirectory(path, bucket);
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Error transfering to S3: " + Environment.NewLine + ex.ToString());
                        Environment.Exit(5);
                    }
                }
            }
            finally
            {
                if (s3 != null) s3.Dispose();
            }
        }
    }
}