Each server will have its own machine key for encrypting/decrypting data
using DPAPI.  Data that is DPAPI-encrypted on one machine cannot be
decrypted on another machine.

From Improving Web Application Security: Threats and Countermeaures
http://msdn.microsoft.com/library/en-us/secmod/html/secmod92.asp

Web Farm Considerations...
DPAPI
To encrypt data, developers sometimes use DPAPI. If you use DPAPI with the
machine key to store secrets, the encrypted string is specific to a given
computer and you cannot copy the encrypted data across computers in a Web
farm or cluster.
If you use DPAPI with a user key, you can decrypt the data on any computer
with a roaming user profile. However, this is not recommended because the
data can be decrypted by any machine on the network that can execute code
using the account which encrypted the data.
DPAPI is ideally suited to storing configuration secrets, for example,
database connection strings, that live on the Web server. Other encryption
techniques should be used when the encrypted data is stored on a remote
server, for example, in a database. For more information about storing
encrypted data in the database, see the module, "Building Secure Data
Access."

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