SQL Server access MS 2010 Access database with password. Finally

Posted on by

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”.

Scenario:

  1. I am connected to SSMS on domain #1 called F which is a SQL Server on Domain D
  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)
  3. In SSMS, I made a new Linked Server using the GUI
    1. Server Objects->Linked Server->R Mouse Click New Linked Server
    2. Type TEST2 in the linked server
    3. Chose Other data source
    4. Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider
      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.
    5. Product Name: c:\temp\dB123.accdb
      1. <I think can be anything you want it to be>
    6. Provider String: ;PWD=your_fav_pwd
      1. HERE IS WHERE YOU PUT THE PASSWORD AND NO WHERE ELSE. Not sure if you need the preceeding ; or not but just put it in cause it works.
    7. Press OK. If it is all correct, it will just work!

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.

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.

[sql]

Select * FROM TEST2…AreaT

[/sql]

And you get results. Holy smokes it works after hours of try this and that and googling.

Synonym to this dB? No problem

[sql]

CREATE SYNONYM [dbo].[12313_EHT_AreaT] FOR TEST2…AreaT
GO

SELECT * from [dbo.12313_EHT_AreaT]

[/sql]

Now was that so hard? The short answer is yes – very hard!

What if you run this SP from lets say ASPX app with a diff user?

OK, so it all worked in SSMS – but when the aspx app ran it , I got an error

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

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

  • goto TEST2->Properties (right mouse click – Properties)
  • Go to the Security Tab (choose this on the left)
  • Click Add in the Users box
  • Choose the user from the pulldown choose our user AppUser-Module-Summary-User
  • Fill in the username
  • Fill in the password
  • Leave the impersonate unchecked
  • The bottom part is for all other users. CLick Be Made Without A Security Context

OK – we are not done. Try a blank password or the password above and … forget about it working. It will either give you an error and if you leave the password blank or look like it is working. Leave the password in for now (heck, put Santa – we will fix it). After doing some digging,  another hint arrived (see this and scroll down to ‘Michael’s suggestion’) which gives JUST enough info to get over this. Do these last steps

Go ahead and look at the script for this user by R mouse clicking on TEST2->Script Linked Server AS->DROP and CREATE to->New Query Editor Window

Lets look at the code before and after. The lines with sp_addlinkedsrvlogin is what we are looking at here. The first is the “other users” that we set above. But the 2nd is for our new user – why is there #### in the @rmtpassword??? Fix it by setting it to an empty string. Fixed

Before

[sql]

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’TEST2′,
@useself=N’False’,@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’TEST2′,
@useself=N’False’,@locallogin=N’AppUser-ModuleSummary-User’,
@rmtuser=N’Admin’,@rmtpassword=’########’

[/sql]

After

[sql]

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’TEST2′,
@useself=N’False’,@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’TEST2′,
@useself=N’False’,@locallogin=N’AppUser-ModuleSummary-User’,
@rmtuser=N’Admin’,@rmtpassword=”

[/sql]

Now it works. Go figure. So like all Access databases, the user is Admin and password is indeed blank. The filename password must be a different password altogether and that is why I was so messed up for so long. MS Access is truely a beast of its own.