locked
Web service fails to connect to make trusted connection to SQL Server after certain period

    Question

  • Hi All

    The scenario is as follows:

    We have IIS running on server 1, with a web service installed which connects to server 2 which is an SQL Server 2000 server. The web service connects using Trusted_Connection=yes and hence passes the identity through to the SQL Server.

    This works flawlessly on hundreds of workstations for about 24 hours. If the workstations remain logged in longer than this, the web service starts passing back exceptions that state " Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection".

    If the workstation is logged off and logged back in, and the web service is called from that workstation, authentication with the database works fine again.

    We have confirmed that authentication from the work station to the web service is still working - i.e. the web service can return the user name of the requesting user. It won't however, pass it through to the SQL Server.

    FYI this is a .NET Framework 1.1 web service.

    Regards
    Adam
    Friday, July 4, 2008 6:35 AM

Answers

  • Hi Adam... and Caddre,
    It seems this is a problem with the Kerberos dll that installed on the client (this is for Windows XP SP2 x86). If the version is lower than
    Windows XP with SP2, x86-based versions
    File nameFile versionFile sizeDateTimePlatformSP requirement
    Kerberos.dll5.1.2600.3192299,00808-Aug-200715:26x86SP2

    the users will experience this problem and will have to log off and log back on.

    In our case we had version 5.1.2600.2698 and had to apply a hot fix.





    Liz
    • Proposed as answer by Lizet Friday, November 7, 2008 10:06 PM
    • Marked as answer by CaddreModerator Friday, November 7, 2008 10:49 PM
    Friday, November 7, 2008 10:05 PM
  • (ALL domain users (i.e. OURDOMAIN\Domain Users) have access to the server at the server level and the databases in question.)

    But this one is failing because you have double hop that is IIS which is your web server is in a different server, that means you fix it by putting the network service account which was introduced in Windows2003 in the Server running SQL Server and in SQL Server on the server level and on the database level.  You apply the domain permissions in AD  and in SQL Server go to Enterprise Manager under Management go to security and apply the permissions this is the third time I telling you to do this.



    (If "OURDOMAIN\Domain Users" has access to the server and databases, then the IIS runtime has access to the database correct? If not, can you please provide more detail as to what is involved in adding the IIS runtime account to SQL Server and the appropriate databases.)

    The network service account was introduced in Windows 2003 a limited access account to allow services which includes runtimes like Asp.net to access resources in a Windows 2003 network.  This is not IIS runtime problem but rather Asp.net runtime you are focusing on the wrong issues so your application is failing.

    (Also, can you address why this issue only appears on workstations after an elapsed period of time but continues to work on workstations that have recently logged in to the domain? If this were not set up correctly, I imagine pass-through authentication would not be working at all, not just failing in this limited circumstance?)

    I have told you in my previous post that timeout could be related to connection pooling not used or not set up correctly and your connection Object not disposed with Using code blocks which is the second Using statement in C# 1.1 and 2.0 and the Using statement introduced in VB.NET 2.0 it calls dispose for you automatically on classes that implements the IDisposable interface the connection object is one such class.  You wrong about pass through authentication failing the short answer is no because Windows passes your user accounts from one server to the other manually not the automatic version you are running that is failing.




    Asp.net MVP, MCPD Web C#, MCITP BI & MCTS SQL Server 2005
    Wednesday, July 16, 2008 3:53 PM
    Moderator

