BACKUP MASTER KEY error reporting oddity by dhammer3407

Resolved

as Not Reproducible Help for as Not Reproducible

Type:	Bug
ID:	771101
Opened:	11/14/2012 10:56:21 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

I have encountered and odd behavior with the BACKUP MASTER KEY command when using SQL Server 2008 R2 with SP1 (10.50.2500). When I execute the BACKUP command on a UNC path that I do not have write access to I would expect an error. What I have observed and can reproduce is that when I run the command once I receive the Command Executed Successfully message but I do not get my backup because I didn't have write access. Then if I run the same script again I get the appropriate error thrown.

Test script used (master key encrypted by the service master key):
USE [Master];
BACKUP MASTER KEY TO FILE = '\\Networkpath\TDEKey.key' ENCRYPTION BY PASSWORD = 'MySuperSecretPassword123#@!';

Once I start to get the error I will continue to get it from re-running the script but I can reproduce the issue if I change the name of the file with the file path remaining the same. Once I alter the file name I will once again get the Completed Successfully and followed by the error on attempt number 2.

The only work-around I have found is that local paths do not appear to have this behavior. I did check MSDN and UNC paths are explicitly stated as supported.

Details (expand)

English

SQL Server 2008 R2 SP1

SQL Engine

Windows Server 2008 R2 (all editions)

English

Execute the following on a UNC path that the SQL Server Service does not have write access to.

USE [Master];
BACKUP MASTER KEY TO FILE = '\\Networkpath\TDEKey.key' ENCRYPTION BY PASSWORD = 'MySuperSecretPassword123#@!';

You should receive a "Command(s) completed successfully." message.

Re-run the same script and you should get the appropriate write access error.

Then, change the file name and re-run.

USE [Master];
BACKUP MASTER KEY TO FILE = '\\Networkpath\TDEKeytest2.key' ENCRYPTION BY PASSWORD = 'MySuperSecretPassword123#@!';

You should get a repeat of the results above, a success and then a failure but in both cases no backup file should have been created.

You should receive a "Command(s) completed successfully." message on the first run.

Re-run the same script and you should get the appropriate write access error:

Msg 15240, Level 16, State 1, Line 2
Cannot write into file '\\Networkpath\TDEKey.key'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

You would get a write access error on the first attempt.

Msg 15240, Level 16, State 1, Line 2
Cannot write into file '\\Networkpath\TDEKey.key'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

X64

0 attachments

Posted by Microsoft on 12/4/2012 at 11:59 AM

Hi,

We're taking a look at this problem and get back to you once we've determined whether this is a bug or not.

Microsoft