Sample Header Ad - 728x90

Problems getting SQL Server linked server to work in web-based admin utility on Windows Server 2022

2 votes
1 answer
207 views
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.
Asked by user1207313 (21 rep)
Jan 17, 2024, 05:44 AM
Last activity: Jun 28, 2025, 04:07 AM