Can you mix on premisses SQL Servers and Azure SQL Servers in a trust group?
0
votes
1
answer
192
views
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?
Asked by Tom Malia
Mar 15, 2022, 02:23 PM
Last activity: Jun 26, 2025, 08:06 AM
Last activity: Jun 26, 2025, 08:06 AM