| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2008-09-15 : 16:35:13
|
To start off, I've been working on this for days and have read all the pertinent posts here (I think) and many other blogs, etc., but clearly am missing something. I have a dtsx package that is executed using xp_cmdshell, which is called by an ASP.Net web page on a separate server using the credentials of DomainA\UserA. That's working, but the client does not want to allow xp_cmdshell on their live servers, so I'm trying to work around it using a SQL Agent job, but keep getting the same error no matter what I do.Executed as user: DomainA\UserA. The process could not be created for step 1 of job 0x232C8F1CB50D6741AE6BF7494177B5AC (reason: A required privilege is not held by the client). The step failed. I don't think it's a debuggable permissions issue because:1) I've used Filemon and don't see any ACCESS DENIED messages.2) Neither the SQL Agent logs nor Profiler show me any permission denied messages.3) I can run the package without error using DTExec or using Run Package in the SSIS module in SSMS.4) I've tried making DomainA\UserA an admin on the server and a sysadmin as a test, and done the same with the user account that's used to run the SQL Agent, but none of that has allowed me to get by the error.I've set up a credential called ProxyCredential that's associated with DomainA\UserA, and an SSIS Package Execution proxy user called ProxyUser which is active to all subsystems with a principal of SQLAgentUserRole (which DomainA\UserA has been assigned). ProxyUser is selected under Run As in the job step that executes the package, and I've tried all three types of package sources. I've also tried with and without a configuration file. I set the package to log to the Windows Event Viewer and it does so when using DTExec, but nothing gets logged to it when calling the job, which makes me think that it's not even getting to the validation of the package (i.e. not starting the package at all). Any help would be greatly appreciated as I feel like I've tried everything now and am banging my head against the wall. Thanks. |
|
|
hey001us
Posting Yak Master
185 Posts |
Posted - 2008-09-15 : 17:19:30
|
| Based on my experience I prepared this document. This may help youSSIS Deployment: 1) Select the file system or SQL Server deployment option which indicates where the SSIS Package is stored. Selecting the SQL Server deployment and Click Validate packages after installation, this will help us to validate the package. Click the 'Next' button to proceed in the wizard. 2) Specify target SQL Server name and Windows Authentication type. Click Rely on server storage for encryption, these use security features of the Database Engine to secure the packages. 3) Select the directory where you want to save the SSIS Package in the file system. Click the 'Next' button to proceed in the wizard. (optional to change the default folder) 4) Confirm Installation - Click the 'Next' button to proceed in the wizard 5) Use the Packages Validation page to view the progress and results of the package validation. 6) Use Finish the Package Installation Wizard click to finish button to finish wizard.Finally create the job to run the DTS.In the job step:1. type step name2. select type as: SSIS package3. run as SQL Agent Service Account4. in general tab:• package source: SQL Server• server: <your server name>• log on to the server : use windows authentication • package: \<your package name> hey |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2008-09-15 : 19:02:15
|
quote:
1) Select the file system or SQL Server deployment option which indicates where the SSIS Package is stored.
Thanks hey. Which wizard is this? The Save Copy of Package interface in Visual Studio?I think I tried everything you mentioned but I will see if I can recreate using your instructions, once you respond. |
|
|
|
hey001us
Posting Yak Master
185 Posts |
Posted - 2008-09-15 : 19:09:04
|
| use BIDS to creat your dts.- build the project & deployis your domain account got privilege to run the DTS packages?I believe its authentication issue. hey |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2008-09-15 : 19:41:46
|
| Thanks again. The domain account has the SQLAgentUserRole and is a local admin on the server. Authentication to what?? I can run it just fine with that user using the DTExec UI, and from the SSIS module in Mgmt Studio. |
|
|
|
hey001us
Posting Yak Master
185 Posts |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2008-09-16 : 12:15:53
|
quote:
I am not sure what kind of setup in your SQL agent service running.
Not sure what that means, but I'm using a user account local to the server as the SQL Agent Service account. The account is a local admin but not a SQL sysadmin.quote:
Try this URL.http://support.microsoft.com/Default.aspx?kbid=918760 height="1" noshade id="quote"> I've been to that page many many times and tried everything except for "Create a package template" as I'm not sure how to do that. |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2008-09-16 : 12:54:45
|
| I just added the Agent user and UserA to more Windows permissions groups and made them both sysadmins and still no joy. Then I rebooted the server and voila! The job still failed but I think because it didn't use the config file, looks like a connection failure. Now I'll have to start removing permissions one at a time until it breaks... |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2008-09-16 : 18:28:45
|
Update: I started getting this error message - quote:
Source: TestPackage Connection manager "DPwDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. End Error Error: 2008-09-16 09:48:16.53 Code: 0xC020801C Source: DimHistory dim_history [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DPwDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2008-09-16 09:48:16.54 Code: 0xC0047017 Source: Dim History DTS.Pipeline Description: compone... The package execution fa... The step failed.
I thought it was a problem with my configuration file, but giving DomainA\UserA sysadmin rights in SQL Server fixed it. Now I have to figure out what rights I really need to give that user since sysadmin is unacceptable. |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2008-09-17 : 13:45:11
|
| I really don't like you, Microsoft. I removed sysadmin permissions and ran the job without error, then restarted the SQL services and the job failed. I think I finally have it figured out though.Here's what I did to get it to work (I think):1. set ProtectionLevel to DontSaveSensitive in package properties in Visual Studio (using SSPI anyway so not sure it mattered)2. created configuration file (had to alter path to config file in dtsx file since it's different on servers than in dev environment)3. instead of importing the package into MSDB, set the job step Package Source to "File system" and pointed to dtsx file on local drive of server4. set the Configurations tab of the job step to point to the dtsConfig file created in step 25. made sure the AD user kicking off the job was part of local server groups: Administrators, SQLServer2005DTSUser$SERVER, and SQLServer2005SQLAgent$SERVER (I eventually removed Administrator permissions because I only needed it to log to Windows Event Log)6. made the associated SQL user to the user in step 5 part of the SQLAgentUserRole in the msdb database7. made sure the local user assigned to the SQL Agent service was part of local server groups: Administrators, SQLServer2005DTSUser$SERVER, and SQLServer2005SQLAgent$SERVERMy advice for those having the same issues as I was is to restart the Integration Services service, the DB Engine service, and the SQL Agent service after you apply (and before testing) each change. |
|
|
|
|
SSIS package failing when called from job