issue with linked server connecting to Access

Hi,
I have a linked server setup to connect to an access file from a filer
server. If I am on the server computer logged in using Windows NT
authencation, the connections works. However, if I use a native SQL login
(with sys admin rights) it gave the following error:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\filer-la03p\Mbs\Credit Mortgage
Group\Programs\Databases\Credit MBS Data.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: ].
The linked server has security mapping as remote user:"admin" with password
null.
I also have noticed that if I connect to the server using EM from my desktop
using NT authencation, the error ocurrs as well. In this case I will say it
is a "double-hopping" issue. But why when using a native SQL account will
have a problem?
From my understanding when using linked server to connect to an access file,
it is using the service account that starts SQL Server. I had setup all
privilages required for the SQL Service account. And as I inicated earlier,
it connects ok if I am at the server using NT authencation to log in.
I rearch online and it seems like I am not the only one who has problems
with linked server for access from other computers. Unfortunately, nobody has
given the resolutions that works for the requester.
Someone please help, we are a microsoft shop and everything is microsoft.
This problem has stopped our development for the business units a great deal.
Our clients were just bought in to convert a lot of their stuff to SQL
Server. Now they are going to back off!
Thanks if someone can respond quickly.
Christine
A few suggestions:
--Make sure that the Access mdb (and mdw) reside on a file share that
the Windows account has read/write/delete ACLs on for the files and
folders.
--Make sure that the Access mdb is totally unsecured (check the owner
property -- it should NOT say 'unknown').
--Make sure that the login you are using is Admin with an empty string
(''), not null.
--Use filemon (www.sysinternals.com) to check to see if SQLS is
getting access to the files/folders.
FWIW, linked servers to Access are a real PITA. You'd have a much
easier time if you worked it the other way around, linking to SQL
Server from the Access .mdb.
--Mary
On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
C@.discussions.microsoft.com> wrote:

>Hi,
>I have a linked server setup to connect to an access file from a filer
>server. If I am on the server computer logged in using Windows NT
>authencation, the connections works. However, if I use a native SQL login
>(with sys admin rights) it gave the following error:
>Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>open the file '\\filer-la03p\Mbs\Credit Mortgage
>Group\Programs\Databases\Credit MBS Data.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: ].
>The linked server has security mapping as remote user:"admin" with password
>null.
>I also have noticed that if I connect to the server using EM from my desktop
>using NT authencation, the error ocurrs as well. In this case I will say it
>is a "double-hopping" issue. But why when using a native SQL account will
>have a problem?
>From my understanding when using linked server to connect to an access file,
>it is using the service account that starts SQL Server. I had setup all
>privilages required for the SQL Service account. And as I inicated earlier,
>it connects ok if I am at the server using NT authencation to log in.
>I rearch online and it seems like I am not the only one who has problems
>with linked server for access from other computers. Unfortunately, nobody has
>given the resolutions that works for the requester.
>Someone please help, we are a microsoft shop and everything is microsoft.
>This problem has stopped our development for the business units a great deal.
>Our clients were just bought in to convert a lot of their stuff to SQL
>Server. Now they are going to back off!
>Thanks if someone can respond quickly.
>Christine
>
|||Mary,
Thanks for replying.
All the checklists you suggested are all there and correct, otherwise it
wouldn't have worked for some cases. It would just not work AT ALL.
I have found the fix for one issue now, but not another:
1. After I added the spn for the server to the AD account that started SQL
Server, I was able to connect from my desktop to the linked server through NT
authencation.
Before this is only possible if I am at the server.
2. If I connect to SQL Server using SQL accounts, it still doesn't work on
either from the server or my desktop. So I was wondering if I login as 'sa',
what kind of security context that was verified against the file server on
the network? After it gets through the file system then the security check
will use "Admin" to open Access as specified in my linked server security
tab. But what happened before then?
I would assume either way (login as SQL or NT authencation to my SQL Server)
it should use the service account that starts SQL Server, but apparently it
is not the case.
I am really puzzled now...
"Mary Chipman" wrote:

> A few suggestions:
> --Make sure that the Access mdb (and mdw) reside on a file share that
> the Windows account has read/write/delete ACLs on for the files and
> folders.
> --Make sure that the Access mdb is totally unsecured (check the owner
> property -- it should NOT say 'unknown').
> --Make sure that the login you are using is Admin with an empty string
> (''), not null.
> --Use filemon (www.sysinternals.com) to check to see if SQLS is
> getting access to the files/folders.
> FWIW, linked servers to Access are a real PITA. You'd have a much
> easier time if you worked it the other way around, linking to SQL
> Server from the Access .mdb.
> --Mary
> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
> C@.discussions.microsoft.com> wrote:
>
>
|||What did filemon show?
--Mary
On Thu, 14 Oct 2004 16:31:02 -0700, "Christine C"
<ChristineC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Mary,
>Thanks for replying.
>All the checklists you suggested are all there and correct, otherwise it
>wouldn't have worked for some cases. It would just not work AT ALL.
>I have found the fix for one issue now, but not another:
>1. After I added the spn for the server to the AD account that started SQL
>Server, I was able to connect from my desktop to the linked server through NT
>authencation.
>Before this is only possible if I am at the server.
>2. If I connect to SQL Server using SQL accounts, it still doesn't work on
>either from the server or my desktop. So I was wondering if I login as 'sa',
>what kind of security context that was verified against the file server on
>the network? After it gets through the file system then the security check
>will use "Admin" to open Access as specified in my linked server security
>tab. But what happened before then?
>I would assume either way (login as SQL or NT authencation to my SQL Server)
>it should use the service account that starts SQL Server, but apparently it
>is not the case.
>I am really puzzled now...
>
>"Mary Chipman" wrote: