ASP.Net Application - SQL Server 2000 Access Problem on Windows 2003 server from XP

[Vaap]

I did lot of googling to see if I can solve the SQL server not found
problem while trying to run ASP.Net community starter kit from an XP
machine to Windows 2003 server hosting SQL server 2000 database. Tried
all possible combinations but it still fails.

I have Windows 2003 server having SQL Server 2000 installed with SP2.
The installation went Ok on a XP professional machine and I was able
to create database and user logins etc on Windows 2003 server through
installation.

If I use this site from Windows 2003 server itself then there is no
problem. I am sure that this is access problem and I tried all
possible combinations but this does not work.

The installation created domain\ASPNET account in SQL server. The SQL
server accepts both windows and SQL server authentication. The
connection string uses 'sa' account and I am able to connect using
this account through server explorer in VS, query analyzer and
Enterprize manager but it fails when I bring up the site.

My questions:

1. The local machine (Win XP pro) hosting this site is using
localmachine\ASPNET account and this works if I connect to a SQL
server on Windows 2000 server but it fails if SQL server is in Windows
2003. What is different in Windows 2003 that I need to do to make it
work. I even granted administrator privilege to domain\ASPNET account
but that did not help. I changed machine.config also to make
impersonate=true and user=domain\ASPNET but that also did not work. I
made sure that site has security rights on domain\ASPNET account.

2. Even if Impersonate=true would have worked, I was not going to use
this approach as it will fail my other sites connecting to other SQL
server and Oracle. What am I missing here?

3. If I try to grant access to localmachine\ASPNET to SQL server
through Enterprize manager then I get error.

-- This works if I do for domain\ASPNET
use MASTER
go

exec sp_revokedbaccess 'ASPNET'
exec sp_revokelogin 'domain\ASPNET'
go

use communitystarterkit
go

exec sp_revokedbaccess 'ASPNET'
exec sp_revokelogin 'domain\ASPNET'
exec sp_grantlogin 'domain\ASPNET'
exec sp_defaultdb 'domain\ASPNET','communitystarterkit'
exec sp_grantdbaccess 'domain\ASPNET', 'ASPNET'
exec sp_addrolemember 'db_owner', 'ASPNET'
go

-- This fails if I do it for localmachine\ASPNET
exec sp_grantlogin 'localmachine\ASPNET'
The error is
Windows NT user or group 'localmachine\ASPNET' not found. Check the
name again. The enterprize manager's dialog box shows the account but
SQL server command fails.

If you have configured ASP.net from Windows XP to SQL server running
on Windows 2003, please let me know if I am missing anything.

Thanks for your help.

 

[Vaap]

This was a very tough problem to solve and I am posting my results so
that others might benefit from it. The issue is with accessing SQL
server on Windows 2003 from another machine through ASP.Net.

After lot of reading and googling, I found following steps and I do
not understand fully well their rationale but the end result is - it
worked.

If SQL Server is installed on Windows 2003 server and you are
attempting to access this through ASP.Net from a remote machine then
there are problems in remote access.

The domain should have domain\ASPNET account and it should have a
strong password. On you local machine, modify ASPNET password to
strong password say admin123. The password on both machines should be
same.

Go to IIS console Directory>Security>Edit on your virtual directory.
Check annonymous access and specify user name and password as ASPNET
and password as admin123. Make sure that the "Allow IIS to control
password" is unchecked. Integrated Windows Authentication should be
checked for debugging the application.

Go to SQL Server Enterprize manager console.

Add account domain\Administrator and make sure that it has System
Administrator Server role assigned. Otherwise SQL Agent will not
start.

Add account domain\ASPNET and allow access to your database and grant
db_owner (??)

Go to your web.config and add a line <identity impersonate="true" />
immediately after <authentication> tag.

The connection string has sql server login say 'sa' and SQL server was
in mixed mode of authentication.

After above, the connection to SQL Server should work. There might be
some other way but this worked for me after lot of combinations. I did
not want to change machine.config as it affects other applications.
The other posts suggested granting "system" privilege or "as a part of
operating system" to ASPNET account but it did not work for me and
changing machine.config for impersonating the use account.

 

[bk]

Vikram
I think you might be the preson who can take me out of my missery. I
have been going crazy with a problem similar to yours. I have been
googling like mad but with no luck until I came across your posting. I
get "SQL Server is unavailable or does not
exist" error while developing asp.net application in vs2003's IDE
development mode. The sql2000 is installed on a windows 2003 server. I
am able to connect to server using Query Analyzer and Enterprise
Manager. My connection string is:

string sConStr= "Server = 192.168.0.28;User ID=myuser;Initial
Catalog=pubs;Password=mypassword"

I use the same connection string succesfully connecting to a windows
2000 server with sql2000 with exactly the same database.

My development machine is a XP with SP2. My server machine is a member
of a domain which I have no control.

I have not yet applied your solution but I can not understand why it
should be necessary to go through such complex steps to solve a problem
arising from simple use of standard Microsoft tools and components.

 

[Andy]

might be the prob with SP2's firewall feature. try to re-configure the
firewall to allow any traffics from/to sql server.
rgds,