Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
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

Reply With Quote
  #2 (permalink)  
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

Reply With Quote
  #3 (permalink)  
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

Reply With Quote
  #4 (permalink)  
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.


Reply With Quote
  #5 (permalink)  
Old April 5th, 2005, 04: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.

Reply With Quote
  #6 (permalink)  
Old November 30th, 2007, 07:31 AM
Friend of Wrox
 
Join Date: Oct 2006
Location: Northampton, Northants, United Kingdom.
Posts: 115
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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
LINKED Servers. WebLadyBug SQL Server 2005 1 April 9th, 2007 02: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



All times are GMT -4. The time now is 02:36 AM.


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