Connecting to dedicated and serverless pool via Invoke-SqlCmd using Token-Authentication

[Lorrin]

Hello All, I'm a data engineer wearing the hat of an DevOps engineer currently. I'm having a big struggle with trying to execute a T-SQL statement (CREATE USER <<>> FROM EXTERNAL PROVIDER) on an azure synapse dedicated or serverless pool but connected as an Azure AD User. I'm attempting to use Token-Authentication and am running the script in the context of the service connection running the azure pipeline, but no matter which resource endpoint I use to get the token from https://management.azure.com, https://dev.azuresynapse.net, and https://sql.azuresynapse.net, I'm getting this error Login failed for user '<token-identified principal>' - I can verify that I'm getting a valid token, but the connection is refused. Have others done this successfully?

[#2]

Hi, have you been able to get any progress on this ? I am facing the same problem. :) I'm getting an access token from within my pipeline via `

az account get-access-token --resource="https://database.windows.net"` and try to use that token for connecting to the Synapse Serverless SQL Database using Invoke-SqlCmd, but I'm facing the same error as you're facing.

The service-principal that is behind the service-connection does have access-rights on Synapse.  (It's a synapse administrator)

[#3]

Hello,
I don't have all the rights needed but one of the suggestions was to use a new version of sqlcmd that does have the right capabilities. I'm waiting for my client to install it on the agent pools. Here is the repo on git: https://github.com/microsoft/go-sqlcmd

You might try this in your environment.

Good luck!

Lorrin

[#4]

@Lorrin Any luck solving the issue? It seems that I have a similar problem with getting the token from/for the Synapase Serverless SQL pool: https://techcommunity.microsoft.com/t5/azure-synapse-analytics/get-token-for-synapse-serverless-sql-...

[#5]

@ssisjoost 

Unfortunately, we had to move onto other priorities.  Maybe I'll give go-sqlcmd again - see link in the thread in my own environment and report back.

Regards,

Lorrin

[#6]

Не удаётся открыть эти ссылки. Одну открыл , но еррор. :)