All replies

  •  You have to add the Asp.net account in server one in to server two on the domain level, then change SQL Server to use both Windows Authentication and SQL Authentication and put the Asp.net account into SQL Server on the server level and database level under managment in Enterprise Manager in SQL Server 2000.  The reason you have a double hop problem.


    Asp.net MVP, MCPD Web C#, MCITP BI & MCTS SQL Server 2005
    Saturday, July 5, 2008 2:09 AM
    Moderator
  • Thanks for your replies

    Swati Jain: We are not using sessions at all. This is a stateless web service. Also, the connection to the SQL server is not timing out, its returning immediately with failed credentials.

    Caddre: The web service uses Windows Authentication, we are not running it under an ASP.NET account. The database is already running in mixed mode. I am not sure why we would need to give any additional accounts access to the database - we want to connect to the database as the user connecting to the web service, not under any other account.

    The application pool is running under the Local System account


    Monday, July 7, 2008 1:58 AM
  • Web service uses the Asp.net runtime so I have given you the short and easy solution but here are the long complicated solution because SQL Server and your application database does not know your users in another hop trying to access resources.  Your users connection to SQL Server and the database must be resolved or the problem will continue.


    http://msdn.microsoft.com/en-us/library/ms998355.aspx

    http://technet2.microsoft.com/WindowsServer/en/library/c312ba01-318f-46ca-990e-a597f3c294eb1033.mspx?mfr=true




    Asp.net MVP, MCPD Web C#, MCITP BI & MCTS SQL Server 2005
    Monday, July 7, 2008 12:57 PM
    Moderator
  • I'm confused by the above answer. Maybe there is a few things I was not clear on:

    - Both servers and all workstations are on a domain (the same domain)
    - All domain users have access to the SQL Server Databases I am trying to connect to.
    - Connections to the web service work fine for a while (including the subsequent connection from web service -> database) and then spontaneously stop working after about 24 hours, reporting that an attempt to log on as (null) was recorded. Generally the user who is logged in to the web service is being passed to the database with no problem until this time. We have not been able to identify anything that runs (or any server reboots) that would cause it to stop working after a certain interval after which workstations need to be logged off and back on to the domain.

    Can you explain what you mean by the "ASP.net account" ? Do you mean the user under which the application pool is running? The application pool on server 1 is running as "Local System". The SQL Server service on Server 2 is running as a domain user service account.

    Thanks
    Adam
    Tuesday, July 8, 2008 6:27 AM
  •  I think you don't understand how permissions work on the Microsoft platform, SQL Server permissions are not defined by Microsoft the company just implements what is defined by ANSI SQL in data control language.  Th only way your users can use SQL Server and database in another server is either pass through authentication or constrained delegation.  So check below all your questions are answered by Microsoft with how to configure constrained delegation.  Again my first post makes it easy.

    http://msdn.microsoft.com/en-us/magazine/cc163740.aspx


    Asp.net MVP, MCPD Web C#, MCITP BI & MCTS SQL Server 2005
    Tuesday, July 8, 2008 1:39 PM
    Moderator
  • Thank you for that last article. It was very helpful.

    However I have checked all SPNs, checked that the accounts the web server and sql server are running under are trusted for delegation. I moved the application pools to run under Network Service (computer's domain account) instead of Local System. I can't tell if it has made a difference yet as the workstations here have been rebooted recently, so I will see how it goes shortly.

    What puzzles me is why it works initially but then stops working spontaneously.

    I suspect the clients must be authenticating with Kerberos initially but at some point after some interval fall back to NTLM which is when delegation can no longer work. At least now when it starts occuring again I can look at the event log on the web server and see if the clients are negotiating to NTLM or to Kerberos.

    On that note, can I also ask - is it impossible to support delegation with browsers other than Internet Explorer. I note that connections to the web server with IE use Kerberos but Firefox connections use NTLM and hence pass-through authentication to the database on another server never works.
    Wednesday, July 9, 2008 12:44 AM
  • I don't think so but since you have changed the account to Network Service account you can fix it by putting the Network Service account in the server running IIS and put is on the domain level and in the two places in SQL Server I covered in my first post and the problem will go away because Null user means SQL Server and the database does not know the Asp.net runtime because if the runtime does not have access your code will always break.  Another reason IIS 6 comes locked down to render only static html check below for the default permissions in IIS 6.

    http://support.microsoft.com/kb/812614





    Asp.net MVP, MCPD Web C#, MCITP BI & MCTS SQL Server 2005
    Wednesday, July 9, 2008 4:30 PM
    Moderator
  • I have now managed to replicate this again, as my workstation has been logged in for > 24 hours.

    My suspicions are correct in that the client machine (an ordinary Windows XP workstation) has for some reason fallen back to NTLM instead of Kerberos.

    This is in the event log for our IIS server after attempting access to a page/web service that connects to the remote SQL server:

    Successful Network Logon:
         User Name:    xxx
         Domain:        XXXXXX
         Logon ID:        (0x0,0xE12760)
         Logon Type:    3
         Logon Process:    NtLmSsp
         Authentication Package:    NTLM
         Workstation Name:    XXX12106
         Logon GUID:    -
         Caller User Name:    -
         Caller Domain:    -
         Caller Logon ID:    -
         Caller Process ID: -
         Transited Services: -
         Source Network Address:    10.1.27.9
         Source Port:    13210

    Whereas yesterday, a log in to a web service/web app produced the following:

    Successful Network Logon:
         User Name:    xxx
         Domain:        XXXXXX
         Logon ID:        (0x0,0xE0D0F7)
         Logon Type:    3
         Logon Process:    Kerberos
         Authentication Package:    Kerberos
         Workstation Name:   
         Logon GUID:    {0c5ea00e-2fd6-82be-9869-1d0c5de88a25}
         Caller User Name:    -
         Caller Domain:    -
         Caller Logon ID:    -
         Caller Process ID: -
         Transited Services: -
         Source Network Address:    10.1.27.9
         Source Port:    2328

    I have also confirmed on the SQL Server, that users who have recently logged out and logged back in to their workstation are continuing to access the web applications/web services with no issue and their logins are showing up on the SQL Server in the event log as a successful Kerberos login whereas mine shows up as a failed NTLM login as "ANONYMOUS LOGON" (because NTLM does not support delegation).

    I think this demonstrates clearly that something unusual is going on here and that constrained delegation is generally set up correctly (and works for all users until their workstation has been logged in for > 24 hours).


    Friday, July 11, 2008 12:16 AM
  • If you can narrow this down sufficiently, you should create a Connect issue, then post the link to the issue as a reply here.
    John Saunders | Use File->New Project to create Web Service Projects
    Saturday, July 12, 2008 2:12 PM
    Moderator
  • The problem does not qualify for a connect issue however the user can open one and I can comment to it because the network contains none Win2003 servers or the configuration is not correct.  There are two very valid alternative solutions for this problem I have given the user the one the user can implement but the user choose to ignore it.

    I just helped a user deploy .NET 1.1 Web service with many third party dlls to WOW64 in a Win2003 64bits the user was getting ready for some rewrites but the Asp.net debugger shows all the problem was the  Asp.net runtime permissions.  Developers can choose implementations and expect fluid permissions propagation well that is in a lab and not real corporate networks where you don't choose your system admin.

    SQL Server is saying the Asp.net runtime and the users are not valid users,why the SQL Server is SQL Server 2000 which means it maybe running on Win2000 which means it is not constrained delegation.


    Asp.net MVP, MCPD Web C#, MCITP BI & MCTS SQL Server 2005
    Saturday, July 12, 2008 2:58 PM
    Moderator
  • All servers on our domain are running Windows 2003. Constrained delegation is working. As I have pointed out, the web applications are working for everyone, they just stop working for individual users if their workstation (Windows XP) has been logged in for a long period of time. I have also posted logs showing Kerberos authentication falling back to NTLM when this starts occuring.

    At this point I believe the issue is with the domain controller infrastructure and not with the web server or database server. I turned Kerberos logging on on the client workstations and our web server and can see Kerberos errors appearing

    Server Time: 1:59:29.0000 7/11/2008 Z

    Error Code: 0xd KDC_ERR_BADOPTION

    Extended Error: 0xc00000bb KLIN(0)

    Client Realm:

    Client Name:

    Server Realm: x

    Server Name: x

    Target Name: x

    Error Text:

    File: 9

    Line: ae0

    Error Data is in record data.


    I would like to hear other valid solutions in case the issue cannot be resolved by addressing the root cause of the problem.

    However the vague responses I have had so far do not provide any alternative solution. I must say I am disappointed at the level of help received as if I am ignoring your responses or not implementing them properly.

    As I have indicated everything is working, we clearly have configured constrained delegation correctly as it works until workstations have been logged in for a long period of time. We have been running web applications using Windows Authentication successfully for years.

    Monday, July 14, 2008 6:51 AM
  • Caddre:
     You have to add the Asp.net account in server one in to server two on the domain level, then change SQL Server to use both Windows Authentication and SQL Authentication and put the Asp.net account into SQL Server on the server level and database level under managment in Enterprise Manager in SQL Server 2000.  The reason you have a double hop problem.


    Adam2342342341:
    The web service uses Windows Authentication, we are not running it under an ASP.NET account. The database is already running in mixed mode. I am not sure why we would need to give any additional accounts access to the database - we want to connect to the database as the user connecting to the web service, not under any other account.



    In a different server if you are using the Asp.net runtime which you are since you did not write your own parser, then you need to give the Asp.net runtime permission because SQL Server in a different server does not know the Network service account in a different server and the same is true for the database because in an RDBMS server access and database access are different per the requirements of the data control langauge. 

    Timing out could be related to wrong connection timeout property setting when connection pooling or the connection object not disposed with Using statement code blocks. 


    (However the vague responses I have had so far do not provide any alternative solution. I must say I am disappointed at the level of help received as if I am ignoring your responses or not implementing them properly.)



    Somebody who knows needs to configure pass through authenticaiton or create a Windows group with your application users and add that group to SQL Server in Enterprise Manager under Management in security.  I worked for a bank with 68 SQL Servers no application passes users to SQL Server we had a Microsoft consulting contract which gives us pass through authentications and the SQL Context account but all users are manually added with their Windows account into SQL Server.  I have not given you any vague answers I have told you in my first post the Asp.net runtime needs permissions.


    Asp.net MVP, MCPD Web C#, MCITP BI & MCTS SQL Server 2005
    Monday, July 14, 2008 2:57 PM
    Moderator
  • ALL domain users (i.e. OURDOMAIN\Domain Users) have access to the server at the server level and the databases in question.

    We only restrict access to certain objects (e.g. stored procedures) by AD user groups. This works flawlessly.

    If "OURDOMAIN\Domain Users" has access to the server and databases, then the IIS runtime has access to the database correct? If not, can you please provide more detail as to what is involved in adding the IIS runtime account to SQL Server and the appropriate databases.

    Also, can you address why this issue only appears on workstations after an elapsed period of time but continues to work on workstations that have recently logged in to the domain? If this were not set up correctly, I imagine pass-through authentication would not be working at all, not just failing in this limited circumstance?
    Wednesday, July 16, 2008 12:55 AM
  • (ALL domain users (i.e. OURDOMAIN\Domain Users) have access to the server at the server level and the databases in question.)

    But this one is failing because you have double hop that is IIS which is your web server is in a different server, that means you fix it by putting the network service account which was introduced in Windows2003 in the Server running SQL Server and in SQL Server on the server level and on the database level.  You apply the domain permissions in AD  and in SQL Server go to Enterprise Manager under Management go to security and apply the permissions this is the third time I telling you to do this.



    (If "OURDOMAIN\Domain Users" has access to the server and databases, then the IIS runtime has access to the database correct? If not, can you please provide more detail as to what is involved in adding the IIS runtime account to SQL Server and the appropriate databases.)

    The network service account was introduced in Windows 2003 a limited access account to allow services which includes runtimes like Asp.net to access resources in a Windows 2003 network.  This is not IIS runtime problem but rather Asp.net runtime you are focusing on the wrong issues so your application is failing.

    (Also, can you address why this issue only appears on workstations after an elapsed period of time but continues to work on workstations that have recently logged in to the domain? If this were not set up correctly, I imagine pass-through authentication would not be working at all, not just failing in this limited circumstance?)

    I have told you in my previous post that timeout could be related to connection pooling not used or not set up correctly and your connection Object not disposed with Using code blocks which is the second Using statement in C# 1.1 and 2.0 and the Using statement introduced in VB.NET 2.0 it calls dispose for you automatically on classes that implements the IDisposable interface the connection object is one such class.  You wrong about pass through authentication failing the short answer is no because Windows passes your user accounts from one server to the other manually not the automatic version you are running that is failing.




    Asp.net MVP, MCPD Web C#, MCITP BI & MCTS SQL Server 2005
    Wednesday, July 16, 2008 3:53 PM
    Moderator
  • Hello Adam2342342341,
    I'm wondering if you were able to troubleshoot your Kerberos issue. We have a similar problem when we request a web page from a Terminal Server with Windows Server 2000. Otherwise the ASP.NET delegation works fine.

    Here's the short story:

    This issue seems related to Kerberos and not to a problem in the way ASP.NET Delegation is configured.

    Let me make a short summary of the problem. We have a  double hop configuration (see http://blogs.msdn.com/nunos/archive/2004/03/12/88468.aspx) Our web server is configured for ASP.NET delegation (web server has IIS6 over MS Server 2003) and passes the credentials to our SQL Server 2005 box running Windows Server 2003 also.

    This configuration works like a charm when the user requests the page using their own desktop or laptop (most of them use Windows XP on their workstations). Only after a couple of days of not logging off, the user cannot see the requested page. This is not a concern for us now as the user can always log off and log back on on its desktop and see the page.

    This web page is served inside a client application that is consumed internally in the enterprise.

    Our main problem is with remote users that use the client application using terminal servers. Everything works fine if the terminal server has Windows Server 2003 but delegation fails if the Terminal Server has Windows Server 2000. Notice there can be several users connected at the same time on the same terminal server requesting the web page.

    We set up Kerberos logging on the Terminal Server running Windows Server 2000. After restarting the box (setting up this logging requires a restart) http://support.microsoft.com/default.aspx?scid=kb;en-us;Q262177
    the first user using the box was able to see the web page (delegation worked). The second and subsequent users were unable to see the web page (delegation didn't work)

    For these users that delegation didn't work, the system event log on the terminal server shows the following two Kerberos errors:

     Kerberos Error Message was received:
             on logon session LogonUser
     Client Time: Domain\Unsername1
     Server Time:
     Error Code: 14:49:1.0000 11/6/2008 (null) 0x34
     Extended Error: KRB_ERR_RESPONSE_TOO_BIG
     Client Realm:
     Client Name:
     Server Realm: Domain
     Server Name: krbtgt/Domain


    and


    A Kerberos Error Message was received:
             on logon session InitializeSecurityContext
     Client Time:
     Server Time:
     Error Code: 14:49:2.0000 11/6/2008 (null) 0x7
     Extended Error:  KDC_ERR_S_PRINCIPAL_UNKNOWN
     Client Realm:
     Client Name:
     Server Realm: domain.server.com
     Server Name: HOST/ControllerName




    Liz
    Thursday, November 6, 2008 7:11 PM
  • No you don't have similar problems because delegation and impersonation in Asp.net in Win2k is different.

    (However, on Windows 2000, if we want to directly Impersonate with
    LogonUser, we need to grant ASPNET with the SE_TCB_NAME user right. This is
    by design.)


    http://www.velocityreviews.com/forums/t76910-re-aspnet-impersonation-delegation.html


    http://support.microsoft.com/kb/810204


    Asp.net MVP, MCPD Web C#, MCITP BI & MCTS SQL Server 2005
    Thursday, November 6, 2008 9:42 PM
    Moderator
  • Hi Cadre,
    I appreciate your prompt reply. Our IIS box is on a Windows Server 2003 machine running IIS6, the SQL Server 2005  box runs on Windows 2003 too.
    Our clients (the ones that have the problem) are the ones running Windows Server 2000 on Terminal Services and they don't have IIS on those machines. They have IE6 as their web client.
    When the web request comes from a Windows XP desktop everything is fine, when it comes from a terminal server with win2k, it only works for the first requester after a reboot. I don't believe this has to do with delegation per se but with a problem in the Kerberos authentication.

    I hope this makes things clearer...

    Liz
    Friday, November 7, 2008 2:48 PM
  • Hi Adam... and Caddre,
    It seems this is a problem with the Kerberos dll that installed on the client (this is for Windows XP SP2 x86). If the version is lower than
    Windows XP with SP2, x86-based versions
    File nameFile versionFile sizeDateTimePlatformSP requirement
    Kerberos.dll5.1.2600.3192299,00808-Aug-200715:26x86SP2

    the users will experience this problem and will have to log off and log back on.

    In our case we had version 5.1.2600.2698 and had to apply a hot fix.





    Liz
    • Proposed as answer by Lizet Friday, November 7, 2008 10:06 PM
    • Marked as answer by CaddreModerator Friday, November 7, 2008 10:49 PM
    Friday, November 7, 2008 10:05 PM