SQL 2016 SE - error 7303: remote execution OK, but fails on localhost!

TIUK1 96 Reputation points
2021-10-07T17:16:45.4+00:00

Hi all,

I have a rather annoying situation. I need to import a set of CSV files stored on the local file system to an equal number of tables. The solution is currently setup on one on premises server, which is being forcibly retired. I am in the process of migrating the solution to a SQL instance running on a Windows 2019 server in Azure.

A quick run down of the solution itself:

  • I create a linked server to the folder on the local file system which contains the files to be imported, using Microsoft.ACE.OLEDB.12.0 as the provider. The folder contains a working schema.ini file.
  • I import each of the files by executing individual stored procedures, using OPENQUERY. For example, ... FROM OPENQUERY(CSV,''SELECT * FROM EAUNH001#csv'')
  • I then delete the linked server
    The basic SQL script is attached. Stored Procs are long, and irrelevant
    138579-import-csvs.txt

If I run this script from my laptop using SSMS it works fine.

However, if I run it from the SQL Server box itself logged in with my user account (just as I do from my laptop), it fails. If I browse to the linked server catalogue to see the files listed under default, I get a 7303 error. The error is pretty much the same when running the script and failing to browse the "CSV" linked server.
138569-7303.png

To reiterate, these CSV files are stored on the SQL box's local file system.

And now here comes the real kicker. If I open SSMS from the SQL Server box logged in as me but elevated (i.e. "run as Administrator"), then the script works just fine. And I can browse the linked server "CSV" catalogue, no problems.

So what gives? I have tried the following to no avail:

  • disabling UAC via the Control Panel
  • adding the AD accounts that are running the DB svc and the SQL Agent svc to as many Temp folders as I can find
  • set Disallow Ad Hoc Access to false on the ACE 12 provider
  • set Allow in Process to true on the ACE 12 provider

Please help! I need to run this from the SQL Server box as a SQL Agent job.

thanks in advance!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,317 questions
{count} votes

Accepted answer
  1. TIUK1 96 Reputation points
    2021-10-13T11:02:36.873+00:00

    My daughter was born recently, and I have no time now to look at this issue. In other threads I have read on this issue, one of which is posted above, the answer to the problem is to "run as admin", which is not really a solution for obvious reasons. No-one on this thread has so far come up with a workable answer or even a definitive explanation, it has all been too vague to be of any practical use. In the end, perhaps "this is on of those mysteries the authorities felt was best left unsolved" - Spinal Tap.
    Nevertheless I would like to say thanks for trying.

    In the end I had to rewrite the entire solution using BULK INSERT. Not ideal for reasons I don't have time to explain.

    0 comments No comments

6 additional answers

Sort by: Most helpful
  1. Ronen Ariely 15,106 Reputation points
    2021-10-07T18:22:25.45+00:00

    Good day @TIUK1 and welcome to the Microsoft QnA forum,

    It seems like you selected the solution only since you learned about Linked Server and you now apply it in any case even if it is totally not fits best. I don't know if this is the reason, but I saw it several times. One time we send an employee to course and when he came back he was worse then when we sent him since he started to enforce himself to apply everything that he learned and he messed up the system - it was very sad.

    I need to import a set of CSV... I create a linked server

    Why? The simplest option for most cases is to use bcp

    https://learn.microsoft.com/en-us/sql/tools/bcp-utility

    I import each of the files by executing individual stored procedures, using OPENQUERY. For example, ... FROM OPENQUERY(CSV,''SELECT * FROM EAUNH001#csv'')

    If you are using OPENQUERY then you can probably also use OPENROWSET instead, without any need to use linked server

    https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver15#format

    cannot initialize the data source object of ole db provider "Microsoft.ace.oledb.12.0" for linked server

    The most common case is that the user does not have the permission or that "Microsoft.ace.oledb.12.0" was not enabled/installed, but there might be other reasons as well.

    Check if the following thread help you solve this in your case, and if not then come back with information about what you tried already - assuming you still want to use Linked Server

    https://stackoverflow.com/questions/26267224/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null

    0 comments No comments

  2. Seeya Xi-MSFT 16,461 Reputation points
    2021-10-08T03:02:26.957+00:00

    Hi @TIUK1

    Agree with pituach. > The most common case is that the user does not have the permission or that "Microsoft.ace.oledb.12.0" was not enabled/installed, but there might be other reasons as well.
    Please refer to this article: SQL SERVER – Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server.
    Hope it could be helpful for you!

    Best regards,
    Seeya


    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. TIUK1 96 Reputation points
    2021-10-08T09:24:34.847+00:00

    Hi both,

    If you read my question, you will see that the issue really has nothing to do with linked servers, so my reason for using them is irrelevant.

    Scenario 1:
    I log into a laptop (remote client) and launch SSMS as domain\user1, without elevated permissions
    I connect to the instance using Windows Auth (i.e. as domain\user1)
    I run a script that queries files stored on the server's local file system using the ACE 12 provider
    It works totally fine

    Scenario 2:
    I log onto the server running SQL and launch SSMS as domain\user1, without elevated permissions
    I connect to the instance using Windows Auth (i.e. as domain\user1)
    I run a script that queries files stored on the server's local file system using the ACE 12 provider
    It fails

    Scenario 3:
    I log onto the server running SQL and launch SSMS as domain\user1, WITH elevated permissions
    I connect to the instance using Windows Auth (i.e. as domain\user1)
    I run a script that queries files stored on the server's local file system using the ACE 12 provider
    It works

    How is it possible that scenario 1 works without having to consider UAC, but when I execute from the server (where the files are, located, I have to elevate. I would kind of expect it the other way around, but this makes no sense to me.

    Can you explain it? Ignore linked servers. FYI I did start out using OPENROWSET, and it obviously has the same problem. But all that history is irrelevant and distracting, so I left it out. I simply want to fix it as it is currently setup. I need to solve 7303 error, and the answer lies between scenario1, 2 and 3.

    Thanks

    0 comments No comments

  4. Erland Sommarskog 106.5K Reputation points
    2021-10-08T21:17:06.69+00:00

    In all three cases, run

    SELECT 1 AS [testcase], * FROM sys.login_token
    

    and save result into a table, and compare which tokens you have avilable. Then look at the permissions for the folder. It could be that when you log in on your laptop, you get a token which you don't get on the server, unless you activate UAC. And that token is required to have permission to the files.

    0 comments No comments