DTS Hashing

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aKersha/dtshashing.asp


    Cheers,

    Alex

    Rogue DBA

  • This is an encryption of the password, not a hash.  The term 'hash' connotes a one-way process.

  • You will find that this encryption actually points to the current version of the dts package.

    If you change the package over time, eventually the link gets lost and the encryption code will not work.

    I speak from experience here. Especially happens when saving as another name and then saving back as the original name, which some testers do at times.


    ------------------------------
    The Users are always right - when I'm not wrong!

  • Much better way is to use integrated security. You just need a /E on the end then

     

  • Agreed with previous comments. I always using  -E switch to get an integrated security.

  • Can you guys elaborate on integrated security or point me in a direction to learn more about this? Thanks.

    Also, as a side not another way I have created the DTS execute command is through the dtsrunui utility. This also allows for more customization with logging and such.

  • Integrated security = Windows NT Security. From books on line: The login is created in Microsoft Windows NT® 4.0 or Windows® 2000 rather than in SQL Server. This login is then granted permission to connect to an instance of SQL Server. The login is granted access within SQL Server.

    SQL Server achieves login security integration with Windows NT 4.0 or Windows 2000 by using the security attributes of a network user to control login access. A user's network security attributes are established at network login time and are validated by a Windows domain controller. When a network user tries to connect, SQL Server uses Windows-based facilities to determine the validated network user name. SQL Server then verifies that the person is who they say they are, and then permits or denies login access based on that network user name alone, without requiring a separate login name and password.

  • If you have a job that runs multiple dts packages, the process you have documented would be long and laborious....

    I generally use the DTSRUNUI.exe executable...

    1) >start>Run>DTSRUNUI 

    2) Specify SQL Server you want to get encrypted command for and the login credentials you want the job to use to run.

    3) click the "..." next to package name

    4) Click the "advanced" button

    5) Specify variables if you want to...

    6) Click the bullet for "Encrypt the command"

    7) Click the "Generate" button

    8) copy n' paste the line of text that is generated into the step of your dts package...

    Wahlah, nice and easy... DTSRUNUI is seperate from enterprise manager, so you can have both apps up at the same time...

  • Thanks for the tip.

    Can this encrypted command line be used as the Data Source on the Linked Server Properties sheet, (when implementing the Package as a linked server this is the equivalent of the dtsrun command-line). This is important to us as the Data Source is stored as open text in the database ???

  • I agree with the other posters regarding the use of the /E option.  The key to making this work is to remember the security context.  In the case mentioned here, the job will run as the user logged in to the SQL Server Agent.  If the DTS package utilizes resources across the network, it may be necessary to set up the SQL Server Agent account with a domain level account.

    We run hundreds of DTS packages this way.  We tried running with the method mentioned here and found it to be a maintenance nightmare because you can't be sure which package is actually being run.

  • Thank you all for your comments!

    True, this is not a true hash but I needed something to title the article. I'll be more careful in the future.

    I also use the DTSRUNUI property sheet which is much more flexible. There are too many options though to describe in a short article so forgive the omission.

    The use of the /E option is another way to do this but is against our security policy to use trusted server connections. Several of you made a good point regarding the use of the "hash" is only good for a point in time run. If ANY change is made to the DTS package, the process must be re-run to get a new string.

    Again, thanks for your input. I'll be writing frequently and welcome all your feedback!

    Alex Kersha


    Cheers,

    Alex

    Rogue DBA

  • I second the DTSRunUI suggestion, it is a much easier way of getting a command line. /E is also good practice. Also be aware that the right-click schedule job, it uses the credentials you use for the server registration in EM, so if you use SQL security in EM, that is what is used, so changing passwords are also an issue. Since almost all cases of schedule packages refer to a package stored on the same saerver as SQL Agent, is there any need to use the encrypted command line? Using trusted security (/E) means you can skip the encryption, and go with visibility and ease of management. There are countless posts on the newsgroups, "How do I tell what package this is?" when using the encrypted version.  Just to top it off, there are cracks out on the web to decrypt this command line.

    Darren Green
    SQLDTS.com   |   SQLIS.com   |   Konesans Ltd

  • I agree with most responses that the encrypted string is more of a convenience factor than addressing security.  Executing the package using Windows/AD integrated security would truly constitute security in my view.

    Regards,

    Uday

     

  • I agree with most posters here.

    The recommended solution should be to use integrated security and do not encrypt the command line.

    The encrypted command line means the "good guys" cannot tell what is going on and have difficulty maintaining the system.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply