run SSIS Package from SQL Agent

Question

I am trying to create a new job to run a SSIS package automatically.

I succeed to execute run the package from SSDT but when I try to run it from sql Agent, it generate the following error:

 Source: Package      Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

I try it with and without cheking the box use the 32 bit runtime. The sql server and SQL Agent are 64 bit

when I changed the protection level to DontSaveSensitive, I get The following error:

Executed as user: VG-DATA2\vgadmin. Microsoft (R) SQL Server Execute Package Utility  Version 12.0.5000.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  22:54:19  Error: 2018-04-22 22:54:20.03     Code: 0xC0202009     Source: Package Connection manager "vg-data2.westeurope.cloudapp.azure.com.WT_Delivery.vigicolis"     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 11.0"  Hresult: 0x80040E4D  Description: "Échec de l'ouverture de session de l'utilisateur 'vigicolis'.".  End Error  Error: 2018-04-22 22:54:20.03     Code: 0xC00291EC     Source: Tâche d'exécution de requêtes SQL Execute SQL Task     Description: Failed to acquire connection "vg-data2.westeurope.cloudapp.azure.com.WT_Delivery.vigicolis". Connection may not be configured correctly or you may not have the right permissions on this connection.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  22:54:19  Finished: 22:54:20  Elapsed:  0.156 seconds.  The package execution failed.  The step failed.

Has someone an idea of this problem?

Thanks in advance

Answer 1

Adding to other answers, as you are running SQL and SSIS 2014, I would recommend switching to SSIS Catalogue and Project Deployment mode. By doing this you can solve your problem of storing connection password in Environments.

Good point of this design is that sensitive passwords could be maintained by other people, say, support admins, not developers themselves. This is quite useful in production environments where developers have no access to.

A good article on how to use SSIS Catalogs and use Environments for connectivity data

Answer 2

I assume you have connection managers to databases that require a user name and a password?

There are numerous articles on this but let me explain anyway.

The short answer is: don't use login/password in your connection managers. Use windows authentication

The long answer is;

How do we store your database password for future use? We certainly don't want to store it in clear text in the package because then anyone with the pacakge can just steal the password. The protection level of a package indicates how the password should be stored. 'save in clear text' is not an option

Here are all the password storage options (called protection levels): SSIS Packages Using Package Protection Level

The default protection level, EncryptSensitiveWithUserKey encrypts the password in the text file. This can only be decrypted under the same user that encrypted it (thats you)

When you run it under SQL Agent, it runs as a different user so the password can't be decrypted and you get your first error message.

The work around, which is bad idea, is to set up your SQL Agent to run as yourself. This is a bad idea, so I won't go into detail on this.

You then changed the protection level to DontSaveSensitive. This protects the password by not saving it at all. So no matter how you run your package, you don't have a password saved. This gets your second error message.

Another option is EncryptSensitiveWithPassword. This encrypts your password with... a password. Again a bad idea because now you just have another password to manage

What I suggest you do (in absence of any detail around what you are doing) is:

1. Make sure all of your SQL Server connection managers use windows authentication
2. Use DontSaveSensitive. this works because there is no password defined in the package
3. Define a windows service account and assign this to SQL Agent
4. Ensure that this windows service account has access to the database

If you have any questions please post in the comments. Please also update your question with more details about what kind of connection managers you are using. (SQL Server? Oracle? Excel?)

Answer 3

If you cannot use Windows Authentication as suggested above by @Nick, I would recommend you either go for Package Configuration, where you can store the passwords and other connection information in a file or table and setup permissions to that appropraitely, i.e. SQL Agent service account should have permission. Else, go for Server Storage package protection level.

Refer to https://msdn.microsoft.com/en-us/library/ms141747(v=sql.120).aspx for more info about Package Protection Level.