Link to home
Create AccountLog in
Avatar of ChadMarsh
ChadMarshFlag for United States of America

asked on

Access DB as linked server fails on SQL2005

Hello,
I'm trying to create a linked server with an Access 2003 Database using SQL Server 2005. Here is the script:

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServer', @srvproduct=N'OLE DB Provider for Jet', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'\\svr\folder\AccessDB.mdb'

This creates the linked server in the SMS just fine, but when I try to test the connection I get the following:

"The test connection to the linked server failed."

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LinkedServer".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PPS-Daten" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)

The server runs on Microsoft Server 2003. The Access database is on a separate server that the SQL server can see.  The Access database also has security set to everyone - full control.  I'm starting to think it's a setting some where (either Server 2003 or MSSQL2005)  because I've tried to set up other linked servers via ODBC and got the same error, but for MSDASQL provider.  This was for a Progress database so I just figured that the driver I was using didn't work right.  I have SQL 2005 Ent installed on my machine (Vista OS)  for development and I have no problems creating these linked servers.  It is allowing me to create linked servers to other SQL servers. Those work just fine. It's just when I try to use one of the other providers.

Any insight would be helpful.
Thanks
Chad
Avatar of Jim P.
Jim P.
Flag of United States of America image

The userid that the SQL Server and SQL Agent runs under (MyDomain\DB_Mgr) in the services panel. Does that have permissions (including write) to the "\\svr\folder\AccessDB.mdb" path and folder?
Avatar of ChadMarsh

ASKER

Yes, These are domain admin accounts.
Do you have the Access/JET engine loaded on the server that you are connecting from?

Is the firewall(s) off on the server?

Is the "Everyone -- Full Control" on the Share or the actual folder?
Yes, There's no firewall between these boxes, Everyone-Full is on the folder
Correction. The folder and the share have Everyone-full security
Avatar of Gustav Brock
I think that script is too simple. See my example.

Also, either the path should be a mapped drive and further, this might even be a local drive. At least try to copy it to a local folder for testing.
Check the BOL on Linked servers and pay notice to the need to create a Registry entry if the user and password of JET security is not Admin and blank.

/gustav
/****** Object:  LinkedServer [VRS]    Script Date: 03/04/2008 17:45:17 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'VRS', @srvproduct=N'JET', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'c:\linktest\databasefile.mdb'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'VRS',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
 
GO
EXEC master.dbo.sp_serveroption @server=N'VRS', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'VRS', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'VRS', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'VRS', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'VRS', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'VRS', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'VRS', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'VRS', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'VRS', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'VRS', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'VRS', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'VRS', @optname=N'use remote collation', @optvalue=N'true'

Open in new window

Thanks for the response.
I edited your script with my info and ran it. I still get the same error.  I've checked the user and password for the database and it is admin with a blank password.
I just tried copying the access db to the local machine that my instance of SQL2005 is on.. I still get

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LnkdSvr". (Microsoft SQL Server, Error: 7303)

Could there really be a provider issue?
Try this:

From a DOS prompt do
--------------------------
runas MyDomain\DB_Mgr_ID "c:\windows\system32\odbcad32.exe"
--------------------------

Then see if you can create an Access ODBC call to the remote servers DB.
But did you copy the database file to a local folder?

The error seems to indicate a simple error in the parameters:
http://msdn2.microsoft.com/en-us/library/aa226395(SQL.80).aspx

or the server doesn't have the permission to open the file:

http://www.sqlservercentral.com/Forums/Topic463860-146-1.aspx

/gustav
Sorry guys, I've been tied up with other things.  I tried executing the runas and the same problem exists.
I also did copy the database to a local folder. Same issue.  It seems to me that something is not configured right.
This SQL server is part of a 7 node cluster.  My experience in the past with this cluster has been that it makes troubleshooting
a lot harder.
SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Glad to be of assistance. May all your days get brighter and brighter.