SQL Server/IIS login problems

[Dylan]

I have written a simple web app that connects to a local instance of
SQL Server to run queries. The virtual directory in IIS is "myapp",
and there is a default.asp file in there. In myapp properties/
directory security/ authentication, I have disabled "basic
authentication" and "integrated windows authentication" and enabled
anonymous access. I have created a restricted user called "MYAPPUSER"
in Windows and this is the user that is granted anonymous access. I
have also executed the following query in my sql server to give access
to the user:

CREATE DATABASE MYAPPDB
EXEC sp_grantlogin 'COMPUTERNAME\MYAPPUSER'
USE MYAPPDB
EXEC sp_grantdbaccess 'COMPUTERNAME\MYAPPUSER'
EXEC sp_addrolemember 'db_owner', 'COMPUTERNAME\MYAPPUSER'

If I try to access my sql server with this connection string:

"Provider=sqloledb;Integrated Security='SSPI';Initial Catalog=myapp;"

I get:

Error Type: Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'COMPUTERNAME\MYAPPUSER'.
/myapp/default.asp, line 34

If I change this user to an Administrator, I can access the sql server
fine. However, I don't want to give the user Administrator priveliges.
Is there some way I can restrict the users access while still allowing
to it to log in to sql server?

Furthermore, if I try to access this page remotely on a Windows XP
machine, I get the error:

HTTP 401.2 - Unauthorized: Logon failed due to server configuration
Internet Information Services

I don't think this even has anything to do with the fact that the asp
accesses the sql server, rather it's just plain not able to access the
files or something.

Perhaps I'm going about this all wrong. Given the specifications I've
outlined, could anyone perhaps let me know what would be the best way
to lay out my access control for this? I need to be able to access the
sql server, but I don't want to have to enter a user name and password
every time I go to the page like with Basic Authentication. And I'm
potentially going to need to access the page through a firewall or via
a proxy server, so I can't use Integrated Windows Authentication.
That's why I chose Anonymous Access...

I'm stuck! Help!

Dylan

[Ken Schaefer]

Hi there:

a) Is anonymous access enabled in the IIS MMC Snapin for the website in
question?

b) Is the anonymous internet user account (in the IIS MMC Snapin) set to:
'COMPUTERNAME\MYAPPUSER' (and optionally, have you either entered the
appropriate password manually -or- checked the box that says "Allow IIS to
control password"?)

c) Does: 'COMPUTERNAME\MYAPPUSER' have NTFS Read/Execute permissions for the
..asp files in question that you want to run?

If all the above are correct, then:
a) you should be able to access the site "anonymously".
b) IIS will use the configured anonymous account ('COMPUTERNAME\MYAPPUSER')
to read the .asp pages off the disk
c) the ASP pages attempt to connect to the database under the current NT
account context, which is 'COMPUTERNAME\MYAPPUSER', and you have granted
that account permission to login to the database.


Cheers
Ken




"Dylan" <heavier@hotmail.com> wrote in message
news:55b5617b.0307081248.5e27d6e9@posting.google.c om...
: I have written a simple web app that connects to a local instance of
: SQL Server to run queries. The virtual directory in IIS is "myapp",
: and there is a default.asp file in there. In myapp properties/
: directory security/ authentication, I have disabled "basic
: authentication" and "integrated windows authentication" and enabled
: anonymous access. I have created a restricted user called "MYAPPUSER"
: in Windows and this is the user that is granted anonymous access. I
: have also executed the following query in my sql server to give access
: to the user:
:
: CREATE DATABASE MYAPPDB
: EXEC sp_grantlogin 'COMPUTERNAME\MYAPPUSER'
: USE MYAPPDB
: EXEC sp_grantdbaccess 'COMPUTERNAME\MYAPPUSER'
: EXEC sp_addrolemember 'db_owner', 'COMPUTERNAME\MYAPPUSER'
:
: If I try to access my sql server with this connection string:
:
: "Provider=sqloledb;Integrated Security='SSPI';Initial Catalog=myapp;"
:
: I get:
:
: Error Type: Microsoft OLE DB Provider for SQL Server (0x80040E4D)
: Login failed for user 'COMPUTERNAME\MYAPPUSER'.
: /myapp/default.asp, line 34
:
: If I change this user to an Administrator, I can access the sql server
: fine. However, I don't want to give the user Administrator priveliges.
: Is there some way I can restrict the users access while still allowing
: to it to log in to sql server?
:
: Furthermore, if I try to access this page remotely on a Windows XP
: machine, I get the error:
:
: HTTP 401.2 - Unauthorized: Logon failed due to server configuration
: Internet Information Services
:
: I don't think this even has anything to do with the fact that the asp
: accesses the sql server, rather it's just plain not able to access the
: files or something.
:
: Perhaps I'm going about this all wrong. Given the specifications I've
: outlined, could anyone perhaps let me know what would be the best way
: to lay out my access control for this? I need to be able to access the
: sql server, but I don't want to have to enter a user name and password
: every time I go to the page like with Basic Authentication. And I'm
: potentially going to need to access the page through a firewall or via
: a proxy server, so I can't use Integrated Windows Authentication.
: That's why I chose Anonymous Access...
:
: I'm stuck! Help!
:
: Dylan

