the situation is this: we have 2 computers, one is for local store (let's call it backend) and the other is a web server (let's call it frontend). Both are PCs running Windows Server 2003 SP2 and both have the SQL Server 2005 Enterprise Edition installed. The webserver is using IIS for ASP Classic pages. The backend PC computer name is dataone.
My need is this: I want to have a database on the backend PC used for local store and access it through ASP Classic on the frontend PC.
What I have done so far: On the backend PC I created a database and called it zkp_database, then a table zkp_users and put some data into it. I got the SQL Server on the frontend PC to connect to the SQL Server on the backend PC. Now I and can manipulate zkp_database from the webserver. On the frontend PC I went Administrative tools->ODBC and mapped zkp_database through the backend's SQL Server fine and well. I used Windows NT authentication. The alias I used is zkpDB.
I have been trying for almost two days now to get a connection string so that ASP on the frontend PC can manipulate the database on the backend PC. Believe me I have tried everything there is among the most popular visited pages with connection string examples on the web!
The ASP errors I get vary with the connection string I am using, in general most of them complain about not finding the right user or the user not being authenticated correctly.
On the backend PC I created a user and a password for zkp_database (I didn't use the sa account), then on the frontend PC I changed the ODBC configuration so that SQL authentication is used -- and at that point the ODBC dialog window simply won't connect!
What am I supposed to do? ... I am at my wits end here.
Software/Hardware used: Windows Server 2003, SQL Server 2005