When creating a SQL Server level account and not using windows authentication, how do you set things up so that you know you have a complex password that meets your requirments? Well that is what the is_policy_checked option is for. If that is set = 1 then SQL Server will read your password policy from the local security policy or the domain policy and enforce those same restrictions on the password. Restrictions are password length and complexity, password history and things like that. But I also need the passwords never to expire. these are accounts that applications use and if the passwords expire then that could cause a big mess when the application can no longer log into the database. Well that is why the put the is_expiration_checked option in as well. Both options are turned off by default but if you select the policy option then the default for expiration also becomes checked so if you do not want it to expire you need to make sure to uncheck that option after you turn on the policy. So if you do not check the expiration then the policy still gets checked but it does not apply the section of your security policy about expiration to the sql auth accounts.
Hope that makes sense and helps you out. Here is a link to BOL about this as well.