![clock](themes/indigo/img/timeicon.gif)
April 14, 2015 07:18 by
Peter
Encryption for Backups could be a new feature introduced in SQL Server 2014 and therefore the advantages of this feature are:
- Encrypting the database backups helps secure the data.
- Encryption also can be used for databases that are encrypted encryption TDE.
- Encryption is supported for backups done by SQL Server Managed Backup to Windows Azure, that provides extra security for off-site backups.
- This feature supports multiple encoding algorithms as well as AES 128, AES 192, AES 256, and Triple DES
You'll be able to integrate encryption keys with Extended Key Management (EKM) providers. The following are pre-requisites for encrypting a backup:
Let’s Create a Database Master Key for the master database.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourpassword@word123';
GO
And then Create a certificate or asymmetric Key to use for backup encryption and write the following code:
Use Master
GO
CREATE CERTIFICATE CertforBackupEncryption
WITH SUBJECT = 'Certificate for Backup Encryption ';
GO
Then Backup the database with encryption:
BACKUP DATABASE [PeterSQL]
TO DISK = N'C:\Backup\PeterSQL.bak'
WITH
INIT,
COMPRESSION,
ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = CertforBackupEncryption
),
STATS = 10
GO
Restoring the encrypted backup:
SQL Server restore doesn't need any encryption parameters to be specified during restores. It will need that the certificate or the asymmetric key used to encrypt the backup file be out there on the instance that you simply are restoring to. The user account performing the restore should have read DEFINITION permissions on the certificate or key. If you're restoring the encrypted backup to a different instance, you must confirm that the certificate is offered on it instance.
SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc. You will not be charged a cent for trying our service for the next 3 days. Once your trial period is complete, you decide whether you'd like to continue.
![](http://fulltrusteuropeanhosting.hostforlife.eu/image.axd?picture=2015%2f4%2fhostforlife+banner.png)
![clock](themes/indigo/img/timeicon.gif)
October 23, 2014 08:03 by
Peter
I was recently asked where to go in Configuration Manager to vary the settings for xp_cmdshell on MSSQL 2014 Hosting. it was then that i spotted that the existence of sp_configure is maybe not universal data. I used to be actually afraid. for a few reason I had thought that DBA’s were born with this data. As this seems to not be the case, I felt it might be an honest plan to allow sp_configure a quick run through. Note: This keep procedure modifies system settings. Make certain you understand the setting that you're changing and the need for the change before you creating it.
The system keep procedure sp_configure is used to view or modify variefty of various system settings. These settings will include the default MAXDOP for the server, whether or not SMOs is enabled, whether xp_cmdshell is enabled and an extended list of others. to urge a whole list you'll either question sys.configurations or run sp_configure with no parameters.
![](/image.axd?picture=2014%2f10%2fbanner+HFL.png)
Why is that the list from sp_configure shorter than the list on sys.configurations? If I will modify xp_cmdshell victimisation sp_configure. Some configurations are thought-about Advanced. If you would like to see them you may need to change the setting show advanced choices (using of course sp_configure).
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
The Reason why you're adding during this RECONFIGURE command, because sp_configure solely changes the configured value. RECONFIGURE updates the running value with the configured value (there are some exceptions wherever a reboot or instance restart is required). thus in order to actually modification the currently running configuration value you have got to use each commands. as luck would have it RECONFIGURE checks that the configured value is valid before setting it because the running value.
If you're fully bound you recognize what you're doing and want to update one of the configuration values with one thing outside of its allowed vary you'll be able to embrace the WITH OVERRIDE choice of the RECONFIGURE command. So here’s the factor, if you're aiming to be administrating SQL Server you would like to have at least a passing familiarity of the sp_configure and RECONFIGURE commands and a reasonable idea of what settings they modify.