0

I'm running an asp.net MVC application that uses signalr,and I'm running signalR off of the SQL server backplane ( https://learn.microsoft.com/en-us/aspnet/signalr/overview/performance/scaleout-with-sql-server ). I was having some issues with this setup earlier, and one of my developers deleted the 'aspstate' database and re-ran aspnet_regsql to 'clear things out' in his mind. Unfortunately that seems to have mucked things up even further, and I don't know how to fix it. Now i'm getting the following error:

The EXECUTE permission was denied on the object 'sp_executesql', database 'mssqlsystemresource', schema 'sys'. Violation of PRIMARY KEY constraint 'PK__Schema__04D490DBAD491E7F'. Cannot insert duplicate key in object 'SignalR.Schema'. The duplicate key value is (1). Installing SignalR SQL objects Database schema [SignalR] already exists Table [SignalR].[Schema] already exists Installing schema version 1 Mesage table [SignalR].[Messages_0] already exists Message ID table [SignalR].[Messages_0_Id] alread exists The statement has been terminated. Schema version 1 installed SignalR SQL objects installed

I've tried deleting this database and pointing it to a new one, but no luck at all, it feels like something is cached and nothing i do will clear it out. Are there any ideas how to approach this? Been beating my head at this for a while, and no amount of reboots/resets/rebuilds has fixed it.

Phil
  • 1,852
  • 2
  • 28
  • 55
  • Does it work on either of the notes in the availability group? I'm not going to be much help in the SignalR side but I'm a database guy so might be able to help there. – Ben Thul Jan 05 '19 at 20:23
  • no it doesn't. I'm not entirely sure the issue is AG related, but thought it should be mentioned. I think the main issue is the fact that i tried to clear it out before in order to fix another unrelated issue, which might have caused this. I deleted all the contents inside the signalr databases (there are 3 of them), but maybe the schema id it's looking for above is cached somewhere, or in a temp database? – Phil Jan 05 '19 at 20:36
  • When does the db error occur, at app startup, during an insert? – Ross Bush Feb 14 '19 at 02:32
  • During the insert. – Phil Feb 14 '19 at 03:04

1 Answers1

1

so apparently the answer ended up being that the DB admin hadn't granted the user that accessed the signalR databases execute permission. Says so right in the error, but i just assumed it was there. Oh well!

Phil
  • 1,852
  • 2
  • 28
  • 55