Basically my end-users are getting Cannot initialize the data souce object of OLE DB Provider "Microsoft.ACE.OLEDB.12.0" for linked server "ICD9_EXCLUSIONS"/ (Microsoft SQL Server, Error:7303. when opening up the linked server in SSMS.
I am running Sql Server 2005 sp2 on a windows 2003 r2 x64 platform in a clustered environment.
I have downloaded the x64 office driver as you see above. When I log into SSMS and open the linked server I can see everything no problem, run queries, no issue when I set the linked server to use login's current security context.. I can create a local sql server account give it SysAdmin, it can access the excel file when I set it to be made without using a security context
However when I take away SysAdmin from the local user account, it can no longer access the excel file, For other domain users, regaurdless if I grant sysadmin or not they can not access the linked server at all.
I set the permissions for the directory and the file to everyone full control. to try and take that out of the equation. I have tried making the .xls file into an .xlsx file, and changing the provider string from Excel 8.0 to Excel 12.0 as needed. I have moved the file from a network share to a local drive on the server. I am sure its something I am just overlooking but its driving me absolutely batty.