[Dylan Smith]

> a) Is anonymous access enabled in the IIS MMC Snapin for > the website
in

> question?

Yes.

> b) Is the anonymous internet user account (in the IIS MMC > Snapin)

set to:

> 'COMPUTERNAME\MYAPPUSER' (and optionally, have you either > entered

the

> appropriate password manually -or- checked the box that
> says "Allow IIS to
> control password"?)

Yes, I control the password. There is none (i.e: "").

> c) Does: 'COMPUTERNAME\MYAPPUSER' have NTFS Read/Execute > permissions

for the

> ..asp files in question that you want to run?

In W2k, yes. I set the permissions. In XP, I did not set them
explicitly, but even when I set the user to an admin, I get 401.2.

> If all the above are correct, then:
> a) you should be able to access the site "anonymously".
> b) IIS will use the configured anonymous account
> ('COMPUTERNAME\MYAPPUSER')
> to read the .asp pages off the disk
> c) the ASP pages attempt to connect to the database under > the

current NT

> account context, which is 'COMPUTERNAME\MYAPPUSER', and
> you have granted
> that account permission to login to the database.

Agreed. I would think that it should work. But it doesn't. Like I said,
in W2k, it works if I set the user to an admin, and in XP, it doesn't
work no matter what.

I'm pretty sure the XP problem is a windows permissions problem, because
I just plain can't access the page. But in W2k, it's a SQL problem. What
are the differences in security b/w W2k and XP? I was under the
impression that they're basically the same (i.e: NT5)...

Thanks for the response,

Dylan


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Ken Schaefer]

Hi,

The pages are running on the same machine right? (ie in the first case you
are accessing the website by using a browser on the server, and in the
second case you are accessing the same webserver, but remotely from a
WindowsXP machine)

Or did I intepret your original post incorrectly?

Cheers
Ken

"Dylan Smith" <heavier@hotmail.com> wrote in message
news:%235Am7xhRDHA.2768@tk2msftngp13.phx.gbl...
: > a) Is anonymous access enabled in the IIS MMC Snapin for > the website
: in
: > question?
:
: Yes.
:
: > b) Is the anonymous internet user account (in the IIS MMC > Snapin)
: set to:
: > 'COMPUTERNAME\MYAPPUSER' (and optionally, have you either > entered
: the
: > appropriate password manually -or- checked the box that
: > says "Allow IIS to
: > control password"?)
:
: Yes, I control the password. There is none (i.e: "").
:
: > c) Does: 'COMPUTERNAME\MYAPPUSER' have NTFS Read/Execute > permissions
: for the
: > ..asp files in question that you want to run?
:
: In W2k, yes. I set the permissions. In XP, I did not set them
: explicitly, but even when I set the user to an admin, I get 401.2.
:
: > If all the above are correct, then:
: > a) you should be able to access the site "anonymously".
: > b) IIS will use the configured anonymous account
: > ('COMPUTERNAME\MYAPPUSER')
: > to read the .asp pages off the disk
: > c) the ASP pages attempt to connect to the database under > the
: current NT
: > account context, which is 'COMPUTERNAME\MYAPPUSER', and
: > you have granted
: > that account permission to login to the database.
:
: Agreed. I would think that it should work. But it doesn't. Like I said,
: in W2k, it works if I set the user to an admin, and in XP, it doesn't
: work no matter what.
:
: I'm pretty sure the XP problem is a windows permissions problem, because
: I just plain can't access the page. But in W2k, it's a SQL problem. What
: are the differences in security b/w W2k and XP? I was under the
: impression that they're basically the same (i.e: NT5)...
:
: Thanks for the response,
:
: Dylan
:
:
: *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
: Don't just participate in USENET...get rewarded for it!