Question

G vinod on Thu, 20 Aug 2015 12:44:29


I am using service broker/Sqldependency to monitor table changes. Actual console application runs as windows service on a server different than the sql server. We stop and start the service everyday. Its working fine other than the two errors happening everyday

First error corresponds to the time that we stop and start the windows service which in tern stops and starts the sqldependency

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

Second error corresponds to the timeframe when the first change happens in the table after  stop and start of sqldependency

The query notification dialog on conversation handle '{4443C789-1047-E511-80DA-005056A32BA6}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service &apos;SqlQueryNotificationService-555c9c9d-d01a-423c-8710-7e9e9a63fbca&apos; because it does not exist.</Description></Error>'.5

Following is the code snippet I am using

OnService Start:

SqlDependency.Stop(connectionString);
SqlDependency.Start(connectionString);


OnService Stop

SqlDependency.Stop(connectionString);

In notification event

 SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);

I am not sure what else I am missing that's causing the above listed errors. Could someone please help me to understand Whats causing these errors and whats the resolution. The service seems to be running fine other than these two errors everyday.

Appreciate your time and reply





Sponsored



Replies

Dv111 on Thu, 20 Aug 2015 16:03:06


ALTER DATABASE MyDB SET TRUSTWORTHY ON

refer

http://dba.stackexchange.com/questions/70066/why-does-the-service-broker-require-a-master-key-after-restoring-to-a-different

G vinod on Thu, 20 Aug 2015 17:40:32


I already saw that on other posts. But turning ON Trustworthy is not an option.

Erland Sommarskog on Thu, 20 Aug 2015 21:07:16


So is there a master key in that database?

It seems like there is something you are not telling me, because you don't need any keys only for query notification, as far as I know. Is the service doing anything more than just setting up SqlDependency?

I am glad to see that you refuse to set the database trustworthy. VIMD should be ashamed for making that suggestion without furhter qualification.

G vinod on Fri, 21 Aug 2015 12:37:43


There is a key in master db for a column encryption for a different table which has nothing to do with sql dependency service. This is our prod db and as per the DBA turning ON trustworthy is not going to happen. I am new to sql dependency/service broker. As I said in my initial post I am just trying to find out whether I am missing anything in my code or doing something wrong. if there's some specific permissions that I can give to the role/user that my service is running under, I would be really happy to do that.

Let me know is there any option to clear any open conversations that got struck during my service stop. Will that solve this? Thanks for your help

Thanks.

 


G vinod on Mon, 24 Aug 2015 15:20:41


Based on the following post , at least if I can find a way to delete any pending subscriptions that would solve the second issue I guess

http://rusanu.com/2007/11/10/when-it-rains-it-pours/

Any help on how to cleanup any pending subscription at shutdown?

Thanks