OleDB connection string password encoding

david_fetters

Hey friends,

So I have been working through ways to create workflows that 1) have database connections and 2) can be opened and used by individuals other than their creator.  This rules out using the Connection Manager since anytime another coworker opens up the stream, they have to alter the database connection stream.

I know that it is possible to store the password with an OleDB connection using the SQLNCL11 driver in the workflow itself, such that other users can open the workflow and the database connection will still be valid.  What I have found is that Alteryx stores a processed form of the password in the workflows itself, just not in plain text.  If you create a new OleDB connection to a SQL database using a username or password, check the "enable saving password" box and set the "persist security info" to "True", then the XML for the workflow will store a version of the password that it can somehow send to the server to access data.

My question for the ACEs and aspirants in the crowd: What exactly is alteryx doing to my plain text password to get it into the hexadecimal format stored in the workflow, and how can I take a plain text password and convert it into the same encoding?  IS it a hash, a base64 encoding to a hex value, next weeks lottery numbers?  Any insight would be useful.  I read through some of the driver documentation and have come up with blanks.  I do NOT need to reverse the value, I just want to understand how it is created.

Example of stored PW value:

   <Node ToolID="1">
...
     <Properties>
        <Configuration>
          <Passwords>482E52439EE665FCD1C3A79857AD22CFB8E2C05B66A96325BF6F8</Passwords>
          <File RecordLimit="" FileFormat="18"><![CDATA[odb:Provider=SQLNCLI11.1;Password=__EncPwd1__;Persist Security Info=True;User ID=MyUserName;Initial Catalog=DefaultTable;Data Source=somedatabase.com,12345;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MYMACHINE01;Initial File Name="";Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False;Application Intent=READWRITE|||SELECT * FROM sometable AS notarealtable
]]></File>
...
</Configuration>

Without knowing what exactly is being done to the password to create the <passwords> value, it makes it difficult to understand the security ramifications of this particular setup.

Claje

Hi,

Does this article explain what you need to know?

https://help.alteryx.com/11.7/index.htm#PasswordEncryption.htm?Highlight=encrypt

Another question for you - with regard to "database connections that can be opened and used by other individuals": Do these individuals have their own access to the databases?

If they can access the databases themselves, you could have each user configure their own Alias in the Connection Manager.  This "should" allow each of them to maintain their own passwords and connection strings, as long as you name these aliases consistently across your organization.