Userpass in OST_email empty, SMTP Email Fails

TheAudioPimp · Oct 19, 2022

Hi all

I'm wondering if anyone can help, we use a older very modified (so cant update) version which works fine apart from its sending emails via PHP and now the emails are getting marked as spam. So as previously we want to use Office 365 email. We have switched on Office 365 legacy SMTP Auth and all seems to be fine with that, and when we enter the SMTP details on the email template page they all check out and its saved correctly, however on sending an emails, we get incorrect credentials in the logs and it still sends via PHP. Upon looking in the DB the USERPASS is blank and not being populated, similar to this issue here:

https://forum.osticket.com/d/86340-smtp-email-configuration-issue-not-saving-password-field

I tried to generate the password using SQL query, but unsure what the string is, just to see if it works this way.

I've tried the following:

update ost_email set userpass = MD5(CONCAT(MD5('MYEMAILPASSWORD'), 'MYSECRETSALT')) where email_id='1';
update ost_email set userpass = MD5('MYEMAILPASSWORD') where email_id='1';

It generates and populates the password, but still the same error so obviously not generating the right hash.

If anyone cant help with generating the right hash that would be ace, as this is a temp fix obvioulsy, but even better if anyone can point as to why this password would not be save and just being blanked, all other items save ok in that table.

Many Thanks

TAP

KevinTheJedi · Oct 19, 2022

TheAudioPimp

You’ll need to use bcrypt. There are many bcrypt generators online. Use 8 round entropy.

Cheers.

TheAudioPimp · Oct 19, 2022

Hi Kevin

Thanks but that didn't work, I saved the generated password with 8 rounds from a Bcrypt generator but I still get this error as the password must be wrong once decrypted? Is the SMTP password using a different hash?

authentication failure [SMTP: Invalid response code received from server (code: 535, response: 5.7.139 Authentication unsuccessful, the user credentials were incorrect. [LO4P265CA0218.GBRP265.PROD.OUTLOOK.COM])]

Any other ideas.

Thanks

TAP

KevinTheJedi · Oct 19, 2022

TheAudioPimp

That's a completely different issue. That means you don't have Basic Authentication enabled on your tenant. On October 1, 2022 MS permanently disabled Basic Authentication if you didn't request to opt-out. So that means you must use Modern Authentication. In order to have Modern Auth support you must upgrade to v1.17, install the OAuth2, plugin, and follow our documentation below:

https://docs.osticket.com/en/latest/OAuth2/Microsoft%20Authorization%20Guide.html

If you have any issues please search older threads as pretty much any issue you run into will have a resolution or a thread on it.

Cheers.

TheAudioPimp · Oct 19, 2022

Hi, Kevin

I've looked at all other threads and changed the SMTP.php file and have some success up until this point. On the Email Templates page I put in the right password and it goes green and accepted and saves (But with blank USERPASS in DB), if I try a wrong password the page complains about wrong password so it must be definitely logging in and checking the password on Microsoft 365 ok. FYI I've turned back on SMTP auth using the commands in Exchange online, which Microsoft blanket disabled recently:

Set-TransportConfig -SmtpClientAuthenticationDisabled $False

Also played around with this too which gives a legacy SMTP address: smtp-legacy.office365.com
Set-TransportConfig AllowLegacyTLSClients

So initially the password is check and accepted and the template saves but the problem is the password is blank and when I update a ticket a blank password hash is pulled from the DB from USERPASS in OST_EMAIL, i can see other templates and password and the generated one above I did looks longer. Doesn't the USER pass using Secret Salt + MD5 ?

If I can figure out why the password isn't saving to USERPASS I think it will fix it for me. Or as a temp fix if I can generate the right hash for OST ticket USERPASS field that would work for now.

Thanks

TAG

KevinTheJedi · Oct 19, 2022

TheAudioPimp

Yea we use a combination of many things. You can search the code for where we hash the password before saving.

Cheers.

TheAudioPimp · Oct 19, 2022

Hi Kevin

I appreciate the help, I'm a network engineer not a software engineer so only know bits, can you advise at all where I would find this and if possible any SQL query / command like I used at the top that would do this as a temporary fix, really thankful for you replying. If I can generate the right hash for that field that would be enough to leave it in there and not change it for now so it worked, I just need to generate the right password for USERPASS.

Cheers

TAP

