Moving Encrypted Database
-
Hey Guys,
I am trying to move an encrypted database that some contractors installed over a year ago and they are now gone. When I try to backup the cert. with encrypted password it tells me I cannot write the file to the location I have selected. Yes I checked permissions and even added my login with god rights to the drive. I also created a folder on a local drive to the sql server and got the same error.
I did read an article that says I have to use the same password that the database was encrypted with and I do not have that. I did want to reach out to you guys first before I come to conclusion that I am !@#$ and have to re-install the application. :crazy:
Msg 15240, Level 16, State 1, Line 1
Cannot write into file ' \etwork drive\SQLPrivateKey'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
MCSE SQL Server 2012\2014\2016
-
Your permissions are irrelevant.
Does the SQL Server service account (the account that SQL runs under) have permission to write to that folder?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Yes, my service account that SQL uses has full permissions to the drive.
MCSE SQL Server 2012\2014\2016
-
What account is SQL running under? Domain, local system, other?
Does the path exist? If that was intended as a UNC path, it's missing a \ at the beginning. If it's not, it's missing a drive letter. It also has a leading space.
What's the exact command you're running that throws that error?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
USE master;
GO
ALTER DATABASE "mydatabase"
SET RECOVERY FULL;
GO
-- Create mydatabase and MBAM mydatabase devices.
USE master
GO
EXEC sp_addumpdevice 'disk', mydatabase Device',
'\etworkdrive\mydatabase.bak';
GO
-- Back up the full mydatabase. -- This is the script I get the error
BACKUP DATABASE [mydatabase] TO [mydatabase Device];
GO
BACKUP CERTIFICATE [mydatabase Encryption Certificate]
TO FILE = '\etworkdrive\SQLServerInstanceCertificateFile'
WITH PRIVATE KEY
(FILE = ' \etworkdrive\SQLServerInstanceCertificateFilePrivateKey',
ENCRYPTION BY PASSWORD = '$PASSWORD$'
);
GO
I got this script straight off of MS site. I have checked my network drive, all my SQL backups go there. If there was a permission issue or the drive did not exsist I would have bigger issues. Yes I have verified everything, users, permissions, drives, all of it. Domain account
Thank you
MCSE SQL Server 2012\2014\2016
-
Ok, first ditch the dump device. I don't know anyone who uses them. Besides, that's not a valid file path, so that won't work anyway.
EXEC sp_addumpdevice 'disk', mydatabase Device','\etworkdrive\mydatabase.bak';
BACKUP CERTIFICATE [mydatabase Encryption Certificate]TO FILE = '\etworkdrive\SQLServerInstanceCertificateFile'
That file path is invalid. If it's a UNC path it needs to be \\Servername\Path\File. If it's a local drive it needs to be DriveLetter:\Path\File
WITH PRIVATE KEY(FILE = ' \etworkdrive\SQLServerInstanceCertificateFilePrivateKey',
ENCRYPTION BY PASSWORD = '$PASSWORD$'
That file path is invalid. It has a leading space. If it's a UNC path it needs to be \\Servername\Path\File. If it's a local drive it needs to be DriveLetter:\Path\File
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
The file path is correct, the post keeps chopping it off.
I took the space out, it dod not work.
MCSE SQL Server 2012\2014\2016
-
Posts don't discard slashes or add spaces.
You have a leading space in your last file reference. You have a missing '\' in all of your file references.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
It took mine off, serveral times.
However it still is not working. I took the spaces out. I will PM you since my paths are not getting on here correct.
MCSE SQL Server 2012\2014\2016
-
-- Back up the full mydatabase. -- This is the script I get the errorBACKUP DATABASE [mydatabase] TO '\\server05\sqlbackups\mydatabase.bak';
GO
BACKUP CERTIFICATE [mydatabase Encryption Certificate]
TO FILE = '\\server05\sqlbackups\EncryptionCert'
WITH PRIVATE KEY
(FILE = '\\server05\sqlbackups\PrivateKeyFile',
ENCRYPTION BY PASSWORD = '$PASSWORD$'
);
GO
Replace with your actual server names and paths. If it's still not working, with what error?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Still getting;
Msg 15240, Level 16, State 1, Line 2
Cannot write into file '\\drive\SQLPrivateKey'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
If I put back slash back slash networkdrive -- it chops it off \etwordrive
If I put \\drive -- it does not chop it off
MCSE SQL Server 2012\2014\2016
-
\\etwordrive
'\\etwordrive\SQLPrivateKey'
\\etwordrive\SQLPrivateKey
Looks OK to me.
does \\drive exist? Does the SQL Server service account have write permissions? Does the file already exist?
Could you post the exact code you're running?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Yes the drive is valid, yes MSSQLSERVER has rights to write to the drive and no it is not there, I have not been able to ever write the key to the drive.
look at your post above, does it really look OK?
\etworkdrive
\\drive
USE master;
GO
ALTER DATABASE "MBAM Recovery and Hardware"
SET RECOVERY FULL;
GO
BACKUP DATABASE [MBAM Recovery and Hardware] TO
DISK = N'\\UNCPath\MBAM Recovery and Hardware\MBAMRec.bak'
WITH NOFORMAT,
NOINIT,
NAME = N'MBAMRec',
SKIP,
REWIND,
NOUNLOAD,
COMPRESSION,
STATS = 10
GO
BACKUP CERTIFICATE [MBAM Recovery Encryption Certificate]
TO FILE = '\\UNCPath\SQLServerInstanceCertificateFile'
WITH PRIVATE KEY
(FILE = '\\UNCPath\SQLPrivateKey',
ENCRYPTION BY PASSWORD = '1MYPASSWORD$'
);
GO
12 percent processed.
21 percent processed.
31 percent processed.
41 percent processed.
51 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
92 percent processed.
Processed 328 pages for database 'MBAM Recovery and Hardware', file 'RecoveryAndHardware' on file 1.
100 percent processed.
Processed 1 pages for database 'MBAM Recovery and Hardware', file 'RecoveryAndHardware_log' on file 1.
BACKUP DATABASE successfully processed 329 pages in 0.720 seconds (3.569 MB/sec).
Msg 15240, Level 16, State 1, Line 2
Cannot write into file '\\UNCPAath\SQLPrivateKey'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
MCSE SQL Server 2012\2014\2016
-
It's the Database!!! (10/28/2013)
look at your post above, does it really look OK?Yes, looks fine. All three lines had the \\ that I typed in. Middle one had single quotes, others didn't. Just copied exactly what you had, but with two
Why are you writing the backup to a sub-folder and the keys to the 'root' of that share? Have you checked the permissions of both? The successful backup shows that SQL has permission to the sub folder, but the error suggests it doesn't have permission to the root. Have you tried writing the keys to the same location as the backup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I got the file to write our once taking the sp_addumpdevice off. Now I am unable to get the cert on the new sql server. always problems...
Thank you Gail for helping me, seems there were two issues.
One) the space and
two) the sp_addumpdevice.
MCSE SQL Server 2012\2014\2016
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply