September 29, 2010 at 10:15 am
We have been running SQL 2000 for 8 years but just now moving over to SQL 2008. I have generated import/export jobs between SQL 2000 and SQL 2008 instances which work when scheduled as a job. However, I can't get jobs between two separate SQL 2008 boxes to work. The OS is Windows 2008 R2.
Before seeing up the systems we had a Microsoft PFE come out. He recommended sending each box up with its own domain account for running SQL Server Agent. I've used the Import Export Wizard to create packages to import data from box 2 to box 1. This all works interactively but when the package is scheduled to run it errors off.
Error Message in Event Viewer Log on Box 2:
[font="Courier New"]Login failed for user 'XXX\SVCACCT1'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [/font]
Do we have to give login permissions on Box 2 to the Box 1 service account? If it matters I have set the Package Protection Level to "Rely on server storage and roles for access control."
Below is the beginning of the error message from the job history:
[font="Courier New"]Executed as user: XXX\SVCACCT1. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 2:18:58 PM
Error: 2010-09-27 14:18:58.95
Code: 0xC0202009
Source: Test 12 Connection manager "SourceConnectionOLEDB"
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 'XXX\SVCACCT1'.". End Error [/font]
September 29, 2010 at 10:53 am
If that login doesn't exist on the other server then yes, you need that login on the other server. It will also need appropriate database level permissions assigned to it as well.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply