Re: Integrated Authentication with SQL

From: Scott Elgram (SElgram_at_verifpoint.com)
Date: 10/07/05


Date: Fri, 7 Oct 2005 11:22:15 -0700

Peter,
    On the IIS level there is no trouble authenticating with kerberos. I
have "Windows Integrated Authentication" as the only option checked for the
entire site and have no trouble accessing any other part. It seems that the
problem is in when I try to flow those credentials over to the SQL server.
    I have turned on Auditing of successful logon events for the Web server
and the SQL server. When I try to access the site I receive the following
record in the Web Servers even log:
----------------------------------------------------------------------------

----
Date:        10/07/2005        Source:        Security
Time:        10:40                Category:    Logon/Logoff
Type:        Success            Event ID:    540
User:        <domain>\<username>
Computer:    WEB01
Description:
Successful Network Logon:
  User Name: <username>
  Domain:  <domain>
  Logon ID:  (0x0,0x4EACB)
  Logon Type: 3
  Logon Process: Kerberos
  Authentication Package: Kerberos
  Workstation Name:
  Logon GUID: {207e942d-6d16-5a6e-630c-d466379edfea}
  Caller User Name: -
  Caller Domain: -
  Caller Logon ID: -
  Caller Process ID: -
  Transited Services: -
  Source Network Address: 192.168.0.103
  Source Port: 1412
----------------------------------------------------------------------------
----
    This, I think is good....I have no problem accessing any other part of
the site that uses Integrated Authentication.  However, I have noticed that
for every one of the above entries in the web server I have the following
entry on the SQL server.
----------------------------------------------------------------------------
----
Date:        10/07/2005        Source:        Security
Time:        10:40                Category:    Logon/Logoff
Type:        Success            Event ID:    538
User:        NT AUTHORITY\ANONYMOUS LOGON
Computer:    SQL01
Description:
User Logoff:
  User Name: ANONYMOUS LOGON
  Domain:  NT AUTHORITY
  Logon ID:  (0x0,0x17BA0E)
  Logon Type: 3
----------------------------------------------------------------------------
----
     If I am understanding this correctly then the credentials being used to
access the site are not flowing to the SQL server as I had intended.  The
part that puzzles me here aside from it not working is that this entry is
"User Logoff".
    Perhaps I am missing some small setting or detail?
-Scott
    "Peter Jakab" <someone@from.hu> wrote in message
