March 7, 2011 at 9:24 am
I have a server that is not on our domain, I need to read a file, create a new one and move the file I just read all on that non-domain server.
So I created an Execute Process Task in my SSIS package
Executeable: cmd.exe
Arguements: /c net use \\10.239.130.175\export password /user:administrator
Working Directory: C:\Windows\System32
I bring up the cmd on the SQL Server type in the above (minus /c ) and it tells me its successful to make sure it works.
Then next in my SSIS package I have Script Task that goes through and reads/creates file in the same folder. As with others when I run the package from SSIS manually runs fine.
Then when I schedule it to a job I get the following error:
Message
Executed as user: MRYV70060\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.5000.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:09:00 AM Error: 2011-03-07 10:09:03.81 Code: 0xC001401E Source: Import_LapLine_Protocol_MachX Connection manager "SourceConnectionFlatFile" Description: The file name "\\10.239.130.175\export\NEW PROTOCOL FILE.csv" specified in the connection was not valid. End Error
Error: 2011-03-07 10:09:03.81 Code: 0xC001401D Source: Import_LapLine_Protocol_MachX Description: Connection "SourceConnectionFlatFile" failed validation. End Error
DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:09:00 AM Finished: 10:09:03 AM Elapsed: 3.547 seconds. The package execution failed. The step failed.
What am I missing or doing wrong?
March 8, 2011 at 2:56 am
Does the SQL Server Agent Account has the necessary permissions to access that remote folder?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 8, 2011 at 5:09 am
That server has a Everyone account and users, wouldn't the SQL Agent fall under one of those two?
I don't know permissions that well.
March 8, 2011 at 5:11 am
smac (3/8/2011)
That server has a Everyone account and users, wouldn't the SQL Agent fall under one of those two?I don't know permissions that well.
Normally the SQL Server Agent account runs under a dedicated account.
You can check it in the SQL Server Configuration Manager.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 8, 2011 at 5:34 am
Koen Verbeeck (3/8/2011)
smac (3/8/2011)
That server has a Everyone account and users, wouldn't the SQL Agent fall under one of those two?I don't know permissions that well.
Normally the SQL Server Agent account runs under a dedicated account.
You can check it in the SQL Server Configuration Manager.
I opened SQL Server Config Manager and have the following:
SQL Server Configuration Manager (Local)
SQL Server 2005 Services
SQL Server 2005 Network Config
*List of Protocols
SQL Native Client Config
Client Protocols
Aliases
Is this what there is suppose to be?
March 8, 2011 at 5:41 am
If you click on services, you can see all the SQL Server services that are running on the server on the right side of the screen.
There should be a service for Agent. Check the properties of that service for the account.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 8, 2011 at 6:21 am
Koen Verbeeck (3/8/2011)
If you click on services, you can see all the SQL Server services that are running on the server on the right side of the screen.There should be a service for Agent. Check the properties of that service for the account.
Would it be the one that says "SQL Server Agent(MSSQLSERVER)?
When I open the properties I see tab Log On, Log on as: Built-in account.
But there is one for SQL Server Integration Services and the Log On is not Built in account but instead This account: Account Name and password.
March 8, 2011 at 6:24 am
Yes, it is the one called SQL Server Agent.
It is the account listed under built-in account that is important. That account should have permissions to access the remote folder.
The Integrations Services account has nothing to do with your problem. Your executing the package from within a SQL Server Agent job, so the package is run under the credentials of the SQL Server Agent account (unless you specify a proxy).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 8, 2011 at 7:08 am
Ok, the built in account that is selected is Local System, but the other choices are Local Service and Network Service.
March 8, 2011 at 7:16 am
I can imagine that an account called "Local System" doesn't have access to a remote folder.
There are two options:
* configure SQL Server Agent to run under a custom account that does have the necessary permissions (check with your administrator, because this can break systems)
* create a proxy account in SQL Server and specify in the jobstep that you'll use this proxy account instead of the built-in Agent account (recommended)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 8, 2011 at 7:28 am
Koen Verbeeck (3/8/2011)
I can imagine that an account called "Local System" doesn't have access to a remote folder.There are two options:
* configure SQL Server Agent to run under a custom account that does have the necessary permissions (check with your administrator, because this can break systems)
* create a proxy account in SQL Server and specify in the jobstep that you'll use this proxy account instead of the built-in Agent account (recommended)
I have briefly read on proxy accounts, this is probably what we'll do.
When you say "specify in the jobstep that you'll use this proxy account instead of the built-in Agent account" do you mean inside the SSIS package or when the job runs?
March 8, 2011 at 7:31 am
In a SQL Server Agent jobstep, you have a dropdown box right under Run As:. In that dropdown box you can select from the various proxies.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 8, 2011 at 7:31 am
Not in the package, but within the job set-up.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 8, 2011 at 7:39 am
Phil Parkin (3/8/2011)
Not in the package, but within the job set-up.
Waw, what a close finish! :w00t:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 8, 2011 at 7:41 am
Gotcha!
I see where you are talking about.
Can I create the credentials and proxy or does the administrator need to do that?
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply