Integrated Authentication with SQL

S

Scott Elgram

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

Scott Elgram

Is there a way to have it use kerberos so the Credentials can be passed to
the SQL server?

-scott

Dominick Baier said:
Hello Scott,

delegation only works if you use kerberos end-to-end. I guess that if you
look in the security log on the web server, you will see a logon event for
the client - but the authentication package is NTLM

read more here:
http://msdn.microsoft.com/msdnmag/issues/05/09/SecurityBriefs/default.aspx
---------------------------------------
Dominick Baier - DevelopMentor
http://www.leastprivilege.com
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(Sql
Connec
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,
Click to expand...
Click to expand...
 
P

Peter Jakab

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
 
S

Scott Elgram

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 said:
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 said:
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
--------------------------------------------------------------------------
Click to expand...
--
Click to expand...
 
P

Peter Jakab

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/pr...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 said:
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 said:
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 said:
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
--------------------------------------------------------------------------
Click to expand...
--
--------

Any help in resolving this problem would be greatly appreciated.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
D

Dominick Baier [DevelopMentor]

Hello Scott,

read the article i pointed you to
http://msdn.microsoft.com/msdnmag/issues/05/09/SecurityBriefs/default.aspx

it contains all answers
---------------------------------------
Dominick Baier - DevelopMentor
http://www.leastprivilege.com
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 said:
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

Hello,
I am trying to create a site using integrated windows authentication
Click to expand...
to
access SQL databases. All the tutorials I have found so far require
Click to expand...
that
both SQL server and IIS reside on the same server. This is a
problem
Click to expand...
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
Click to expand...
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
Click to expand...
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(Sql
Connec
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
---------------------------------------------------------------------
Click to expand...
--
--------

Any help in resolving this problem would be greatly appreciated.

Thanks,

-- -Scott
Click to expand...
Click to expand...
Click to expand...
 
S

Scott Elgram

Dominick,
Thanks for that article....It was a big help especially for
understanding the SetSPN.exe utility. However, It still doesn't seem to
work. I have even written the author to see if he can help.

-Scott
Dominick Baier said:
Hello Scott,

read the article i pointed you to
http://msdn.microsoft.com/msdnmag/issues/05/09/SecurityBriefs/default.aspx

it contains all answers
---------------------------------------
Dominick Baier - DevelopMentor
http://www.leastprivilege.com
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 said:
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


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(Sql
Connec

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
Click to expand...
Click to expand...
Click to expand...
 
S

Scott Elgram

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 said:
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/pr...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 said:
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 said:
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

Hello,
I am trying to create a site using integrated windows
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
------------------------------------------------------------------------- -
Click to expand...
Click to expand...
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Windows authentication from ASP.NET to SQL Server 2
Integrated Windows Authentication 2
WCF and Integrated Windows Authentication 7
Login failed for user '(null)'. Reason: Not associated with a trusted SQL 4
Integrated Authentication with SSL 6
.NET, Integrated Windows Authentication, and more 1
Integrated Authentication 1
ASP.NET Integrated Windows Authentication 2

Members online

Total: 23 (members: 4, guests: 19)
Robots: 315

Forum statistics

Threads
473,755
Messages
2,569,535
Members
45,007
Latest member
obedient dusk

Latest Threads

Top