January 31, 2008 at 9:04 am
I have a little problem with our old DTS packages.
We changed them to SSIS packages.
They work as long executed in a way that all connections (i.e. file sources) are local and our sqlagent(local system user) has no problem accessing them.
I would like to access a remote file system and changed the job owner to a domain user. Still the package is run under the sqlagent id, and I have no option to change that (at least in the GUI, which I am still using being new to SQL Server)
any help, anything I somehow missed here ??
The error message I get is ,,Executed as user: ....\svcsqlagent. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:41:01 Error: 2008-01-31 16:41:02.17 Code: 0xC001401E Source: .... Connection manager "..." Description: The file name "\\...\...$\...\....txt" specified in the connection was not valid
the job owner is a domain user with sysadmin role. this user can access the file.
the sqlagent cannot, since it is not a domain user and we actually cannot change that.
thanks
Juliane
January 31, 2008 at 1:24 pm
Packages in jobs run in the security context of the SQL Server Agent service account regardless of the job owner. If the SQL Servre Agent account is not a domain login, there's no way you'll be able to access a remote file.
Greg
February 6, 2008 at 8:29 am
Actually,
You can run the SQL Server Agent as a specified domain user.
http://msdn2.microsoft.com/en-us/library/ms191543.aspx
Greg Russell
February 8, 2008 at 1:26 pm
Hi you smart guys,
solution found:
I created credentials and a proxy and now everything is up and running fine.
Being a newbie myself I am a little disappointed nobody told me this here but instead told me that there is no option.
(Well I am aware a forum is a forum ...)
February 8, 2008 at 2:35 pm
Glad you found a solution, however you did NOT get a 'it cant be done' answer here in this 'forum'.
Greg R
January 20, 2009 at 9:52 am
Can I extend this question one step further?
I have an SSIS package that imports from a file on a network folder. My windows user account has access to this file and I can design and execute the package just fine when I'm logged in. I have created credentials and a proxy using my domain account to have a SQL job run successfully because the SQL Server Agent account does not have the proper network permissions to access that file.
However, I need to be able to execute my SSIS package (happens to be stored in a file on the SQL Server) from client workstations. Since SSIS needs to run where the Integration Services are installed and we don't want to install SSIS on all the workstations, we have developed a stored procedure that uses xp_cmdshell to run DTEXEC. However, we are unable to access the network file when we do this (getting error message: The file name specified in the connection was not valid.)
Is this the best method for running SSIS packages remotely? I don't want to have to create jobs for these imports (there are too many to easily maintain) and I don't want users to log directly into the SQL Server to execute the packages. We also considered writing a web service we could call from a custom application, but we have concerns with opening up our SQL Server to potential security breaches in IIS.
Any help/insight would be greatly appreciated.
-Dan
January 20, 2009 at 12:52 pm
Does anyone know what is the best book to buy to become a database administrator and take the test for MCP DBA?
January 22, 2009 at 1:18 am
Sorry, cannot help you - we use jobs and that works fine.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply