Can't query linked Access database specified by UNC

Hello,
I link Access database to sql server 2000 with ole db 4 provider; tell it to
logon with username ADMIN, empty password.
If the database is on a local drive, everything works correctly, I see the
tables of the access database in EM, and I successfully run query like
select * from accessdb...table1
If the database is on a remote drive and is specified by UNC, I still see
the tables in EM when EM runs on the server; but when I run EM on another
machine, focusing the tables under the linked server returns error
0x80004005. Same when I try to run a query (even on the server):
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine can
not
open the file '\\server\share\accessdb.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
Sql server is running on domain administrative account. At the same time as
the message is issued, I can log on that account on the server, launch
Access, and successfully open that same remote database using the same UNC
path.
What gives?
thanks,
Vadim Rapp
Polyscience, Inc.The first is to do is ensure that the database is not being
opened exclusively by other users. If that's all fine then
the error is likely related to permissions.
The account accessing the database needs a minimum of
something similar to Create, Destroy, Read, Write (depending
on the OS) on the folder where the mdb and ldb lives so it
can work with the .ldb file. Those are the minimum
permissions needed in general but it depends upon what
version of Access, what version of MDAC, what OS.
If the permissions are correct, you need to drill down
further to the access control and make sure that the account
has change permissions on the folder where the ldb file
resides. Make sure these are correct by explicitly setting
the permissions for the account.
I've seen several places just use a specific folder for just
the mdb (and consequently the ldb) and give the account full
control over that folder to make things less convoluted in
terms of permissions and the requirement for the ldb file.
-Sue
On Mon, 17 Jan 2005 13:35:38 -0600, "Vadim Rapp"
<vrapp@.nospam.polyscience.com> wrote:

>Hello,
>I link Access database to sql server 2000 with ole db 4 provider; tell it t
o
>logon with username ADMIN, empty password.
>If the database is on a local drive, everything works correctly, I see the
>tables of the access database in EM, and I successfully run query like
>select * from accessdb...table1
>If the database is on a remote drive and is specified by UNC, I still see
>the tables in EM when EM runs on the server; but when I run EM on another
>machine, focusing the tables under the linked server returns error
>0x80004005. Same when I try to run a query (even on the server):
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine ca
nnot
>open the file '\\server\share\accessdb.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>Sql server is running on domain administrative account. At the same time as
>the message is issued, I can log on that account on the server, launch
>Access, and successfully open that same remote database using the same UNC
>path.
>What gives?
>
>thanks,
>Vadim Rapp
>Polyscience, Inc.|||Hello Sue:
You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
SH> The first is to do is ensure that the database is not being
SH> opened exclusively by other users. If that's all fine then
SH> the error is likely related to permissions.
I thought that as well; that's why, as I wrote, I manually logged on the sql
server machine, with the same userid as sqlserver; started Access and opened
the same database successfully.
In other words, I manually did exactly what sql server couldn't do, with the
same login id on the same machine.
Vadim|||Yes...but you still need to check the permissions - the
whole ldb thing. And you need to make sure the permissions
are explicitly set. Logging on and starting access and
opening the database manually is not the same thing.
-Sue
On Tue, 18 Jan 2005 16:07:49 -0600, "Vadim Rapp"
<vrapp@.nospam.polyscience.com> wrote:

