anushahanna
asked on
permissions to run package in job
the package run fine, when did manually.. when put in a sql job, it fails for permissions sake.
it is run from server1, and in one task, the connection manager is for server 2 - that is where it is failing. but the server #1's service account is local admin in server2. won't that be enough? what other permissions would it need?
thanks
it is run from server1, and in one task, the connection manager is for server 2 - that is where it is failing. but the server #1's service account is local admin in server2. won't that be enough? what other permissions would it need?
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
reza_rad, the error is
Message
Executed as user: SRBSV\POST04. Microsoft (R) SQL Server Execute Package Utility Version 10.0.4000.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:13:24 PM Error: 2011-03-01 13:24:49.82 Code: 0xC0202009 Source: TRACKER Connection manager "POSS0041.master" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'SRBSV\POST04'.". End Error Error: 2011-03-01 13:24:49.87 Code: 0xC00291EC Source: Restore TRACKER on POSS0041 Execute SQL Task Description: Failed to acquire connection "POSS0041.master". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:13:24 PM Finished: 1:24:49 PM Elapsed: 685.733 seconds. The package execution failed. The step failed.
Message
Executed as user: SRBSV\POST04. Microsoft (R) SQL Server Execute Package Utility Version 10.0.4000.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:13:24 PM Error: 2011-03-01 13:24:49.82 Code: 0xC0202009 Source: TRACKER Connection manager "POSS0041.master" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'SRBSV\POST04'.". End Error Error: 2011-03-01 13:24:49.87 Code: 0xC00291EC Source: Restore TRACKER on POSS0041 Execute SQL Task Description: Failed to acquire connection "POSS0041.master". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:13:24 PM Finished: 1:24:49 PM Elapsed: 685.733 seconds. The package execution failed. The step failed.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
reza_rad, how i have it now is consistent with what the article shows except:
Is EncryptSensitiveWithUserKe y OK, if the package is working on this server, and we intend to keep it only in this server?
is that vital for success?
Is EncryptSensitiveWithUserKe
is that vital for success?
ASKER
mordi, how is the "rely on server..." method fool proof for the permissions issue?
ASKER
dba2dba, it is only a OS admin (actually service account), not SQL SA..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
your ideas helped- it was not a permissions issue as you mentioned..
thanks for sharing an very pertinent solution..
thanks for sharing an very pertinent solution..
So what was the problem and solution?
ASKER
is package protection level a property- where do i change it?