Login Fails for SQL Server from Web Service

[Coy]

I've added the ASPNET user to my local SQL Server 2000,
but I still get an unhandled exception: Login failed for
user 'C594891-A\ASPNET'. This is when using a web
service. A similiar ASP.NET application works perfectly
fine, even before I gave any access to the ASPNET account.

I read KB article 316989. In SQL Server 2000, I've added
a login, dbaccess, and granted full rights to the
'C594891-A\ASPNET user. This doesn't make any difference.
The article describes an aspnet_wp "security context" or
account. There is no such account, and SQL Server can't
do anything about any secuity context. Since you can't use
ASPNET to log onto XP, I haven't tested queries in the
database, but I did added the same rights to another account
and when I log on with Windows Integrated security and start
Query Analyzer, that account can do anything in the database.
Anyway, the failure is at login, so I'm guessing web services
need something different to pass the login, than ASPNET
applications require.

The web.config files are the same for the web service, as
for the ASP.NET application. Both are IIS applications.
The application displays data fine, but the web service can't
even log on. Is there a way to trace how IIS is handling the
request.

Below are the non-vanilla portions of the web.config.
<appSettings>
<add key="ConnectString"
value="Server=(local);Database=nes;Trusted_Connection=Yes"></add>
</appSettings>
<authentication mode="Windows" />

Thanks

 

[Mas Jabier]

Don't use Trusted Connection.

Instead use usual SQL connection string used to access SQL
Server :
<add key="ConnectString"
value="Server=(local);Database=nes;user id=C594891-
A\ASPNET;password='xxx';database=xxxx"></add>

Hope this helps

Jody Ananda
MCAD.NET,MCSD.NET
"All programs are poems, it just not all programmers are
poets."

 

[Carl Prothman [MVP]]

Coy,
Try using "Integrated Security=SSPI" rather than "Trusted_Connection=Yes".
http://www.able-consulting.com/dotnet/adonet/Data_Providers.htm#SQLClientManagedProvider

Also, if you really want the calling User's credititals to be used,
rather than the ASP..NET's work processs credititals, then make sure
to add a <identity impersonate="true" /> to your web.config file.

You can run a SQL Profiler (Start | All Programs | Microsoft SQL Server |
Profiler) to see what which user is connecting, and what SQL commands
are being passed in. A very handy debugging tool!
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_86ib.asp

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP

Hire top-notch developers at
http://www.able-consulting.com

 

[Coy]

Okay,
with <identity impersonate="true" /> I see my data. However,
I find that it is impersonating me, not the ASPNET account.
This implementation is a Windows Forms application reaching
out to a Web Service. I want all incoming requests to impersonate
the same Windows NT account. I thought ASPNET was going to
be that account, but I thought wrong. Anyway, I don't want the
name and password of such an account to be in the web.config
as that seems sloppy and old fashioned. I guess I need to find out
how the ASP.NET role based security works and try to set that up.
Thanks for your help.

 

[Carl Prothman [MVP]]

Okay,
with <identity impersonate="true" /> I see my data. However,
I find that it is impersonating me, not the ASPNET account.

That is what impersonate="true" means... ;-)

This implementation is a Windows Forms application reaching
out to a Web Service.
the same Windows NT account. I thought ASPNET was going to
be that account, but I thought wrong. Anyway, I don't want the
name and password of such an account to be in the web.config
as that seems sloppy and old fashioned.

With Anonymous Authentication enabled for a Web Service web site,
and authentication mode set to Windows with impersonate set to false
in the web.config file, I was able to have the ASPNET account
(on Windows XP) and NT AUTHORITY\NETWORK SERVICE (on
Windows Server 2003) be able to select data from the Northwind database
after adding the corresponding account as SQL Server users and giving
the account read rights to the Northwind database.

Are you sure you the ASPNET user account is being used? Try adding the
following code to your web service, then step through and see which account
is being used.
Dim username As String = WindowsIdentity.GetCurrent().Name

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP

Hire top-notch developers at
http://www.able-consulting.com

 

[Carl Prothman [MVP]]

Are you sure the ASPNET user account is being used? Try adding
the following code to your web service, then step through and see
which account is being used.
Dim username As String = WindowsIdentity.GetCurrent().Name

Also, exactly how did you add the account as a SQL Server user with
permissions to the database?

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP

Hire top-notch developers at
http://www.able-consulting.com

 

[Carl Prothman [MVP]]

Adding the required access to use a SQL server database
requires 3 steps:

Did you add the user account to SQL Server first?
e.g. sp_addlogin

use Tunes
exec sp_grantlogin 'C594891-A\ASPNET

Also call "sp_defaultdb" and "sp_defaultlanguage"
e.g.
exec sp_defaultdb N'CARL20\ASPNET', N'Northwind'
exec sp_defaultlanguage N'CARL20\ASPNET', N'us_english'

Make sure NOT to give the user account access to the "master"
database (for the default database).

use Tunes
exec sp_grantdbaccess 'C594891-A\ASPNET'

Isn't that
EXEC sp_grantdbaccess N'CARL20\ASPNET', N'ASPNET'

use Tunes
Grant select, insert, delete on Tunes to 'C594891-A\ASPNET'

