Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 31st, 2005, 05:01 AM
Authorized User
 
Join Date: Jul 2003
Location: , , United Kingdom.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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

 
Old March 31st, 2005, 11:00 AM
Friend of Wrox
 
Join Date: Jan 2004
Location: , , USA.
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old April 1st, 2005, 05:29 AM
Authorized User
 
Join Date: Jul 2003
Location: , , United Kingdom.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old April 1st, 2005, 06:14 AM
Authorized User
 
Join Date: Jul 2003
Location: , , United Kingdom.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


 
Old April 5th, 2005, 03:19 AM
Authorized User
 
Join Date: Jul 2003
Location: , , United Kingdom.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old November 30th, 2007, 07:31 AM
Friend of Wrox
 
Join Date: Oct 2006
Location: Northampton, Northants, United Kingdom.
Posts: 114
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
LINKED Servers. WebLadyBug SQL Server 2005 1 April 9th, 2007 01:27 AM
Linked Servers rklio SQL Server 2005 0 February 5th, 2007 12:00 PM
Linked Servers msrnivas .NET Web Services 1 January 8th, 2005 09:27 AM
linked servers msrnivas General .NET 1 November 26th, 2004 01:19 AM
linked servers bukky Classic ASP Databases 1 March 6th, 2004 06:31 AM





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