Trigger linked server SP via MDS data updates

Nandan Hegde 29,891 Reputation points MVP
2022-01-29T06:19:30.963+00:00

@Erland Sommarskog
I have a database server hosted in IaaS which also includes an MDS(Master data services) database along with normal one.

My intention is, whenever someone publishes a new data in an MDS table, an SP should be triggered in Azure SQL datawarehouse.

So I went with the below approach:

  1. Create a linked server between IaaS SQL and Azure SQL DW
  2. Create a trigger

I initially created a dummy trigger in a normal database in that server and tested out the linked server property change which you suggested and that worked as expected when I tried to insert a record into a table.

But when I followed the same process of creating a trigger on an MDS backend table, and when I am trying to insert a new records via UI explorer ; I am getting the error:

Access to the remote server is denied because the current security context is not trusted.

And unable to add any records.

Note: I am able to update existing records in UI but not add new records as I have created the trigger on Just Insert and not Update.

So is there any other property that I need to change ?

Note : The Is_trustworthy_On property is enabled on the MDS database already

Azure SQL Database
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.1K Reputation points MVP
    2022-01-29T13:40:48.127+00:00

    I have already partially answered Nandan in another thread, but since that thread originally was about something else, I suggested that he should start a new thread. I will repeat that answer for reference, and add some material before and after.

    Having thought a little more about this, I think you should change your approach. This can be probably solved with a trigger, but it will be complex and there will be a learning path for you. At the same time, adding a trigger in a third-party product is always questionable. I don't know about MDS, but do Microsoft officially support adding triggers on tables in MDS? Having been working with an ISV for many years, I can say that I would never agree to customers adding triggers to our tables. There is a great chance that they mess things up, and calls our support desk giving the vendor the blame.

    So I would suggest that you should detect the changes in some different way, maybe a job that runs regularly. A trigger is really only called for if you need a synchronous update. And since you cannot make it a distributed transaction, you already lose there. If the MDS transaction would fail and roll back further ahead, the data is still there in Synapse - which it probably shouldn't be.

    If you still want this in a trigger, read on. Or, well, skip to the dotted line for the additional material.

    Apparently, the MDS application is running an EXECUTE AS USER. When you impersonate a database user, the impersonation is not trusted out side the database, unless two doors are opened. This reason for this is that a power user in the database with no permissions outside it should not be able to impersonate a user who is sysadmin or a user who have access to another database and in that way elevate his or her permissions..

    As I said, this can be changed by opening two doors to get out of that sandbox. Apparently the door out of the sandbox itself is already open - the database is trustworthy.

    But the door to where you want do is apparently still closed. The database owner would need to have the permission AUTHENTICATE SERVER. But beware! That will create exactly the elevation risk that I mentioned above. If the instance is dedicated to MDS, and you are the only user, no sweat. If there are other databases, and users with power permission inside the MDS database, you cannot take this step. Security issue.

    ...........

    There is an alternate solution, but it is complex. First you create an SQL login, call it synapseproxy. Then you set up login mapping for synapseproxy for the linked server. You will need to create an SQL login for this in Synapse.

    Next, you create a certificate in master, and export that certificate to the MDS database. You change the trigger to have EXECUTE AS OWNER. You sign the trigger with the certificate, and you bracket access to the linked server with these statements: 

    EXECUTE AS LOGIN = 'synapseproxy'
INSERT SYNAPSE.db.dbo.tbl (....) 
   SELECT FROM ....
REVERT

    Next you create login from the certificate and you grant this login two permissions: AUTHENTICATE SERVER and IMPERSONATE ON LOGIN::synapseproxy.

    I have written a whole lot more about this technique in an article on my web site: https://www.sommarskog.se/grantperm.html. To fully understand what I have discussed here, you would have to read the main article in whole, except for the last chapter on Cross-database access. You would also need to read chapters 2, 3, 4 and 6 in the appendix, to understand the trick with AUTHENTICATE SERVER, which I explain in detail the context of Service Broker, which can be confusing if you are not familiar with Service Broker.

    So after all, you may be prefer to set up a job that monitors the table...

    0 comments