locked
password in the connection RRS feed

  • Question

  • Hello All,

    I have a problem when type the connection password and reopen the package i have to retype the password , however i checkd that checkbos "Save password"

    am connection to SQL Server 2008 ... any clue

    thanks in advance

    Sunday, April 10, 2011 4:04 PM

Answers

  • "when setting the SQL OLEDB connection password and setting the "Save password" checkbox as one user if  another user open the package for editing he must retype the connection password again."

    you should mentioned it earlier.

    It exactly the ProtectionLevel problem. by default the protectionLevel is EncryptSensitiveWithUserKey, and this means that only the user who are developing the ssis package can see sensitive data ( like password in the connection string ).

    with changing the protectionLevel to EncryptSensitiveWithPassword, and defining a password, every user needs this password to see sensitive data. if they enter wrong password they can work with package but they should re-setup connections because passwords don't show up.

    you can see this tutorial on how to set protectionLevel and deploy package and run it as job with EncryptSensitiveWithPassword protection level:

    http://www.protalk.in/sql-server/steps-to-create-and-deploy-ssis-package-as-a-sqlagent-job/


    http://www.rad.pasfu.com
    • Marked as answer by Jerry Nee Saturday, April 23, 2011 4:17 AM
    Monday, April 11, 2011 11:17 AM
  • You will need to edit the Command Line of the SQL Agent job (set all of the other settings first as I believe that once you change the Command Line the other property boxes won't work as expected).  You add the following to the command line /De "Password"

    http://msdn.microsoft.com/en-us/library/ms162810.aspx

    http://support.microsoft.com/kb/918760


    Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008
    • Marked as answer by Jerry Nee Saturday, April 23, 2011 4:17 AM
    Monday, April 11, 2011 11:18 AM

All replies

  • I'm not sure about what do you mean exactly.

    But when you create a connection and save the password, this connection will hold password in package. and you can use it everytime.

    Did you got error in connection while execution? what error?


    http://www.rad.pasfu.com
    Sunday, April 10, 2011 5:34 PM
  • A couple other things to check:  check the package protection level (http://msdn.microsoft.com/en-us/library/ms141747.aspx, http://bi-polar23.blogspot.com/2009/04/ssis-and-package-protectionlevel.html).  If it is DontSaveSensitive, then passwords won't be stored.

    If it is the default (EncryptSensitiveWithUserKey) then are you setting the password and setting the "Save password" checkbox as one user and then editing the package as another user.


    Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008
    Sunday, April 10, 2011 5:58 PM
  • A couple other things to check:  check the package protection level (http://msdn.microsoft.com/en-us/library/ms141747.aspx, http://bi-polar23.blogspot.com/2009/04/ssis-and-package-protectionlevel.html).  If it is DontSaveSensitive, then passwords won't be stored.

    If it is the default (EncryptSensitiveWithUserKey) then are you setting the password and setting the "Save password" checkbox as one user and then editing the package as another user.


    Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008


    Hi Russ,

    have checked that solution by changing the protectionlevel to EncryptSensitiveWithpassword but i have to type a password for the whole package however that solve my problem as you described but i don't know if it will wrok when call the package from JOb or i have to do some custome configuration


    Monday, April 11, 2011 11:04 AM
  • I'm not sure about what do you mean exactly.

    But when you create a connection and save the password, this connection will hold password in package. and you can use it everytime.

    Did you got error in connection while execution? what error?


    http://www.rad.pasfu.com


    sorry wasn't clear enough,here is  the problem

    when setting the SQL OLEDB connection password and setting the "Save password" checkbox as one user if  another user open the package for editing he must retype the connection password again.

    this problem solved by changing the protection level to EncryptSensitiveWithpassword but i have a concern, what if i schedullad a package is there any custom configuration since the whole packge now has a password after changed the protection level

    thanks in advance

     


    Monday, April 11, 2011 11:09 AM
  • "when setting the SQL OLEDB connection password and setting the "Save password" checkbox as one user if  another user open the package for editing he must retype the connection password again."

    you should mentioned it earlier.

    It exactly the ProtectionLevel problem. by default the protectionLevel is EncryptSensitiveWithUserKey, and this means that only the user who are developing the ssis package can see sensitive data ( like password in the connection string ).

    with changing the protectionLevel to EncryptSensitiveWithPassword, and defining a password, every user needs this password to see sensitive data. if they enter wrong password they can work with package but they should re-setup connections because passwords don't show up.

    you can see this tutorial on how to set protectionLevel and deploy package and run it as job with EncryptSensitiveWithPassword protection level:

    http://www.protalk.in/sql-server/steps-to-create-and-deploy-ssis-package-as-a-sqlagent-job/


    http://www.rad.pasfu.com
    • Marked as answer by Jerry Nee Saturday, April 23, 2011 4:17 AM
    Monday, April 11, 2011 11:17 AM
  • You will need to edit the Command Line of the SQL Agent job (set all of the other settings first as I believe that once you change the Command Line the other property boxes won't work as expected).  You add the following to the command line /De "Password"

    http://msdn.microsoft.com/en-us/library/ms162810.aspx

    http://support.microsoft.com/kb/918760


    Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008
    • Marked as answer by Jerry Nee Saturday, April 23, 2011 4:17 AM
    Monday, April 11, 2011 11:18 AM