For one, you can try and use version of the Adventure Works sample that only has features designed for Standard edition of the server.
You will find Standard Edition of Adventure Works sample installed under C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project. By default your installation location might be different.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Hi,
Okay, I am stuck here with this error and other related errors. before anything I will describe the scenario. Please do tell me What is wrong, How it happened and how to correct it.
Initially I had 3 relational tables in SQL server.
1. Dim_A (col1a, col2a, col3a)
2. Dim_B (col1b, col2b)
3. Fact_data (col1a,Col1b)
There are no Primary keys, foreign keys and relationships defined in the SQL relational database between these tables.
I created a OLAP database by:
1. creating the datasource
2. creating the datasource view and setting the logical primary keys and relationships as follows:
a. Fact_data.col1a references -> Dim_A.col1a
b. Fact_data.col1b references -> Dim_B.col1b
3. Next, I created a Cube, without automatically creating attributes or hierarchies (no auto build)
4. A Measure Group was automatically created, with a measure called Fact_data count (which i think is the count of the number of rows and was equal to 145). I checked the relational table "Fact_data" to find that the fact table had indeed 145 rows.
5. Next I built the project with not errors, then deployed and processed it.
6. I had no errors and I was able to browse the cube in the SQL BI studio.
After all this, I had created a asp.net page to issue the "Process" command to the complete OLAP database. (I had used the Process Script XMLA from the BI studio). This was also running perfectly.
Now the problem, suddeny next day when i ran the Process from my Asp.Net page, it gave me errors saying that there are Errors in the OLAP Storage engine. The attribute Key cannot be found, with the Table name, Column name and the value. It also gave other errors saying: The record was skipped because the attribute key was not found. it gave the info of the attribure, Dimension, database, cube, measure group and Partition.
I checked the SQL relational tables to find a change in the "Fact_data" table structure. Here is the changed structure of the Fact_data table:
Fact_data (col1a,Col1b,colNewC)
A new column "colNewC" was added to the table. Actually this was a new table with the same name as the old one with a new column. the old table was renamed and probably changed by some other person.
so i thought that the change in structure was the problem.
I created a new Analysis services project in the SQL BI studio, repeated the steps stated above to create a OLAP database. Then I built the project with no erros.
Next while trying to deploy and process the project. It is giving me the same errors. Attribute Key not found. I am neither able to deploy only or deploy and process.
Next I changed the error handling settings and set it to convert the not found keys to unknown. now it gave me esactly 145 errors, with the No attribute keys found. but with deployment and processing complete.
Now trying to browse the cube gives no records at all.
I analyzed the tables and its records, I felt that this was because the records present in the Fact_data table had no matching records in the Dim_A table. or specifically there were no matching "col1a" columns between them. thus all the 145 rows of the Fact_data table were shown as errors.
My question is:
1. So is this only a data in-consistency issue?
2. When somebody changed the Fact_data table structure, they also changed the data which had no matching columns with the dim_A table? is this correct? and hence the errors.
3. The only possible way to correct this is to populate the correct records, is this right?
Sorry for the lengthy post, but i thought it would describe the scenario better. Please do give a detailed answer for this and also how to correct it.
Thanks and regards
Try defining primary key-foreign key relationships in your SQL database.
This should help you to deal with referential integrity errors you are seeing.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
When running the XMLA Script run the default refresh of the dimesions first then process the cube for a full refresh..
The Error means that the Fact tables foriegn key doest match with the Primary key of the Dim Table AKA the lookup table (DIM) doest have a value to match with the DIM Table.Hope this Explains.
If there are lotsa invalidated data in the fact table change the Error procession configuration to notify and continue.
|||I got exactly the same error message as your post here when I tried to process and deploy the cube in BIDS, did the answer posted here help you solve the problem above?
I want to knwo if that helped solve the problem then I may try this solution.
With best regards,
Yours sincerely,
Hi,
Okay, I am stuck here with this error and other related errors. before anything I will describe the scenario. Please do tell me What is wrong, How it happened and how to correct it.
Initially I had 3 relational tables in SQL server.
1. Dim_A (col1a, col2a, col3a)
2. Dim_B (col1b, col2b)
3. Fact_data (col1a,Col1b)
There are no Primary keys, foreign keys and relationships defined in the SQL relational database between these tables.
I created a OLAP database by:
1. creating the datasource
2. creating the datasource view and setting the logical primary keys and relationships as follows:
a. Fact_data.col1a references -> Dim_A.col1a
b. Fact_data.col1b references -> Dim_B.col1b
3. Next, I created a Cube, without automatically creating attributes or hierarchies (no auto build)
4. A Measure Group was automatically created, with a measure called Fact_data count (which i think is the count of the number of rows and was equal to 145). I checked the relational table "Fact_data" to find that the fact table had indeed 145 rows.
5. Next I built the project with not errors, then deployed and processed it.
6. I had no errors and I was able to browse the cube in the SQL BI studio.
After all this, I had created a asp.net page to issue the "Process" command to the complete OLAP database. (I had used the Process Script XMLA from the BI studio). This was also running perfectly.
Now the problem, suddeny next day when i ran the Process from my Asp.Net page, it gave me errors saying that there are Errors in the OLAP Storage engine. The attribute Key cannot be found, with the Table name, Column name and the value. It also gave other errors saying: The record was skipped because the attribute key was not found. it gave the info of the attribure, Dimension, database, cube, measure group and Partition.
I checked the SQL relational tables to find a change in the "Fact_data" table structure. Here is the changed structure of the Fact_data table:
Fact_data (col1a,Col1b,colNewC)
A new column "colNewC" was added to the table. Actually this was a new table with the same name as the old one with a new column. the old table was renamed and probably changed by some other person.
so i thought that the change in structure was the problem.
I created a new Analysis services project in the SQL BI studio, repeated the steps stated above to create a OLAP database. Then I built the project with no erros.
Next while trying to deploy and process the project. It is giving me the same errors. Attribute Key not found. I am neither able to deploy only or deploy and process.
Next I changed the error handling settings and set it to convert the not found keys to unknown. now it gave me esactly 145 errors, with the No attribute keys found. but with deployment and processing complete.
Now trying to browse the cube gives no records at all.
I analyzed the tables and its records, I felt that this was because the records present in the Fact_data table had no matching records in the Dim_A table. or specifically there were no matching "col1a" columns between them. thus all the 145 rows of the Fact_data table were shown as errors.
My question is:
1. So is this only a data in-consistency issue?
2. When somebody changed the Fact_data table structure, they also changed the data which had no matching columns with the dim_A table? is this correct? and hence the errors.
3. The only possible way to correct this is to populate the correct records, is this right?
Sorry for the lengthy post, but i thought it would describe the scenario better. Please do give a detailed answer for this and also how to correct it.
Thanks and regards
Try defining primary key-foreign key relationships in your SQL database.
This should help you to deal with referential integrity errors you are seeing.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
When running the XMLA Script run the default refresh of the dimesions first then process the cube for a full refresh..
The Error means that the Fact tables foriegn key doest match with the Primary key of the Dim Table AKA the lookup table (DIM) doest have a value to match with the DIM Table.Hope this Explains.
If there are lotsa invalidated data in the fact table change the Error procession configuration to notify and continue.
|||I got exactly the same error message as your post here when I tried to process and deploy the cube in BIDS, did the answer posted here help you solve the problem above?
I want to knwo if that helped solve the problem then I may try this solution.
With best regards,
Yours sincerely,
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?
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?
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?
