ASP.NET + SQL Server Windows authentication

[Lior Amar]

Hey All,

Trying to understand why I can not get SQL server to trust my IIS server. I
have two machines set up, 1 App and 1 DB, and I'm trying to validate the
applications access to the DB server via NT Authentication. The App comes in
via NTLM which from my understanding only supports Single hop security
delegation. So far I understand why it doesn't work, although seems to me
like a very bad problem. Now, Basic Authentication will transfer the PW and
the UID which will allow IIS to login to the DB server and then NT
Authentication will work. But we all know how non-secure Basic
Authentication is.

Here's the confusion, if Kerberos permits token transferring with no
limitation why can't IIS receive a token via NTLM and transfer it to the DB
server?

I've been reading all of these articles

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/[/url]
vbconaccessingsqlserverfromwebapplication.asp
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/[/url]
vbtskaccessingsqlserverusingwindowsintegratedsecur ity.asp
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnauth/html[/url]
/dnauth_security.asp
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnauth/html[/url]
/signfaq.asp
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;Q176377[/url]

and a bunch of other documents and they all come down to two valid
solutions: Basic Authentication or SQL Users. These are only valid if the
level of security you wish to achieve is not something that needs to pass a
certain level of security (would not pass in industries that require maximum
security).

If I am bound to NT Authentication, is my only option Basic Authentication
(of course under SSL)? And why is it that we don't have these problems with
other Database vendors? Is there any way we can utilize ADSI to get the
users NTLM credentials to pass on to SQL server?

Any help or suggestions will be very appreciated.

Thank you,

[Sherif ElMetainy]

Things that you have to check are:


1- What is the account the webserver is using? in asp.net using default
configuration (no impersonation), it is ASPNET, it can be the
IUSR_MachineName account, or any other account.
in asp.net you can easily find out with this code
Response.Write(System.Security.Principal.WindowsId entity.GetCurrent().Name);
to change the username underwhich the code executes for asp.net change the
<identity> in machine.config

2- Is this account a local account or a domain account?

If it is a domain account, then check that in the SQL server security that
the is permitted to access the server, and has access to the its default
database (or the database specified in the connection string).

If it is a local account, then use a domain account.

If there is no domain, then the username and password for the local account
must be valid on the database server, ie the same username and password on
both machines, I think when ASPNET account is created a random password is
generated for it. so the password is not the same for both machines, and
changing the ASPNET account password is not recommended.

In all cases make sure that the account has access to SQL Server.



"Lior Amar" <lior_amar@hotmail.com> wrote in message
news:uHPZbT#aDHA.2928@tk2msftngp13.phx.gbl...

> Hey All,
>
> Trying to understand why I can not get SQL server to trust my IIS server.

I

> have two machines set up, 1 App and 1 DB, and I'm trying to validate the
> applications access to the DB server via NT Authentication. The App comes

in

> via NTLM which from my understanding only supports Single hop security
> delegation. So far I understand why it doesn't work, although seems to me
> like a very bad problem. Now, Basic Authentication will transfer the PW

and

> the UID which will allow IIS to login to the DB server and then NT
> Authentication will work. But we all know how non-secure Basic
> Authentication is.
>
> Here's the confusion, if Kerberos permits token transferring with no
> limitation why can't IIS receive a token via NTLM and transfer it to the

DB

> server?
>
> I've been reading all of these articles
>
>

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/[/url]

> vbconaccessingsqlserverfromwebapplication.asp
>

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/[/url]

> vbtskaccessingsqlserverusingwindowsintegratedsecur ity.asp
>

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnauth/html[/url]

> /dnauth_security.asp
>

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnauth/html[/url]

