I am working with SQL Server 2012 and I would like to create a linked server on a shared network drive. I have several TXT and CSV files that are ftp’d to the shared directory nightly. To avoid reimporting the files every day I thought linking the files to the shared drive was a pretty good solution. (http://www.users.drew.edu/skass/sql/TextDriver.htm
I had a similar process working on a local instance SQL Server Express 2005 / 2008 on my laptop using “login’s current security context” but once I tried to replicate the functionality on an Enterprise SQL Server I ran into several problems. I was hoping to get some suggestions on what might be configured incorrectly. I attached a screenshot of what I have set up now that doesn’t work.
I attached a screen shots to show what I have configured (LnkSvr_All_01.jpg). The login SQLuser has access to \\Domain\Shared\. If I login to windows server 2012 using SQLuser, I can navigate to the shared drive using windows explorer. However in SQL server management studio, I am apparently lacking some authentication piece. Any assistance would be greatly appreciated.
The test connection to the linked server failed.
An exception occurred while executing a Transaction-SQL statement or batch.
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “FTPsvr”
OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “FTPsvr” returned message “Unspecified error”. (Microsoft SQL server, Error:7303)