I would suspect that when you create a database manually, you are logged with sysadmin permissions, which you hopefully you are not when you are logged in from the application. In this case the permissions to xp_create_subdir is lacking.

A remedy would be to sign the trigger with a certificate, but I think a better solution is to take the backup asynchronously. I would suggest that you set up an event notification, and then you have an activation procedure on a Service Broker queue that creates the backup.