当前位置: 动力学知识库 > 问答 > 编程问答 >

Issue accessing MS Access database via ASP Classic / SQL Server linked server in Server 2012

问题描述:

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!

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: