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

Discussion in 'ASP .Net' started by Vaap, Mar 7, 2004.

  1. Vaap

    Vaap Guest

    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, Mar 7, 2004
    #1
    1. Advertising

  2. Vaap

    bk Guest

    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.




    Vaap wrote:
    > 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, Dec 30, 2004
    #2
    1. Advertising

  3. Vaap

    Andy Guest

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

    "bk" <> wrote in message
    news:...
    > 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.
    >
    >
    >
    >
    > Vaap wrote:
    > > 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.

    >
     
    Andy, Jan 1, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Lori
    Replies:
    9
    Views:
    568
  2. C Did
    Replies:
    3
    Views:
    4,036
    Chris Lithgow
    Jun 20, 2006
  3. Lori
    Replies:
    9
    Views:
    214
  4. Adnan Hebibovic

    Windows 2003, SQL Server 2000 and Webservice

    Adnan Hebibovic, Jan 26, 2005, in forum: ASP .Net Web Services
    Replies:
    3
    Views:
    195
    [MSFT]
    Jan 28, 2005
  5. Denis

    Access 2000 or SQL Server 2000

    Denis, Jan 25, 2004, in forum: ASP General
    Replies:
    1
    Views:
    240
    Nicole Calinoiu
    Jan 25, 2004
Loading...

Share This Page