[Warm-Standby] master replication problem

[marcin_najs]

Hi.

I established warm standby replication for master database:

Replicating the master database in a warm standby environment for ASE

For most actions like: adding login (or db user), role grants it works fine.

There is one problem which I cannot resolve.

On my Active site I have configured  a password policy (each login MUST change password after first logon) - it is expired by default.

On my Standby site I have identical master..syslogins table (so all logins are also expired by default).

When someone changes his password on ACTIVE site, DSI thread goes down:

E. 2015/09/10 11:29:21. ERROR #13045 DSI EXEC(125(1) clininet_standby.master) - seful\cm.c(3784)

        Failed to connect to server 'clininet_standby' as user 'test'. See CT-Lib and/or server error messages for more information.

I. 2015/09/10 11:29:21. Message from server: Message: 4022, State 1, Severity 10 -- 'The password has expired, but you are still allowed to log in. You must change your password before you can continue. If a login trigger is set, it will not be executed.

'.

I. 2015/09/10 11:29:21. Message from server: Message: 5701, State 2, Severity 10 -- 'Changed database context to 'master'.

'.

E. 2015/09/10 11:29:21. ERROR #1028 DSI EXEC(125(1) clininet_standby.master) - seful\cm.c(3784)

        Message from server: Message: 7742, State 3, Severity 16 -- 'You must change your password using the sp_password system stored procedure before you can continue.

DSI is shutdown becouse of Message: 4022.

I tried to resolve this by:

1. Adding custom error class and binding it to my STANDBY connection

2. Assigning action WARN for errors.

Replication Server/15.6/EBF 20155 ESD#3/X64/Windows

Error still occurs.

Maybe someone knows how to solve this. Without proper password replication there is no point to replicate master database at all.

I would be grateful for any information.

Best regards.

--

Marcin

[marcin_najs]

Mark,

I have tried to map all 3 messages:4022, 5701 and 7742 (to WARN or IGNORE) but DSI is going down (and I think it is not the point). I've analyzed transaction from exception log:

begin transaction

[...]

exec sp_password_rep @caller_password=NULL , @new_password=0xc00799bd5cb791ee0cb227186a27d2ddc481510f67dd87ae0cf38bb98ab939a6c7deef3885587e851119, @loginame=NULL , @immediate=0

[...]

commit transaction

Repserver connects to STANDBY ASE database as user 'test' and executes: sp_password_rep.

It will work fine if expiration for login is not set. When syslogins.status is set to 'expired' the only command that you can use is 'sp_password'.

Regards.

--

Marcin

[Mark_A_Parsons]

Thanks for the additional details (including the actual transaction).

This looks like a bug/issue the SRS engineers will need to work out with the ASE engineers.

When a login is expired ASE will disallow all commands/SQL-batches (submitted by said expired login) other than a call to 'sp_password'. ("Duh, Mark!" ?)

So while the SRS/ASE engineers figured out how to translate a sp_password call into a sp_password_rep call, it doesn't appear they addressed the issue when the associated login's password is expired in the RDS (ie, ASE should be allowing sp_password_rep calls in addition to the sp_password call).

-----------

I'm not finding anything in the documentation about replication of password changes associated with expired passwords.

I can think of a couple possible workarounds but they'd require hacking the sp_password and/or sp_passwordpolicy procs in the PDS, and would require ongoing upkeep/maintenance (eg, re-installing the custom system procs after a DBA runs installmaster).

Interesting problem ...

----------

Hmmmm, just off the top of my head ... might be interesting to see if a solution could be derived through the use of the sp_extrapwdchecks proc ...

If sp_extrapwdchecks is called in the RDS when the DSI issues a sp_addlogin (or 'create login') call, the sp_extrapwdchecks proc could:

- if calling application is Repserver

- and calling login is the maintuser

- and password policy is set to automatically expire the password then

- unset the local password policy for this login (ie, do *NOT* auto-expire the password; this would require some research) and

