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.
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)
7 Comments. Leave new
thanks!!!
thanks, this solution worked for me.
Hi Pinal, Thanks for this post! We had this issue on the old SQL Servers 2005. Started without anything changed on SQL Servers while the LAN undergoing some changes (upgrade). Do you think that could caused the error and need to set db TRUSTWORTHY on? Thanks!
Thanks, fixed my issue immediately.
Hi, thanks but for me the issue is still there (I SET TRUSTWORTHY ON), is there anything else to check?
Awesome – this has helped me a bunch today. I have secured a Linked Server to be used by 1 local user and to impersonate another and setting the Intake DB to trustworthy solved the error! Thank you Pinal Dave. Your generous work pays off after 4 years, yet again.
This is confusing. How can you change the is_trustworthy_on property for a database on a linked server from the server that has the linked server definition? sys.databases will only list local databases, not databases on linked servers.