Linked SQL Server to Access

Hi, I now this question has been posted a few times, but I did not find an
answer.
I have a linked server on my SQL server to an access database via a mapped
drive on a domain. (2 computers, one with SQL server and the other has the
access database)
When I log in to query analyzer using windows authentication I run a stored
procedure to query data from the access database via a linked server,
everything works fine.
When I log in as SA, I get this error”It is already opened exclusively by
another user, or you need permission to view its data”
The same is with the DTS package, if I right click the package and execute
the DTS, it works. But if I create a job to call the DTS it gives me the sam
e
error, even if I say that my domain username is the password.
I even ran DTS via command prompt and that too works.
I know it’s some kind of permission level, but I don’t know what to do.
I changed my c drive (the one with the access database) security to
everyone. I changed the access database security to everyone too.
Any one knows how to fix this?Your sqlagent proxy is probably not set with proper access.
http://msdn.microsoft.com/library/d.../>
sz_8sdm.asp
Also, be aware that SP3 has added a new flag. You might have to set it to
your environment.
e.g.
--restrict to admin only. 1=yes, 0=no
EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0
go
-oj
"Andr" <Andr@.discussions.microsoft.com> wrote in message
news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35@.microsoft.com...
> Hi, I now this question has been posted a few times, but I did not find an
> answer.
> I have a linked server on my SQL server to an access database via a mapped
> drive on a domain. (2 computers, one with SQL server and the other has the
> access database)
> When I log in to query analyzer using windows authentication I run a
> stored
> procedure to query data from the access database via a linked server,
> everything works fine.
> When I log in as SA, I get this error"It is already opened exclusively by
> another user, or you need permission to view its data"
> The same is with the DTS package, if I right click the package and execute
> the DTS, it works. But if I create a job to call the DTS it gives me the
> same
> error, even if I say that my domain username is the password.
> I even ran DTS via command prompt and that too works.
> I know it's some kind of permission level, but I don't know what to do.
> I changed my c drive (the one with the access database) security to
> everyone. I changed the access database security to everyone too.
> Any one knows how to fix this?
>|||HI,
You are correct, I did not have my proxy set. After I changed both my proxy
and EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0 I still get
the same error.
What else should I try?
"oj" wrote:

> Your sqlagent proxy is probably not set with proper access.
> http://msdn.microsoft.com/library/d...
a-sz_8sdm.asp
> Also, be aware that SP3 has added a new flag. You might have to set it to
> your environment.
> e.g.
> --restrict to admin only. 1=yes, 0=no
> EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0
> go
>
> --
> -oj
>
> "André" <Andr@.discussions.microsoft.com> wrote in message
> news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35@.microsoft.com...
>
>|||Sorry for the late reply...Anyway, you might need to restart the service for
this to take effect.
-oj
"Andr" <Andr@.discussions.microsoft.com> wrote in message
news:8151940E-EF12-4AA3-AB40-1B267952EA1D@.microsoft.com...[vbcol=seagreen]
> HI,
> You are correct, I did not have my proxy set. After I changed both my
> proxy
> and EXECUTE msdb..sp_set_sqlagent_properties @.sysadmin_only = 0 I still
> get
> the same error.
> What else should I try?
>
> "oj" wrote:
>|||Hi,
I rebooted my server and I still get the same results. I did check to make
sure that my proxy is still set. and it is.
Thank you,
Andre
"oj" wrote:

> Sorry for the late reply...Anyway, you might need to restart the service f
or
> this to take effect.
> --
> -oj
>
> "André" <Andr@.discussions.microsoft.com> wrote in message
> news:8151940E-EF12-4AA3-AB40-1B267952EA1D@.microsoft.com...
>
>|||Andre,
How's about dropping and recreating the link like so:
--Linked server to Access on Shared drive
EXEC sp_addlinkedserver
@.server = 'myAccess',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = '\\ghost\ghost\oj\Access.mdb'
GO
--Everyone connects via Admin Access db user
EXEC sp_addlinkedsrvlogin 'myAccess', 'false', NULL, 'Admin', NULL
go
-oj
"Andr" <Andr@.discussions.microsoft.com> wrote in message
news:0B8EF739-AFA3-4C60-91CD-E3E1AEA2D62D@.microsoft.com...[vbcol=seagreen]
> Hi,
> I rebooted my server and I still get the same results. I did check to
> make
> sure that my proxy is still set. and it is.
> Thank you,
> Andre
> "oj" wrote:
>|||I still get an error
"oj" wrote:

> Andre,
> How's about dropping and recreating the link like so:
> --Linked server to Access on Shared drive
> EXEC sp_addlinkedserver
> @.server = 'myAccess',
> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> @.srvproduct = 'OLE DB Provider for Jet',
> @.datasrc = '\\ghost\ghost\oj\Access.mdb'
> GO
> --Everyone connects via Admin Access db user
> EXEC sp_addlinkedsrvlogin 'myAccess', 'false', NULL, 'Admin', NULL
> go
>
> --
> -oj
>
> "André" <Andr@.discussions.microsoft.com> wrote in message
> news:0B8EF739-AFA3-4C60-91CD-E3E1AEA2D62D@.microsoft.com...
>
>|||I tried this on another Sql server and I get the same results. Do I have to
be an Admin on the network for this to work?
Andre
"André" wrote:
[vbcol=seagreen]
> I still get an error
> "oj" wrote:
>|||This is the error i get in query analyzer
Server: Msg 7399, Level 16, State 1, Procedure UpdateSQL, Line 3
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 '\\mypath'. 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: ].
"André" wrote:
[vbcol=seagreen]
> I tried this on another Sql server and I get the same results. Do I have
to
> be an Admin on the network for this to work?
> Andre
> "André" wrote:
>|||Sorry for the late reply...I've been out of town.
Anyhow, if you use this code:
--Everyone connects via Admin Access db user
EXEC sp_addlinkedsrvlogin 'myAccess', 'false', NULL, 'Admin', NULL
you essentially allow all users to connect via sqlserver service account.
You will have to check to make sure sqlserver is not started by LocalSystem.
This special NT account does not have access to network resources. In this
case, it's your Access file on a network shared.
Btw, you might want to check MS KB. I remember seeing an article which
details steps to resolve error 7399.
-oj
"Andr" <Andr@.discussions.microsoft.com> wrote in message
news:4301AD8D-A3DC-42BC-80EC-21B3AFEFFB32@.microsoft.com...[vbcol=seagreen]
> This is the error i get in query analyzer
> Server: Msg 7399, Level 16, State 1, Procedure UpdateSQL, Line 3
> 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 '\\mypath'. 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: ].
>
> "Andr" wrote:
>