Installing Access Database Engine 2016 x64 alongside Office x86

In Power BI, you can open XLSX files without any additional drivers, but opening XLSB files requires you to first install the Access Database Engine driver (available for free at https://www.microsoft.com/en-us/download/details.aspx?id=54920) or you’ll get an error like this:

We encountered an error while trying to connect. Details: "Excel Workbook: the 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit bersion of the Access Database Engine 2010 Access Database Engine OLEDB provider may be required to read.
‘Microsoft.ACE.OLEDB.12.0’ provider error message

If you have Microsoft Office installed, you already have this component, but most people use a version of Office that’s x86 – since Power BI is x64, you’ll need the x64 version of the driver as well. However, if you try to install it with Office x86 on your computer, you’ll get this error and you’ll be stuch:

Microsoft Access database engine 2016 x64 Installation Error screenshot - "You cannot install the 64-bit version of Microsoft Access Database Engine 2016 because you current have 32-bit Office products installed. If you want to install 64-bit Microsoft Access Database Engine 2016, you will first need to remove the 32-bit installation of Office products. After uninstalling the following product(s), rerun setup in order to install 64-bit version of Micorsoft Access Database Engine 2016: Office 16 Click-to-Run Extensibility Component"
Microsoft Access database engine 2016 x64 Installation Error screenshot

There’s a way to install both! To avoid this error and install the x64 database engine without removing Office x86, you need to download the installation for the Access Database Engine x64 and run the installation with a command line switch:

AccessDatabaseEngine_X64.exe /quiet
Command to install Access database engine using the "/quiet" switch to run to installation without an error

Once you run this command, you’ll get a black command window for a few seconds, and then it will close once it’s done. To confirm that it’s installed successfully, you can either check for “Microsoft Access database engine 2016” in your Windows “Programs and Features” window, or you can just launch Power BI and attempt to import an XLSB file!

I haven’t noticed any ill effects to doing this, but if you do have issues, comment below.

Changing SQL Server data file locations in clustered instance using service SIDs

Recently I added a second LUN to a clustered instance of SQL Server to isolate the logs files (and another for the TempDB) and I mounted them to an empty NTFS folder as I’ve done before, but when I started SQL Server, I recieved the following error:

CREATE FILE encountered operating system error 5 (Access is denied.)
while attempting to open or create the physical file
'X:\MSSQL10_50.INSTANCENAME\TempDB\tempdb.mdf

The error means that SQL Server doesn’t have NTFS rights to the location of the TempDB, but when I tried to add those rights, the permissions weren’t granted to the domain proxy account as I’d expected, but were instead granted to the service SID account, MSSQL$InstanceName. I attempted to grant the permissions to this account at the new location, but couldn’t get it to resolve to an actual account. What finally worked was:

  1. In the permissions change dialog box, change the “Location” from the domain to the local machine (even though it’s a cluster and your using a domain account to run the service)
  2. In the text box, type “NT Service\MSSQL$INSTANCENAME” and click “Check Names”

Even though that appears to be a local account, it will resolve properly on all the cluster nodes involved. This step, as opposed to using the domain proxy account the service is running as, was necessary because (during the initial SQL Server setup process) I’d selected to use the proxy account SID to host permissions rather than a domain group. The better choice permissions-wise, but the source of some confusion!