Many SQL databases can be configured to require a username and password when a connection is made. This is useful for preventing unwanted access and restricting the range of operations that certain users can execute. This attention to security is important, since databases are typically server based.
There are a number of issues for DatabaseLink that need to be considered when working with passwords. These depend on the level of security you want and how this should be balanced with convenience. Another issue is whether you are running the Wolfram Language in a standalone mode or inside a server (as in webMathematica).
The most convenient way to work with a password is to place it in a connection configuration file, as described in "Database Resources: Connection Configuration". However, the password will be stored in plain text, and an intruder could inspect the configuration file and learn the password. Since this is a security risk, the New Connection Wizard, described in "The Database Explorer: New Connection Wizard", does not save a password. However, you can edit the configuration file and add a password. You could provide further protection by ensuring that the permission on the configuration file is restricted to those who are intended to run the Wolfram Language.
A higher level of security is obtained if you use a GUI to enter the password, which has the advantage that the password is never stored. The GUI for the password is opened whenever you use a password setting of "$Prompt".
You could also enter the password in the OpenSQLConnection command, and then make sure that you deleted your Wolfram Language input as soon as you made the connection.
Using a GUI is useful for an interactive session of the Wolfram Language, but is not very useful if you run the Wolfram Language inside a web server (as in webMathematica). In this case, you have a number of options. You could place the password in a configuration file and use file permissions to restrict access to those who are running the Wolfram Language process in the web server. An alternative would be to store the password in an authenticated mechanism provided by the web server. For example, the Tomcat server provides a mechanism based on JDBC Realms. The database password could be retrieved from the web server and passed to the Wolfram Language, which could use it in an OpenSQLConnection command. Any hostile inspection of the Wolfram Language code would not find the database password without breaking the web server authentication mechanism.
For greater security, use SSL to protect the transactions between the Wolfram Language and the database. This is described in "Secure Socket Layer (SSL)".