Help connecting to Access DB from web service

[Jeff Richardson]

I am creating a web service that returns data from an Access 2000 database
using VS2005. The Access database is located on a different server than
where the web service is running. The Access database is also opened by a
different desktop application running on various workstations on the
network.

While developing / debugging the web service is hosted on my development
machine using the 'ASP.NET Development Server' that is included with VS2005.
When deployed it is being host with IIS.

I am using the following connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source="\\slo-science1\data\PBI Chem
data\PBIChemDatabase.mdb"

If the database has NOT opened by any of the workstations running the
desktop application then the web service runs correctly on both my
development machine as well as on the IIS server.

If the database has been opened by any of the workstations running the
desktop application the web service runs correctly ONLY on my development
machine and fails on IIS with the following error:

System.Data.OleDb.OleDbException: Could not use ''; file already in use.

What are the differences between the 'ASP.NET Development Server' and IIS
that would effect how a web service opens a remote Access database?

Does anyone know how what settings can be changed so that the web service
runs correctly on IIS?

Thanks in advance,
Jeff Richardson

[Steven Cheng[MSFT]]

Hi Jeff,

Welcome to the MSDN newsgroup.

Regarding on the accessing Access mdb database in ASP.NET application, it
is likely the ASP.NET application doesn't have sufficient permission or the
mdb file is locked. From your description, you've another desktop
application that will also open and access the mdb file, if so, I'm afraid
the ASP.NET application hosted in IIS will not be able to access it
concurently (if get locked). This is different when accessing from local
computer or in testserver because when deploying in IIS, the asp.net
appliation runs under the asp.net worker process (under the worker proces
indenitity) and this process's security session is not a interactive
session which can not perform many action like desktop application. So for
your scenario, I think you may consider the following options:

1. Do not make other application access the same mdb file concurrently that
make it get locked.

2. Use SQL express db instead, this is also file based however is designed
for concorrent server-side application and has much better performance
against access.

Regards,

Steven Cheng
Microsoft Online Community Support
==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Jeff Richardson]

Hello Steve,

Thank you for your reply.

I would love to move the database to SQL Express but that is not an option
since the desktop application is a comerical application and its native file
format is Access.

Given that I can not move the data to a better database, what can be done to
make my web service work well with the Access db while hosted on IIS? Can
the worker process for this application be given additional permission?

Thanks,
Jeff.
"Steven Cheng[MSFT]" <st*****@online.microsoft.com> wrote in message
news:dD**************@TK2MSFTNGXA01.phx.gbl...

Hi Jeff,

Welcome to the MSDN newsgroup.

Regarding on the accessing Access mdb database in ASP.NET application, it
is likely the ASP.NET application doesn't have sufficient permission or
the
mdb file is locked. From your description, you've another desktop
application that will also open and access the mdb file, if so, I'm
afraid
the ASP.NET application hosted in IIS will not be able to access it
concurently (if get locked). This is different when accessing from local
computer or in testserver because when deploying in IIS, the asp.net
appliation runs under the asp.net worker process (under the worker proces
indenitity) and this process's security session is not a interactive
session which can not perform many action like desktop application. So
for
your scenario, I think you may consider the following options:

1. Do not make other application access the same mdb file concurrently
that
make it get locked.

2. Use SQL express db instead, this is also file based however is
designed
for concorrent server-side application and has much better performance
against access.

Regards,

Steven Cheng
Microsoft Online Community Support
==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Steven Cheng[MSFT]]

Thanks for your response Jeff,

After some further research, If you do have to use the Access as the
datastorage which will be accessed by multiple processes, you need to take
care of the following things:

1. Since your mdb file is put on a fileshare, make sure you've grant
sufficient permission in both the fileshare and the NTFS's permission
setting. In my test case I grant both of them everyone full control though
you can adjust them according to your decent scenario.

2. In the OLEDB connectionstring, specify the connection mode as
"Mode=Share Deny None", this means that the connection to the db file won't
lock it and won't deny any type of access from other client/process(read,
write....). e.g here is the connectionstring generated through the VS ide
connection wizard:(used in my desktop application)

========================
Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet
OLEDB:Database Locking Mode=1;Data
Source="D:\Share_Data\mydb.mdb";Mode=Share Deny None;Jet OLEDB:Engine
Type=5;Provider="Microsoft.Jet.OLEDB.4.0";Jet OLEDB:System database=;Jet
OLEDB:SFP=False;persist security info=False;Extended Properties=;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy
Locale on Compact=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1
========================

Then, in our ASP.NET application, just use the same connection setting to
access the mdb file(through remote file share). Based on my local test, it
works correctly(I can access it well in both desktop & asp.net application
concurrently).

Hope this helps.

Regards,

Steven Cheng
Microsoft Online Community Support
==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Jeff Richardson]

Thanks Steven,

Permissions was the source of my troubles.

Jeff.
"Steven Cheng[MSFT]" <st*****@online.microsoft.com> wrote in message
news:1g****************@TK2MSFTNGXA01.phx.gbl...

Thanks for your response Jeff,

After some further research, If you do have to use the Access as the
datastorage which will be accessed by multiple processes, you need to take
care of the following things:

1. Since your mdb file is put on a fileshare, make sure you've grant
sufficient permission in both the fileshare and the NTFS's permission
setting. In my test case I grant both of them everyone full control though
you can adjust them according to your decent scenario.

2. In the OLEDB connectionstring, specify the connection mode as
"Mode=Share Deny None", this means that the connection to the db file
won't
lock it and won't deny any type of access from other client/process(read,
write....). e.g here is the connectionstring generated through the VS ide
connection wizard:(used in my desktop application)

========================
Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet
OLEDB:Database Locking Mode=1;Data
Source="D:\Share_Data\mydb.mdb";Mode=Share Deny None;Jet OLEDB:Engine
Type=5;Provider="Microsoft.Jet.OLEDB.4.0";Jet OLEDB:System database=;Jet
OLEDB:SFP=False;persist security info=False;Extended Properties=;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy
Locale on Compact=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1
========================

Then, in our ASP.NET application, just use the same connection setting to
access the mdb file(through remote file share). Based on my local test, it
works correctly(I can access it well in both desktop & asp.net application
concurrently).

Hope this helps.

Regards,

Steven Cheng
Microsoft Online Community Support
==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Steven Cheng[MSFT]]

You're welcome Jeff,

Glad that you're figured out the problem.

Have a good day!

Regards,

Steven Cheng
Microsoft Online Community Support
==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)