March 19, 2009 at 11:26 am
Apologies for posting a SQL 2000 query however we still have a few DTS packages propping up our data warehouse.
I have a requirement to copy a simple table with summary stats from a call centre system at the same time each evening for KPIs reporting purposes.
The call centre system has SQL 2000 databases on SQLsvr1 and the reporting server (the destination) is SQLsvr2.
The SQLsvr1 security properties have been set to "Windows only" authentication and not "SQL Server and Windows" authentication.
I can access both SQL servers via a windows login that has "System Administrators" ticked in the Server Roles and db_owner ticked in Database Access for the database I'm accessing.
In my DTS I have a source linking to the desired table in SQLsrv1 using Windows Authentication and one in my reporting server, SQLsvr2 again using Windows Authentication.
I can preview the source table and run the DTS package to export the data within enterprise manager no problem but when I put this into a schedule within SQL Server Agent > Jobs it falls over with this error message in the job history:
Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
I need to know what other permissions or security group I need to be a member of in order for this to run in the scheduler.
March 19, 2009 at 11:38 am
I believe this is running under the SQL Agent account in 2000, or possibly running under the owner of the job. Check both of those accounts and see if they have permissions for the source server.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply