CODEWITHSUNDEEP
azurepowershellconnection-string
user3680976
user3680976

Reputation: 69

Connection string needed to Azure Data Warehouse

I'd like to connect to our Azure Data Warehouse database to write PowerShell test results from Azure Analysis Services (AAS) to a SQL table, so it can act as an audit trail and have a variety of other uses.

My group has rejected using SSIS and SQL Linked Servers as a solution. The options open to us are PowerShell and Azure Data Factory (ADF).

Below are the values I have to connect to Azure Data Warehouse (ADW):

        ADW name:                      XXX-dev-adw99e99999.database.windows.net
        ADW Database:                  dev-adw
        Service account:               [email protected] 
        AAS server:                    asazure://westus.asazure.windows.net/YYYYYYYYYYY 
        AAS model name:                WWWWWW
        $CmdQuery:                     'select 1'

Running the following code:

$QueryResults = Invoke-Sqlcmd -ServerInstance $ADWServerName -Database $ADWDatabaseName -Query $CmdQuery -Username '[email protected]' -Password 'QQQQQQQQQ'

I get the error below during execution:

Invoke-Sqlcmd : Login failed for user 'svcZZZZZ_dev'.
At line:224 char:21
+ ... ryResults = Invoke-Sqlcmd -ServerInstance $ADWServerName -Database $A ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Invoke-Sqlcmd : 
At line:224 char:21
+ ... ryResults = Invoke-Sqlcmd -ServerInstance $ADWServerName -Database $A ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], ParserException
    + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

The service account has tons of permissions. What specifically would my connection string look like to write to ADW in PowerShell?

Upvotes: 0

Views: 138

Answers (1)

4c74356b41
4c74356b41

Reputation: 72151

what I think is happening here - you are trying to login with Azure AD auth, but Invoke-SqlCmd doesnt support that yet. you could use other means of logging in, like this one, example number 8.

Upvotes: 1

Related Questions