What are recommendations, best practices and have-to-dos regarding handling connection strings in web applications? What things one should never, ever do?
Best option: Don't use a password for the database, but instead employ (Windows) Integrated Authentication if it's possible, and of course isolate the application's identity that is authorized for the database. That way you have the OS/Webserver/SCM manage your identity, in a very secured manner.
If it's not possible to go the way of IWA, you'll need to securely encrypt the connstring, preferably using something like DPAPI (so you dont have to manage the encryption key), and store the encrypted value in a protected Registry key with strong ACLs.
If you are on ASP.NET (from your comments, but not OP), there are built-in tools to do this automatically (aspnet_setreg, aspnet_regiis, etc depending on version...)
things you should never do
- putting connection string in a text file.
- have configuration file in public accessible folder/directory.
things you should do.
- create helper function that generates database password on fly.
- frequently change database password and username.
- check if your server is configured properly, that you are not serving excutable files as plain text.
I'd recommend storing your connection string outside of the website root directory.
If you're using ASP.NET, then your web.config file will be in the website root directory, but you can encrypt a section of your web.config file using the Data Protection API, which stores the decryption key securely.
Which framework are you using for your web application?
As an option, try to use application role to access sql server
- Regular network administrators can manage data access without needing to consult the database administrator (DBA), simply by controlling who has access to an application.
- You don’t need to worry about keeping track of changing users with SQL Server itself. Set up an application role and you can delegate that back to the network level.
- You can limit data availability to a single application. For example, a user might be able to modify accounting information only when using the general ledger application, and not when he connects directly to SQL Server.