none
An entity of type service cannot be owned by a role, a group, or by principals mapped to certificates or asymmetric keys. RRS feed

  • Question

  • I'm using SQL Server 2008 R2 and a WCF service in Visual Studio 2010. I am trying to set up a SQL Dependency to detect changes in the database, and I have set up a queue and service. I keep getting the above error when running the service, though. 

    I already have the service set to AUTHORIZATION [dbo], and I have tried logging in as a regular user (I usually log in as dbo) but so far nothing has worked. 

    I have looked around a lot and have not been able to find anything helpful. Any help would be much appreciated!

    Monday, August 8, 2016 7:06 PM

Answers

  • I have not worked much with SqlDependency, but it may be that it sets up some things behind your back. I would recommend that you use Profiler to spy on your application to see what commands it produces. Include the events Error:Exception and Error:UserMessage, and also all Starting events, since there may not be a Completed event when there is an error.

    If you can see to which principal that is used for the ownership this may help to make the penny drop.

    • Marked as answer by Lane95 Tuesday, August 9, 2016 7:33 AM
    Monday, August 8, 2016 9:17 PM

All replies

  • Hi,

    have you seen this thread?

    I hope it helps.

    János


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Monday, August 8, 2016 7:09 PM
  • Thank you for responding so quickly.

    I did see this, but it didn't help. I saw that I was logged in as dbo, but when I logged in as a regular user I got the same result. 

    I also tried granting authorization to the same regular user that I logged in as to the service (as opposed to granting authorization to dbo) and that didn't help either.

    Thank you though!

    Monday, August 8, 2016 7:20 PM
  • Can you post your T-SQL code used for setting service and queue up please?

    Thanks,

    János


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Monday, August 8, 2016 7:26 PM
  • CREATE QUEUE AndroidQueue

    CREATE SERVICE [AndroidService]  AUTHORIZATION [dbo]  ON QUEUE [dbo].[AndroidQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])

    These were both made on the correct database (called CG1). 

    Thanks again for your help!

    Monday, August 8, 2016 7:33 PM
  • Hi,

    Did you enable service broker in your database? ALTER DATABASE CG1 SET ENABLE_BROKER


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Monday, August 8, 2016 7:44 PM
  • Yes it's enabled.
    Monday, August 8, 2016 7:51 PM
  • I have not worked much with SqlDependency, but it may be that it sets up some things behind your back. I would recommend that you use Profiler to spy on your application to see what commands it produces. Include the events Error:Exception and Error:UserMessage, and also all Starting events, since there may not be a Completed event when there is an error.

    If you can see to which principal that is used for the ownership this may help to make the penny drop.

    • Marked as answer by Lane95 Tuesday, August 9, 2016 7:33 AM
    Monday, August 8, 2016 9:17 PM
  • I'll try your suggestion. Thank you!
    Tuesday, August 9, 2016 7:06 AM
  • Your suggestion worked. When I ran the profiler, I saw that the app was connecting via the IIS APPPOOL\ASP.NET 4.0 Integrated role, and it seems that that's what's causing the problem.

    Thank you for your help!

    • Proposed as answer by DynamicL Tuesday, February 26, 2019 9:01 PM
    Tuesday, August 9, 2016 7:34 AM
  • Your suggestion worked. When I ran the profiler, I saw that the app was connecting via the IIS APPPOOL\ASP.NET 4.0 Integrated role, and it seems that that's what's causing the problem.

    Thank you for your help!

    This was the issue in my instance too as the identity was set to LocalSystem in the App pool. Changed this to ApplicationPoolIdentity and problem solved.

    I've not come across this answer elsewhere so thanks!


    • Edited by DynamicL Tuesday, February 26, 2019 9:03 PM typo
    Tuesday, February 26, 2019 9:02 PM
  • I'm happy that this is still useful. Good luck with your project.
    Tuesday, February 26, 2019 9:07 PM