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 · Accepted solution]

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.

[david_fetters]

Great find.  I think that explains it clearly enough for my purposes.

So if the password is stored in the XML, it is the result of a DES encryption with a private key that resides within each alteryx installation.  Since DES is not a secure cryptographic function if you've got the time and the computing resources, it's worth taking to heart the warning from that page that "Note: Alteryx makes no guarantee that encrypted passwords are secure!"

I think it's worth bringing up that this means there must be a hardcoded key somewhere within the files installed by Alteryx, and (assuming a password encrypted using the input tool works on multiple versions across multiple license environments) that key does not change with any regularity.  Although not a trivial task, a dedicated party could feasibly brute force the private key value and would have the key to decrypt any passwords stored using the "Hide" option created on ANY alteryx install at ANY point in time.  I'm not losing any sleep over it because we don't normally do this to provide access to our DBs, but it answers my question how a completely different computer can take that hex value and use it to authenticate a DB connection.

To your second question, in production we use a couple of different approaches for DB access including the one you mentioned.