Db Connection Failure

[GD]

I've downloaded the ASP.Net Commerce Starter kit (Sample ASP .Net
application) from the Microsoft website and have set it up to run using a
SQL Server on a remote machine.

My problem is that the app can't find the database. Using debug mode I've
checked that the correct connection string is being applied and I've written
a quickie VB .Net app to check that the connection string/Logon details are
valid. In the VB app the DB connects without any problem.

I suspect the problem has something to do with the fact that the Db access
is being attempted from a DLL (ASPNETCommerce.dll) within the IIS app. To
this end I've disabled the firewall on both machines...but still nothing.
Using SQLProfiler I can see that no connection attempt is ever made to the
Db.

Is there a setting in IIS that I've overlooked ?

The error reported is - System.Data.SqlClient.SqlException: SQL Server does
not exist or access denied.

Both machines are Windows 2000, SP 4. I am using .Net framework 1.1

Thanks

Gary

 

[Hayato Iriumi]

There is a way to check the connection to the database.

1. Create a text file on your desktop and rename it as Test.udl
2. Double click on the file.
3. Click on the Provider tab and choose Microsoft OLE DB Provider for SQL
Server.
4. Enter the SQL Server name, User name, and Password.
5. Choose the target database
6. Finally, click on Test Connection.

 

[GD]

Thanks - I've done that ... and as I suspected, the connection succeeded. I
think there is a security/rights issue around the DLL thats attempting the
connection from IIS.

Gary

 

[Hayato Iriumi]

Wait a minute, if the DLL uses the same connection information to
connect to the database, it should work. If the test works, and the DLL
is using the same exact connection information, there should be no
different. Now, are you using Windows Integrated security to conenct to
the database? If so, then you should really look into who you are
running the DLL as. From what I read, you use the DLL from your web app,
so it's probably running as aspnet account.

 

[GD]

After a bit of searching around in MSDN I found the solution.

The problem stems from the fact that Aspnet_wp.exe runs under the default
ASPNET account on the local machine and does not have access to the remote
machine that is hosting SQL Server.

The solution was to enable "Impersonation" in the Web.Config file and
supplying a valid username and password for the remote machine.

e.g.
Add the line
<identity impersonate="true" userName="RemoteMachineName\UserName"
password="myPass"/>

From a security point of view this could be seen as a bit lacklustre so you
could also enter the username and pwd into the registry ( full details are
in the .Net help files )

Anyone interested should take a look at Kb article 315159 (
http://support.microsoft.com/default.aspx?scid=kb;en-us;315159 ) and then
look up "ASP Impersonation" in the .Net help file.

Gary