2

I've been stuck on this for sometime now. I have an SSIS package thats supposed to read a file and populate a database. I need to run it from a SQL Server Agent Job and the source files to read are located on a folder in another server that I have shared with this server. The shared path to the folder looks like like this: \\server\D\folder\folder
However when I run agent job through a service account it tells me File name property is not valid. Filename is a device or contains invalid characters
The SQL Server Agent uses a service account to run this job. It runs just fine if the source path is located somewhere on the machine where the database instance lives, however I can't get it to run from a shared folder. If I run it myself by right clicking on the SSIS catalog I can run it just fine. I am aware that it is most likely a credentials issue, but all of these servers and accounts were not set up by me. Can someone help me explain how I should go about adding appropriate permissions to the said SA account so it can read the files successfully? Some examples/references would be greatly appreciated!
Things I've tried: Going to the folder security tab and adding all permissions to everyone on both the server where the folder originally is from and the server that the folder is being shared from. I can confirm everyone has the permissions with the windows PowerShell Get-Acl command.
Switching owner of the job task in SQL Server Agent to my account (I don't think its supposed to work ever to begin with) - this makes Agent complain about being "Unable to determine if user has server acces" with SA account it does have server access, it just can't read the folder.
I saw a post where someone suggests to change the SQL Agent Job advanced step option to "execute as user" and change the user with appropriate credentials, but I don't even see that option in my MSSQL.
I have stumbled upon this thread here , it was never really solved it seems but it looks like the 3 steps given should help me:

Assume that we need to write \serv\share\dir1..\dirN\targetDir\somefile.txt using SSIS throught SQL Agent Job and nonadmin proxy account MyDomain\TestAccount

  1. MyDomain\TestAccount need read/write access to share \serv\share

  2. MyDomain\TestAccount needed at least FILE_READ_DATA permission for all folders (share,dir1,..dirN)

  3. MyDomain\TestAccount needed the CHANGE rights + FILE_DELETE_CHILD permission for folder targetDir

    However, me being new to this, I have no idea how to properly check whether or not all these 3 conditions are true and if they are even completely relevant to the problem

EDIT:

There is a project-level variable in SSIS that determines where to read from (in this case set to \\server\d\folder\folder)
This variable is passed into forEach file enumerator in a for loop.
There is also a fileName variable used to check if file name was already loaded in the db as I store them in the table. The variable goes like this:
DECLARE @FileName VARCHAR(50) SET @FileName='' IF EXISTS (SELECT 1 FROM FileLoadStatus WHERE fileName = @FileName) BEGIN SELECT 1 AS FileExistsFlg END ELSE BEGIN SELECT 0 as FileExistsFlg END

If variables are at fault, I still don't know why it works if I execute it manually through catalog myself, but SQL Server Agent is unable to execute it through an SA account

EDIT 2: Exact errors say the following: enter image description here

EDIT 3: Now that I have set a windows system task to execute the SSIS package instead of a SQL Server Agent Job it just tells me that the "for each file enumerator is empty" basically meaning it can't find any files in the destination to read, even though files are there

Coda759
  • 107
  • 14
  • 1
    If it were permissions, you'd get a different error from SSIS. `File name property is not valid. Filename is a device or contains invalid characters` smells more like something went awry building out the UNC path. Are there expressions involved with determining where to pick up the file? – billinkc Nov 19 '21 at 21:10
  • I updated the question. Check the edit in the bottom part of the question @billinkc – Coda759 Nov 19 '21 at 21:24
  • 1
    Seems like the SQL Server Agent Service account does not have access to the path. – Thom A Nov 22 '21 at 19:51
  • Do you have an error message AFTER the above error ? The next error (or previous error) in most cases will give you the exact path to the file. This can help to rule out any issues with the construction of the file name. – Subbu Nov 23 '21 at 02:32
  • Most likely, your SA account is not known on the network. When the Sql Job executes, it cannot authenticate to another server on the network as that account does not exist anywhere but the source SQL Server. You can test this by setting the permissions on the share, file & folder to allow EVERYONE read access. That allows unauthenticated connections so don't keep it long term. If that solves it, then you'll need to create a network account to run the job/package with. Look into proxies. – Wes H Nov 23 '21 at 20:15
  • @WesH it is already set to everyone on the share to both read and write, I set it like that to see if it would do anything so I think you might be right – Coda759 Nov 23 '21 at 20:20
  • I would create a new SQL Server Credential in SSMS using a network account that has access to the share (network service account with a fixed password), then in SSMS under SQL Server Agent create a proxy linked to this credential. Then when you create the job step you can choose to run it as this proxy. – Steve Ford Nov 23 '21 at 21:49
  • 1
    If you look at the error message it tells you that filename "\\edidvwjmpa)4\D\TransmissionTo01\Data\" is invalid, which of course it is because the filename is not present. Your SQL code above sets @filename = '' but then doesn't appear to set it! – Steve Ford Nov 23 '21 at 22:02
  • Is the SQL Server Agent running under a AD Account or a built in account? If the latter, then it won't have access to any of the network shares. Doesn't matter if the share is accessible to everyone or not. – Thom A Nov 24 '21 at 15:42

1 Answers1

0

it might be a late respond, for all who come to check for an answer to this issue: the main thing is to be sure that the SQL agent has the authority to read from the shared folder:

1- hold down the Windows key and press R on your keyboard to open the Run command in windows.

2- type services.

3- search for SQL Server Agent.

4- as in the screenshot shows on the logon option you will find which user the agent is using, be sure that this user has the authority to read from the shared folder.

or change the user to another one with the right credentials.

enter image description here

5- you can check the users of the shared folder by right clicking on it and choosing properties --> security. From this window you can change the credentials of the users.

enter image description here

Guissous Allaeddine
  • 425
  • 1
  • 4
  • 19