> /signfaq.asp
> [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;Q176377[/url]
>
> and a bunch of other documents and they all come down to two valid
> solutions: Basic Authentication or SQL Users. These are only valid if the
> level of security you wish to achieve is not something that needs to pass

a

> certain level of security (would not pass in industries that require

maximum

> security).
>
> If I am bound to NT Authentication, is my only option Basic Authentication
> (of course under SSL)? And why is it that we don't have these problems

with

> other Database vendors? Is there any way we can utilize ADSI to get the
> users NTLM credentials to pass on to SQL server?
>
> Any help or suggestions will be very appreciated.
>
> Thank you,
>
>
>
>

[Lior Amar]

Think the problem is just a limitation of NTLM single hop. Don't think there
is a way around it other than using SSL and Basic Authentication. ASPNET is
set up properly and is impersonating the user approriately. Don't think
there is anyway around this limitation.

Thanks for the help though

Lior


"Lior Amar" <lior_amar@hotmail.com> wrote in message
news:uHPZbT#aDHA.2928@tk2msftngp13.phx.gbl...

> Hey All,
>
> Trying to understand why I can not get SQL server to trust my IIS server.

I

> have two machines set up, 1 App and 1 DB, and I'm trying to validate the
> applications access to the DB server via NT Authentication. The App comes

in

> via NTLM which from my understanding only supports Single hop security
> delegation. So far I understand why it doesn't work, although seems to me
> like a very bad problem. Now, Basic Authentication will transfer the PW

and

> the UID which will allow IIS to login to the DB server and then NT
> Authentication will work. But we all know how non-secure Basic
> Authentication is.
>
> Here's the confusion, if Kerberos permits token transferring with no
> limitation why can't IIS receive a token via NTLM and transfer it to the

DB

> server?
>
> I've been reading all of these articles
>
>

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/[/url]

> vbconaccessingsqlserverfromwebapplication.asp
>

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/[/url]

> vbtskaccessingsqlserverusingwindowsintegratedsecur ity.asp
>

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnauth/html[/url]

> /dnauth_security.asp
>

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnauth/html[/url]

> /signfaq.asp
> [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;Q176377[/url]
>
> and a bunch of other documents and they all come down to two valid
> solutions: Basic Authentication or SQL Users. These are only valid if the
> level of security you wish to achieve is not something that needs to pass

a

> certain level of security (would not pass in industries that require

maximum

> security).
>
> If I am bound to NT Authentication, is my only option Basic Authentication
> (of course under SSL)? And why is it that we don't have these problems

with

> other Database vendors? Is there any way we can utilize ADSI to get the
> users NTLM credentials to pass on to SQL server?
>
> Any help or suggestions will be very appreciated.
>
> Thank you,
>
>
>
>

[Lewis Wang [MSFT]]

Hi Lior,

You may check this link for your reference.

Windows Integrated Security with IIS, ASP.NET, SQL Server across multiple
servers
[url]http://groups.google.com/groups?hl=zh-CN&lr=lang_zh-CN|lang_zh-TW|lang_nl|la[/url]
ng_en&ie=UTF-8&oe=UTF-8&frame=right&th=f63605f61d2b9a59&seekm=04b501c320 a8%2
49493bad0%24a301280a%40phx.gbl#link2

I think this problem may be better answered in
microsoft.public.inetserver.iis.security. Thanks.

Hope this helps.

Best regards,
Lewis

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| From: "Lior Amar" <lior_amar@hotmail.com>
| References: <uHPZbT#aDHA.2928@tk2msftngp13.phx.gbl>
| Subject: Re: ASP.NET + SQL Server Windows authentication
| Date: Wed, 27 Aug 2003 12:09:30 -0400
| Lines: 71
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <eRxXzVLbDHA.4020@tk2msftngp13.phx.gbl>
| Newsgroups:
microsoft.public.dotnet.framework.aspnet,microsoft .public.dotnet.framework.a
spnet.security,microsoft.public.sqlserver.security
| NNTP-Posting-Host: p113.n-sfpop03.stsn.com 199.107.154.113
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftn gp13.phx.gbl
| Xref: cpmsftngxa06.phx.gbl
microsoft.public.dotnet.framework.aspnet.security: 6451
microsoft.public.sqlserver.security:15430
microsoft.public.dotnet.framework.aspnet:171959
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.security
|
| Think the problem is just a limitation of NTLM single hop. Don't think
there
| is a way around it other than using SSL and Basic Authentication. ASPNET
is
| set up properly and is impersonating the user approriately. Don't think
| there is anyway around this limitation.
|
| Thanks for the help though
|
| Lior
|
|
| "Lior Amar" <lior_amar@hotmail.com> wrote in message
| news:uHPZbT#aDHA.2928@tk2msftngp13.phx.gbl...
| > Hey All,
| >
| > Trying to understand why I can not get SQL server to trust my IIS
server.
| I
| > have two machines set up, 1 App and 1 DB, and I'm trying to validate the
| > applications access to the DB server via NT Authentication. The App
comes
| in
| > via NTLM which from my understanding only supports Single hop security
| > delegation. So far I understand why it doesn't work, although seems to
me
| > like a very bad problem. Now, Basic Authentication will transfer the PW
| and
| > the UID which will allow IIS to login to the DB server and then NT
| > Authentication will work. But we all know how non-secure Basic
| > Authentication is.
| >
| > Here's the confusion, if Kerberos permits token transferring with no
| > limitation why can't IIS receive a token via NTLM and transfer it to the
| DB
| > server?
| >
| > I've been reading all of these articles
| >
| >
|
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/[/url]
| > vbconaccessingsqlserverfromwebapplication.asp
| >
|
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/[/url]
| > vbtskaccessingsqlserverusingwindowsintegratedsecur ity.asp
| >
|
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnauth/html[/url]
| > /dnauth_security.asp
| >
|
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnauth/html[/url]
| > /signfaq.asp
| > [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;Q176377[/url]
| >
| > and a bunch of other documents and they all come down to two valid
| > solutions: Basic Authentication or SQL Users. These are only valid if
the
| > level of security you wish to achieve is not something that needs to
pass
| a
| > certain level of security (would not pass in industries that require
| maximum
| > security).
| >
| > If I am bound to NT Authentication, is my only option Basic
Authentication
| > (of course under SSL)? And why is it that we don't have these problems
| with
| > other Database vendors? Is there any way we can utilize ADSI to get the
| > users NTLM credentials to pass on to SQL server?
| >
| > Any help or suggestions will be very appreciated.
| >
| > Thank you,
| >
| >
| >
| >
|
|
|

[Lewis Wang [MSFT]]

Hi Lior,

Here is an article about Configure an ASP.NET Application for a Delegation
Scenario. You may check it for your reference.
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;810572[/url]

Hope this helps.

Best regards,
Lewis

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "Lior Amar" <lior_amar@hotmail.com>
| References: <uHPZbT#aDHA.2928@tk2msftngp13.phx.gbl>
| Subject: Re: ASP.NET + SQL Server Windows authentication
| Date: Wed, 27 Aug 2003 12:09:30 -0400
| Lines: 71
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Message-ID: <eRxXzVLbDHA.4020@tk2msftngp13.phx.gbl>
| Newsgroups:
microsoft.public.dotnet.framework.aspnet,microsoft .public.dotnet.framework.a
spnet.security,microsoft.public.sqlserver.security
| NNTP-Posting-Host: p113.n-sfpop03.stsn.com 199.107.154.113
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftn gp13.phx.gbl
| Xref: cpmsftngxa06.phx.gbl
microsoft.public.dotnet.framework.aspnet.security: 6451
microsoft.public.sqlserver.security:15430
microsoft.public.dotnet.framework.aspnet:171959
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.security
|
| Think the problem is just a limitation of NTLM single hop. Don't think
there
| is a way around it other than using SSL and Basic Authentication. ASPNET
is
| set up properly and is impersonating the user approriately. Don't think
| there is anyway around this limitation.
|
| Thanks for the help though
|
| Lior
|
|
| "Lior Amar" <lior_amar@hotmail.com> wrote in message
| news:uHPZbT#aDHA.2928@tk2msftngp13.phx.gbl...
| > Hey All,
| >
| > Trying to understand why I can not get SQL server to trust my IIS
server.
| I
| > have two machines set up, 1 App and 1 DB, and I'm trying to validate the
| > applications access to the DB server via NT Authentication. The App
comes
| in
| > via NTLM which from my understanding only supports Single hop security
| > delegation. So far I understand why it doesn't work, although seems to
me
| > like a very bad problem. Now, Basic Authentication will transfer the PW
| and
| > the UID which will allow IIS to login to the DB server and then NT
| > Authentication will work. But we all know how non-secure Basic
| > Authentication is.
| >
| > Here's the confusion, if Kerberos permits token transferring with no
| > limitation why can't IIS receive a token via NTLM and transfer it to the
| DB
| > server?
| >
| > I've been reading all of these articles
| >
| >
|
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/[/url]
| > vbconaccessingsqlserverfromwebapplication.asp
| >
|
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/[/url]
| > vbtskaccessingsqlserverusingwindowsintegratedsecur ity.asp
| >
|
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnauth/html[/url]
| > /dnauth_security.asp
| >
|
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnauth/html[/url]
| > /signfaq.asp
| > [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;Q176377[/url]
| >
| > and a bunch of other documents and they all come down to two valid
| > solutions: Basic Authentication or SQL Users. These are only valid if
the
| > level of security you wish to achieve is not something that needs to
pass
| a
| > certain level of security (would not pass in industries that require
| maximum
| > security).
| >
| > If I am bound to NT Authentication, is my only option Basic
Authentication
| > (of course under SSL)? And why is it that we don't have these problems
| with
| > other Database vendors? Is there any way we can utilize ADSI to get the
| > users NTLM credentials to pass on to SQL server?
| >
| > Any help or suggestions will be very appreciated.
| >
| > Thank you,
| >
| >
| >
| >
|
|
|