August 4, 2008 at 9:20 am
I have an SSIS package that connects to a couple servers each night and copies/transforms data. The job runs fine under both my account and my maintenance account (if I run SSMS as it); however, if I try to schedule it as an agent job, the execution fails citing DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. Login failed for user ''. The user is not associated with a trusted SQL Server connection. The AcquireConnection method call to the connection manager "Earth" failed with error code 0xC0202009. *Earth is the name of the server, which happens to be the same server where the SSIS package is stored (in MSDB.)
Both servers are set to Windows Authentication Mode. I know the error message is a very common one, but I cannot seem to get this one licked. It feels like I've tried EVERYTHING!
Any suggestions?
Thanks a bunch!
Jay
August 4, 2008 at 9:25 am
This probably is an issue with what user the agent is running under Local Service this user will not be recognized by the other machine and the operation will not occur.
To correct:
Open the configuration manager and look at the user for the SQL Server Agent it needs to be a user who has enough rights to both machines and the different SQL server instances to complete the task.
August 4, 2008 at 9:26 am
Change the Authentication Mode of the SQL server from "Windows Authentication Mode (Windows Authentication)"
to "Mixed Mode (Windows Authentication and SQL Server Authentication)".
August 4, 2008 at 9:29 am
Kenneth Augustine (8/4/2008)
Change the Authentication Mode of the SQL server from "Windows Authentication Mode (Windows Authentication)"to "Mixed Mode (Windows Authentication and SQL Server Authentication)".
That KB article looks to be very generic. I'd prefer to not use mixed mode since we're only using windows authentication and I'd prefer to limit the server's surface area and vulnerability.
August 4, 2008 at 9:33 am
Alan (8/4/2008)
This probably is an issue with what user the agent is running under Local Service this user will not be recognized by the other machine and the operation will not occur.To correct:
Open the configuration manager and look at the user for the SQL Server Agent it needs to be a user who has enough rights to both machines and the different SQL server instances to complete the task.
The interesting thing is that the server noted in the error message is actually the same server that the SSIS package is on. From the looks of it, the job doesn't even get to the point where it could fail while trying to connect to the other machine.
Additionally, I have the agent to run as our domain-wide maintenance account. Naturally, it has access to both servers, instances, and databases. I've also run SSMS as the maint account to test whether it would run, and it does. I'm pretty stumped. :ermm:
August 4, 2008 at 9:34 am
If your are on a domain the easiest is to create an account DOMAIN\[This task user] and if possible avoid mixed mode. Security is much easier to maintain this way.
August 4, 2008 at 9:34 am
btw... Thanks for the superfast replies, guys!
Jay
August 4, 2008 at 9:35 am
I understand. Try this.
Use a valid Windows login to connect to SQL Server. If you must continue to use a SQL Server login, you can change the security authentication mode in SQL Server to SQL Server and Windows. To do this, follow these steps:
1. Start Enterprise Manager.
2. Expand Microsoft SQL Servers, and then expand SQL Server Group.
3. Right-click the server that you want to change to SQL Server and Windows authentication, and then click Properties.
4. In the SQL Server Properties dialog box, click the Security tab, click SQL Server and Windows, and then click OK.
5. When you are prompted to re-start the SQL Server service, click Yes.
Note If you use SQL Server 2005, use SQL Server Management Studio instead of Enterprise Manager to change the security authentication mode.
August 4, 2008 at 9:36 am
Alan (8/4/2008)
If your are on a domain the easiest is to create an account DOMAIN\[This task user] and if possible avoid mixed mode. Security is much easier to maintain this way.
Yes, Alan. That's what I've set up. My maint account has a login on the server with rights to the databases.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply