2

I have an ASP classic web application based on an SQL Server database, with an admin component. The database has a linked server with Microsoft.ACE.OLEDB.12.0 provider to allow bulk import of records from an access database. Over the years, I have gotten it to work on Windows 2008 server, 2012 server, and Windows 10 (my development machine), though not without losing some hair.

Now I am trying to port the app to an AWS EC2 instance with 2022 Server Datacenter edition, and can't make it work. I can run a linked server query in a SQL Server Management Studio window with no problem, so it works to that extent -- the linked server exists and works in principle. But if the same query is attempted from the web app, it fails with:

"Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "[name of server]" returned message "Unspecified error".

The error message seems very uninformative -- just a long-winded way of saying the query didn't work for unknown reasons. It's possible to query the SQL server directly from the web app, and to query the Access database directly from the web app using the same provider as used by the linked server. Only linked server queries fail.

Because it works from my admin login via Management Studio but not from an IIS process, the problem could be 1) permissions; or 2) something about configuration of IIS or its application pools or the ability of the query to work from within an IIS process.

Regarding permissions, I already made sure IUSR has full control of the folder where the linked server Access database sits. Based on other research, I tried firing up Dcomcnfg and giving IUSR full permissions for MSDAINITIALIZE (even though this was not necessary on my 2012 Server or Windows 10 machines). It didn't help.

Regarding configuration, "Allow in process" is checked for the linked server provider, same as for my earlier machines, where linked server queries are successful from the web app.

1 Answer 1

0

After poking at the problem for another day, I figured out that IUSR needs permissions to the folder "C:\Windows\ServiceProfiles\MSSQL$SQLEXPRESS\AppData\Local" -- so it's a matter of tracking down the relevant temp folder in the version of Windows server on which the database is running. I still have problems with sporadic w3wp.exe crashes, but that's probably another problem.

0

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.