>Hello Sue:
>You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
> SH> The first is to do is ensure that the database is not being
> SH> opened exclusively by other users. If that's all fine then
> SH> the error is likely related to permissions.
>I thought that as well; that's why, as I wrote, I manually logged on the sq
l
>server machine, with the same userid as sqlserver; started Access and opene
d
>the same database successfully.
>In other words, I manually did exactly what sql server couldn't do, with th
e
>same login id on the same machine.
>Vadim|||Not that it's to much help, but I've seen a similar issue with Excel files.
I never really found a solution other than having the files on the
SQLServer. I've also tried with various accounts that has full admin access
to the server, domain, local machine etc. but to no avail. I'd be happy to
know the solution/reason since it's a bit annoying that I have to put the
files on the SQLServer to make it work.
Regards
Steen
Vadim Rapp wrote:
> Hello Sue:
> You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
>
> I thought that as well; that's why, as I wrote, I manually logged on
> the sql server machine, with the same userid as sqlserver; started
> Access and opened the same database successfully.
> In other words, I manually did exactly what sql server couldn't do,
> with the same login id on the same machine.
> Vadim|||Hello Steen:
You wrote in conference
microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
Fri, 21 Jan 2005 13:02:07 +0100:
SP> Not that it's to much help, but I've seen a similar issue with Excel
SP> files. I never really found a solution other than having the files on
SP> the SQLServer. I've also tried with various accounts that has full
SP> admin access to the server, domain, local machine etc. but to no avail.
SP> I'd be happy to know the solution/reason since it's a bit annoying that
SP> I have to put the files on the SQLServer to make it work.
the only good news is that, as I've found, it works correctly with SQL
Server 2005.
Vadim|||Hello Sue:
You wrote in conference
microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
Tue, 18 Jan 2005 16:11:38 -0700:
SH> Yes...but you still need to check the permissions - the
SH> whole ldb thing. And you need to make sure the permissions
SH> are explicitly set. Logging on and starting access and
SH> opening the database manually is not the same thing.
hmm... if I open the database manually, obviously, Access is creating the
ldb... thus, it can do it... How is it not the same thing?
And it looks like in sql server 2005 it works just fine.
Vadim|||SQL Server does not start up Access and then open up an mdb.
It accesses the mdb.
Just like it's different if you double click a file vs. open
an application and then do a File, Open to open the file.
-Sue
On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
<vr@.myrealbox.nospam.com> wrote:

>Hello Sue:
>You wrote in conference
>microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
>Tue, 18 Jan 2005 16:11:38 -0700:
> SH> Yes...but you still need to check the permissions - the
> SH> whole ldb thing. And you need to make sure the permissions
> SH> are explicitly set. Logging on and starting access and
> SH> opening the database manually is not the same thing.
>hmm... if I open the database manually, obviously, Access is creating the
>ldb... thus, it can do it... How is it not the same thing?
>And it looks like in sql server 2005 it works just fine.
>Vadim|||Vadim,
That last example wasn't a good one. Nonetheless, SQL Server
doesn't start Access and then open a database.
Other than security issues, combinations of MDAC/Jet drivers
can also cause problems and you may want to check those. If
you feel certain that this is a bug in SQL Server, you can
open a case with product support. You will not be charged
for the incident if it is a bug.
-Sue
On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
<vr@.myrealbox.nospam.com> wrote:

>Hello Sue:
>You wrote in conference
>microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
>Tue, 18 Jan 2005 16:11:38 -0700:
> SH> Yes...but you still need to check the permissions - the
> SH> whole ldb thing. And you need to make sure the permissions
> SH> are explicitly set. Logging on and starting access and
> SH> opening the database manually is not the same thing.
>hmm... if I open the database manually, obviously, Access is creating the
>ldb... thus, it can do it... How is it not the same thing?
>And it looks like in sql server 2005 it works just fine.
>Vadim|||one thing that worked for me on loading dumps from UNCs is that i had
to use an absolute UNC path and not the share name.
for example:
if i had a share named "share" on folder X on my C drive
rather than going to //myComputer/share
i had to go to //myComputer/C$/X
and everything worked fine...it is worth a shot.
hth,
hans
Sue Hoegemeier wrote:
> Vadim,
> That last example wasn't a good one. Nonetheless, SQL Server
> doesn't start Access and then open a database.
> Other than security issues, combinations of MDAC/Jet drivers
> can also cause problems and you may want to check those. If
> you feel certain that this is a bug in SQL Server, you can
> open a case with product support. You will not be charged
> for the incident if it is a bug.
> -Sue
> On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
> <vr@.myrealbox.nospam.com> wrote:
>
>microsoft.public.sqlserver.security,microsoft.public.sqlserver.server
on[vbcol=seagreen]
creating the[vbcol=seagreen]