December 7, 2009 at 9:19 am
Hope everyone is doing well. I’ve got a SQL Server 2005 question that I’m hoping is a quick and easy answer. Basically what we are trying to do is export a table from a database on one SQL server to a separate sql server. I’ve set up a package to do the export, and it runs fine when I run it from the business intelligence studio, or if I do a manual export. The issue is that when I schedule the package as a job, it fails with the message: “Executed as user: NT AUTHORITY\NETWORK SERVICE. The package execution failed. The step failed.” From what I understand from doing some research is that when I run the job manually, I’m running it as myself and when it runs as a scheduled job it runs under the Network Service account. I’ve tried adding the NT Authority\Network Service account as a login to both databases and giving it access to the tables. This has not changed the results. What am I doing wrong? Any help would be greatly appreciated.
December 7, 2009 at 10:03 am
zachsteve77 (12/7/2009)
Hope everyone is doing well. I’ve got a SQL Server 2005 question that I’m hoping is a quick and easy answer. Basically what we are trying to do is export a table from a database on one SQL server to a separate sql server. I’ve set up a package to do the export, and it runs fine when I run it from the business intelligence studio, or if I do a manual export. The issue is that when I schedule the package as a job, it fails with the message: “Executed as user: NT AUTHORITY\NETWORK SERVICE. The package execution failed. The step failed.” From what I understand from doing some research is that when I run the job manually, I’m running it as myself and when it runs as a scheduled job it runs under the Network Service account. I’ve tried adding the NT Authority\Network Service account as a login to both databases and giving it access to the tables. This has not changed the results. What am I doing wrong? Any help would be greatly appreciated.
I believe your problem is that since SQL Server (and agent) are running in the context of NT AUTHORITY\NETWORK SERVICE when it reaches into another server it can't use that. If I remember security correctly NT AUTHORITY\NETWORK SERVICE and localsystem are local ONLY accounts, they impart no security outside the LOCAL machine. You are correct in your assumption, when YOU ran it, it was running under your security context, when the server runs it, it is happening under its and since that user has no rights on the other machine it can NEVER suceed, no matter what. With that said you could look at creating an agent proxy and assigning it to the job which WOULD allow the server to connect to the other server. One caveat, are all the machines in the same domain?
With all that said, I am a big fan of SQL Server logging in as a domain account and recommend that to basically everyone..
CEWII
December 7, 2009 at 10:05 am
zachsteve77 (12/7/2009)
Hope everyone is doing well. I’ve got a SQL Server 2005 question that I’m hoping is a quick and easy answer. Basically what we are trying to do is export a table from a database on one SQL server to a separate sql server. I’ve set up a package to do the export, and it runs fine when I run it from the business intelligence studio, or if I do a manual export. The issue is that when I schedule the package as a job, it fails with the message: “Executed as user: NT AUTHORITY\NETWORK SERVICE. The package execution failed. The step failed.” From what I understand from doing some research is that when I run the job manually, I’m running it as myself and when it runs as a scheduled job it runs under the Network Service account. I’ve tried adding the NT Authority\Network Service account as a login to both databases and giving it access to the tables. This has not changed the results. What am I doing wrong? Any help would be greatly appreciated.
In SSIS package automation you cannot run the package with limited access Network Service account, you must run the job with admin permissions which either runs the package as a proxy account or just admin. The reason packages in automation are sometimes used to move important data and Microsoft wants the tasks going back to an admin who approved it.
Kind regards,
Gift Peddie
December 7, 2009 at 10:59 am
Thanks for the response. So changing the actual user that the SQL SSIS service is running under to a domain admin would solve the problem? Or could it just be a local admin that is the same on both servers?
December 7, 2009 at 11:07 am
zachsteve77 (12/7/2009)
Thanks for the response. So changing the actual user that the SQL SSIS service is running under to a domain admin would solve the problem? Or could it just be a local admin that is the same on both servers?
The permission issue is with how the package is run so you could use the proxy account to run the package because it is two SQL Server. If your package is SQL Server and other RDBMS then the owner of the package must be admin in SSIS and the other RDBMS.
Kind regards,
Gift Peddie
December 7, 2009 at 12:31 pm
Gift Peddie (12/7/2009)
zachsteve77 (12/7/2009)
Thanks for the response. So changing the actual user that the SQL SSIS service is running under to a domain admin would solve the problem? Or could it just be a local admin that is the same on both servers?The permission issue is with how the package is run so you could use the proxy account to run the package because it is two SQL Server. If your package is SQL Server and other RDBMS then the owner of the package must be admin in SSIS and the other RDBMS.
GP,
I disagree with that. The user that executes the package must have rights on the machine to run DTExec and sufficient rights to access both SQL Servers. They do NOT have to be admins anywhere. Just most people do it that way because it is easy.
In response to the other question asked regarding running SSIS Service as a domain admin it might solve the problem, it certainly wouldn't hurt to try. You might consider changing the SQL Agent service. The local admin or members of the administrators group on a single box have no inherent rights on another box that is why you need to use a domain account, OR you can modify the package to use SQL Security to reach out into that other server, but that solution would cause other problems..
In my experience, most organizations create a domain level login that their SQL servers login as. This login shouldn't have domain admin rights (and for good reason). It is often a member of the administrators group on a particular server. I would suggest this model. It would simplify future security work.
CEWII
December 7, 2009 at 12:54 pm
So if I have this right I need to create a credential and a proxy on the server that is running the package that matches up to a login on the server that is receiving the table. Thanks for all the help.
December 7, 2009 at 1:06 pm
This is Microsoft documentation of accounts qualified to run the Agent and why jobs running SSIS fails and many tests in domain settings needs admin account. The reason it depends on the tasks performed by the package.
http://support.microsoft.com/kb/918760
http://msdn.microsoft.com/en-us/library/ms345380.aspx
These are actual threads with users whose packages will not run until the relevant permissions changes are applied. These are new to SQL Server 2005 and up because the then SSIS team were not interested in SSIS being used with Oracle or DB2 or any other RDBMS most of these people are not in the team at Microsoft now because we enabled use with other RDBMS without Microsoft providing support.
In the filesystem task in a domain require ACL propagation in the domain and when you automate a package with DB2 without admin permissions please post the code.
http://qa.sqlservercentral.com/Forums/Topic661486-148-1.aspx
Kind regards,
Gift Peddie
December 7, 2009 at 1:08 pm
zachsteve77 (12/7/2009)
So if I have this right I need to create a credential and a proxy on the server that is running the package that matches up to a login on the server that is receiving the table. Thanks for all the help.
I'm not sure I understand what you are saying.. You need to have a login that has rights to the database on both servers, that means a domain account. You can configure that as an "SSIS Package Execution" proxy. Also the other thing that might be needed is to make sure that the user selected has rights to the SSIS binaries. But I wouldn't worry about that just yet.
CEWII
December 7, 2009 at 1:33 pm
Sorry, you'll have to excuse my ignorance, I'm just trying to make sure I understand it. You said what I was trying to say, in a much more coherent way. I feel like I've got an idea now. Thanks for all of the help.
December 7, 2009 at 1:35 pm
You are very welcome, I hope it gets to working for you.
CEWII
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply