I've never tried password expiration within SQL (we use AD accounts when we need to expire passwords), but technet has http://technet.microsoft.com/en-us/library/ms161959.aspx

"Password expiration policies are used to manage the lifespan of a password. When SQL Server enforces password expiration policy, users are reminded to change old passwords, and accounts that have expired passwords are disabled."