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
> >> >
> >> >
> >>
> >>
> >
> >
>
>