C# - XLSX to SQL Server - OpenRecordSet not working in C# but works in MS SQL Server Management Studio

Question

The following script works within a SQL Server 2014 Management Studio in a Stored Procedure but not when I call the stored proc via a C# app .NET Framework 4.8. SQL Code:

create proc getData
as
Insert INTO tmpLeaveImport ([CarName], Year, Make , Model) 
    SELECT *  FROM OPENROWSET('Microsoft.ACE.OLEDB.15.0',
    'Excel 12.0;Database=E:\Cars\Cars.xlsx',
    'SELECT * FROM [Car Report$]')

From C# I get the following error:

System.Data.SqlClient.SqlException: 'Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.15.0" for linked server "(null)". OLE DB provider "Microsoft.ACE.OLEDB.15.0" for linked server "(null)" returned message "Unspecified error".'

When this code is executed in C#:

  //Tried the conn strign with Integrated Security=yes and SSPI
  string ConnString = @"Data Sournce=MySQLServerDB;Initial Catalog=DBName;Integrated Security=true;";
  using (SqlConnection conn = new SqlConnection(ConnString))
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                conn.Open();
                cmd.CommandText = "getData";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
            }
            conn.Open();
        }

Answer 1

Short Version

According to this possibly duplicate question the Excel file may be open. Or this could be a more serious error.

Don't use OPENROWSET to import Excel data into SQL Server. Use a library like ExcelDataReader to read it without using the Access Engine and insert it to the target table with SqlBulkCopy. You'll avoid a lot of pain.

using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        using (var bcp = new SqlBulkCopy(connString))
        {
            bcp.DestinationTableName ="SomeTable";
            bcp.WriteToServer(reader);
        }
    }
}

Long Version

In both cases, the stored procedure runs on SQL Server, not on the client. SSMS is just another client as far as SQL Server is concerned. Assuming the same server is used in both cases, what's different is that the account that executes the stored procedure is different in each case.

With SSMS, it's the developer's account which quite often has sysadmin privileges on the server. With C#, the account may be the end user's, or the application pool account that runs a web site, which has very restricted privileges. SQL Server's default service account is a restricted account too.

This matters because the Access Engine is a COM component. To use it, applications need to look it up in the registry, which requires its own permissions. If you search SO for the error you got you'll see questions where the choice of service account affected whether Access Engine can be used or not. In other cases, the file was open.

Another potential problem is that ACE must target the same architecture as any previous Office components installed on a machine. If you have a x86 Office application, you can only install the x86 version of ACE. That's because you can't use COM component created for one architecture from a process that targets another one.

This also means you can't use an x86 ACE in a x64 installation of SQL Server, which is the most common installation option in the last 10+ years.