Linkedserver to Remore MS ACCESS FILE

Question

Linkedserver to Remore MS ACCESS FILE

Rajesh Menon-461740

SSC Enthusiast

Points: 165
More actions
March 7, 2010 at 1:50 am

#96899

I am trying to create a linked server using below
EXEC sp_addlinkedserver
@server = N'ACCESS_CUSTOMERS',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'ACCESS',
@datasrc = N'\\192.1.1.124\d$\Oasis\honey_be.mdb'
GO
-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'ACCESS_CUSTOMERS',
@useself = N'FALSE',
@locallogin = NULL,
@rmtuser = N'Admin',
@rmtpassword = NULL
GO
but the problem is I am getting an error message while trying to access the linkedserver as below
'The microsoft jet database engine can not open the file \192.1.1.124\d$\Oasis\honey_be.mdb, it's already open exclusively by another user or you need permission to view the data'
Please help

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Rajesh this is a common security issue. The problem is that when you access any resource OUTSIDE ofSQL server, like network shares, local hard drives,sp_OA type functions etc, it doesn't matter what YOUR credentials are, like Domain Admin,Local Admin etc, because SQL will not carry those credentials to the "outside of SQL" security context.

SQL Server uses the account it starts with to try and access the resource:

That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.

As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.

Once that is done, and you stop and start the SQL service to make it use that account instead of old running values, your linked server would work.

you can prove this is the issue by simply putting in your credentials, with your domain account and password, and confirm the linked server works when SQL is run your credentials, so you'd know you need a domain account to access the resource.

Rajesh Menon-461740 (3/7/2010)
I am trying to create a linked server using below
EXEC sp_addlinkedserver
@server = N'ACCESS_CUSTOMERS',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'ACCESS',
@datasrc = N'\\192.1.1.124\d$\Oasis\honey_be.mdb'
GO
-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'ACCESS_CUSTOMERS',
@useself = N'FALSE',
@locallogin = NULL,
@rmtuser = N'Admin',
@rmtpassword = NULL
GO
but the problem is I am getting an error message while trying to access the linkedserver as below
'The microsoft jet database engine can not open the file \192.1.1.124\d$\Oasis\honey_be.mdb, it's already open exclusively by another user or you need permission to view the data'
Please help

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rajesh Menon-461740 SSC Enthusiast Points: 165 More actions · Answer 2

Thanks Lowell

It's working now!!!! Gr8 help on right time.

Rajesh Menon-461740 SSC Enthusiast Points: 165 More actions · Answer 3

Also, if anyone wants to schedule a job based on the above link table they need to change the log on account of SQL AGENT as mentioned above by Lowell.

Rajesh