Can you mix on premisses SQL Servers and Azure SQL Servers in a trust group?

I need to be able to create cross-server views which include tables from databases on SQL Servers in a local corporate domain and joining to tables in a database on a hosted Azure SQL Server instance and I need to be able to access those views from stored procedures in the local databases using the “EXECUTE AS” operator.

We were able to setup a linked server on the local SQL Server to the Azure database and we were able to write and execute the views that join across the two system and the stored procedure that utilized those views executed successfully when executed within management studio. However, when the stored procedure is execute from within the context of our ERP system (happens to be Microsoft Dynamics SL) the stored procedures fail.

I believe one of the reasons they fail is because the ERP system executes all SQL calls with in a predefined security context of a special Login (I may be mangling the details… I’m at the edge of my expertise with respect to security in SQL here) functionally equivalent to things being called with an “Execute As” clause added.
So, for testing, I tried explicitly adding the EXECUTE AS to the stored proc. and after doing so, attempts to execute the procedure in management studio return an error that the linked Server is not “Trustworthy”….

So, this finally brings me to the actual question I’ve posted….
How can you establish a trust relationship between a linked Azure server/database and a database on a local domain SQL server instance?

I believe you may be running into just the fact the database itself doesn’t have TRUSTWORTHY enabled. Pinal Dave has a good article about it in SQL SERVER – FIX: Msg 15274 – Access to the Remote Server is Denied Because the Current Security Context is not Trusted.

As per that article, in your source database, you can check the TRUSTWORTHY setting with the following:

SELECT is_trustworthy_on, name
FROM sys.databases
WHERE name = 'YourSourceDatabase';

And to turn TRUSTWORTHY on you can use the following:

ALTER DATABASE YourSourceDatabase SET TRUSTWORTHY ON;

Side note, you may want to consider using a technology like Replication as a way to materialize the data you need to your local server. That way you don’t need to leverage a Linked Server for querying, which is known for specific limitations and potential performance issues.