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.

 

[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,