Azure Hosted Integration Services catalog getting USER_SID() Error

Derek Price 26 Reputation points
2022-11-08T22:08:31.727+00:00

We have SSIS hosted in Azure Data Factory - attached to a Azure Hosted SQL server - we have a dedicated SSIS DB and a Integration Runtime in ADF. As of Friday afternoon all calls/connections/deployments/pipelines have been getting the error:

'USER_SID' cannot be invoked with parameters in this version of SQL Server. (Microsoft SQL Server, Error: 40507)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.0937&EvtSrc=MSSQLServer&EvtID=40507&LinkId=20476

This is clearly some backend call being made - as the function when run locally returns some value - Select USER_SID();

At this time our SSIS instance is completely non-functional and there is no support on the issue. Restarting the SSIS-IR did nothing; The Database itself is still up and running, though.

Any words of advice?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,111 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,517 questions
0 comments No comments
{count} votes

Accepted answer
  1. Pratik Somaiya 4,206 Reputation points
    2022-11-09T07:42:28.717+00:00

    Hello @Derek Price

    While running this locally, have you tried creating a variable and then passing that variable to Select USER_SID();

    What parameter are you passing with USER_SID as Azure SQL might not support it

    https://learn.microsoft.com/en-us/sql/t-sql/functions/suser-sid-transact-sql?view=sql-server-ver16

    Also, parallely can you raise a ticket from Azure Portal with Microsoft so that a dedicated team can look into it

    Thanks

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Derek Price 26 Reputation points
    2022-11-09T18:05:05.477+00:00

    @Pratik Somaiya - It was actually a bad deployment going on the Western US servers in which they needed to roll back. It was not a function I was calling - it was a function that Integration Services was trying to run to validate the user in the cloud.

    Thank you for taking the time to respond and I wanted to let you know what happened.

    1 person found this answer helpful.