KevinTheJedi · Oct 19, 2022

TheAudioPimp

I don't know the exact location off the top of my head and unfortunately I'm way too busy to look that up for you. However you can do recursive searches on Github and should be able to track it down.

It won't be a simple MySQL query as you can't generate the hash via SQL it has to be done through the methods in the code. So you can generate the hash and then run a simple MySQL query but you first must generate the hash.

Cheers.

TheAudioPimp · Oct 19, 2022

Hi Kevin

I see this in the Class.email.php, is this what is doing it ? Thanks.

function getSMTPInfo($active=true){
$info=array();
if(!$active || ($active && $this->isSMTPEnabled())){

        $info = array ('host' => $this->info['smtp_host'],
                       'port' => $this->info['smtp_port'],
                       'auth' => $this->info['smtp_auth'],
                       'username' => $this->info['userid'],
                       'password' =>Misc::decrypt($this->info['userpass'],SECRET_SALT)
                       );
    }

    return $info;
}

function update($vars,&$errors) {
    if($this->save($this->getId(),$vars,$errors)){
        $this->reload();
        return true;
    }

    return false;
}`

KevinTheJedi · Oct 19, 2022

TheAudioPimp

update() where we update the config but we encrypt the password before that. Should be passed in with $vars.

Cheers.

TheAudioPimp · Oct 19, 2022

KevinTheJedi

yes I see this section in the same file with Encrypt lower down, but I'm unsure what to do next. If you do get a chance to help along with this, that would be awesome or if anyone else has any input or had a similar issue then I'm all ears and would be grateful. Cheers

` if(!$errors) {
$sql='updated=NOW(),mail_errors=0, mail_lastfetch=NULL'.
',email='.db_input($vars['email']).
',name='.db_input(Format::striptags($vars['name'])).
',dept_id='.db_input($vars['dept_id']).
',priority_id='.db_input($vars['priority_id']).
',noautoresp='.db_input(isset($vars['noautoresp'])?1:0).
',userid='.db_input($vars['userid']).
',userpass='.db_input(Misc::encrypt($vars['userpass'],SECRET_SALT)).
',mail_active='.db_input($vars['mail_active']).
',mail_host='.db_input($vars['mail_host']).
',mail_protocol='.db_input($vars['mail_protocol']?$vars['mail_protocol']:'POP').
',mail_encryption='.db_input($vars['mail_encryption']).
',mail_port='.db_input($vars['mail_port']?$vars['mail_port']:0).
',mail_fetchfreq='.db_input($vars['mail_fetchfreq']?$vars['mail_fetchfreq']:0).
',mail_fetchmax='.db_input($vars['mail_fetchmax']?$vars['mail_fetchmax']:0).
',mail_delete='.db_input(isset($vars['mail_delete'])?$vars['mail_delete']:0).
',smtp_active='.db_input($vars['smtp_active']).
',smtp_host='.db_input($vars['smtp_host']).
',smtp_port='.db_input($vars['smtp_port']?$vars['smtp_port']:0).
',smtp_auth='.db_input($vars['smtp_auth']);

        if($id){ //update
            $sql='UPDATE '.EMAIL_TABLE.' SET '.$sql.' WHERE email_id='.db_input($id);
            if(!db_query($sql) || !db_affected_rows())
                $errors['err']='Unable to update email. Internal error occured';
        }else {
            $sql='INSERT INTO '.EMAIL_TABLE.' SET '.$sql.',created=NOW()';
            if(!db_query($sql) or !($emailID=db_insert_id()))
                $errors['err']='Unable to add email. Internal error';
            else
                return $emailID; //newly created email.
        }
        
    }else{
        $errors['err']='Error(s) Occured. Try again';
    }

    return $errors?FALSE:TRUE;
}`

KevinTheJedi · Oct 19, 2022

TheAudioPimp

Yea you must be using a much older version then. In older versions we did only use secret salt. We’ve since switched it to use a combination of many things. You can see where we encrypt the password from the snippet you posted:

',userpass='.db_input(Misc::encrypt($vars['userpass'],SECRET_SALT)).

So this is the part to use:

Misc::encrypt($vars['userpass'],SECRET_SALT)

Cheers.

TheAudioPimp · Oct 19, 2022

Thanks for your help Kevin

It was the PHP version had to lower it and it saved ok and now sends fine without error.

Cheers

TAP