locked
SqlDependency with Sql Authentication RRS feed

  • Question

  • I'm using SqlDependency to be notified when data has changed in the Sql Database. Works fine with integrated security. If I switch to Sql Authentication, I am only able to get it to work by setting Persist Security Info=True. Is there a way to get it to work using Sql Authentication without setting Persist Security Info=True?
    Tuesday, December 22, 2015 9:03 PM

Answers

  • Hi Geoffrey,

    That is my mistake, thank you noted, I've updated the link.

    You could try to do some other database operations such as select or update check if it works? If not, I guess it’s not a configuration issue.  It may be system's security settings or security settings of the router(hub).

    Hope that helps.

    Best regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by Fred Bao Monday, December 28, 2015 7:37 AM
    • Marked as answer by Geoffrey Dike Monday, December 28, 2015 2:31 PM
    Thursday, December 24, 2015 2:57 AM
  • I found my issue to be in how I was getting the connection string. I already had an entity framework connection open to the db, so I was getting the connection string from ctx.Database.Connection.ConnectionString instead of getting it from the .config file. Of course, without the Persist Security Info turned on, that connection string did not contain the password.

    Thanks for the help anyway!

    • Marked as answer by Geoffrey Dike Monday, December 28, 2015 2:31 PM
    Monday, December 28, 2015 2:25 PM

All replies

  • Hi Geoffrey,

     

    Create two roles containing the required permissions, but you should also make sure that the user that starts SqlDependency has its own schema of which it is the owner.

     

    Below is an article about Minimum Database Permissions Required for SqlDependency.

     

    http://www.codeproject.com/Articles/12862/Minimum-Database-Permissions-Required-for-SqlDepen

     

    Hope that helps.

     

    Best regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Fred Bao Thursday, December 24, 2015 2:15 AM update the link
    Wednesday, December 23, 2015 5:34 AM
  • Thanks for the response. The link provided seems to be a link back to this post. I did find a post on the creation of the roles and the permissions needed and followed the steps in that post. The strange thing is that the SqlDependency seems to work fine from a hub in my MVC project even with Sql Authentication. Where it doesn't seem to work without the Persist Security Info is when I try to add it to my Windows Service. Is there anything special about that configuration that would require it where the hub doesn't need it?
    Wednesday, December 23, 2015 2:28 PM
  • Hi Geoffrey,

    That is my mistake, thank you noted, I've updated the link.

    You could try to do some other database operations such as select or update check if it works? If not, I guess it’s not a configuration issue.  It may be system's security settings or security settings of the router(hub).

    Hope that helps.

    Best regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by Fred Bao Monday, December 28, 2015 7:37 AM
    • Marked as answer by Geoffrey Dike Monday, December 28, 2015 2:31 PM
    Thursday, December 24, 2015 2:57 AM
  • I found my issue to be in how I was getting the connection string. I already had an entity framework connection open to the db, so I was getting the connection string from ctx.Database.Connection.ConnectionString instead of getting it from the .config file. Of course, without the Persist Security Info turned on, that connection string did not contain the password.

    Thanks for the help anyway!

    • Marked as answer by Geoffrey Dike Monday, December 28, 2015 2:31 PM
    Monday, December 28, 2015 2:25 PM