{"id":631,"date":"2014-02-14T23:04:09","date_gmt":"2014-02-14T23:04:09","guid":{"rendered":"http:\/\/elbsolutions.com\/projects\/?p=631"},"modified":"2022-02-03T11:25:03","modified_gmt":"2022-02-03T17:25:03","slug":"sql-server-access-ms-2010-access-database-password-finally","status":"publish","type":"post","link":"https:\/\/elbsolutions.com\/projects\/sql-server-access-ms-2010-access-database-password-finally\/","title":{"rendered":"SQL Server access MS 2010 Access database with password. Finally"},"content":{"rendered":"

So I have had LOTS of attempts to get into a password protected MS 2010 Access database and most people online say it cannot be done (the advice in many google’ed articles) , however one guy gave me the hint to get this solved for me in “Answer 4”<\/a>.<\/p>\n

Scenario:<\/p>\n

    \n
  1. I am connected to SSMS on domain #1 called F which is a SQL Server on Domain D<\/li>\n
  2. MS Access file is on Domain D on the c drive c:\\Temp\\dB123.accdb (this c: drive is the one where the SQL Server is running)<\/li>\n
  3. In SSMS, I made a new Linked Server using the GUI\n
      \n
    1. Server Objects->Linked Server->R Mouse Click New Linked Server<\/li>\n
    2. Type TEST2 in the linked server<\/li>\n
    3. Chose Other data source<\/li>\n
    4. Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider\n
        \n
      1. for this to show up you need to install some s\/w, run 3 queries AND set a registry entry on the same computer that SQL Server is installed on. See this article I have already written.<\/a><\/li>\n<\/ol>\n<\/li>\n
      2. Product Name: c:\\temp\\dB123.accdb\n
          \n
        1. <I think can be anything you want it to be><\/li>\n<\/ol>\n<\/li>\n
        2. Provider String: ;PWD=your_fav_pwd\n
            \n
          1. HERE IS WHERE YOU PUT THE PASSWORD AND NO WHERE ELSE.<\/strong> Not sure if you need the preceeding ; or not but just put it in cause it works.<\/li>\n<\/ol>\n<\/li>\n
          2. Press OK. If it is all correct, it will just work!<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

            Do you want a script to do this? Simply right mouse on TEST2 and choose “Script Linked Server As->DROP AND CREATE TO->File” and save it somewhere.<\/p>\n

            So you want to be able to test it? If you look at the article that I sent you – try the one that didn’t work for him.<\/p>\n

            \r\n\r\nSelect * FROM TEST2...AreaT\r\n\r\n<\/pre>\n
            And you get results. Holy smokes it works after hours of try this and that and googling.<\/p>\n
            Synonym to this dB? No problem<\/p>\n
            \r\n\r\nCREATE SYNONYM [dbo].[12313_EHT_AreaT] FOR TEST2...AreaT\r\nGO\r\n\r\nSELECT * from [dbo.12313_EHT_AreaT]\r\n\r\n<\/pre>\n
            Now was that so hard? The short answer is yes – very hard!<\/p>\n
            What if you run this SP from lets say ASPX app with a diff user?<\/h2>\n
            OK, so it all worked in SSMS – but when the aspx app ran it , I got an error<\/p>\n
            The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “TEST2” reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “TEST2”. OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “TEST2” returned message “Cannot start your application. The workgroup information file is missing or opened exclusively by another user<\/p>\n
            Crap – so now what? Well, the Stored Proc which had the code – that worked now – was being called from ASPX as another user called AppUser-Module-Summary-User (the user in the connection string). This Stored Proc needed a GRANT for this user to EXECUTE it – that is assumed that this is done – it is the rest that we have to do. If you to to TEST2->Properties, go to the 2nd Security tab and add a user<\/p>\n