master key and certificate error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

master key and certificate error

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-11-10 : 11:15:26

Guys,

I have restored from one instance to another to setup Transaction Replication based on backup of the database

when I set up the subscription I am getting the following error

Messages
SQL Server could not create a subscription for Subscriber 'NV-DEVSQL4\elroy'. (New Subscription Wizard)

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Please create a master key in the database or open the master key in the session before performing this operation.
Changed database context to ' '. (Microsoft SQL Server, Error: 15581)

Is there any way around it.

Any suggestions and inputs would help

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-10 : 11:37:23

Was database encrypted in Primary server? Did you search for error in google?

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-11-10 : 11:53:17

On Microsoft tech forums following code snippet should be used

open master key decryption by password = '...';

alter master key add encryption by service master key

but when I run the first statement I get

Msg 15313, Level 16, State 1, Line 1
The key is not encrypted using the specified decryptor.

which I figure is the sa password but how do I pass the specific encrypted value when do I get the value from.

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-10 : 11:54:24

Found by googling your error:
http://www.eggheadcafe.com/software/aspnet/30238935/master-key-issue.aspx

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-11-10 : 12:15:15

Thanks

BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp_backups\keys\service_master_key' ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4';

RESTORE SERVICE MASTER KEY
FROM FILE = 'c:\temp_backups\keys\service_master_key'
DECRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4';

I was trying to encrypt the password - is this password related to sql login (I dont think so but trying to confirm)

How do I encrypt a password is there any standard sql server utililty that encrypts and that is accepted by backup and restors service command.

Thanks

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-11-10 : 14:44:10

It worked below are the steps that I have run

-- from publisher database
BACKUP SERVICE MASTER KEY TO FILE = 'c:\key\service_master_key' ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4';

-- from subscriber database

RESTORE SERVICE MASTER KEY
FROM FILE = 'c:\key\service_master_key'
DECRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4'

RESTORE MASTER KEY
FROM FILE = 'c:\key\service_master_key'
DECRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4'
ENCRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD'
FORCE
GO

open master key decryption by PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD'

alter master key add encryption by service master key

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources