My database is logging frequent errors and I am unable to determine the cause. These errors appear to be related to the Service Broker. Below is the database log file after a database restart and attempted access to the database through a web application. The first error (bottom of the logfile) is error 28054. I have searched on this error code and have found nothing helpful. Any assistance or direction would be greatly appreciated.
Database Log:
04/13/2006 12:26:05,spid22s,Unknown,An error occurred in the service broker message dispatcher<c/> Error: 15517 State: 1.
04/13/2006 12:26:05,spid22s,Unknown,Error: 9644<c/> Severity: 16<c/> State: 14.
04/13/2006 12:26:04,spid57s,Unknown,The activated proc [dbo].[SqlQueryNotificationStoredProcedure-aa148e0f-2980-4a23-b9cf-b44dbfddf783] running on queue CDR.dbo.SqlQueryNotificationService-aa148e0f-2980-4a23-b9cf-b44dbfddf783 output the following: 'Cannot execute as the database principal because the principal "dbo" does not exist<c/> this type of principal cannot be impersonated<c/> or you do not have permission.'
04/13/2006 12:26:01,spid22s,Unknown,An error occurred in the service broker message dispatcher<c/> Error: 15517 State: 1.
04/13/2006 12:26:01,spid22s,Unknown,Error: 9644<c/> Severity: 16<c/> State: 14.
04/13/2006 12:26:01,spid56s,Unknown,The activated proc [dbo].[SqlQueryNotificationStoredProcedure-aa148e0f-2980-4a23-b9cf-b44dbfddf783] running on queue CDR.dbo.SqlQueryNotificationService-aa148e0f-2980-4a23-b9cf-b44dbfddf783 output the following: 'Cannot execute as the database principal because the principal "dbo" does not exist<c/> this type of principal cannot be impersonated<c/> or you do not have permission.'
04/13/2006 12:26:01,spid56s,Unknown,The activated proc [dbo].[SqlQueryNotificationStoredProcedure-aa148e0f-2980-4a23-b9cf-b44dbfddf783] running on queue CDR.dbo.SqlQueryNotificationService-aa148e0f-2980-4a23-b9cf-b44dbfddf783 output the following: 'Cannot execute as the database principal because the principal "dbo" does not exist<c/> this type of principal cannot be impersonated<c/> or you do not have permission.'
04/13/2006 12:26:01,spid56s,Unknown,The activated proc [dbo].[SqlQueryNotificationStoredProcedure-aa148e0f-2980-4a23-b9cf-b44dbfddf783] running on queue CDR.dbo.SqlQueryNotificationService-aa148e0f-2980-4a23-b9cf-b44dbfddf783 output the following: 'Cannot execute as the database principal because the principal "dbo" does not exist<c/> this type of principal cannot be impersonated<c/> or you do not have permission.'
04/13/2006 12:26:01,spid52,Unknown,Service Broker needs to access the master key in the database 'CDR'. Error code:25. The master key has to exist and the service master key encryption is required.
04/13/2006 12:26:01,spid52,Unknown,Error: 28054<c/> Severity: 11<c/> State: 1.
1> Who is the database owner? Is this a windows login or a SQL login? If this is a windows login, is this a domain account? Are you connected to the domain controller?
2> Did you move the database from one SQL Server instance to another?
OR
3> Did you change the service account that runs the SQL Server database engine?
Thanks,
Rushi
|||
This error is caused by the EXECUTE AS infrastructure being unable to impersonate the CDR database owner. Typically this is a result of moving the database between two machines. Change the owner of this database to a valid login. Use one of this to change the CDR owner:
ALTER AUTHORIZATION ON DATABASE::[CDR] TO [SA];
HTH,
~ Remus
|||
I'm getting similar errors, only with:
Error 1204, Severity: 19, Sate: 4
Error: 3602, State: 145 and
Error: 9644, Severity:16, State: 16.
What do these mean, and how to resolve them?
|||
1204 is The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
3602 is a transact abort notification
9644 is SSB complaining about an error that happened while trying to dispatch a message.
So it seams that your machine is out of resources, specifically LOCK resources. This causes a transaction abort that causes SSB to complain. The root of the problem is how come you've exhausted all LOCK resources. See http://msdn2.microsoft.com/EN-US/library/aa337440.aspx
|||
I am having similar problems.
When I look in the server event log I'm frequently seeing:
Service Broker needs to access the master key in the database 'MyDB'.
Error code:25. The master key has to exist and the service master key encryption is required.
I've looked up the Database Properties
"Database Properties, General" indicates that the owner is 'sa'
"Database Properties, Permissions" indicates only one present 'MyCompanyName'
The only reason that the Service Broker is running is to enable sqlCacheDependency.
The database is accessed via ASP.NET2 and relevant web.config settings (in application one) are:
<connectionStrings>
<add name="MyDBConnString_live" connectionString="datasource=.;initial catalog=MyDB;Pooling=True;Min Pool Size=5;Max Pool Size=80;Connection Lifetime=300;packetsize=4096;userid=MyCompanyName;persist security info=False;password=bl4h12e;" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<authentication mode="Forms" blah blah />
<caching>
<sqlCacheDependency enabled="true" pollTime="10000">
<databases>
<add name="Client_mw40" connectionStringName="MyDBConnString_live" pollTime="2000" />
</databases>
</sqlCacheDependency>
</caching>
When I activated the Service Broker and created the ASP.NET objects I did so by logging onto the computer via MSTSC using the 'Administrator' account for that server.
In order to get the sqlCacheDependency working I ran the following from SQL Server Management Studio Express:
ALTER DATABASE MyDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
Then I checked that it had worked:
SELECT is_broker_enabled FROM sys.databases WHERE name = 'MyDB';
Next I ran a series of commands from the DOS command line:
c:
CD C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
aspnet_regsql.exe -E -S myServerName -d MyDB -ed
aspnet_regsql.exe -E -S myServerName -d MyDB -t Blah1 -et
aspnet_regsql.exe -E -S myServerName -d MyDB -t Blah2 -et
aspnet_regsql.exe -E -S myServerName -d MyDB -lt
The net result was that the sqlCacheDependency seemed to be properly setup and everything seemed to work fine ...
or not because now I discover that everything is NOT working properly.
How can I debug this and fix the problem? The error message above (top of post) is absolutely useless and doesn't give me a clue.
PS: I checked the queues below. The first has two items in (do these correspond to my two aps.net applications which are both using sqlCacheDependency?) The second queue was empty.
SELECT conversation_handle, is_initiator, s.name as 'local service', far_service, sc.name 'contract', state_desc
FROM sys.conversation_endpoints ce
LEFT JOIN sys.services s ON ce.service_id = s.service_id
LEFT JOIN sys.service_contracts sc ON ce.service_contract_id = sc.service_contract_id;
SELECT * FROM sys.transmission_queue;
I've just looked at the database script and the start of it reads:
CREATE ROLE [aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess]
GO
CREATE USER [MyCompanyName] FOR LOGIN [MyCompanyName] WITH DEFAULT_SCHEMA=[dbo]
GO
CREATE SCHEMA [aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess] AUTHORIZATION [aspnet_ChangeNotification_ReceiveNotificationsOnlyAccess]
GO
The user load on this server is typically very low. Any clues as to why I'm having these problems?
