Solved

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

Posted on 2011-03-07
9
1,515 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Expert Comment

by:lalitgada
Comment Utility
use this site

connectionstring.com

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

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
Thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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…

772 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

9 Experts available now in Live!

Get 1:1 Help Now