Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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();
            }
        }
    }
}
share|improve this question
    
Any chance you're posting that s script anywhere when you're done? I'd like to do something similar and would love to use this as a starting point. –  SqlRyan Oct 10 '12 at 14:00
    
@SqlRyan: Added the script and source to a short C# program that does the S3 upload. If you find a functional, command-line S3 client for Windows please let me know. s3.codeplex.com crashed when I ran it, and it is no longer maintained. –  Eric J. Oct 11 '12 at 19:36
add comment

2 Answers 2

up vote 2 down vote accepted

Access the backups files is controlled on the Windows end, not necessarily by SQL Server. SQL Server only requires that the account the SQL Server service is run as is given read/write access to the backup directory.

If you want another account to have access to the backup directory you will have to specifically add it to the ACL of that directory, or a group that might already have the permissions.

share|improve this answer
add comment

The permissions to the file will be coming from the parent folder. You'll need to change the rights on the folder that the backup is being written to then the new backups will get the correct rights.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.