Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to set up SqlDependency on my dev system. The db on my system is a few months old production db which I have attached.

When running SqlDependency from ASP.NET application, I see the following error in Event Viewer.

Service Broker needs to access the master key in the database 'MyDb'. Error code:26. The master key has to exist and the service master key encryption is required.

I searched and found,

When a database is first attached to a new instance of SQL Server, a copy of the Database Master Key (encrypted by the Service Master Key) is not yet stored in the server. You must use the OPEN MASTER KEY statement to decrypt the Database Master Key. Once the Database Master Key has been decrypted, you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY statement to provision the server with a copy of the Database Master Key encrypted with the Service Master Key. This option is discussed in ALTER MASTER KEY (Transact-SQL).

http://msdn.microsoft.com/en-us/library/ms174433(v=SQL.90).aspx

However, it is not very clear what password should I use for the OPEN MASTER KEY statement and from where would I get it? I used the Key_GUID value taken from sys.symmetric_keys from both the machines but got an error "Msg 15151, Level 16, State 1, Line 7 Cannot find the symmetric key 'master key', because it does not exist or you do not have permission."

How do I get this to work?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

When the Database Master Key was created a password was supplied to CREATE MASTER KEY it's this password which is required. If this password is not available to the developers then recreating the encrypted objects losing the currently protected data is the only option.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.