Backup command not reading contents of the encryption file
francisco
Posts: 3
Hi,
we have been using backup encryption for a while, we have one specific cluster where the file containing the encryption file is not being read, instead, the password used to encrypt the backup is the name of the file.
This is the code we use:
DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASES [msdb] TO DISK = [\sql_backups<AUTO>_encr.sqb] WITH PASSWORD = [ FILE:C:EncryptionDCR.txt ] , KEYSIZE=256, COMPRESSION = 2, ERASEFILES_ATSTART = 30, MAILTO_ONERROR = [francisco@kareo.com] " ', @exitcode OUT, @sqlerrorcode OUT
The contents of file c:EncryptionDCR.txt is what we use as password.
The backup executes fine, but the encryption key is set to "C:encryptionDCR.txt" instead of the contents of the file.
This is running the same way across the rest of the sql clusters, but we don't have these issues in any other but this cluster.
We can't figure out what's wrong with this particular one. The sql backup version on this server is 7.7.0.18.
Any ideas?
we have been using backup encryption for a while, we have one specific cluster where the file containing the encryption file is not being read, instead, the password used to encrypt the backup is the name of the file.
This is the code we use:
DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASES [msdb] TO DISK = [\sql_backups<AUTO>_encr.sqb] WITH PASSWORD = [ FILE:C:EncryptionDCR.txt ] , KEYSIZE=256, COMPRESSION = 2, ERASEFILES_ATSTART = 30, MAILTO_ONERROR = [francisco@kareo.com] " ', @exitcode OUT, @sqlerrorcode OUT
The contents of file c:EncryptionDCR.txt is what we use as password.
The backup executes fine, but the encryption key is set to "C:encryptionDCR.txt" instead of the contents of the file.
This is running the same way across the rest of the sql clusters, but we don't have these issues in any other but this cluster.
We can't figure out what's wrong with this particular one. The sql backup version on this server is 7.7.0.18.
Any ideas?
Comments
The ability to store the password in a plain text file was introduced in SQL Backup V7.5, for users who did not want specify the password in their scheduled jobs. Therefore protecting the plain text file using Windows file permissions.
The fact that you have this configured and working on other SQL clusters makes it a little confusing.
Lets start with the basic questions:
1. In your posted you state that the backup version is V7.7.0.18. Can you please confirm the actual server components version?
V7.7.0.18, is the version of SQL Backup GUI installed. If you right click the cluster name under the registered servers panel (to the left of the time line) and select properties, a small table will appear where you can confirm the version of server components installed (SQL backup Version and Service Application Version).
Another indication that the server components are still using an earlier version and not up to date with the GUI, there will a grey cog icon to the right of the server name in the Registered Servers Panel.
2. In the backup syntax you supplied, there is a space at the beginning and at end - WITH PASSWORD = [ FILE:C:EncryptionDCR.txt ], if you modify it to remove the spaces so it becomes WITH PASSWORD = [FILE:C:EncryptionDCR.txt] - I have experienced strange problems in the past with spaces in supplied strings.
3. As the use of the '[ ] ' characters is most likely working on other machines, consider using two single quote to replace each [ ] characaters, for example WITH PASSWORD = [FILE:C:EncryptionDCR.txt] becomes WITH PASSWORD = ''FILE:C:EncryptionDCR.txt''.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
you were right, the server components were still showing an old version while the GUI was showing the more recent version.
I updated the server components and the issue is now resolved, all our backups are using the contents of the file containing the encryption password.
Thank you for your help.