August 18, 2010 at 4:01 pm
We recently moved a database using a tool provided by NetApp that detaches the database, moves the data files, and then re-attaches using CREATE DATABASE...FOR ATTACH
Since that move, we have a job that runs primarily on that database but which also calls a database on a linked server. This fails with the following error:
Executed as user: CallTrackingImport. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274). The step failed.
The user CallTrackingImport has db_datareader and db_datawriter access to the database. The linked server is setup using the sa login on the remote server; and we're able to run queries against it. It only fails when run as a scheduled job.
Is there something else I need to check?
August 18, 2010 at 4:55 pm
you need to make the database trustworth.
Try this:
alter database [DBNAME] set trustworthy on
August 19, 2010 at 7:59 am
you need to make the database trustworth.
Try this:
alter database [DBNAME] set trustworthy on
Tried that and it didn't work, but thanks for the suggestion 🙂
I ended up "fixing" the problem by backing up the database and restoring it. I don't quite understand why that worked, but it was a suggestion from a friend and it seems to have done the trick. It was a small db, and not one that is accessed directly by end-users, so it wasn't a problem.
After the restore, I could once again run queries via scheduled job that used a linked server; however the stored procedure the job I was worried about still didn't work. I fixed that by removing "with execute as self" from the stored procedure.
My bigger concern is figuring out why this happened in the first place. Why would detaching a database, moving the files, and then re-attaching cause this sort of problem? And, while I don't particularly care about having to remove "with execute as self" from the procedure, why did that work before and not after the move?
Mainly, I don't want this issue popping up with any of the more critical databases.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply