OK, making the priority changed allowed my to change a local user's password. However, it appears to break SSSD users. When I try to change myu password using a LDAP account (LDAP is accessed via sssd), I get:

tim@de8:~$ passwd
Current Password: <I type my password here>
passwd: Authentication token manipulation error
passwd: password unchanged

And /var/log/auth.log shows:

Mar 23 18:40:35 de8 passwd[13503]: pam_unix(passwd:chauthtok): user "tim" does not exist in /etc/passwd
Mar 23 18:40:46 de8 passwd[13503]: pam_unix(passwd:chauthtok): user "tim" does not exist in /etc/passwd
Mar 23 18:40:46 de8 passwd[13503]: pam_sss(passwd:chauthtok): Password change failed for user tim: 4 (System error)

I've set the SSD LDAP log to 10, but there's a lot of cruft in there. As far as I can tell, the relevant part seems to be a few lines:

(Fri Mar 23 18:40:46 2012) [sssd[be[default]]] [sdap_pam_chpass_handler] (2): starting password change request for user [tim].
(Fri Mar 23 18:40:46 2012) [sssd[be[default]]] [be_pam_handler_callback] (4): Backend returned: (3, 4, <NULL>) [Internal Error (System error)]
(Fri Mar 23 18:40:46 2012) [sssd[be[default]]] [be_pam_handler_callback] (4): Sending result [4][default]
(Fri Mar 23 18:40:46 2012) [sssd[be[default]]] [be_pam_handler_callback] (4): Sent result [4][default]

What is really odd here is that this almost make it sound like it's an issue with the LDAP back-end. However, if I revert to the old configuration (i.e. with the sss priority set to 512), password changing starts working again, so it seems like my back-end is working OK.

Here are the contents of /etc/pam.d/common-password (when I had the sss priority at 128):

#
# /etc/pam.d/common-password - password-related modules common to all services
#
# This file is included from other service-specific PAM config files,
# and should contain a list of modules that define the services to be
# used to change user passwords.  The default is pam_unix.

# Explanation of pam_unix options:
#
# The "sha512" option enables salted SHA512 passwords.  Without this option,
# the default is Unix crypt.  Prior releases used the option "md5".
#
# The "obscure" option replaces the old `OBSCURE_CHECKS_ENAB' option in
# login.defs.
#
# See the pam_unix manpage for other options.

# As of pam 1.0.1-6, this file is managed by pam-auth-update by default.
# To take advantage of this, it is recommended that you configure any
# local modules either before or after the default block, and use
# pam-auth-update to manage selection of other modules.  See
# pam-auth-update(8) for details.

# here are the per-package modules (the "Primary" block)
password        [success=2 default=ignore]      pam_unix.so obscure sha512
password        sufficient                                      pam_sss.so use_authtok
# here's the fallback if no module succeeds
password        requisite                       pam_deny.so
# prime the stack with a positive return value if there isn't one already;
# this avoids us returning an error just because nothing sets a success code
# since the modules above will each just jump around
password        required                        pam_permit.so
# and here are more per-package modules (the "Additional" block)
# end of pam-auth-update config