Prob: Connecting to MSSQL using windows authentication

[Peter Lykkegaard]

Hi

I have a problem connecting to MSSQL using windows authentication on
an intranet website
I need to enable integrated windows authentication in IIS6 (Windows
2003 server) and connect to mssql as individual windows users due to
some auditing
Number of people allowed access to the site is limited (4-6)

I have created a new website running on a specified port
ASP.NET 2 is enabled
Integrated windows authentication is enabled
The site is using its own app pool (running with the network service
account)

C# classes/files etc is copied to a directory on the server

Webconfig is edited with this content

<system.web>
<identity impersonate="true"/>
<authentication mode="Windows"/>
<authorization>
<deny users="?"/> <!-- Deny anonymous access -->
</authorization>
</system.web>

My mssql connection is using SSPI

<add name="*************"
connectionString="Data Source=*********;
Initial Catalog=************;
Integrated Security=SSPI;"
providerName="System.Data.SqlClient"/>

The database is located on an server different from the webserver

-----------------
When I start IE on the webserver everything is running ok
When I start IE on my own local computer I get this error:

Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 17-04-2009 15:10:33
Event time (UTC): 17-04-2009 13:10:33
Event ID: c079d364ccb34dcd96ad4b840931f0ad
Event sequence: 4
Event occurrence: 1
Event detail code: 0

Application information:
Application domain: /LM/W3SVC/1643659441/
Root-5-128844474292801572
Trust level: Full
Application Virtual Path: /
Application Path: D:\WebSites\<webfolder>
Machine name: <webserver>

Process information:
Process ID: 3380
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK SERVICE

Exception information:
Exception type: SqlException
Exception message: Login failed for user 'NT AUTHORITY\ANONYMOUS
LOGON'.

Request information:
Request URL: <url>
Request path: <aspx-file>
User host address: <host-ip>
User: <domain>\myuseraccount>
Is authenticated: True
Authentication Type: Negotiate
Thread account name: NT AUTHORITY\NETWORK SERVICE


Maybe it's an IIS issue?
Just wondering if I missed anything obvious?

I have tried to google through various sites/blogs etc about the topic
but unfortunately I haven't find the key to solve my problem

Thanks in advance

- Peter

 

[Cowboy \(Gregory A. Beamer\)]

There are two ways to solve this:

1. Switch to a non SSPI connection string with an account. This means
everyone uses the same account, so it might not work for you. The windows
auth still stops an unauthorized user from using the site, but any
authorized user can see the data.

2. Set up impersonation so your account, rather than NETWORK SERVICE, is
sent to SQL Server. I don't have a link, so this is a google topic.

#1 is quicker, but it can be less security than you need. #2 is better
security, but you will have to spend a bit of time learning.

 

[bruce barker]

window authentication (ntlm) has a 1 hop rule. to get around this you
have the following options:

host the sqlserver on the webserver

switch to basic authentication (be sure to switch to ssl). this will
give the webserver a primary token it can use to access the sqlserver

switch to kerberos and enable credentials forwarding on the desired servers.

-- bruce (sqlwork.com)

 

[Peter Lykkegaard]

There are two ways to solve this:

1. Switch to a non SSPI connection string with an account. This means
everyone uses the same account, so it might not work for you. The windows
auth still stops an unauthorized user from using the site, but any
authorized user can see the data.

I am using SQL authentication atm

2. Set up impersonation so your account, rather than NETWORK SERVICE, is
sent to SQL Server.

That's is my problem in a nutshell

I don't have a link, so this is a google topic.

After what I could find using Google it looks like impersonation is
setup correctly
I am not sure whether it's asp.net, iis, windows or mssql I need to
concentrate on

#1 is quicker, but it can be less security than you need. #2 is better
security, but you will have to spend a bit of time learning.

Spending time learning is not an issue I just need a little push in
right direction

Please feel free to ask if I have failed to add all relevant/needed
information

Thanks /Peter

 

[Ian McCaul]

What Identity is the Application Pool using... this is probably NETWORK
SERVICE. You could change the identity to use a domain account that has
rights to the web server directories and sql server database.

 

[Peter Lykkegaard]

What Identity is the Application Pool using... this is probably NETWORK
SERVICE.
Correct

You could change the identity to use a domain account that has
rights to the web server directories and sql server database.

I need to have the actual window account who is working with the
dataset for auditing reasons

I have a range of triggers on the tables which stores changes (updates/
inserts/deletions)
This is done on the tables then all apps etc are using the same
auditing system

- Peter

 

[Peter Lykkegaard]

window authentication (ntlm) has a 1 hop rule.

Ahh thanks, that'll explain my "little" problem

to get around this you have the following options:
host the sqlserver on the webserver

Sorry not possible

switch to basic authentication (be sure to switch to ssl). this will
give the webserver a primary token it can use to access the sqlserver

Ok one way of doing it
Could SSL and Challenge/Response do the same trick (probably not)

switch to kerberos and enable credentials forwarding on the desired servers.

I have read a few things about kerberos, I'll give this solution a
try

I was afraid I had misunderstood something obvious and asp.net related

Thanks /Peter

 

[Cowboy \(Gregory A. Beamer\)]

here are some google results. I went through and looked quickly at some that
might help. I have always like Rick Strahl's blog:
http://www.west-wind.com/WebLog/posts/2153.aspx
http://www.west-wind.com/WebLog/posts/1572.aspx

If you want to code it:
http://www.codedigest.com/CodeDigest/9-Identity-Impersonate-at-Code-Level-in-ASP-Net.aspx
http://aspnet101.com/aspnet101/tutorials.aspx?id=68

Config is a bit easier, but a bit more limited.

One difficulty here is where the browser thinks it is. If the site is in the
Intranet zone, it makes things a bit easier, as it will pass credentials
without a pop up. I assume this is an Intranet site?