June 26, 2017 at 1:27 am
We have this issue here that we've been trying to solve for a couple of days. I hope somebody here can shed some light in to this (dark) matter.
We're developing a new process which has to read (and maybe move) some XML files from a SharePoint folder. For the development we mapped the SharePoint folder to a network drive and it worked just fine.Then We depolyed it to the SSIS catalog and it stopped reading any file. After a quick google search we found out that network drives are not such a good idea for the SQL Agent and we tried changing the path to a UNC path that looked like that:
\\openmind.company.es@80\comunidades\NewProduct\Shared documents\Report_User
And then again it worked on the Windows file explorer and on Visual Studio (all of this logged in with the domain user that SQL Agent uses!) but it didn't read anything when executed from the agent.
We're almost sure that this is a permission problem, but we have to be sure if its a problem on the sql server permissions or on the SharePoint permissions, before we start asking other departments to get involved. So. Anyone has any experience on this? Any ideas to test the permitions? Thank you guys! :-)Francesc Garcia
Thank you guys! 🙂
Francesc Garcia
June 26, 2017 at 6:19 am
I am having a similar issue but maybe we can brainstorm. My issue is I am getting access denied error when running as an agent but runs fine executed manually. I know that the SQL Service Account must be started with a domain account instead of local service and the domain account must have rights to the folder and it must use UNC naming convention, do you have all three? Anything you might be able to shed light on my issue?
June 26, 2017 at 7:05 am
bswhipp - Monday, June 26, 2017 6:19 AMI am having a similar issue but maybe we can brainstorm. My issue is I am getting access denied error when running as an agent but runs fine executed manually. I know that the SQL Service Account must be started with a domain account instead of local service and the domain account must have rights to the folder and it must use UNC naming convention, do you have all three? Anything you might be able to shed light on my issue?
I don't undestrant the " know that the SQL Service Account must be started with a domain account instead of local service " my service account is a domain account. Is that what you mean¿
Being that true I think I have the three of them.
One thing I've read on some forums and that you don't have in your list is that you should check that you have acces to all of the folders in the path (not only de last one).
Another thing that might help you, even if it didn't helped me so much, is this script to check if a directory is readable from the SqlAgent.
USE master
GO
DECLARE @ServiceAcct sysname, @Path as varchar(256)
SET @Path ='\\openmind.whatevercompany.es@80\'
EXECUTE dbo.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
@value_name = N'ObjectName',
@value = @ServiceAcct OUTPUT
select @ServiceAcct
--EXECUTE AS LOGIN = 'ES\axbcproxy' --@ServiceAcct;
EXEC master.dbo.xp_fileexist @Path
EXECUTE master.dbo.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',
@value_name = N'ObjectName',
@value = @ServiceAcct OUTPUT
EXECUTE AS LOGIN = @ServiceAcct;
EXEC master.dbo.xp_fileexist @Path
June 26, 2017 at 7:14 am
I posted a separate post and I think I got the answer to my question, I feel so stupid. The SQL Agent Service must also be started with a domain account for the agents to work correctly.
The SQL Service account is the account used to start SQL. On the Server under Services.msc, their are SQL service accounts . One being the sQL service account itself to start SQL and another as the Agent Service. Both of these need to be started with Domain accounts instead of local service.
June 26, 2017 at 7:26 am
As pointed out, when running any task via the SQL Agent, the service account must have access to the location being referenced, and, also have the relevant permissions (for example, read, write, modify, etc).
By default, an instance of SQL Server will probably set the SQL Server Agent to log on as "NT AUTHORITY\NETWORK SERVICE". This account is a local account, is isn't going to have access outside of that machine. If you're on a Domain, that means that accessing files, directory, etc on a different server isn't going to work. Therefore you'll need to set up a "Service account". Let's say you call that account "YourDomain\SQL_Agent". This account will still need access to all the normal locations that the NETWORK SERVICE logon had access to. For example, you'll want it to have access to your back up location (defaulting on SQL 2016 as something like C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup).
To access the Sharepoint directory, you'll need to give "YourDomain\SQL_Agent" access to the directory. Now, if your directory is called something like "\\SharepointServer\Sharepoint\ReportData\Repository" then your service account is going to need access to the Share Sharepoint. It'll then need read access to each level after that that it needs access. You can't simply grant it read access at "\\SharepointServer\Sharepoint\ReportData\Repository", but not at "\\SharepointServer\Sharepoint". That won't work, as authentication to the first directory will fail.
Alternatively, you could create a new Share, for example "\\SharepointServer\ReportDateRepo" which maps directory to the Repository Directory. Then you could simply allow access at the share and repository directory.
On a seperate note, how are you running your SSIS tasks? SSISDB, msdb, etc? Check your logs for your tasks, and see what they say when the task attempts to read from the directory.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 26, 2017 at 7:35 am
Great Info, Thank you.
June 26, 2017 at 8:21 am
I think you gave us all the necessary clues. I'll talk to the DBA son. I'll inform you guys if we sort it out.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply