Keeping SQL Server secure is not a simple matter of applying hotfixes. The self-education required to keep SQL Server safe is far reaching, covering a number of different topics. This collection of quick resources will help you understand the scope and dimension of SQL Server security problems that you must be ready for.
Microsoft's own site conglomerates quite a bit of basic SQL Server security information in one place. Obviously this advice is coming from an MS-centric perspective, which suggests that to get secure is to upgrade to SQL Server 2005, which ships by default in a heavily locked-down configuration. If this isn't practical, it does provide advice for how to keep earlier versions secure.
SQL Security is a great third-party "one-stop-shop" for generic security advice as well, with details about best practices and auditing tools.
SQL-specific malware, like the Slammer worm, are crafted to exploit buffer overflows in SQL Server and allow someone else's code to run (with predictably bad consequences). Net-security.org maintains a list of all SQL worms currently in the wild, along with fixes and detailed briefings about how they work.
Passwords and user accounts
Passwords and accounts must be set up and handled with care to prevent outsiders from gaining access, even if only inadvertently. An article on the SQL Server security model at Developer.com has good advice about how to use SQL Server's native features to prevent user-account-based attacks.
This is one of the sneakiest methods to subvert SQL Server. SQL injection involves submitting malformed data to SQL Server, typically through a Web form, which can be executed as a command. (For instance, SQL injection attacks have been used to subvert the popular phpBB bulleting-board forum software. Even though phpBB uses MySQL, the principles are the same.) The SQL Security site explains how SQL injections work and how to avoid them, including testing tips.
Encrypting data and procedures to keep out prying eyes is a new but rapidly-growing field for SQL Server. The full scope of in-database encryption and protection probably deserves its own piece, but SQL Server 2005 now has it as a standard feature to encrypt data and third-party products like SQL Shield offer it for earlier versions of SQL Server.
Top 10 security enhancements in SQL Server 2005
Not upgrading? Keep SQL Server 2000 secure
Research best practices for locking down SQL Server