dopydb (4/17/2014)
do i need to change protection level - should it be "encrypt sensitive with password?"i can only run the app out of hours hence why i cant test now
That is an option as well.
I guess your current protection level is EncryptSensitiveWithUserKey. When run with SQL Agent, another user is used so the password is lost.
Personally I prefer configurations (XML or SQL Server), but changing the protection level should work as well.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP