How do I access database via virtual folder which points at a remote share

Question

I'm having a problem getting access to a database which lives on a remote server.

I have a ASP.NET 2.0 webpage that is trying to connect to a database.
The database is accessed via a virtual folder (which I set up in IIS).
The virtual folder points at a remote share which contains the database.

The virtual folder (in the web apps root directory) is pointing at a share on a remote server via a UNC path:

\\databaseServerName\databaseFolder$\

The virtual folder has 'read' and 'browse' permissions set to 'true'.

I store the connection string in the 'appSettings' section of the web.config:

<add key="conStrVirtual" value="Provider=Microsoft.Jet.OleDb.4.0;Data Source=http://webAppServerName/virtualFolderName/databaseName.MDB;Jet OLEDB:Database Password=dumbPassword;"/>

The connection object is declard on my .aspx page:

Dim objConnVirtual As New OleDbConnection(ConfigurationManager.AppSettings("conStrVirtual"))

Here is the code that tries to use the connection object:

Public Sub Test()
 If objConnVirtual.State <> ConnectionState.Open Then
  objConnVirtual.Open()
 End If
 Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM TableName", objConnVirtual)
 objDR = cmd.ExecuteReader()
 If objDR.Read() Then
  response.write("Shazaam! Data shows up here")
 End If
 objDR.Close()
 objConnVirtual.Close()
End Sub

When I run the above code I get the following error (on this line of the code 'objConnVirtual.Open()':
Exception Details: System.Data.OleDb.OleDbException: Not a valid file name.

I have checked the database name and it is correct (even copy/pasted it to make sure)

If I put the 'Data Source' section of the connection string into the address bar of my browser I can successfully see the contents of the share on the remote server.

Not sure if this is a problem with permissions or with the code.
I have googled the crap out of this but have not been able to find a solution.

Any help is much appreciated.

Answer 1

make sure the two servers have internal access to each other and also specify the ip & port of db server in your connection string .

Answer 2

When accessing a remote Access MDB database, you have to specify a UNC path like \\remoteMachine\Share\test.mdb.

Make sure your application pool identity has the right permissions to connect to the remote share. By default on IIS 6 you are working with the Network Service account, which is by default not allowed to access a remote share.

The best way is to let the AppPool run with a dedicated service user.

Answer 3

Update

I should also mention that it works on my machine (but not once loaded up to the production box) if I declare the connection string in the 'appSettings' section of the web.config like this:

<add key="conStrVirtual" value="Provider=Microsoft.Jet.OleDb.4.0;Data Source=\\databaseServerName\databaseFolder$\databaseName.MDB;Jet OLEDB:Database Password=dumbPassword;"/>

This leads me to think that it could be an issue with needing to use domain credentials other than the local IUSER account.

Answer 4

What is the account being used on your server when your web app tries to read the db file? Whatever this user account is, it needs to have permissions to read that folder/file. In IIS6 you can configure the virtual folder to use any user account... on the Directory Security tab there's an Edit button under Authentication and access control.

It seems likely that your error message is just a generic error message, and the permissions problem is your real issue.

Answer 5

UPDATE

First up, thank you to everyone who submitted answers.

However, we ended up not using the 'connect to remote database via virtual folder' method because the complexity of the permissions needed to get this to work was causing us more problems than it was worth. We put the UNC path back into the connection string, which may not be the best way to do this, but is working for us.