July 7, 2004 at 1:01 pm
Hello
How can I create a linked server to an Access 2000 database protected with a password?
Thanks in advance
July 7, 2004 at 3:02 pm
I have done this with an Access 97 password protected database from SQL 2000 before. You should be able to plug in your db location in the data source box, which version of Access in the Product Name box. Then, on the Security tab, choose "Be made with this security context:" and enter the user and password.
July 7, 2004 at 3:40 pm
The problem is that my Access database is protected with just a password, without user. When I define the linked server, I can't leave the login in blank and when I enter any login, authentication fails.
July 8, 2004 at 8:17 am
I have not found any way to link to the access database from SQLServer if it has a password set using Tools/Security/Database Password in access. All the access methods work on userids/passwords set at the user level. The same is true for linking to the access database using DTS and ODBC/OLEDB. None of them work if the database has a database password on it. You will have to remove the database password. If you are worried about securing the database set up user ids with passwords inside the access database (I have never done this so I don't know how secure it is). Every access database comes with user id 'Admin' included, with no password set.
Hope this is clear.
Peter
July 8, 2004 at 8:59 am
Thanks Peter. Did you ever try to create a linked server to an Access Database with password protection using Microsoft OLE DB Provider for ODBC Drivers and specifying an ODBC System DSN as datasource?
I did it and I can connect to Access database through the linked server using Enterprise Manager (I can see all the tables in the database), but when I try to perform a query in Query Analizer (e.g. SELECT * FROM mylinked server...mytable), the query fails with the following error:
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'
Do you have any idea about it?
Thanks in advance,
Lavin
July 8, 2004 at 10:06 am
How did you setup linked server, Using Microsoft OLE DB Provider for ODBC or Microsoft Jet OLE DB Provider?
July 8, 2004 at 11:55 am
I first tried with Microsoft OLE DB Provider, but I could't find a way to authenticate correctly. Then I created a system DSN, specifying the password (without user name) and setup the linked server using OLE DB provider for ODBC and, in this case, I can see the access database tables in Enterprise Manager but the query SELECT * FROM mylinkedserver...mytable (where mylinkedserver is the linked server name and my table is an access database table) fails with the error
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'
July 8, 2004 at 12:00 pm
I think in Query Analyzer you can use OpenQuery function, like this:
select * from OpenQuery(<linked server name>, 'select * from <tablename>')
July 8, 2004 at 12:15 pm
Try SELECT * FROM mylinkedserver.yourcatalogname..mytable, your catalog name can be found from EM by click tables under the linked server.
Have you try to use Microsoft Jet OLE DB Provider to create linked server?
July 8, 2004 at 12:35 pm
Allen, the query SELECT * FROM mylinkedserver.yourcatalogname..mytable doesn't work, fails with the same error. I look at Enterprise Manager and, when I select Tables under the linked server, the Catalog Column shows the full path of the access database, so I replace 'yourcatalogname' in the query by the database's name.
I have tried to use Microsoft Jet OLE DB Provider, but I couldn't find how to supply the password. I selected 'Be made using this security context' (Security tab), user 'Admin' and the database password, but this didn't work
July 13, 2004 at 8:22 pm
How must also have added the lonkedserver login using sp_addlinkedsrvlogin I hope this will resolve the problem.
Kalyan
July 15, 2004 at 3:59 am
You need to put the user password (usually "") for user id Admin in the connection string, not the database password. The only way I can think of bypassing the problem, if you are determined not to remove the database password (especially as you seem to know it), is to:
create an empty access database with no database password
set up link tables to the protected database, using the database password
link to the new access database from SQL server using user id="Admin" and password=""
The only problem with this is if the database password protected .mdb file gets replaced with a new version of the file. In this case you will probably have to set up the access link tables again.
Hope this helps
Peter
July 15, 2004 at 6:27 am
Thanks Peter.
I had try this solution and it works. I couldn't find any other way to connect to the database protected with password.
Thanks again,
Lavin
July 22, 2005 at 4:45 am
You can connect to an Access database that is secured with a database level password if you set up an ODBC System DSN which specifies the DB password and then using the OLE DB Provider for ODBC Drivers to link the server (as I think someone mentioned elsewhere).
You can get over the SELECT * FROM mylinkedserver...mytable issue by including the Catalog show for the Linked Server in EM with square brackets
e.g. I was using c:\test.mdb so the syntax was
SELECT * FROM mylinkedserver.[c:\test]..MyTable
I'm using SQL 2000, Access 2000 by the way.
July 22, 2005 at 7:09 am
Look in BOL
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS a
GO
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply