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.
- Linked server “test connection” was working just fine.
- Linked server was not created using “SQL Server” option, but “Other data source” was used before application wanted to use a different name.
- 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.
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)