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

Question

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].

Backticks are for code/CLI snippets, not emphasis. – Daniel Mann Apr 03 '23 at 19:36 — Daniel Mann, Apr 03 '23 at 19:36

Shamrai Aleksander · Accepted Answer · 2023-04-04T06:43:39.007

1

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.

edited Apr 04 '23 at 06:43

answered Apr 04 '23 at 06:36

Shamrai Aleksander

13,096
3
24
31

great info! I'm glad my question did not get closed [yet at least] to be able to read it! Not sure why so much disdain for this question. – WestCoastProjects Apr 04 '23 at 07:03

WestCoastProjects · Answer 2 · 2023-04-04T15:07:25.880

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

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

2 Answers2