SQL SERVER – FIX: Msg 15274 – Access to the Remote Server is Denied Because the Current Security Context is not Trusted

While working with a client, I encountered few errors while using linked server and I am going to share my solutions via a few blog posts. In this blog we would talk about fixing error 15274 – Access to the remote server is denied because the current security context is not trusted.

Before I talk about the problem and the solution, I must share a few environment details.

Remote Server Error

In my lab, I got two servers SQLSERVER-0 and SQLSERVER-1. The linked server was created on SQLSERVER-1 which was given name as ONE, connecting to SQL SERVER-1. There are two databases involved here. On source server (SQLSERVER-0) I have a database called SQLDB0 and on destination (SQLSERVER-1), a database is called SQLDB1.

1. Linked server “test connection” was working just fine.
   
2. Linked server was not created using “SQL Server” option, but “Other data source” was used before application wanted to use a different name.
   
3. Simple queries were working fine but a stored procedure which was using “execute as user” was failing.

Here is the error which was coming when we were executing a stored procedure. Here is an oversimplified version of the stored procedure. The procedure is created in database SQLDB0.

CREATE PROCEDURE usp_fetch_data
AS
BEGIN
	EXECUTE AS user = 'user_no_login'
	SELECT *
	FROM One.SQLDB1.dbo.Table_1
	REVERT
END

And here is the error message when I execute it as below.

Here is the text of the error message.

Msg 15274, Level 16, State 1, Procedure usp_fetch_data, Line 5 [Batch Start Line 9]
Access to the remote server is denied because the current security context is not trusted.
I captured profiler trace but found nothing interesting.  Since error message was talking about “TRUSTED”, I recalled TRUSTWORTHY property of the database.

WORKAROUND/SOLUTION

My feeling was correct. As soon as I changed the database property on the source database, the above error disappeared. Here is the T-SQL to check the property.

SELECT is_trustworthy_on, name 
FROM sys.databases
WHERE name = 'SQLDB0'
GO

If you see is_trustworthy_on set as 0 (ZERO) then run below command to enable it and make it 1 (ONE).

ALTER DATABASE SQLDB0 SET TRUSTWORTHY ON
GO

Have you seen a similar error? Did you find any other solution?

Reference: Pinal Dave (https://blog.sqlauthority.com)