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
view_requests retrieves the data from a table in the Access database:
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
Any suggestions on what I can try would be much appreciated. Thanks!
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.
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.
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.