Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server

I'm getting the following error when running a query on a linked server for ODBC DSN (Access):

OLE DB provider "MSDASQL" for linked server "JOBBOSS" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "JOBBOSS" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "JOBBOSS" returned message "[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'.  It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "JOBBOSS".

The properties of the linked server:
Linked server: JOBBOSS
Server type: Other data source
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product name: JOBBOSS
Data source: JOBBOSS32  <- I have this named DSN configured under System DSN

Query I'm running:
select * from openquery(JOBBOSS, 'select * from Source')
LVL 9
stu29Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
have you followed the exact steps for creating linked server, I gave in my following article?

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

stu29Author Commented:
I'm not linking using Jet.  I'm using an ODBC DSN and I wrote down all the properties of it above.  The Access thing I need to connect to has a custom .mdw so I have to go with ODBC instead of directly linking to the .mdb file with the registry modification.
Mark WillsTopic AdvisorCommented:
after adding the linked server, you will need to add linked server logins, even if a default for 'Admin' . It needs someone to link as.

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
     [ , [ @useself = ] 'TRUE' | 'FALSE' | 'NULL']
     [ , [ @locallogin = ] 'locallogin' ]
     [ , [ @rmtuser = ] 'rmtuser' ]
     [ , [ @rmtpassword = ] 'rmtpassword' ]


e.g.

sp_addlinkedsrvlogin
     @rmtsrvname =  'JOBBOSS' ,
     @useself =  'FALSE' ,
     @locallogin =  NULL ,
     @rmtuser =  'rmtuser' ,          -- this will be your user and password set up in the Access DB. Or just use 'Admin' and no password
     @rmtpassword =  'rmtpassword'
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

stu29Author Commented:
Thanks for your input!
I already had that set under Properties -> Security using the login given to me by the Access guys.
(checked) Be made using this security context:
Remote login: xyz
With password: xyz

I ran your sp anyway to see if it would make any difference for both the "xyz" and Admin users.  It's still the same error msg.
Mark WillsTopic AdvisorCommented:
Just a lateral thought, can you access that data via the DSN in say Excel ?

Could be a DSN issue, or maybe the custom component is causing problems for MSDASQL.
stu29Author Commented:
Yes, the DSN works.  I verified that in VS.NET Studio.  Also, there's Test Connection when right clicking on the server.  I increased both of below to 1000000 and it seems working.  What are good values of these?  My Access file is in a remote server and the connection is via a DSL line.

Properties -> Server Options -> Connection Timeout & Query Timeout.

Thanks,
Mark WillsTopic AdvisorCommented:
If set to 0 originally then it uses the sp_configure settings which is 20 seconds for remote connection by default and 10minutes for remote query.

You can check that with :

SELECT * FROM sys.configurations
ORDER BY name ;
GO

So, it really depends on how long you are prepared to wait - fine if always going to connect and return data, but not so good if there is an issue. I would suggest just trying to change the Connection timeout and set to 60 first up and progress up to 300 which is a 5 minute wait, and an eternity if you are sitting watching it. For Query timeouts, would be inclined to leave at 0 which should default to the system config which should be 600 (ie ten minutes), you can increase, but if you are currently getting results back within a few minutes, then it is most likely the connection timeout that it is tripping over.

So, first try 60 for connect   and 0 for query timeouts.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.