problem connecting to dbase from webservice with impersonation

Discussion in 'ASP .Net Security' started by Nadav Popplewell, Oct 16, 2006.

  1. Hi everybody,

    I've got a ASP.NET 2.0 WebService that need to connect to a SQL Server on
    another machine.

    I've setup the anonymous Account of the web application to an domain account
    that has permissions to access the dbase,
    and I've set

    <identity impersonate="true" />

    in the web.config file.

    The connection to the dbase fails with error
    "Login failed for user ''. The user is not associated with a trusted SQL
    Server connection."

    If I put the user and password of the domain account in the <identity ..>
    element (SAME account as that defined as the annonymous account) then the
    WebService Connects to the dbase.

    I've debugged the WebService and checked
    System.Security.Principal.WindowsIdentity.GetCurrent().

    These are the properties of the WindowsIdentity:

    No user/password:

    AuthenticationType "MICROSOFT_AUTHENTICATION_PACKAGE_V1_0
    ImpersonationLevel Delegation
    Name "NI\\nadav_p"

    with user/password:

    AuthenticationType "Kerberos"
    ImpersonationLevel Impersonation
    Name "NI\\nadav_p"

    I've tried reading about this, and as far as I can see delegation does not
    work unless kerberos is used.
    Is this right?

    Has anybody got any ideas how I can connect to the dbase from my WebService
    without writing my password in the web.config?

    Thanks,

    Nadav
     
    Nadav Popplewell, Oct 16, 2006
    #1
    1. Advertising

  2. I've tried connecting to the same SQL from an asp.net 1.1 WebService on the
    same IIS (same anonymous account as the ASP.NET 2.0 WebSite).

    for ASP.NET 1.1 the connection succeeds even if the user/password are not
    specified in the web.config file.

    This seems to be something that's changes in asp.net 2.0.

    Nadav
     
    Nadav Popplewell, Oct 16, 2006
    #2
    1. Advertising

  3. Hello Nadav,

    From your description, you have an ASP.NET 2.0 webservice application which
    will acess a remote SQL Server instance, so you want to let your ASP.NET
    application run under a domain account so as to access the remote SQL
    Server. Your current implementation is configured the domain account as
    IIS virtual directory's anonymous user identity and set impersonate=true in
    web.config so as not to expose username/password credentials in web.config
    file. However, you found this not work and will result to "Login failed for
    user The user is not associated with a trusted SQL Server connection."
    error. If there is anything I missed , please feel free to corret me.


    As you mentioned that the same setting works well for ASP.NET 1.1, based on
    my experience, there is no such significant change from ASP.NET V1 to V2.
    For your scenario, I've also tested according to your setting on my local
    environment and such solution can work well for connecting remote SQL
    Server through domain account. There is not particular difference on this
    from ASP.NET V1 and V2.

    As for the problem you met, I think it is likely environment specific. I
    think you can have a check on the following things:

    1. As for the connection string, are you specify the "integraetd
    security=true" option as below?

    <add name="TestConnectionString" connectionString="Data
    Source=sha-weilu-test;Initial Catalog=Test;Integrated Security=True"
    providerName="System.Data.SqlClient" />

    2. Since you configure your ASP.NET application to impersonate and running
    under a domain account, have you granted that domain account sufficient
    permission for accessing any necessary ASP.NET resources. For .net 2.0,
    you can use the aspnet_regiis -ga command to grant a given account
    sufficient permission to executing ASP.NET code:

    #ASP.NET IIS Registration Tool (Aspnet_regiis.exe)
    http://msdn2.microsoft.com/en-us/library/k6h9cz8h.aspx

    3. Based on the debugger trace info, when you encountered the problem, the
    authenticationtype display " MICROSOFT_AUTHENTICATION_PACKAGE_V1_0" which
    is not clear whether it uses NTLM or Kerberos( the two main protocol for
    intergrated windows authentication). It is possible that the anonymous
    account's login is not authenticated through one of the valid integrated
    windows authentication protocol and result to the "login failed" at SQL
    Server side. (in my local test under ASP.NET 2.0, it display "kerberos")

    for this point, you can check your IIS virtual directory or site's
    authentication setting to see whether it is configured to use NTML or
    kerberos(negotiate), the following kb article has demonstrate how to
    configure this in IIS. You can even force your ASP.NET 2.0 webservice's
    virtual directory to use NTLM only for test to see whether it works.(NTLM
    is enough for accessing remote resource under single hop)

    #How to configure IIS to support both the Kerberos protocol and the NTLM
    protocol for network authentication
    http://support.microsoft.com/kb/215383/en-us

    Just some of my understanding and suggestion. Please feel free to let me
    know if you have anything unclear or any further finding.

    Sincerely,

    Steven Cheng

    Microsoft MSDN Online Support Lead



    ==================================================

    Get notification to my posts through email? Please refer to
    http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
    ications.



    Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
    where an initial response from the community or a Microsoft Support
    Engineer within 1 business day is acceptable. Please note that each follow
    up response may take approximately 2 business days as the support
    professional working with you may need further investigation to reach the
    most efficient resolution. The offering is not appropriate for situations
    that require urgent, real-time or phone-based interactions or complex
    project analysis and dump analysis issues. Issues of this nature are best
    handled working with a dedicated Microsoft Support Engineer by contacting
    Microsoft Customer Support Services (CSS) at
    http://msdn.microsoft.com/subscriptions/support/default.aspx.

    ==================================================



    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Steven Cheng[MSFT], Oct 16, 2006
    #3
  4. Hi Steven,

    Thanks for your reply,

    > 1. As for the connection string, are you specify the "integraetd
    > security=true" option as below?
    > <add name="TestConnectionString" connectionString="Data
    > Source=sha-weilu-test;Initial Catalog=Test;Integrated Security=True"
    > providerName="System.Data.SqlClient" />


    Yes, I've specified Integrated Security=True.

    > 2. Since you configure your ASP.NET application to impersonate and running
    > under a domain account, have you granted that domain account sufficient
    > permission for accessing any necessary ASP.NET resources. For .net 2.0,
    > you can use the aspnet_regiis -ga command to grant a given account
    > sufficient permission to executing ASP.NET code:


    The ASP.NET code runs, I can debug it, it's just that the Connection to the
    Dbase fails.

    > for this point, you can check your IIS virtual directory or site's
    > authentication setting to see whether it is configured to use NTML or
    > kerberos(negotiate), the following kb article has demonstrate how to
    > configure this in IIS. You can even force your ASP.NET 2.0 webservice's
    > virtual directory to use NTLM only for test to see whether it works.(NTLM
    > is enough for accessing remote resource under single hop)


    > #How to configure IIS to support both the Kerberos protocol and the NTLM
    > protocol for network authentication
    > http://support.microsoft.com/kb/215383/en-us

    I've executed the steps in article.

    My IIS is set to "Negotiate,NTLM"

    Nadav
     
    Nadav Popplewell, Oct 16, 2006
    #4
  5. Hi Nadav,

    Thanks for your reply.

    What's your server and the domain environment? (such as server OS version,
    IIS version and Domain condition). My local test is performed on a windows
    2003 server sp1 with IIS6 and virtual directory is allowing NTLM/Negotiate
    authentication.

    So far, I think the problem is likely due to the windows authentication
    against the domain account on the webserver machine. And the problem can be
    specific to ether ther webserver(IIS metabase) and the domain account. If
    you have some other server machine available, I suggest you do the
    following tests:

    1. Move the same application to another server machine and configure the
    same authentication setting in IIS and test again to see whether the
    problem remains.

    2. I noticed that the debug info in your last reply indicate that the
    problem windowsIdentity has a "Delegation" level and the authenticationType
    is also a bit strange, I wonder whether the domain account is configured as
    delegatable in DC, you can also verify this. And for test, you can switch
    to use another normal domain account to see the behavior.

    I've also discussed with some other engineers and they also mentioned that
    it is possible caused by the kerberos authentcation context.

    Anyway, please feel free to post here if you have any new finding.

    Sincerely,

    Steven Cheng

    Microsoft MSDN Online Support Lead


    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Steven Cheng[MSFT], Oct 17, 2006
    #5
  6. Hi Steven,

    Both my Web Server and Dbase Server are windows XP SP2.
    the IIS version is 5.1, and is set to "Negotiate,NTLM".
    The Domain controller is Windows 2003 server std R2.

    I have performed the same test on three different computers with three
    different domain accounts with the same results.

    I don't know if the account has the delegatable permission.
    How do I see if it does?

    Nadav
     
    Nadav Popplewell, Oct 23, 2006
    #6
  7. Thanks for your response Nadav,

    Seems quite strange comparing to my local test. For account's delegation
    setting, you can refer to the following msdn article:

    http://msdn.microsoft.com/library/en-us/dnpag2/html/paght000024.asp?frame=tr
    ue

    BTW, I think you can still check whether the impersonated account can be
    used to access any other remote resources. For example, a straightforward
    test is accessing some file on a share directory of the remove server
    machine, you can open the folder's NTFS system auditing log to get whether
    the impersonated anonymous account is correctly loged on the remote
    machine. If file access can work correctly, it seems the problem also
    related to database. Else, you can concentrate on the IIS to ASP.NET's
    anonymous account authentication.

    Please feel free to let me know if you have any further finding.

    Sincerely,

    Steven Cheng

    Microsoft MSDN Online Support Lead


    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Steven Cheng[MSFT], Oct 23, 2006
    #7
  8. Hi Steven,

    Thanks for the quick reply.

    I've created a shared folder on the dbase server with write permissions for
    the account the ASP.NET is impresonating.
    The WebSite can create a file in that shared folder (and the owner is
    reported as the impersonated account), but the connection to the dbase fails.

    Nadav
     
    Nadav Popplewell, Oct 23, 2006
    #8
  9. Hi Steven,

    I've did some more tests and I've managed to narrow down the problem.

    The WebSite code CAN read from the dbase.
    The problem is with the Membership provider.
    When I run Membership.ValidateUser() I get the execption about the not
    trusted connection.
    The Membership provider (SqlMembershipProvider) uses the same connection
    string saved in web.config, but for some reason Membership.ValidateUser()
    fails even though my code can connect to the SQL and read from it.

    I can't believe I didn't notice this before...

    Nadav
     
    Nadav Popplewell, Oct 23, 2006
    #9
  10. Thanks for your followup Nadav,

    Seems the problem has been quite narrowed. So it is only when performing
    query against the SQLMembershipProvider 's database table does the error
    occur. Are you currently put the membership's database tables in the same
    database with your other application data?

    I think you can run SQL profiler at SQLServer side to see what is output
    when the Validateuser is called at client-side, you can also compare it
    with your normal database access to see what's the difference between them
    in SQL profiler trace.

    Sincerely,

    Steven Cheng

    Microsoft MSDN Online Support Lead


    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Steven Cheng[MSFT], Oct 24, 2006
    #10
  11. Hi Steven,

    Yes, The membership's database tables are in the same database as our
    application data.
    (Some other developer in our team created the dbase, As I understand it he
    used some tool of Microsoft to create the membership's tables).

    I'll check to see what the difference is between the sql queries from the
    membeship provider and normal queries.

    Nadav
     
    Nadav Popplewell, Oct 24, 2006
    #11
  12. Hey Nadav,

    Any further update on this? Have you got any useful clue from the SQL
    profile log?

    Please feel free to let me know if there is anything we can help.

    Sincerely,

    Steven Cheng

    Microsoft MSDN Online Support Lead


    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Steven Cheng[MSFT], Oct 27, 2006
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Aurelio
    Replies:
    0
    Views:
    2,770
    Aurelio
    Jan 2, 2006
  2. nicholas
    Replies:
    2
    Views:
    409
    nicholas
    Oct 7, 2004
  3. shieldguy

    DBase database connection Problem

    shieldguy, May 23, 2007, in forum: .NET
    Replies:
    0
    Views:
    328
    shieldguy
    May 23, 2007
  4. Jarred Sargent

    Asp.Net and Webservice using Impersonation/App Pools

    Jarred Sargent, Jul 7, 2004, in forum: ASP .Net Security
    Replies:
    0
    Views:
    146
    Jarred Sargent
    Jul 7, 2004
  5. Gangolf
    Replies:
    3
    Views:
    190
Loading...

Share This Page