Caching: Unable to connect to the SQL database for cache dependency registration.

sbornstein2
sbornstein2 used Ask the Experts™
on
Hello all,

I am beating my head trying to get this to work.  I am using this article:
http://www.dotnetcurry.com/ShowArticle.aspx?ID=263&AspxAutoDetectCookieSupport=1

I am trying to enable ASP.Net and SQL Server 2008 SQL Caching Dependency in a test application.  I am totally confused on what the user should be for the:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "TESTSERVER\ASPNET"

I created a user called "TestUser", that user has grant access where I ran all of these:


GRANT CREATE QUEUE TO "TestUser"

GRANT CREATE SERVICE TO "TestUser";

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "TestUser";

GRANT RECEIVE ON QueryNotificationErrorsQueue TO "TestUser";

My code I am getting an error: "Unable to connect to the SQL database for cache dependency registration." on the line:
 SqlCacheDependencyAdmin.EnableNotifications(query.Context.Connection.ConnectionString);

Any idea what I am suppose to do?




Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan McCauleySenior Data Architect

Commented:
Though I almost hesitate to mention it, have you confirmed that the connection string that's being used in your .EnableNotifications call is correct and points to the right database? If it explicitly connects to the your cache database as a particular SQL user, does that user have rights to open that connection and view all the objects?

Author

Commented:
It is db_owner of the database.

Author

Commented:
actually the inner exception says login failed but that makes no sense because I set it up via the LINQ to SQL and I can see tables etc.  Everything looks correct.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Ryan McCauleySenior Data Architect

Commented:
If you have SQL Profiler handy (it's part of the SQL Server client tools), you can monitor the database server when you run your process and see any errors or login failures - this might shed some light. It's possible that it's something else permissions-related and not a failed login specifically, but it's just being reported that way.

Author

Commented:
I also know it works as I just placed a simple asp:sqldatasource and loaded a dropdown fine with it

Author

Commented:
using Express so I cant run profile trace it seems
Ryan McCauleySenior Data Architect

Commented:
Have you tried that statement using your ASPNET app pool account? I believe the LOCALHOST\ASPNET is the default application pool account, so it may actually need to be that user (or, if you're running your application under a custom pool, then whatever account you're using to host the pool).
Senior Data Architect
Commented:
There's an open-source app that does some basic sql tracing, much like SQL Profiler, but specifically designed for people using the Express edition of SQL Server:

https://sites.google.com/site/sqlprofiler/

Author

Commented:
Figured it out, it was for some reason the query.Context.Connection.ConnectionString does not work and pass through the password.  I had to just create the connection string and use that for some reason.  very odd.

Thanks anyway

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial