criskrit
asked on
SQL 2014, SSIS packages, SQL Agent jobs, and security
I am new to SQL 2014 and I am bumping into issues with SSIS packages. I have a package that reads an Excel file and imports the data into a table. It runs fine on my laptop but fails to validate/run on the server due to permissions issues. The problem is... I cannot figure out the security context that the packages use on the server.
Is there a way to specify what user the packages run/validate under? Is this configurable or not? Is it different for the Validation and for running the packages on SQL Agent? Also is it possible to edit packages via SQL Mgmt Studio on the server? Right now I am using Visual Studio on my laptop and then publish them.
Thanks
Christos
Is there a way to specify what user the packages run/validate under? Is this configurable or not? Is it different for the Validation and for running the packages on SQL Agent? Also is it possible to edit packages via SQL Mgmt Studio on the server? Right now I am using Visual Studio on my laptop and then publish them.
Thanks
Christos
Did you copy the package to the server? Or you just change the package to point to the server database?
ASKER
Hello and thanks for the replies.
My questions are a bit more generic in regards to SSIS operation:
- I launch SQL Mgmt Studio, connect to server, find the package in SSISDB and I do a "validate". What user does it validate under?
- Can I edit the SSIS package configuration via SQL Mgmt Studio? I can view the config but not edit. Is this by design or can this be enabled?
Thanks
Christos
My questions are a bit more generic in regards to SSIS operation:
- I launch SQL Mgmt Studio, connect to server, find the package in SSISDB and I do a "validate". What user does it validate under?
- Can I edit the SSIS package configuration via SQL Mgmt Studio? I can view the config but not edit. Is this by design or can this be enabled?
Thanks
Christos
You can change the configuration when you put a package to run in SQL Server Agent.
Hi You will get solution from my Article.
https://www.experts-exchange.com/articles/8452/Execute-SQL-Job-with-Proxy.html
https://www.experts-exchange.com/articles/8452/Execute-SQL-Job-with-Proxy.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
...you may set SSIS proxi as well to run this Sql agent step
--
make sure your file connection is UNC based; \\servername\share
(also need to check this share and NTFS permissions)
--
if you need to adjust the SSIS pack connection string\values og the global vars;
you can do this from sql agent job -step properties