0

I have an old ASP Classic site which connects to a SQL Server database which uses a Linked Server to access a Microsoft Access database via OPENQUERY in a view (!), like this:

  • A Linked Server is configured to connect to the Access database using Microsoft.ACE.OLEDB.12.0

  • A view view_requests retrieves the data from a table in the Access database:

    SELECT * 
FROM OPENQUERY(MY_LINKED_SERVER, 'SELECT * from Requests') AS Rowset_1

  • The ASP Classic page retrieves the data: 

    Set getRequests = dbConnect.Execute("SELECT * FROM view_requests")

Since moving it from a Server 2003 to a Server 2012 machine it no longer works.

The error given on the ASP page is:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MY_LINKED_SERVER" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

But I have a feeling this is misleading for several reasons. The error seems to occur in lots of different scenarios and none of the posts I've found represent mine exactly. The main thing to note is that the view works fine and I can therefore access the data via SQL Server, which to me suggest it's not an issue with user mappings and so on, but I may be wrong. The problem occurs when connecting via the ASP Classic page. We've tried using both .mdb and .accdb formats.

Any suggestions on what I can try would be much appreciated. Thanks!

Improve this question
2
  • Hi. I'm not connecting to the Access db via ASP Classic, I'm connecting to a SQL Server view, which in turn connects to the Access db via OPENQUERY. Thanks Commented Aug 24, 2015 at 15:50
  • Good point. Should have realised that. Commented Aug 24, 2015 at 16:03

3 Answers 3

Reset to default
2

This sounds like it might be a permissions problem (I was going to say 'access' but that came out confusing!).

How are you controlling access to the SQL database? Is it Windows Authentication or using a SQL logon? If it's Windows Auth, then you need to give either the calling user's (if using Impersonation on the web site) or the Windows Identity that ASP.NET is running under access to the Access database file. If using a SQL Logon then you need to give the Windows Identity that SQL Server is running under that access.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

"The problem occurs when connecting via the ASP Classic page." Where in that sentence does the OP mention ASP.Net?
If it was permissions it would report Access Denied in this case it quite clear the file is open somewhere else, properly in a copy of the MS Access program.
Fair point. That just takes the Impersonation bit out of the equation though.
You're probably right. I just assumed that the user would have eliminated something that obvious though. Probably not sensible of me...
It could be a red herring though, the issue is likely to do with how linked server queries through the ACE provider work. Or it's looking in the wrong place.
|
1

The error is not necessarily accurate and from my experience, it probably is not. The account that SQL Server is running under (NOT the SQL Login) must have full permission on the folder where the Access file is located. Another possibility is that the linked server is not using the correct ACE driver, or it is not installed. If using SQL Server 64 bit, you'll need the 64 bit ACE driver.

Improve this answer

3 Comments

I've checked the version of the ACE driver and it's definitely 64 bit. As for the "The account that SQL Server is running under", we've checked this and it was "NT SERVICE\MSSQLSERVER". We've now changed this to run as the domain administrator and it still fails with the same error. Thansk!
To fully eliminate the folder permissions issue, temporarily give full permission on the folder to 'everyone'. Make sure that the Access file is not in use by anyone else, delete the linked server, restart SQL Server and re-create the linked server. Yes, this is probably overkill, but could help track down the issue.
Finally got around to trying your suggestions and we got the error "Access to the remote server is denied because no login-mapping exists". We've seen this error a few times while trying different things to fix the issue, but I've looked into it further and I think I was mapping the user incorrectly. I've removed the Everyone access now and it still works, so I think that was the issue. Thanks for pointing me in the right direction!
0

It seems the issue was that we were incorrectly mapping the SQL user to the remote server (Linked Server > Properties > Security), even though it seemed we'd tried every possible combination of settings under the sun! So it seems the original misleading error was trying to tell us that the user didn't have the permission.

It is currently working with the Local Login mapped to Admin, the default Access database user which has a blank password. Under For a login no defined in the list above, connections will: we have selected Be made using the login's current security context.

Thanks to AVG for pointing me in the right direction.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.