ssis handling of oracle and teradata passwords

Anonymous
2020-09-22T15:30:55.107+00:00

Hi,

This has gotten so bad I have to ask if there is a newer feature in ssis (like maybe vs 2017) that handles oracle and teradata passwords. We work in a secure organization. They don't want us leaving passwords in the packages for security reasons (I understand that ssis encrypts passwords it knows are passwords in the .dtsx file) so we have to clear them when development is done. We also use what might be called a password vault for oracle and teradata passwords for our run time jobs which makes the transition from client development to batch job difficult. The passwords are in a variable and referenced in the connection string override. ssis is constantly attempting logins for metadata, even when you aren't working on the oracle and teradata tasks, and unless you are really paying attention, oracle and teradata lock our userids for missed attempts. Can anyone tell me why it would ever be useful for ssis to attempt a login to Oracle or Teradata when ssis knows the password it has is null or blanks? A more paranoid part of me might think that Microsoft, with Windows Auth, is trying to drive their users off of Oracle or Teradata. So is there some ssis job level parameter I can set that instructs ssis not to attempt an oracle or teradata access for metadata unless it has a password or I explicitly tell it I want to access the database? Some of our jobs access oracle or teradata in 5 or 10 different places, running in there to set Work Offline, DelayValidation, or ValidateMetadata for each one of those accesses is difficult. I know I'm getting a password fail just by opening the package to look at it. And I don't want to be told to set Oracle and Teradata to use Windows Auth (if that's even possible). I don't control that and I need something that I, as an etl developer, control. I can't believe SSIS competes with products like Datastage with the situation concerning Oracle and Teradata passwords being so bad.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,402 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Monalv-MSFT 5,891 Reputation points
    2020-09-23T07:56:08.613+00:00

    Hi @$$ANON_USER$$,

    Do not save sensitive (DontSaveSensitive)
    Suppresses the values of sensitive properties in the package when the package is saved. This protection level does not encrypt, but instead it prevents properties that are marked sensitive from being saved with the package and therefore makes the sensitive data unavailable to other users. If a different user opens the package, the sensitive information is replaced with blanks and the user must provide the sensitive information.

    Please set the Protection Level of the packages and project as "DontSaveSensitive".

    Best Regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.