DPAPI actually works through 2 pretty simple function calls: CryptProtectData and CryptUnprotectData.
To make a long story short, CryptProtectData has two mode of operation: User or system. If you use the user mode (default), then the data is encrypted using the current user logon data. It means that you can only sucessfully call CryptUnprotectData on the same data from the same user context as the one you called CryptProtectData from. (in "system" mode, any process running on that same machine can decrypt the data but not from another system: no too good when the data is in a database).
This means that, if you initially encrypted your data in the SQL server database using a specific AD user account, you're permanently limited to using this user to access it. (This might be considered a defect or a feature depending on the application).
What does it means to you? Well, unless you can change the way the application works or you're willing to reset encrypted data completely, you cannot change the identity used by the web application process.
The good news, however, is that this doesn't have to be insecure: if your web application is an ASP.NET app and uses connection strings stored in the
web.config file in a standard way, you can protect them using the DAPI as well but this time with a delegate to the system. Microsoft has a pretty lengthy explanation on how to do that.
Finally, there is always the usual way to dead with that kind of issues: contact the application developers: they are the ones that can tell you exactly what is supported and how. If necessary, they are the ones that can also make the necessary changes to their system to reach the security goals that you have set.
Edit: There is an additional element that you must take into account in your case: when using DAPI, the master key used for encryption is stored in the user profile.
This means that, when you're using a virtual user account (as the ones created for you when running a web app under
AppPoolidentity), DAPI actually will not be persisted or reloaded since there is no actual profile created and you will experience the issue you describe: IISreset causes the application to lose its master encryption key because it cannot persist it.
To solve that, you can switch to switch to the
NetworkService identity for your web application pool. You will lose the segregation of user provided by the virtual account but, beyond that, it should work in the same way. Microsoft also suggests that you check the "LoadUserProfile" property of the web application pool but I'm dubious that changing it will solve your issue on its own: try it but my guess is that you'll have to use
NetworkService as well.