Typically you grant execute permissions on stored procedures
and not on tables.

I no longer think it is a good idea to grant any database
access specifically to ASPNET, since ASPNET is
effectively the anonymous account. This means I've
actually invited all anonymous users into my database.

I agree, but if you only give exec permissions to selected
Stored Procedures, it should be fine.

Also, I shouldn't be using Windows authentication for
Internet users, since they obviously don't have domain
accounts. I think I can either use basic authentication
over SSL

Basic Auth still validates against a Domain acccount.

or possibly Passport - whatever I can do
when I don't control or provide the client interface
that is contacting my web service. No much change
here since the days of ASP?

Next I need to see if it is a good idea to impersonate
some domain account for the database access. I would
need to log on previously authenticated users as that
domain account. I'd set up database access for that account
instead of ASPNET. However, I'm also not sure how to
log on a user programmatically from inside a web service
in order to use that strategy.

If you truly need Anonymous Authentication (which it sounds like you do),
then I would keep Anonymous Authentication enabled on on the Web Service
web site and use either:

1) Use a trusted connection string and impersonate a user account which has access
to the database and it's username/password is stored in the system registery for
secuirty reasons:
e.g.
<configuration>
<system.web>
<identity>
userName="registry:HKLM\Software\AspNetIdentity,Name"
password="registry:HKLM\Software\AspNetIdentity,Pwd"
</identity>
</system.web>
</configuration>
http://msdn.microsoft.com/library/en-us/cpgenref/html/gngrfidentitysection.asp

2) Or use a standard SQL Server username / connection string. You can encrypt
the SQL Server username and password if you like.
http://msdn.microsoft.com/library/en-us/dnbda/html/daag.asp
- See section "Storing Connection Strings"

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP

Hire top-notch developers at
http://www.able-consulting.com

 

[Coy]

We never use sp_addlogin these days, its for SQL Server
authentication only. That's a high maintenance login since
you have to create and maintain it on every server every
time a password needs to change.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
sp_adda_0q7i.asp

Therefore you can see that while you're using
<authentication mode="Windows" />
such a login is never interrogated.

Anyway, you can do:
exec sp_helplogins on any machine around you and see that
the domain ASPNET account has already been installed by
the Visual Studio setup. Master is its default database. So
you don't have to add any login for it, at least on development
machines.

Since you're not doing any authentication and ASPNET has
sufficient access permissions you haven't done anything we
all haven't done. For a web service, obviously the database
login will be deliberate, and the granted permissions strict.
IIS won't be set to anonymous for me. I'm flipping through
MSDN magazine to see which authentication implementations
have the Redmond seal of approval. I'll make my points
here later in the week.

I'm saving all your other points on the database setup:

Also call "sp_defaultdb" and "sp_defaultlanguage"
e.g.
exec sp_defaultdb N'CARL20\ASPNET', N'Northwind'
exec sp_defaultlanguage N'CARL20\ASPNET', N'us_english'

Make sure NOT to give the user account access to the "master"
database (for the default database).

Typically you grant execute permissions on stored procedures
and not on tables.

I don't get use any N's in Query Analyser. I guess that's essential for
enforcing wide character strings under .NET when programmatically
building strings for queries.

I no longer think it is a good idea to grant any database
access specifically to ASPNET, since ASPNET is
effectively the anonymous account. This means I've
actually invited all anonymous users into my database.

I agree, but if you only give exec permissions to selected
Stored Procedures, it should be fine.

I like the idea of using sp_addrole and sp_addrolemember
to manage permissions for a set of role members.

Also, I shouldn't be using Windows authentication for
Internet users, since they obviously don't have domain
accounts. I think I can either use basic authentication
over SSL

Basic Auth still validates against a Domain acccount.

Yes, I overlooked this. However, the username and
password can also be validated by other means.

If you truly need Anonymous Authentication (which it sounds like you do),
then I would keep Anonymous Authentication enabled on on the Web Service
web site and use either:

No I don't want Anonymous, I will present a login screen as
soon as necessary.

1) Use a trusted connection string and impersonate a user account which has access
to the database and it's username/password is stored in the system registery for
secuirty reasons:
e.g.
<configuration>
<system.web>
<identity>
userName="registry:HKLM\Software\AspNetIdentity,Name"
password="registry:HKLM\Software\AspNetIdentity,Pwd"
</identity>
</system.web>
</configuration>
http://msdn.microsoft.com/library/en-us/cpgenref/html/gngrfidentitysection.a
sp

2) Or use a standard SQL Server username / connection string. You can encrypt
the SQL Server username and password if you like.
http://msdn.microsoft.com/library/en-us/dnbda/html/daag.asp
- See section "Storing Connection Strings"

I'll go read about this and similar topics.

Thanks

 

[Coy]

How could the error message text from the
database driver be wrong?

I added that code anyway. However I don't know
how to break in the web service when I start from
a windows forms client on the same machine. Therefore
I don't get to see the assignment. I suppose I should
just trace it instead of breaking, but I haven't done
so as yet. Anyway, I need to set IIS away from
anonymous before that code runs. I'll try Passport,
Basic and other authentication. Right now, when
I debug the web service directly, the principal is
me, but of course that is a bogus test.

Thanks