I am trying to create linked servers from SQL 2000 and 2005 to a network share csv file. I am using Microsoft Jet 4.0 OLE DB Provider with "text" as the provider string.
With regards to security, I have tried the following scenarios:
- Authentication "be made using the login's current security context" - doesn't work for sysadmin and other users
- Created a login, granted full permissions to this csv file and using the same login in
No luck for sysadmin and other users
- Created the same login on SQL server, added the user to master database and used it in the following with impersonation and without impersonation:
this works for myself (system admin) but not for other AD users which do have access to same SQL server database and file location. Error:
[OLE/DB provider returned message: Unspecified error] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ]. Msg 7399, Level 16, State 1, Line 3 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Can anyone please help?