學海無邊，書囊無底

使用Login1 (伺服器層級擁有sysadmin權限)的帳號登入SQL Server 執行以下語法查詢 excel 資料可正常執行

但使用Login2 帳號(伺服器層級僅擁有public，加上某個資料庫的dbo權限)，執行以下語法出現以下錯誤訊息

SELECT *  FROM OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;
   Database=D:\SSIS\ExcelData.xls'
, 'SELECT * FROM [備註$]')

訊息 7415，層級 16，狀態 1，程序 vExcelData，行 13
特定存取至 OLE DB 提供者 'Microsoft.ACE.OLEDB.12.0' 已經遭到拒絕。您必須透過連結伺服器來存取此提供者。

解決方法：Manually modify the registry, and add the DisallowAdHocAccess value

檢視'Microsoft.ACE.OLEDB.12.0　屬性時可看到 allowAdhotAccess屬性不在此UI介面

不過仍就可以藉由修改regedit 將DisableAdhotAccess屬性加入

Value name: DisallowAdHocAccess

Data type: REG_DWORD

Radix: Hex

Value data: 0

修改regedit 之後，Login2 帳號就可以查詢了 :)

注意事項：

值的變化DisallowAdHocAscess從1到0將不需要SQL服務重新啟動，而從0變為1就必須有一個SQL服務重新啟動該作出生效的變化

參考：https://support.microsoft.com/en-us/kb/327489

http://dba.stackexchange.com/questions/61739/help-with-sql-server-error-ad-hoc-access-to-ole-db-provider-microsoft-ace-oled

補充：錯誤訊息 7303

訊息 7399，層級 16，狀態 1，行 1
連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 報告了錯誤。提供者並未給予任何關於錯誤的資訊。
訊息 7303，層級 16，狀態 1，行 1
無法初始化連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 的資料來源物件。

參考：Cannot initialize datasource for linked server implementation

we were able to find the permissions problem.  It turns out that by default, the SQLEXPRESS service is set up to log on using the "NT AUTHORITY\NetworkService" Account.  The permissions problem (with the original linked server) occurs because the .JET. engine creates a few temporary files in the Network Service temp folder (\Documents and Settings\NetworkService\Local Settings\Temp).  We used the SysInternals FileMon tool to track this down (a great tool, by the way).

So, the fix is to do one of the following:

- Change the SQL Server's service Log On to "Local System account" OR

- Provide full access to the "\Documents and Settings\NetworkService\Local Settings\Temp" for any account that needs to run the queries.

上段文章敘述中提及的SysInternals FileMon 工具目前已改為Process Monitor v3.2 

下載後 開啟Procmon.exe，即可監控應用程式當下所使用的伺服器路徑

以下範例以Windows 2008為例

透過windows工作管理員，先確認MSSQLSERVER使用的PID

開啟Procmon.exe，設定過慮條件

可在下圖中看到 SQL Server使用本機服務帳戶查詢excle資料時，會在c:\windows\ServiceProfiles\LocalService\AppData\Local\Temp..產生臨時的暫存檔

若SQL Server服務帳戶為 NetworkService 則臨時站存檔會產生於 c:\windows\ServiceProfiles\NetworkService\AppData\Local\Temp中

當 服務帳戶NetworkService存取 excel資料出現下列錯誤訊息時

訊息 7399，層級 16，狀態 1，行 1
連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 報告了錯誤。提供者並未給予任何關於錯誤的資訊。
訊息 7303，層級 16，狀態 1，行 1
無法初始化連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 的資料來源物件。

要確保NetworkService服務帳戶有權存取 NetworkService\AppData\Local\Temp 目錄

若沒有的話，手動加入即可