R
Robert
Hi,
I am trying to link a secured Access (Jet) database to a SQL Server (MSDE)
database; however, I cannot get it to work. The msdn article, "PRB: Need to
Map to Default Admin Account and Use NULL for Password in Order Query Linked
Server to Access," says that this cannot be done; however, I have seen other
articles that say to change a registry setting to my workgroup file to
access secured Access databases.
I use the following to create the link to the unsecured Access database:
sp_addlinkedserver 'RemoteServer', 'OLE DB Provider for Jet',
'Microsoft.Jet.OLEDB.4.0', 'database.mdb'
sp_addlinkedsrvlogin 'RemoteServer', 'false', 'sa', 'Admin', NULL
This works fine.
For linking to the secured database I update the registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB
with my mdw file, and change the login command to
sp_addlinkedsrvlogin 'RemoteServer', 'false', 'sa', 'DbUser', 'user'
to include a user name DbUser from the workgroup and its password. Then when
I try to query (actually, I'm creating a view) I receive the following
error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Cannot start your application. The
workgroup information file is missing or opened exclusively by another
user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
This is VERY frustrating since we (programmers) can open secured Access
databases through Microsoft's APIs. Why can't they!
I have tried variations of the sp_addlinkedserver to include variations of
the connection string in the procedures provider string parameter. I also
created an ODBC data source for the secured Access database and tried
linking to it. I receive a different error:
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog
specified for the provider.].
I read somewhere that MS has a bug in reading OLE DB provider schema
information. Is there a workaround for this?
The Access database that I am connecting to is secured, and that cannot be
changed. I really, really don't want to change the default Access workgroup
on the computers that our product gets installed on (even if that method
worked). If I cannot solve this, I will have to dump SQL Server and use a
secured Access database (better technology?) to link the target secured
Access database to.
It seems that a lot of projects link Access to SQL Server and in a
production environment the Access database must (should) be secured. I'm
sure somebody else has these requirements.
Please let me know if there is a way to solve this problem.
Thanks,
Robert
I am trying to link a secured Access (Jet) database to a SQL Server (MSDE)
database; however, I cannot get it to work. The msdn article, "PRB: Need to
Map to Default Admin Account and Use NULL for Password in Order Query Linked
Server to Access," says that this cannot be done; however, I have seen other
articles that say to change a registry setting to my workgroup file to
access secured Access databases.
I use the following to create the link to the unsecured Access database:
sp_addlinkedserver 'RemoteServer', 'OLE DB Provider for Jet',
'Microsoft.Jet.OLEDB.4.0', 'database.mdb'
sp_addlinkedsrvlogin 'RemoteServer', 'false', 'sa', 'Admin', NULL
This works fine.
For linking to the secured database I update the registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB
with my mdw file, and change the login command to
sp_addlinkedsrvlogin 'RemoteServer', 'false', 'sa', 'DbUser', 'user'
to include a user name DbUser from the workgroup and its password. Then when
I try to query (actually, I'm creating a view) I receive the following
error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Cannot start your application. The
workgroup information file is missing or opened exclusively by another
user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
This is VERY frustrating since we (programmers) can open secured Access
databases through Microsoft's APIs. Why can't they!
I have tried variations of the sp_addlinkedserver to include variations of
the connection string in the procedures provider string parameter. I also
created an ODBC data source for the secured Access database and tried
linking to it. I receive a different error:
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog
specified for the provider.].
I read somewhere that MS has a bug in reading OLE DB provider schema
information. Is there a workaround for this?
The Access database that I am connecting to is secured, and that cannot be
changed. I really, really don't want to change the default Access workgroup
on the computers that our product gets installed on (even if that method
worked). If I cannot solve this, I will have to dump SQL Server and use a
secured Access database (better technology?) to link the target secured
Access database to.
It seems that a lot of projects link Access to SQL Server and in a
production environment the Access database must (should) be secured. I'm
sure somebody else has these requirements.
Please let me know if there is a way to solve this problem.
Thanks,
Robert