news:OdM4dl1yFHA.2960@tk2msftngp13.phx.gbl...
> See
>
> http://support.microsoft.com/?id=215383
>
> In iis 6 metabase is an xml file that you can edit with notepad.
>
>
http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/7258232a-5e16-4a83-b76e-11e07c3f2615.mspx
>
> I think, Kerberos cannot be forced, Negotiate means: it tryes with
kerberos,
> when it fails, switches to ntlm.
>
> Regards
>
> Peter
>
>
>
>
> "Scott Elgram" <SElgram@verifpoint.com> wrote in message
> news:uo0ESY1yFHA.2372@TK2MSFTNGP10.phx.gbl...
> > Yeup, quite sure.
> >    From what I have been reading there are two methods windows can use
in
> > this instance.  The first is NTLM which is what is being used most often
> > and
> > where I think my problem is.  NTLM does not allow for authentication
past
> > singe hop and therefore can delegate or do anything fancy like that.
What
> > I
> > need to use is the second method.  Kerberos can impersonate, delegate
and
> > make additional hops.  My problem, I think, is that Kerberos is not
being
> > used but I really don't know enough about it to troubleshoot it and have
> > found very little online about exactly how to set this up.
> >    I was using Windows 2k with IIS 5 but because this is all
experimental
> > for me right now I have upgraded to Windows 2k3 and IIS 6 to see if that
> > makes any difference.
> >
> > -Scott
> >
> > "Peter Jakab" <someone@from.hu> wrote in message
> > news:eRAslZyyFHA.3864@TK2MSFTNGP12.phx.gbl...
> >> Scott, are you sure, that in IIS manager for the application you
disabled
> >> anonymous access?
> >>
> >> (find your application, right click, properties, derectory security,
> >> anonymous access and identity control, click edit, and be sure that
> >> anonymous access is unchecked, AND integrated windows authentication is
> >> checked)
> >>
> >> It should work, in case there is just 1 hop!
> >>
> >> Best regards
> >>
> >> Peter
> >>
> >> "Scott Elgram" <SElgram@verifpoint.com> wrote in message
> >> news:ehqT9GfyFHA.3864@TK2MSFTNGP12.phx.gbl...
> >> > Hello,
> >> >    I am trying to create a site using integrated windows
authentication
> > to
> >> > access SQL databases.  All the tutorials I have found so far require
> > that
> >> > both SQL server and IIS reside on the same server.  This is a problem
> > for
> >> > me
> >> > because I need to access multiple SQL servers from the same site so a
> >> > stand
> >> > alone web server would be ideal.
> >> >    From what I have been able to gather so far:
> >> >        - "Anonymous Access" is unchecked and "Windows Integrated
> >> > Authentication" is checked in IIS
> >> >        - The machine running IIS must be set as "trusted for
> >> > delegation"
> >> > in
> >> > active directory.
> >> >        - The domain user accounts that will be accessing the
databases
> > an
> >> > site must not be marled "Account is sensitive and cannot be
delegated".
> >> >        - The tags <Identity impersonate="true"> and <Authentication
> >> > mode="windows"> is set in web.config
> >> >        - comImpersonationLevel="Delegate" and
> >> > comAuthenticationLevel="PktPrivacy" are set in machine.config
> >> >    After all that is set then the connection string
"server=SQLserver;
> >> > Integrated Security=SSPI; Trusted_Connection=YES;
database=SQLdatabase"
> >> > should be able to connect to the SQL database using the clients
> >> > credentials.
> >> > However, I receive the following error:
> >> > --------------------------------------------------------------------
> >> > Exception Details: System.Data.SqlClient.SqlException: Login failed
for
> >> > user
> >> > 'NT AUTHORITY\ANONYMOUS LOGON'.
> >> >
> >> > Stack Trace:
> >> >
> >> > [SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]
> >> >   System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
> >> > isInTransaction) +472
> >> >
> >> >
> >
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
> >> > tionString options, Boolean& isInTransaction) +370
> >> >   System.Data.SqlClient.SqlConnection.Open() +383
> >> >   Rules.WebForm1.Page_Load(Object sender, EventArgs e) in
> >> > d:\inetpub\wwwroot\rules\rules.aspx.cs:47
> >> >   System.Web.UI.Control.OnLoad(EventArgs e) +67
> >> >   System.Web.UI.Control.LoadRecursive() +35
> >> >   System.Web.UI.Page.ProcessRequestMain() +750
> >>
>
>> -------------------------------------------------------------------------
-
> > --
> >> > --------
> >> >
> >> > Any help in resolving this problem would be greatly appreciated.
> >> >
> >> > Thanks,
> >> >
> >> > -- 
> >> > -Scott
> >> >
> >> >
> >>
> >>
> >
> >
>
>


Relevant Pages

  • Re: Kerberos machine authentication - apparent authentication fail
    ... until a user logon event. ... the Netdiag utility will show the Kerberos error in this scenario ... On these machines I ... me a plausible starting point to solve my Kerberos authentication problem. ...
    (microsoft.public.windows.server.security)
  • Re: Kerberos machine authentication - apparent authentication fail
    ... Subsequent Netdiag attempts after a reboot show the failed Kerberos ... >>> mean that kerberos authentication is not being used. ... >>> computer for logon events and the domain controller for account logon ...
    (microsoft.public.windows.server.security)
  • Re: Kerberos machine authentication - apparent authentication fail
    ... Kerberos result when I hardwired a laptop to a switch port. ... to authenticate with K on reboot AND authentication appears to take place ... > denied access until you can authenticate to a domain controller as a user. ... > You should have logging of account logon events enabled in Domain Controller ...
    (microsoft.public.windows.server.security)
  • Re: View Type of Protocol
    ... Thanks a lot for the feedback John. ... information about the type of authentication being used. ... some reason Kerberos is not available. ... they are not authenticated and the SQL Server login fails. ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: IIS (ASP) -> SQLServer Authentication Issue
    ... I understand that you'd like to use IIS Intergration authentication in the ... and ASP "impersonates" authencitaed users to access SQL Server on ... only kerberos authentication allows double-hops from clients ...
    (microsoft.public.sqlserver.security)