p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   Linked Servers (http://p2p.wrox.com/showthread.php?t=27517)

anothervbaddict March 31st, 2005 05:01 AM

Linked Servers
 
:( Does anyone have an examples of how to link a password protected MSAccess database into SQL Server 2000 please?

I've tried using the following stored procedures:
sp_addlinkedserver
sp_addlinkedsrvlogin

The actual link is created fine, but selecting the Tables produces:
 
Quote:

quote:Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.


The actual parameters used for the stored procedures are:
sp_addlinkedserver:
@server='TestDB'
@provider='Microsoft.Jet.OLEDB.4.0'

(I've then tried @datasrc or @provstr separately)
@datasrc='c:\test.mdb'
@provstr='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.mdb;Jet OLEDB:Database Password=xyz'

sp_addlinkedsrvlogin:
@rmtsrvname='TestDB'
@useself='False'
@locallogin=Null
@rmtuser='Admin'
@rmtpassword='xyz'

If anyone can shed any light on this matter or show me what I'm doing wrong, would be very grateful.

Thanks in advance.
Carl


shahchi1 March 31st, 2005 11:00 AM

http://support.microsoft.com/default.aspx?scid=285833


anothervbaddict April 1st, 2005 05:29 AM

Thanks for the link. Have tried these and various other combinations and still not having any luck.

1. Do I need to use a .mdw file (currently not using one as I don't need user level security)
2. As the default user in MSAccess is 'Admin', does there need to be an 'Admin' user in Logins associated with Linked Servers. (Have set one up but no apparent effect).
3. With sp_addlinkedsrvlogin I've tried the following values for @locallogin: Admin, sa, Null
4. @rmtpassword was set to Null

I have proved that the linked server can work, by taking the database password off the MSAccess database. As soon as I put the password back on I get the Error 7399 again.

I have also proved that using a password with the 'Admin' user (in MSAccess database) and not using a database password does work if the registry key does point to system.mdw (but this isn't what I was after).

Any addition advice? I can't see the woods for the trees at the moment.

Thanks,
Carl


anothervbaddict April 1st, 2005 06:14 AM

Further to my last reply ...
If I remove the database password from the MSAccess database, then add a password to the 'Admin' user and use the examples from the Microsoft link, it does work.
Unfortunately this isn't what I'm after as I need to retain the database password on the MSAccess database.



anothervbaddict April 5th, 2005 03:19 AM

If anyone had this problem and was using the Search and found this thread, here is my work-around.
I don't actually link the MDB but using T-SQL try the following:

 
Quote:

quote:SELECT * FROM
Quote:

OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\mytest.mdb;
Persist Security Info=False;
Jet OLEDB:Database Password=mypassword')...MyTable
I've also tried the Data Source as a network path and that also works. The only thing I can't confirm is the Jet drivers on the same box as the SQL Server installation.


seananderson November 30th, 2007 07:31 AM

I tried your workaround and got the following

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Regards,

Sean Anderson


All times are GMT -4. The time now is 02:19 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.