Job running SSIS package sends clear text passwords via LDAP

  • Hi,

    I have a job running SSIS package, which has 2 sql server connection managers using sql logins, one connection manager to a local log file, one to a linux server (postgress) and one to exchange server.  I've tried ticking "Enable Secure Sockets Layer (SSL)" on the last one hoping this would resolve the issue, but it did not.  Am I missing anything else?  Attaching a screenshot of the package.

    Thanks.

     

    Attachments:
    You must be logged in to view attached files.
  • Roust_m wrote:

    Hi,

    I have a job running SSIS package, which has 2 sql server connection managers using sql logins, one connection manager to a local log file, one to a linux server (postgress) and one to exchange server.  I've tried ticking "Enable Secure Sockets Layer (SSL)" on the last one hoping this would resolve the issue, but it did not.  Am I missing anything else?  Attaching a screenshot of the package.

    Thanks.

    Aren't SQL logins used only to connect to SQL Server? Not to Linux or Postgres.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Enabling SSL would encrypt the connection between the source and destination.  One of the main benefits of SSL is that MITM (man in the middle) attacks don't work on them as all of the data is encrypted in motion.

    Now, if you are sending the credentials across in plain text, they are going to arrive in plain text.  SSL protects data in motion, not data at rest.

    An example of this, using SQL Server, is if you connect using a secure connection (ie SSL), and you type in "SELECT 'PASSWORD123' AS [Password]", the data is encrypted going from your machine to the SQL Server and encrypted going from the SQL Server to your machine, but at each "end" it is unencrypted.  If someone has a tool for watching the queries executed against SQL Server (extended events and profiler for example) used by SQL Server on the server, they would see your query in plain text.  If someone had access to see your computer screen, they could see the query results.

    Now, doing a quick check on LDAP authentication via OpenLDAP (not a clue what LDAP tool you are using or how it is configured, it was just one I found) - it is possible to configure that tool to allow plain text passwords.  The link I found states "OpenLDAP does support unencrypted, plaintext authentication, but the instructions for setting that up are non-existent" (link - https://www.digitalreplica.org/articles/openldap-for-ldap-plain-text-password-capture/).  Now, Microsoft AD, from my understanding, stores the passwords in hashes.  You can send the request with the HASH or you can send the request with the plain text password and AD can generate the hash to compare.

    Based on your screenshot, since "use windows authentication" is unchecked, I would expect that you are either not using Active Directory OR you are not actually using LDAP for authentication.  If you are using active directory, turning that on SHOULD (ie check the documentation for the connection as I am not an expert in all connectors) know it is windows authentication and allow for hashed passwords instead of plaintext passwords.  If you are using LDAP, but not Active Directory, it may not hurt to try turning that on to see if your LDAP provider supports hashed passwords or not.

    TL;DR version - If you are getting plain-text passwords sent across for the LDAP authentication, it is HIGHLY likely that you have either your LDAP provider configured to accept plain text passwords OR your connection manager is configured to send across plain text passwords.

  • When you say it's sending clear text passwords, where are you seeing them?  In a log?  Are you running a packet capture?

  • Phil Parkin wrote:

    Roust_m wrote:

    Hi,

    I have a job running SSIS package, which has 2 sql server connection managers using sql logins, one connection manager to a local log file, one to a linux server (postgress) and one to exchange server.  I've tried ticking "Enable Secure Sockets Layer (SSL)" on the last one hoping this would resolve the issue, but it did not.  Am I missing anything else?  Attaching a screenshot of the package.

    Thanks.

    Aren't SQL logins used only to connect to SQL Server? Not to Linux or Postgres.

    This is correct, did I say something different?

  • Mr. Brian Gale wrote:

    Enabling SSL would encrypt the connection between the source and destination.  One of the main benefits of SSL is that MITM (man in the middle) attacks don't work on them as all of the data is encrypted in motion.

    Now, if you are sending the credentials across in plain text, they are going to arrive in plain text.  SSL protects data in motion, not data at rest.

    An example of this, using SQL Server, is if you connect using a secure connection (ie SSL), and you type in "SELECT 'PASSWORD123' AS [Password]", the data is encrypted going from your machine to the SQL Server and encrypted going from the SQL Server to your machine, but at each "end" it is unencrypted.  If someone has a tool for watching the queries executed against SQL Server (extended events and profiler for example) used by SQL Server on the server, they would see your query in plain text.  If someone had access to see your computer screen, they could see the query results.

    Now, doing a quick check on LDAP authentication via OpenLDAP (not a clue what LDAP tool you are using or how it is configured, it was just one I found) - it is possible to configure that tool to allow plain text passwords.  The link I found states "OpenLDAP does support unencrypted, plaintext authentication, but the instructions for setting that up are non-existent" (link - https://www.digitalreplica.org/articles/openldap-for-ldap-plain-text-password-capture/).  Now, Microsoft AD, from my understanding, stores the passwords in hashes.  You can send the request with the HASH or you can send the request with the plain text password and AD can generate the hash to compare.

    Based on your screenshot, since "use windows authentication" is unchecked, I would expect that you are either not using Active Directory OR you are not actually using LDAP for authentication.  If you are using active directory, turning that on SHOULD (ie check the documentation for the connection as I am not an expert in all connectors) know it is windows authentication and allow for hashed passwords instead of plaintext passwords.  If you are using LDAP, but not Active Directory, it may not hurt to try turning that on to see if your LDAP provider supports hashed passwords or not.

    TL;DR version - If you are getting plain-text passwords sent across for the LDAP authentication, it is HIGHLY likely that you have either your LDAP provider configured to accept plain text passwords OR your connection manager is configured to send across plain text passwords.

    Thanks for the detailed explanation.  Where should I look to fix this problem?  I thought the exchange server connection manager was to blame, as all others don't use  AD authentication.  But it did not work.

    • This reply was modified 1 week, 1 day ago by  Roust_m.
  • ZZartin wrote:

    When you say it's sending clear text passwords, where are you seeing them?  In a log?  Are you running a packet capture?

    The events are shown in Microsoft Cloud App Security Dashboard.  The time coincides with the job schedule 100%.  I've disabled the job for the weekend and the events stopped.

     

  • Roust_m wrote:

    Phil Parkin wrote:

    Roust_m wrote:

    Hi,

    I have a job running SSIS package, which has 2 sql server connection managers using sql logins, one connection manager to a local log file, one to a linux server (postgress) and one to exchange server.  I've tried ticking "Enable Secure Sockets Layer (SSL)" on the last one hoping this would resolve the issue, but it did not.  Am I missing anything else?  Attaching a screenshot of the package.

    Thanks.

    Aren't SQL logins used only to connect to SQL Server? Not to Linux or Postgres.

    This is correct, did I say something different?

    I think I misunderstood. I thought you were saying something like

    I have two SQL logins: one used for a local log file and one for connecting to a Linux server.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • My opinion on the fix - try checking off the other box in the Exchange configuration (use windows authentication) UNLESS you are not using windows authentication for Exchange authentication.  If that is the case, you are likely going to need some sort of "middle ware" to hash the password and send it over to Exchange, or possibly a setting on the Exchange side to allow for hashed passwords to be sent across.

  • Mr. Brian Gale wrote:

    My opinion on the fix - try checking off the other box in the Exchange configuration (use windows authentication) UNLESS you are not using windows authentication for Exchange authentication.  If that is the case, you are likely going to need some sort of "middle ware" to hash the password and send it over to Exchange, or possibly a setting on the Exchange side to allow for hashed passwords to be sent across.

    Tried this already, did not help.

Viewing 10 posts - 1 through 10 (of 10 total)

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