Post reply

Error while connecting a password protected MS access 2003 database from MS SQL 2005/2008

  • June 14, 2011 at 8:17 am

    #99396

    Hi all,

    I have done the following steps:

    1.Assign the necessary network permissions to access both the access workgroup information file and the MA Access database to the network account used to start SQL services (tested accessing the database while login with this account on the computer),

    2.Modify the registry key \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB to point to UNC of the Access workgroup information file (*.mdw).

    3.Create a C:\Temp folder and give full access permission on that folder to the account used to start MS SQL services

    4.For the account used to start the MS SQL services, set the environment variable TEMP and TMP to C:\Temp

    5.Restart MS SQL services

    6.Create the link server to the database using: @server = Servername , @Provider=’Microsoft Jet 4.0 OLE DB Provider’, @datasrc=’\\server\path\database.mdb’ (UNC); and set to remote users, 1 SQL user and 1 AD integrated user, both connect to the Access database using the same remote user and password.

    This is what I get:

    When I connect via the query editor of SSMS using the SQL account, I can query the tables on the linked server, but when I connect using the AD integrated account, I get the following message:

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Servername" returned message "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.".

    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 "Servername".

    Any idea why?

    Thanks a lot,

    Uvernes

  • June 14, 2011 at 9:03 am

    #1338448

    based on the error, is there any chance someone has the database open? the linked server requires exclusive access, right? that error "It is already opened exclusively by another user, or you need permission to view its data."." makes me think it's open right now.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 14, 2011 at 9:16 am

    #1338460

    Hi Lowell,

    Thank for the tip, but, not, the message is not really significative, the database is not open.

    Thanks a lot,

    Uvernes

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply