Solved

sql2008 sys.symmetrics_keys

Posted on 2013-01-28
2
300 Views
Last Modified: 2013-02-22
Hi,
I wonder what is use of this key when we installed sql 2008 ? it seems there in Master db ##MS_ServiceMasterKey##  and for ##MS_DatabaseMasterKey##   is the user db is encrypted ? but I don't think it use for TDE.

name                          principal_id symmetric_key_id key_length  key_algorithm algorithm_desc                                               create_date             modify_date             key_guid                             key_thumbprint                                                                                                                                                                                                                                                   provider_type                                                cryptographic_provider_guid          cryptographic_provider_algid
----------------------------- ------------ ---------------- ----------- ------------- ------------------------------------------------------------ ----------------------- ----------------------- ------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ------------------------------------ ------------------------------
##MS_ServiceMasterKey##       1            102              128         D3            TRIPLE_DES                                                   2012-01-07 06:24:00.507 2012-02-09 06:33:59.060 CE72EE2E-F077-4B64-B803-D11FD12E4BC6 NULL                                                                                                                                                                                                                                                             NULL                                                         NULL                                 NULL

(1 row(s) affected)









name                          principal_id symmetric_key_id key_length  key_algorithm algorithm_desc                                               create_date             modify_date             key_guid                             key_thumbprint                                                                                                                                                                                                                                                   provider_type                                                cryptographic_provider_guid          cryptographic_provider_algid
----------------------------- ------------ ---------------- ----------- ------------- ------------------------------------------------------------ ----------------------- ----------------------- ------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ------------------------------------ ------------------------------
##MS_DatabaseMasterKey##      1            101              128         D3            TRIPLE_DES                                                   2012-09-18 14:51:21.077 2012-09-18 14:51:21.080 8EAD9200-E2C1-4668-815F-C68F5AAF8ABA NULL                                                                                                                                                                                                                                                             NULL                                                         NULL                                 NULL

(1 row(s) affected)
0
Comment
Question by:motioneye
2 Comments
 
LVL 10

Accepted Solution

by:
deviprasadg earned 500 total points
ID: 38830041
MS_DatabaseMasterKey is used for database encryption and is different for each database.

MS_ServiceMasterKey is a symmetric key  used for saving linked server login passwords , credential secrets etc..

Refer:
http://www.kodyaz.com/articles/sql-server-2005-database-encryption-step-by-step.aspx

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/f4d292c0-72d5-4075-adf3-03427542567e/
0
 

Author Comment

by:motioneye
ID: 38830069
so,
when we installed sql 2008 and have the lnked  server, credential saved, we should have this backup, is it correct ?
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now