locked
Worker Process Identity Requirements for a Data Service RRS feed

  • Question

  • I ran into an issue yesterday regarding DB security which after some investigation I think I may've resolved. However, it lead me to wonder about what are the app pool identity requirements for a DataService? I've a setup where the data service is hosted on IIS6 on a different machine from the DB. The service is configured to run as an anonymous user which is a domain account with read/write access to the DB and the app pool identity uses a different domain account with public role access only to the DB. Under most circumstances I'm able to query the DB and even make updates without any issues. However, for some specific scenarios I get the following error:

    Cannot open database "Northwind" requested by the login. The login failed. Login failed for user 'DOMAIN\ACCOUNT'

    DOMAIN\ACCOUNT here is the app pool identity.

    This error occurs for these specific scenarios in my case:

    1) Querying with the $expand option
    2) Calling DataServiceContext.SaveChanges with the batch option


    The issue is similar to this posting:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataservices/thread/d343d4cf-d4b8-4156-83ed-db6b61627239


    The way I fixed this (after reading the above posting) was by using either approach:

    - Use a SQL login OR
    - Give app pool identity user read/write access to the DB

    So my question here is about the inconsistent behavior for the specific scenarios that I've listed above. It seems like some data sevice code executes in the wpg context as opposed to the anonymous user I've specified. Am I right about this assumption or is there some specific detail that I am missing in my setup? Could it be because the data source I'm using is LINQ To SQL with a known issue? Any insight would be appreciated.

    P.S. The solution also fixed another issue I had posted a while back but couldn't resolve at that time:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataservices/thread/c8560e2a-98c2-44cb-9f5f-24f233d0ab23

    Tuesday, June 30, 2009 5:05 PM

Answers

  • Hi Ahmed,
      The Data Service itself doesnt require any specific Security policy to be able to call the provider. 
      It is the provider which should be configured appropriately to handle data access when hosted on an authenticated website.
      Can you take a SQL Profiler trace to see if the identity of the connection is the same accross the failing and the URIs that work ?
      Also , can you share the connection string without the password , if any ?
    Phani Raj Astoria http://blogs.msdn.com/PhaniRaj
    Tuesday, June 30, 2009 5:26 PM
    Moderator

All replies

  • Hi Ahmed,
      The Data Service itself doesnt require any specific Security policy to be able to call the provider. 
      It is the provider which should be configured appropriately to handle data access when hosted on an authenticated website.
      Can you take a SQL Profiler trace to see if the identity of the connection is the same accross the failing and the URIs that work ?
      Also , can you share the connection string without the password , if any ?
    Phani Raj Astoria http://blogs.msdn.com/PhaniRaj
    Tuesday, June 30, 2009 5:26 PM
    Moderator
  • Here is the connection string using windows auth:

    Data Source=server\dev;Initial Catalog=DB_UnitTest;Integrated Security=SSPI;

    Here is using SQL login:

    Data Source=server\dev;Initial Catalog=DB_UnitTest;Integrated Security=false;User ID=user;Password=password;

    I'll have to get back to you on the SQL Profiler trace.

    Tuesday, June 30, 2009 5:38 PM
  • Hello, have you found any workaround for this behavior? I am experiencing the same issue. I am using linqtosql provider too. It is really strange issue.
    Wednesday, January 30, 2013 8:19 PM