none
Why does SSIS attempt Oracle and Teradata logins with a null password? RRS feed

  • Question

  • Hi,

         In the new SOX world, passwords are a big deal.   I was wondering if anyone knows why SSIS attempts logins to Oracle and Teradata with a null password?  It's obvious the login is going to fail, why attempt it and move in the direction of locking the developer id?   I work with a group of experienced ETL developers who are very fluent in all forms of SQL databases and we live in constant fear of SSIS locking our database userids.  And unless you set ValidateMetadata=False and DelayValidation=True in 15 places surrounding your Teradata and Oracle access, just opening your package gets you a few failed logins (we are using sophisticated encryption routines required by our SOX organization to fetch the Oracle and Teradata passwords, not passwords that we type into ssis) without even trying to run the job.  I have a new one for Microsoft.  Our organization will not allow a Teradata TD2 login id to run from a client machine, it has to run on a server.   All we have is Azure for server execution so we can't do development on a server.  We are allowed to use our personal Teradata LDAP id's on clients and I locked mine the other day before I could wrestle ValidateMetadata off.  I worked with Datastage for years and never once had it login to database when I didn't want it to.  With Datastage, if you wanted metadata you fetched it once with your credentials and got the metadata.   With SSIS, once you define a database resource, every time you open the package after that its inclination is to open the database to fetch the metadata.  So can't Microsoft give us one small break and not attempt an Oracle or Teradata logins if the password is null?

    Wednesday, September 18, 2019 4:12 PM

All replies

  • Hi flash7gordon,

    Please try to run the package under a proxy account.

    Here is the article regarding how to create it in SQL Server Agent.

    Check SQL Credentials and Proxy for Agent Job.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, September 19, 2019 2:07 AM
  • if anyone knows why SSIS attempts logins to Oracle and Teradata with a null password?

    It don't, SSIS passes the password you entered in the connection manager. I created a lot's of packages accessing Oracle and it works without any issue.

    So you are doing something wrong, e.g. setting the package property "ProtectionLevel" to "DontSaveSensitive", so that the password isn't stored and you have to re-enter it: Set or Change the Protection Level of Packages


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, September 19, 2019 5:57 AM
  • Zoe,

         Thanks for the help.   I'm not so concerned with running the package.   When our package runs the encryption routines deliver the password to a variable, the variable plugs into a ConnectionString override for Teradata and Oracle, and the package runs.   I'm concerned with doing development and attempts by ssis to connect to the database for metadata, the encryption routines don't run when you are doing development and it tries to login with a null password.  I'm arguing that in the visual studio development foreground, ssis shouldn't attempt a teradata or oracle login (or an ms sql login if you are using SQL Server Authentication) if the password is null. 

    Thursday, September 19, 2019 3:51 PM
  • Olaf,

         I don't enter a password in the connection manager because our organization dictates that we have to fetch oracle and teradata passwords with a specialized encryption tool.  Some people in our organization argue that the native microsoft ssis password encryption in the .dtsx or .dtsconfig file is superior to our encryption tool but we lost that battle.

    Thursday, September 19, 2019 3:58 PM
  • Hi flash7gordon,

    Are you using SSIS 2005 - 2008R2?

    Starting from SSIS 2012 onwards, SSISDB Catalog has Connections Managers settings. And it doesn't expose any passwords. If a password was typed, it will show up as asterisks.

    No *.dtsconfig files anymore since 2012.

    Please see below.



    Thursday, September 19, 2019 5:22 PM
  •  I don't enter a password in the connection manager because our organization dictates that we have to fetch oracle and teradata passwords with a specialized encryption tool.  Some people in our organization argue that the native microsoft ssis password encryption in the .dtsx or .dtsconfig file is superior to our encryption tool but we lost that battle.

    Hi flash7gordon,

    you answered your own question.

    It appears that you have to comply with what you are said to do. Fetch the password, pass it to the package, or let the package retrieve it.

    You have to provision this.


    Arthur

    MyBlog


    Twitter

    Thursday, September 19, 2019 5:37 PM
    Moderator
  • Arthur, It works at run time, it doesn't work when you are doing development and ssis wants metadata from teradata.  I can't run the encryption tasks when I am doing development.
    • Edited by etlman Thursday, September 19, 2019 6:43 PM
    Thursday, September 19, 2019 5:52 PM
  • Yitzhak, We are using VS 2015 enterprise.  we use .dtsx packages and .dtsconfig extensively.   We don't really care what password someone might see in the vs GUI (there is nothing to see) and we in general delete the ssis teradata password once we establish the initial Teradata Data Flow.   What we are concerned about is the bad guys reading the .dtsx package or dtsconfig file directly from the file system and getting passwords from there.  I know that ssis encrypts passwords in the .dtsx and .dtsconfig file, our organization says not to use that.

    • Edited by etlman Thursday, September 19, 2019 6:43 PM
    Thursday, September 19, 2019 5:57 PM
  • Hi flash7gordon,

    Have you resolved your issue? Would you please kindly give us some feedback?

    If you fund above post was helpful, you could mark it as answer. It will help other people who has similar issue and benefit the community.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, September 20, 2019 9:40 AM