• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1066
  • Last Modified:

Linked Server from SQL Server 2008 to Access 2000 database gives error

I have a Access 2000 database residing on one sever and an installation of SQL Server 2008 residing on another.  I created a linked server using the code below.   When I run EXEC sp_tables_ex 'PropCont' I get the following,
"OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:  The provider did not give any information about the error.].
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error."

I tried changing the code to specify my account information, which has Administrative privileges on the domain and I get,
"OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d:  Authentication failed.].
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed."

Originally I was getting an error that said, "The workgroup information file is missing or opened exclusively by another user"  I found an article on line that suggested I do the following,
"1.      Open SSMS
  2.      Expand Server Objects, Linked Servers, Providers
  3.      Double click the provider (Microsoft.Jet.OLEDB.4.0)
  4.      UNTICK the "Allow inprocess" option"
I did this and I stopped getting that error but started getting the error I listed above however when I went back and checked the "Allow inprocess" option was still checked.  I can't get it to remain unchecked.  I can't find anything regarding how to specify the MDW file which is located in the same folder as the database file.

Another article suggested making sure it is checked and also checking "Nested Queries".  This had no effect.

Any suggestions would be greatly appreciated.

EXEC sp_addlinkedserver 
	@server = 'PropCont', 
	@provider = 'Microsoft.Jet.OLEDB.4.0', 
	@srvproduct = 'Access',
	@datasrc = '\\<Server Name>\Shared\Databases\PropCont.mdb'
GO

EXEC sp_addlinkedsrvlogin 
	@rmtsrvname = 'PropCont',
	@useself = 'false',
	@rmtuser = 'Admin', 
	@rmtpassword = ''
GO

Open in new window

0
leskelly
Asked:
leskelly
  • 6
  • 4
  • 2
1 Solution
 
NormanMainaCommented:
is the os 32bit or 64 bit?
have you confirmed that the jet engine in installed properly?

for office 2007 files use the Microsoft.Ace.Oledb.12 provider

Install the client tools here:
http://www.microsoft.com/downloads/en/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

more info:
http://msdn.microsoft.com/en-us/library/ms190479.aspx
0
 
leskellyAuthor Commented:
Both servers are 32 bit.

The database has been in production as a run-time database for years so I assume the jet engine is installed properly.

It is Access 2000 not 2007

I believe the linked server has been created correctly as I can see it and it's properties in SSMS and it shows up in the list of linked servers when I run, EXEC  sp_helplinkedsrvlogin

Any other thoughts?
0
 
Nico BontenbalCommented:
If you revert back to the situation where you got the "The workgroup information file is missing or opened exclusively by another user". Can you then perhaps resolve this by changing the registry setting as suggested in this article?
http://support.microsoft.com/kb/246255
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
leskellyAuthor Commented:
Hello Nicobo,

Thanks for your response.  I can't get back to the situation where I get the "The workgroup information file is missing or opened exclusively by another user".  I even tried deleting and recreating the linked server.  I always get the 'Authentication failed' error.

I followed the instructions in the article anyway but the results are the same.  I made the registry changes on the server that contains the installation of SQL Server.  In may initial post I had said that I was working with SQL Server 2008 but that is on my development machine.  On the server itself SQL Server 2000 is installed.

On the server I made the registry change suggested in the article so that the given key value points to the System.MDW file that is on the server where the mdb file is.

Any other thoughts?
0
 
Nico BontenbalCommented:
This problem is also discussed for example here:
http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/5d53eb0c-bb02-4c85-bc65-b492ee83c7c0
Could it be a permission issue? The SQL Server account must have read, write and create (for the .ldb file) permission on the folder where the .mdb is located. You could try to copy the .mdb to a folder on the SQL server (preferably a SQL server data folder so you know SQL server has permissions on this folder). And then check if you can create the linked server. If you can you'll know it is not a problem with the MS Access security (mdw). So than the problem most likely is with Windows Security.
0
 
leskellyAuthor Commented:
I tried doing as you suggested and made a copy of the database in the SQL server data folder.  I then created a linked server to it.  Originally I had it run under the security context of the Access Admin account but got a Access Denied message.  I then tried using my Access Account and got a Authentication Failed message.  I opened up the database in Access and it opened up with out asking for a login.  The database is unsecured but by opening up the User and Group Permissions dialog I could see that it was using the default work group.  I used the work group administrator to join the work group that the original database uses.  At that point I got the  Authentication Failed message for any login I use.  I think you are right that it is a permissions issue but I can't figure out what it is.  Below is a recap of the security situation.
1.) SQL Server and the SQL Server Agent run under a domain account named SQLAdmin which has full NTFS permissions on the folder that the mdb and ldb files are in.
2.) It also has Read & Execute, List Folder Contents, and Read permissions on the folder the mdw file is in.  I tried giving it full permissions as well.
3.) The linked server runs under the security context of my Access account which is the owner of the database and all of it's objects.

If you have any more ideas I'd love to hear them.
0
 
leskellyAuthor Commented:
This is the full error that I receive:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d:  Authentication failed.].
0
 
Nico BontenbalCommented:
I probably live more than 5000km away from you, but I can feel your frustration.

I did some testing, and when trying to link a Access 2000 database I also got Authentication errors (though slightly different than yours). Linking to an Access 2002 (XP) database was no problem.
So I wonder if Access 2000 might be the cause of the problems. Could you convert your database to Access 2002-2003 format, and check if you can link to that database.
Eventually I was able to link to Access 2000 data by creating a Access 2002 database with linked tables to the Access 2000 database.

Maybe you have more luck using the "Microsoft OLE DB Provider for ODBC Drivers" provider and not the "Microsoft Jet 4.0 OLE DB Provider"
0
 
leskellyAuthor Commented:
Hello,

I tried your second suggestion but I had no success with it.  On my development machine I have Access 2000 and 2010.  This is a legacy database that hopefully won't be around much longer so I don't want to spend too much time on it.  I've created a SQL job to import the data daily and will work with it from there.  Thanks for all your effort the points are yours.
0
 
NormanMainaCommented:
If it has been working -what has changed?
Did you install a new anti virus system?a new firewall? or are security changes in teh domain about teh same time the problem started?

I'm just wondering if the server where sql server resides has access to resources in teh server with the ms access database.
Can you browse to the access folder from the sql server to the access database using teh same credentials that sql is using?
0
 
leskellyAuthor Commented:
The database continues to work as a run time Access database.  I haven't connected to it before as a linked server.

Following Nicobo's suggestion I copied the database to the same server that SQL Server is on but the results were the same.
0
 
Nico BontenbalCommented:
You could try to convert the database to and Office 2010 accdb and then use the "Microsoft Office 12.0 Access database Engine OLE DB Provider" as the Provider. Maybe that works. And it might even work attaching the 2000 database tables in a 2010 database and then link the 2010 database to SQL server.
When I used this technique (but with 2000 and 2003) the tables didn't show up in the Object Explorer, but I could query them.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now