May 4, 2021 at 12:05 am
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.
May 4, 2021 at 8:53 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 4, 2021 at 5:19 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 4, 2021 at 5:34 pm
When you say it's sending clear text passwords, where are you seeing them? In a log? Are you running a packet capture?
May 5, 2021 at 6:21 am
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?
May 5, 2021 at 6:24 am
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.
May 5, 2021 at 6:27 am
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.
May 5, 2021 at 8:15 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 5, 2021 at 2:39 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 5, 2021 at 11:36 pm
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply