I have an IIS-hosted ASP.NET website which is trying to query an MSAccess database file using an OdbcConnection.
When I run the code in LinqPad (under my own account) it works fine:
var databaseFilePath = @"C:\...\database.accdb";
var conn = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="
+ databaseFilePath;
using (var odbc = new OdbcConnection(conn))
{
odbc.Open();
var cmd = new OdbcCommand(@"SELECT COUNT(*) FROM MyTable", odbc);
Console.WriteLine($"Count = {cmd.ExecuteScalar()}");
}
However, on the very same machine when I run this code from IIS (running under application pool identity) it fails with the following exception:
Exception Type: System.Data.Odbc.OdbcException
Error message: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
What do I need to do to "permission" the app pool identity account to the MSAccess driver (if indeed this is the issue)?
