backup Keys

Hi, In DOL, sql server insists on backing up Certificates but not keys
(symmetric or asymmetric).
Does that mean that DB backup is sufficient to restore all keys without the
need to do special backups as for certificates?
thanksSalamElias (eliassal@.online.nospam) writes:
> Hi, In DOL, sql server insists on backing up Certificates but not keys
> (symmetric or asymmetric).
> Does that mean that DB backup is sufficient to restore all keys without
> the need to do special backups as for certificates?
As I understand it, the main reason you would export a certificate with
BACKUP CERTIFICATE, is that you need the same ceriticate in several
places. For instance, if you want to sign a procedure in your database
with a certificate to give users rights to run BULK INSERT through that
procedure, you need to have the certificate both in your local database
and in master.
As long as you are using the certificate within a single database, there
is no need to use BACKCUP certificate. The certificate is saved in the
backup file for the database, like everything else in the database.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland, I understand all related activities regarding Certificates,
but my inquiry was regarding Summetric and asymmetric keys.
Do you have any idea about scenarios regarding backup/restore for Keys
best regards
"Erland Sommarskog" wrote:

> SalamElias (eliassal@.online.ric keysnospam) writes:
>
> As I understand it, the main reason you would export a certificate with
> BACKUP CERTIFICATE, is that you need the same ceriticate in several
> places. For instance, if you want to sign a procedure in your database
> with a certificate to give users rights to run BULK INSERT through that
> procedure, you need to have the certificate both in your local database
> and in master.
> As long as you are using the certificate within a single database, there
> is no need to use BACKCUP certificate. The certificate is saved in the
> backup file for the database, like everything else in the database.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||SalamElias (eliassal@.online.nospam) writes:
> Thanks Erland, I understand all related activities regarding Certificates,
> but my inquiry was regarding Summetric and asymmetric keys.
> Do you have any idea about scenarios regarding backup/restore for Keys
It does not seem that you can export these, but they are local to the
database. Note that when you back up the database, the backup also includs
any keys in the database.
I think the syntax for BACKUP CERTIFICATE is a nisnomer; EXPORT CERTIFICATE
would have been more appropriate, but I guess they did not want to make
EXPORT a reserved word.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello Salam,
Regarding on the symmetric and asymmetric keys in SQL Server 2005 database,
they are all stored in the database itself. Therefore, when you backup the
database, you've already backup all those symmetric and asymmetric keys in
that certain database. You can lookup those system catalog views (for
encryption) below
#Security Catalog Views (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms178542.aspx
sys.asymmetric_keys
sys.crypt_properties
sys.certificates
sys.key_encryptions
sys.credentials
sys.symmetric_keys
Also, for SQL Server 2005 database which contains symmetric or asymmetric
keys, the important point is that you need to backup the master key of the
database. Since all those secruity/encryption objects(symmetric or
asymmetric keys) are encrypted through a hierarchical structure, and
database masterkey is the topest key in a database. Whenever you've moved a
database to other server instance, you'll surely need to import/restore the
master key so that any down level secured objects can be used. You can also
get some sense on this through the following articles:
#Improving Data Security by Using SQL Server 2005
http://www.microsoft.com/technet/it.../sqldatsec.mspx
#Encrypting Your Valuable Data With SQL Server 2005: Part 1
http://www.sql-server-performance.c...tion_2005_1.asp
Hope this helps some.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||So many thanks, wonderfull articles. In the first article it is mentioned
"Back up the SQL Server 2005 encryption keys by using the corresponding SQL
Server 2005 Transact-SQL commands."
This was exactly my question : what are the T-SQL commandes to backup keys?
Al I misunderstanding something?
Thanks again
"Steven Cheng[MSFT]" wrote:

> Hello Salam,
> Regarding on the symmetric and asymmetric keys in SQL Server 2005 database
,
> they are all stored in the database itself. Therefore, when you backup the
> database, you've already backup all those symmetric and asymmetric keys in
> that certain database. You can lookup those system catalog views (for
> encryption) below
> #Security Catalog Views (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms178542.aspx
> sys.asymmetric_keys
> sys.crypt_properties
> sys.certificates
> sys.key_encryptions
> sys.credentials
> sys.symmetric_keys
>
> Also, for SQL Server 2005 database which contains symmetric or asymmetric
> keys, the important point is that you need to backup the master key of the
> database. Since all those secruity/encryption objects(symmetric or
> asymmetric keys) are encrypted through a hierarchical structure, and
> database masterkey is the topest key in a database. Whenever you've moved
a
> database to other server instance, you'll surely need to import/restore th
e
> master key so that any down level secured objects can be used. You can als
o
> get some sense on this through the following articles:
> #Improving Data Security by Using SQL Server 2005
> http://www.microsoft.com/technet/it.../sqldatsec.mspx
> #Encrypting Your Valuable Data With SQL Server 2005: Part 1
> http://www.sql-server-performance.c...tion_2005_1.asp
> Hope this helps some.
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> [url]http://msdn.microsoft.com/subscriptions/support/default.aspx." target="_blank">http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>
>
>
>|||Thanks for your quick response Salam,
You haven't missed anything. For your question:
This was exactly my question : what are the T-SQL commandes to backup keys?
========================================
==================
For database level, the most important key is the Database Master
Key(DMK),therefore, you'd better also backup it when you backup database.
The T-SQL statement used to backup and restore database master key are:
BACKUP MASTER KEY (Transact-SQL)
RESTORE MASTER KEY (Transact-SQL)
You can find many other T-SQL statements on master key manipulation.
Here is another MSDN refernce which describe the metadata of database when
we move it between database instances and it include the topics about
encryption keys(master key) of database.
#Managing Metadata When Making a Database Available on Another Server
Instance
http://msdn2.microsoft.com/en-us/li...#encrypted_data
Hope this also helps.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||SalamElias (eliassal@.online.nospam) writes:
> So many thanks, wonderfull articles. In the first article it is
> mentioned "Back up the SQL Server 2005 encryption keys by using the
> corresponding SQL Server 2005 Transact-SQL commands."
> This was exactly my question : what are the T-SQL commandes to backup
> keys?
You can backup the database master key and the server master key separtely.
However, you cannot make separte backups of symmetric keys and asymmetric
keys. (With the exception of certificates, but that is really a case of
export, not backup.)
As for just backing upi keys for disaster recovery, the command is
BACKUP DATABASE db TO ...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Salam,
Have you any further questions on this? Please feel free to post here if
there is anything we can help.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.