How to Reset the SA Password in SQL Server 2000

Does an known account name like sa, pose a security threat to database?

A "god" user account with a known name is generally considered a worse idea than a god user with a less well known name. It makes brute force attacks that bit easier as the attacker only has to guess the password and not the username and the password.

Also having a god user anyway can be dangerous. You are generally better off having specific users with specific rights for what they need to do. This sort of privilege based security is easier to implement from scratch than it is to retrofit into your environment later.

Disabling sa and giving specific users specific admin rights as needed in SQL server is essentially the same recommendation as disabling root and handing out admin rights as needed via sudo under Linux and similar. You can always re-enable sa once directly connected to the machine with adequate privileges should anything go wrong and you end up dropping all the rights your users need to operate (and fix the issue) just the same as you can engineer root access to a Linux box if you have physical access to the box - so disabling the account is no magic bullet (but once an attacker has physical access to your machine, or full Administrative access via RDC or SSH, all bets are off anyway).

When using windows authentication on SQL Server does it impose the same password policy(if it was set to say account lockout after 5 times)?

When using Windows Integrated Authentication SQL server has no control over account lockouts and such - it just maps a Windows user to an SQL user and asks the OS to vouch for the fact that the user has provided appropriate credentials. For interactive human users this means any lockout would occur as the user attempted to authenticate with Windows, not as they logged in to SQL Server.

As stated in the comments to your question SQL Server will not revert a password back to the previous one used. I believe the problem you are having is that your application has the sa password stored somewhere that it needs to be saved to. That is likely why you are receiving the error regarding login credentials. I would suggest checking the documentation for your application (if it is COTS) to see where the password for the SQL Login is set at.

If the application is a Windows Service you will likely find the password set in some configuration file or registry key. I would hope not in plain text, but no product is perfect. If you find the password is not in plain text (garbled or unreadable) this would indicate that the application has some configuration API (GUI or command-line/CLI) that needs to be used to set that password so the application stores it where it needs to see it.