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?
Best Answer
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.