SQL Server 2012 Linked Server Authentication (Error 7399 & 7303)

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.
Additional information:
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)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HRISTeamAuthor Commented:
After having the server rebuilt, I am getting a different error.

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FTPsvr".

Additionally I tried to link to the actual servers C & D drives which resulted in the same error.
Daniel_PLDB Expert/ArchitectCommented:
When you use linked server by using OLE DB provider temporary DSN is being created in the TEMP directory for the account that started the SQL Server service. But the OLEDB provider will execute under the account from which query was executed. To be able to use that temporary DSN account issuing query to that file need to have write access to the TEMP directory for the SQL Server service account.
You need to grant issuing user account access (read+write) to TEMP folder for SQL Server service account profile
(C:\Documents and Settings\<sql svc acc>\Local Settings\Temp).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HRISTeamAuthor Commented:
I have admin right to the server (through AD). Is there something that might not be allowing my credentials to authenticate?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

HRISTeamAuthor Commented:
Could this be a 64Bit compatibility problem with the "Microsoft.Jet.OLEDB.4.0"?
HRISTeamAuthor Commented:
In an attempt to get the link strategy to work, I switched over to using the microsoft office 12.0 access database engine ole db provider. I can use the linked server only if I log in using the service account for sql server. I don't know why it won't work with my domain account.
HRISTeamAuthor Commented:
I figured out how to get the connection to work. I am using the "Be made with using a security context." and I added the sql server service account to the impersonate list. Now I just need to get the syntax to work.
HRISTeamAuthor Commented:
The post was informative but not really related to my configuration errors and now I have a new problems.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2012

From novice to tech pro — start learning today.