MS Access as a Linked Server
I needed to simulate an Oracle server, but didn’t like the idea of trying to install and run Oracle Express. So I created a linked Server pointing to an Access MDB with tables linked to a SQL Server, which had tables dumped from the Oracle Server. This allows for OPENQUERY queries without the “db.dbo” prefix that you’d need if you’d just referenced the SQL database directly. You know a database is hard to use when you can’t convince database professionals to use the free copy installed on their machine.
These are the things I did to get my lined server working:
- Switch connection string from SQL authentication to Windows Authentication.
Without that, you get can’t find installable ISAM or authentication errors.
What didn’t work
- Giving more rights to IUSER and ASPNET account (this was only failing in my web app, not in SQL Server Management Studio)
- Setting the password in the provider string.