Solved

From within sql server, to read access mdb which has a password.

Posted on 2011-03-07
9
1,530 Views
Last Modified: 2012-06-27
SQL Server 2005
Access 2002

select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'\\mateen\pbapps10\Distr\Acc\test_with_password.mdb';
'admin';'',grpmf) a

this works
but now I put a password 'expert' to 'test_with_password.mdb'

select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'\\mateen\pbapps10\Distr\Acc\test_with_password.mdb';
'admin';'expert',grpmf) a
and this does not work. the error message is

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I dont want to first unset the access mdb password.
My object/target is to read the outsource access mdb with password.
Is it possible
0
Comment
Question by:Mehram
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 9

Assisted Solution

by:mayank_joshi
mayank_joshi earned 250 total points
ID: 35066434
If you are trying to open an external file such as a TXT, XLS, CSV,MDB using SQL's OPENROWSET or OPENDATASOURCE functionality and keep getting errors that look like the ones below then here is the fix/workaround.

The Errors:

OLE Database provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE Database provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


The fix:

What you need to do is visit the link below and download the

2007 Office System Driver: Data Connectivity Components download

Once downloaded and installed on the SQL Server you will notice a new driver in the list of drivers on the server at this path:

SERVER\Server Objects\Linked Servers\Providers

The new driver is called:

Microsoft.ACE.OLEDB.12.0

All you then need to do is change your SQL Query to use this new driver rather than the old Microsoft.Jet.OLEDB.4.0 one.

Please follow the steps as given below

1. Execute the following statement in your SQL Server 2008 :

SP_CONFIGURE 'Ad Hoc Distributed Queries'


If It results any Err (or) run_value = 0 then , Do the following steps... Otherwise skip the steps

SP_CONFIGURE 'show advanced option',1

RECONFIGURE

GO

SP_CONFIGURE 'Ad Hoc Distributed Queries',1

RECONFIGURE



2. Try the scripts now

SELECT Column1, Column2

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'C:\Documents and Settings\ smu\Desktop\Website\FileUpload\FIALED_PAPER_LIST_JAN2010_01042010.mdb';

      'admin';'',TABLE_NAME)


Note:
TABLE_NAME should be the name of the Table which is available in the mdb (FIALED_PAPER_LIST_JAN2010_01042010.mdb)

 That should sort you out.
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35066553
As an alternative you can try  via linked server:-

EXEC master.dbo.sp_addlinkedserver @server = N'yourmsaccessdb', @srvproduct=N'yourmsaccessdb'', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'C\yourmsaccessdb.mdb'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'yourmsaccessdb',@useself=N'False',@locallogin=NULL,@rmtuser=N'Admin',@rmtpassword='########'

Open in new window

0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35066656
then:-

SELECT * FROM yourmsaccessdb...yourTableName

Open in new window



Further Refrence:-

http://www.aspfree.com/c/a/Microsoft-Access/Configuring-a-Linked-Microsoft-Access-Server-on-SQL-2005-Server/1/

0
 

Author Comment

by:Mehram
ID: 35066778
Hi mayank_joshi

The second method shows the exact error which i said in the question. Therefore, I would go after your first suggestion.

Thanks for your help
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Expert Comment

by:lalitgada
ID: 35067252
use this site

connectionstring.com

in this site you get all the connetion type.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35069014
And you should know that there is no JET 64-bit driver, so if you are using SQL Server 2005 64-bit you cannot use JET.
0
 

Author Comment

by:Mehram
ID: 35078869
Hi Aceperkins

<<And you should know that there is no JET 64-bit driver, so if you are using SQL Server 2005 64-bit you cannot use JET.>>

Then why in  the world  I am able to read unprotected access database (The following code works)
select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'\\mateen\pbapps10\Distr\Acc\test_with_password.mdb';
'admin';'',grpmf) a
but unable to read protected access database
the following code same as above with password given does not work
select *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'\\mateen\pbapps10\Distr\Acc\test_with_password.mdb';
'admin';'expert',grpmf) a

The alternate way linked server method also does not work with protected access db and the error is exactly the same as with openrowset method. Is it a bug of SQL 2005


0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 35082990
>>Then why in  the world  <<
That would be because you are not using a 64-bit version of SQL Server 2005.

But to answer your question, I have no idea why you cannot read an "unprotected" MS Access database.
0
 

Author Closing Comment

by:Mehram
ID: 35093486
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now