Question 103630 in Azure Question posted in
The official documentation to get you started can be found here.

UPDATE Azure sql table fails from onprem sql server using linked server

I have an on premise SQL Server database that is the backend for our project management software, a Azure sql table that contains limited data used for reporting with power bi and a linked server to connect the two. Both of the databases have a specific user/pass account just for this which is stored in the linked server. Heres the problem:
When I run a SQL Server Agent job to update the azure table from the on prem table using the linked server everything works fine.
When I manually run a sql update statement from an open window in SSMS to do the same everything works fine.
When I use a workflow in the project management software to trigger a Stored Procedure to execute the same code (update Azure from the on prem database) I get the following error:

The OLE DB provider "SQLNCLI11" for linked server "LinkedServerName" reported an error. One or more arguments were reported invalid by the provider.
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "LinkedServerName" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI11" for linked server "LinkedServerName" returned message "The parameter is incorrect.". Error occurred in: STORED_PROCEDURE_NAME[CRLF]Error occurred on line 23

There’s nothing on line 23, and like I mentioned earlier, if I manually run the same update statement it works and if I have a SQL Server Agent Job run the same statement it works. Why does it fail when the code is executed by the project management software? Anyone have experience with this?

This is the code to insert the data from on prem into Azure:
INSERT INTO [LinkedServerName].DatabaseName.SchemaName.TableName ([ProjectNumber],[CreateDate],[SyncDate])

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I ended up taking a different strategy. We know using a scheduled SQL Agent Job to insert data in to azure works, it just wouldnt work in any script ran by our software and the user it uses to access the on prem database. So I created a SP in the on prem database that the software executes through a built in workflow. The SP executes saving the data to a staging table, then executes the SQL Job, which reads from the staging table and then inserts the data into an Azure table. Everything worked in the testing environment, but when I replicated all the scripts into production I got a permissions error. After doing a lot of research and testing adjustments to the user I ended up getting it to work by assigning the role TargetServersRole and db_ddladmin to the user in the msdb database and that worked.

    ssms screenshot

    Below are the two articles that let me to this conclusion:

    Article 1

    Article 2


  2. 0

    I’m not sure about this with Azure but I had a similar issue with a remote server and had to disable promotion of distributed transactions. It might not be the best thing to do in a production environment so read up carefully about the implications of doing this.

    I’m only suggesting to try this to narrow down what the real issue is..

    enter image description here

    Change this setting and test.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search