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?
Daniel_PLConnect With a Mentor DB 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).
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.
HRISTeamAuthor Commented:
I have admin right to the server (through AD). Is there something that might not be allowing my credentials to authenticate?
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.