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

Azure Devops structure for running a query on Azure SQL preferably via a Subscription

There is a rich API to manage Azure instances, servers, and security policies at az sql reference and Azure CLI reference commands for Azure SQL . But how can we simply connect to an existing subscription [or at least to a connection string] and run basic sql commands? I have not found an alternate API or any methods within these existing api’s to do so. Can one or more of the admin-focused methods be repurposed to run general queries / run general DML commands?

The preference would be to use an existing Azure-Subscription as the basis of the connectivity. In that way we do not need to access KeyVault for the server passwords [and URI’s etc].

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I'm trying out Sqlcmd and will embed it in a Bash@3 task. Note it does work from macOS and Linux

    ubuntu:

    apt install -y install SqlCmd

    Sqlcmd -S our-server.database.windows.net -d ourdb -G

    Notice there are no passwords being shoved around here: the -G denotes to use ActiveDirectory behind the scenes with the logged-in user's credentials. This should translate as well to Azure

    That brings up the T-sql prompt. Then the insert statement we need can be invoked.

    Update On ADO the ActiveDirectory login is not found automatically. I will need to update this approach with the mechanisms to enable it.

    The specific error message:

    Failed to authenticate the user '' in Active Directory
(Authentication option is 'ActiveDirectoryIntegrated').

    Running on Azure it does need login mechanics still:

commit;'

2023-04-04T08:34:50.8668817Z Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.

2023-04-04T08:34:50.8670276Z Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Failed to authenticate the user '' in Active Directory (Authentication option is 'ActiveDirectoryIntegrated').

2023-04-04T08:34:50.8671087Z Error code 0xA190; state 41360

2023-04-04T08:34:50.8671557Z Error acquiring Kerberos credentials

2023-04-04T08:34:50.8672027Z GSS status: No credentials were supplied, or the credentials were unavailable or inaccessible

2023-04-04T08:34:50.8672561Z Mechanism status: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_1001)

2023-04-04T08:34:50.8672954Z .

2023-04-04T08:34:50.8673556Z Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Timeout error [258]. .

2023-04-04T08:34:50.8676187Z Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Unable to complete login process due to delay in login response.

2023-04-04T08:34:50.8690776Z + script_out='OK

  2. 0

    Consider using PowerShell Core and Invoke-Sqlcmd. It supports cross-platform: Invoke-Sqlcmd is Now Available Supporting Cross-Platform. Check examples of connecting to Azure SQL: Connect to Azure SQL Database. You may share query results through the logging commands: About task.setvariable

    Setup PowerShell@2 to use PowerShell Core:

    By default, PowerShell v2 uses PowerShell Core for Linux agents and
    Windows PowerShell for Windows agents. To use the latest version of
    PowerShell on Windows agents, set the pwsh parameter to true.
    PowerShell Core will then be used instead.

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

Back To Top
Search