- replace the desired password with a new random password (except sp_extrapwdchecks isn't defined to return a new/changed password ... need more research)

This would have the effect of leaving the expired password in place in the PDS, setting the password as valid/unexpired in the RDS (so no worry about the 4022 error), while disallowing the user direct access to the RDS (they'll get a login failure due to not knowing the randomized password).

An alternative would be to get rid of the serve-wide setting for auto-expiration of the password and leave it up to the sp_extrapwdchecks proc to auto-expire the password at a login-level basis (per sp_passwordpolicy/expire login passwords/<login> ?).  Would only work if the sp_extrapwdchecks proc is called after the initial insertion of the login into master..syslogins (and before the sp_password proc is called); would obviously need to validate this works if 'create login' is used instead of sp_addlogin.  So in the PDS you could auto-expire an individual login if sp_addlogin (create login?) is not submitted by 'Repserver', while in the RDS the password would not be auto-expired ... would then need to figure out how to disable the login from connecting to the RDS until it's changed its password in the PDS ... >>need a flashlight for this rabbit hole<<

[terry_penna · Accepted solution]

Hi Marcin

There are two CR's related to this issue one is in RepServer and the other in ASE RAT to address this issue. 

The RS CR is 626316

The ASE CR is 657779

Both of these CR's are fixed in ASE 15.7 and RS 15.7.1.  I recomend the latest  sp release of each product to ensure that you get all the current fixes for both ASE and RS.

To workaround the issue you will have to manually fix the expired password on the target master database.  You can then resume the connection and skip the transactions once you have fixed the password on the target.

Terry

[marcin_najs · Accepted solution]

Mark, Terry

Thank you for all information.

I will try to check it on my test environment for ASE 15.7 and RS 15.7.1 and post results here.

Best Regards.

--

Marcin

[marcin_najs · Accepted solution]

Terry,

Can you write some more details about CR's ?

I've tested this case using:

* ASE (active / standby): Adaptive Server Enterprise/16.0 GA PL01/EBF 22544 SMP/P/x86_64/Enterprise Linux/ase160sp00pl01/3523/64-bit/FBO/Tue Apr 15 13:24:31 2014

* RS: Replication Server/15.7.1/EBF 22526 SP201 rs1571sp201/Linux AMD64/Linux 2.6.18-128.el5 x86_64/1/OPT64/Fri Apr 25 12:50:41 2014

DSI is still going down.

Best regards,

--

Marcin

[terry_penna · Accepted solution]

If you are testing this do you have a download of SRS 15.7.1 sp208.  It would be better to test this in the latest vesion if I need to verify a CR with engineering.

Also can you please cut and paste the complete DSI errors from the SRS log in sp201.

Terry

[marcin_najs · Accepted solution]

Hi Terry.

Errors from RS 15.7.1 sp201 are similar to those from comment #1.

I. 2015/09/11 16:33:09. Message from server: Message: 4022, State 1, Severity 10 -- 'The password has expired, but you are still allowed to log in. You must change your password before you can continue. If a login trigger is set, it will not be executed.

'.

I. 2015/09/11 16:33:09. Message from server: Message: 5701, State 2, Severity 10 -- 'Changed database context to 'master'.

'.

E. 2015/09/11 16:33:09. ERROR #1028 DSI EXEC(144(4) penny.master) - seful/cm.c(5046)

        Message from server: Message: 7742, State 3, Severity 16 -- 'You must change your password using the sp_password system stored procedure before you can continue.

'.

E. 2015/09/11 16:33:09. ERROR #13045 DSI EXEC(144(4) penny.master) - seful/cm.c(5050)

        Failed to connect to server 'penny' as user 'testowy'. See CT-Lib and/or server error messages for more information.

I. 2015/09/11 16:33:09. The DSI thread for database 'penny.master' is shutdown.

Transaction from exception log:

exec sp_password_rep @caller_password=NULL , @new_password=0xc007189d0b1edebb2c89e2bedf984e24ed035bcbc7811c2bdcef36680c14ebcb3b344159337596f48ab4, @loginame=NULL , @immediate=0

I will try to download sp208....

Best Regards.

--

Marcin

[former_member214567]

After conferring with Terry on this issue, we found that the ASE portion of the fix for this was implemented, but the Rep portion was not. The recommended workaround is documented in the New Features Guide:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00783.1571202/doc/html/je...

"Password Expiration Intervals for Master Replication

If you set up Adaptive Server master database replication in a warm standby environment, Sybase recommends setting longer password expiration intervals on the standby master database compared to the expiration intervals on the active master database. This allows the active master database to control any change of password and allows replication of password changes to proceed."

I hope this Helps,

Dale

[marcin_najs]

Hi Dale,

Thank you for advice. I can set longer password expiration intervals on standby ASE but "expired" bit in master..syslogins is replicated when I create login on Active site. It cannot be unset on Standby site just by changing global pasword policy. It would require updating Standby syslogins every time I create a login on active site or changing default policy on Active site which I cannot do. I will try to check it on sp208.

Regards.

--

Marcin