SSIS deploying package - error: Login failed for user

1
votes

I have an SSIS package that runs fine in BIDS (visual studio) but in SQL Agent I get this error:

"Login failed for user 'cooldude'.". Source: Execute SQL Task Execute SQL Task Failed to acquire connection "server.database.cooldude".

Package settings:

1.Encrypt sensitive with password
2.XML configuration files are being used (contains connection 
      string, with windows user name/password)
3.Everythins is under same domain.
4.no "-" in config file names.

SQL Agent Settings:

1.Runs from File System on same computer that has SQL AGENT
2.SA only has control of SQL Agent, but package runs as
   windows user (in xml connection string.) Not integrated.
3.connections are all selected.

Server: SQL Server 2008 Sp 1, SQL Server 2000

3
c#sqldatabasetsqlssis
Is this a deployment issue (your title) or an execution issue (body)? Either way, do you have capture failed logins to the server? Can you verify it is pointing to the right server or do you have any way to verify the xml configs are being used? Does the Agent's account have permission to access the folder where the XML config lives? – billinkc

3 Answers

1
votes

Resave the package with setting 'Do not save sensitive data'. Then the package will use the correct connection string from your xml config file.

0
votes

What is the account that SQL Server Agent service runs under? Try using a domain account with the necessary permissions.

You can change this in the SQL Server Configuration Manager.

0
votes

If you open SQL Server Agent, Jobs, right click the job, properties, Steps, Select the SSIS package, data sources make sure connection string is set as you expect.

Also if using integrated security for one of the connection strings check the "run as" drop down and make sure it is set correctly. This should be the Security context under which the package is run.

If everything looks good try to manually connect to the db while logged in as the user specified in the "run as" drop down for the step. May also want to check to make sure the default db for the "run as" user exists and the user can connect to it.

Good Luck.