ASP.Net web site on IIS connecting to Access DB with Links to SharePoint lists on another machine

Question

Dear All,

My Problem is the following,

I have an ASP.Net web site hosted on IIS. This web site connects to an Access 2007 DB file on the same server. The DB file contains links to sharepoint lists on another server on the same domain. If I run the web site on the visual studio built-in server, I can Open connection to the DB file and retrieve data just fine. But whenever I try to run the web site over the IIS, the web site can't open a connection with the DB file.

What do you think the problem is?

Thanks.

Answer 1

Have a look at this article:

How to connect to a remote Access database from Active Server Pages
http://support.microsoft.com/kb/253580

Yes, I know your database is on the same machine, but there are differences in permissions and authentication for files and folders when you run IIS. The chances are good that whatever is hanging you up is covered in this article.

Whether mixing Access databases and IIS is a Good Thing or not is a separate discussion...

Answer 2

I'm using IIS 6 on Windows Server 2003

Answer 3

It will be a permissions problem. When you run with Visual Studio's web server it is running as you, IIS on the other hand runs (by default) as Network Service. This is limited in what it can do.

To access an access database (no pun intended) the account that IIS runs under will need the ability to read, write, execute, change and delete for the directory the database is in. By default this should be granted to the APP_DATA directory, if your access database is elsewhere you will need to grant these permissions.

Connecting via Access to a remote machine is a whole other problem, it's unlikely this will work because the Network Service account is a local account and will not exist on the sharepoint server, and you cannot grant access because it does not exist.

If you were in a domain you could run IIS under a suitable locked down domain account and it might work, but frankly it's all a bit messy!

Answer 4

if you were in a domain you could run IIS under a suitable locked down domain account

Actually blowdart, I'm not sure if I know what does that mean. Can you explain a little?

thanks

Answer 5

Using Access as a fudge to access SharePoint from an ASP.net site is going to give you lots of pain in the long run.

A much better way would be to use SharePoint's web services to access that data - removing Access from the equation altogether.

http://www.scribd.com/doc/8634090/Accessing-SharePoint-Data-Using-C-Without-Running-Code-On-the-SharePoint-Server-Part-1

MSDN - Using ASP.NET web services

Creating and Consuming a Web Service

Answer 6

Access 2007 makes connecting to SharePoint a breeze. After you link to a list, then ADO, and even DAO + VBA code and your sql queries work on those lists!

I seen a good number of people thus start using the JET (actually it called ACE in access 2007) as a data connector to SharePoint for this very reason. Since your are building a web based system then we not using ms-access here, but ONLY using the 2007 version of the JET data engine that ships with access 2007.

Someone does need to come out with a OLEDB provider for SharePoint lists that allows ones web site or .net code to view those lists as standard sql tables like ms-access can. Using JET as the data connector system is likely not such a good idea. I do realize that you not storing nor placing data in the access JET table, but are using the wonderful ability of the new access JET engine to view and use SharePoint lists as regular sql tables. This approach does allow your ASP.net code to execute sql updates on that data or do whatever as if this was a regular sql table. It does make this oh so easy.

The security permissions on the SharePoint site are thus going to view this connection as not as a end user, but always see the USER as the actual web site connecting (or at least the user you were in ms-access when you linked the table to SharePoint). I don’t believe the SharePoint user authentication process can work reliable when you do this. What will happen is you change a few things on the SharePoint site, and next thing you know the JET connection will be trying to prompt you for new logon credentials. You can’t provide nor control those logon credentials at that point in time.

So, as others pointed out, this is going to be problematic and not reliable.