Ron,
A reasonable (security-wise) approach for your situation would be to have a
user-defined encryption key (either static or derived from passphrase,
initialization vector (iv), etc) which should be used by all servers on your
farm. What you need to do is at application installation (on each server)
define this key and store it in secure form, so that only your application
can retrieve it. If your application is a Windows service (or some Windows
app which can run with loaded user profile), you can encrypt this key (and
store in the registry or app.config file) using DPAPI with user store of the
account under which the app will run. This, of course assumes that your app
and the app used to encrypt key run under the same user account. There may
be some logistical challenges here, but it is feasible. You will also have
to store the key (or key characteristics, such as iv, etc) somewhere to make
sure you can redefine it on another system or if your original server fails
or if you decide to run the app under a different account.
If your app is an ASP.NET app (or some app which cannot run with loaded user
profile, such as Web service), you can only use DPAPI with machine key. You
can use the same logic/process, but it is not secure, because anyone who
gets access to the server will be able to decrypt data (in the previous
case, the user would need to know the password of the user account, which is
unlikely). While some may argue that their servers are unhackable, life
proves that even most guarded systems can be broken into (due to viruses,
app vulnerabilities, admin/user mistakes, etc). Anyway, if you feel this is
acceptable risk (and it may be depending on the value of your data, support
infrastructure, etc), go ahead and use it; otherwise, it will be a bit
tricky. Check this article; it may offer you some ideas and relevant
references: "Safeguard Database Connection Strings and Other Sensitive
Settings in Your Code"
(
http://msdn.microsoft.com/msdnmag/issues/03/11/ProtectYourData/default.aspx
).
In any case, under no circumstances should you encrypt database data using
DPAPI (with either user or machine store). If your server gets rebuilt (or
user is changed), you will lose data and there is nothing you will be able
to do to restore it since you will not know how to generate the same key.
Alek