Forum Discussion

amitaiemd's avatar
amitaiemd
Copper Contributor
Jul 06, 2023

how to execute sql script via powershell in azure pipelines yml

Hi Team,

 

I have a requirement where I need to execute sql script in SQL Server(not Azure one) via pipelines yml. I hav got host name, userid and password. 

I need a sample powershell command which I can refer in pipelines to execute the script.

Can you please help me solve this. Thanks

azure devops

6 Replies

  • LeonPavesic's avatar
    LeonPavesic
    Silver Contributor
    Jul 06, 2023

    Hi amitaiemd,

    to execute an SQL script in a non-Azure SQL Server using PowerShell in Azure Pipelines YAML, you can use the Invoke-SqlCmd cmdlet and this example PowerShell script:

    - task: PowerShell@2
    displayName: 'Execute SQL Script'
    inputs:
    targetType: 'inline'
    script: |
    $serverName = 'your_server_name'
    $databaseName = 'your_database_name'
    $userName = 'your_username'
    $password = 'your_password'
    $sqlScriptPath = 'path_to_your_sql_script'

    # Connect to the SQL Server
    $connectionString = "Server=$serverName;Database=$databaseName;User ID=$userName;Password=$password;"
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $connection.Open()

    try {
    # Read the SQL script file
    $scriptContent = Get-Content -Path $sqlScriptPath -Raw

    # Execute the SQL script
    $command = New-Object System.Data.SqlClient.SqlCommand($scriptContent, $connection)
    $result = $command.ExecuteNonQuery()

    Write-Host "SQL script executed successfully."
    }
    catch {
    Write-Host "An error occurred while executing the SQL script: $_"
    throw
    }
    finally {
    # Close the connection
    $connection.Close()
    }


    Replace the values (your_server_name, your_database_name, your_username, your_password, and path_to_your_sql_script) with the actual values specific to your environment and SQL script.

    Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

    Kindest regards

    Leon Pavesic

    • amitaiemd's avatar
      amitaiemd
      Copper Contributor
      Jul 07, 2023

      Thanks for the quick help, one problem I am facing with this, when connecting to database via pipeline it's saying "Login failed for the user 'domain\username'". Please note when I launch my SQL server manually there I need to provide my domain password(which is same in this case also and windows authentication) and I need to execute a command which is :%windir%system32\runas.exe/netonly/user:domain.add\username <space > path to SQL server exe file.

      Can you please help me further. Thanks much

      • LeonPavesic's avatar
        LeonPavesic
        Silver Contributor
        Jul 07, 2023

        Hi amitaiemd,

        To authenticate with Windows credentials and execute the SQL script using the runas command in PowerShell, you can use this:

        - task: PowerShell@2
        displayName: 'Execute SQL Script'
        inputs:
        targetType: 'inline'
        script: |
        $serverName = 'your_server_name'
        $databaseName = 'your_database_name'
        $userName = 'your_username'
        $sqlScriptPath = 'path_to_your_sql_script'

        # Construct the "runas" command
        $runAsCommand = '%windir%\system32\runas.exe'
        $runAsArgs = "/netonly /user:domain\username `"sqlcmd.exe -S $serverName -d $databaseName -U $userName -i `"$sqlScriptPath`"`""

        # Execute the "runas" command
        $processInfo = New-Object System.Diagnostics.ProcessStartInfo
        $processInfo.FileName = $runAsCommand
        $processInfo.Arguments = $runAsArgs
        $processInfo.UseShellExecute = $false
        $processInfo.RedirectStandardInput = $true
        $processInfo.RedirectStandardOutput = $true
        $processInfo.RedirectStandardError = $true
        $process = New-Object System.Diagnostics.Process
        $process.StartInfo = $processInfo
        $process.Start() | Out-Null
        $process.WaitForExit()

        # Check the exit code
        $exitCode = $process.ExitCode
        if ($exitCode -eq 0) {
        Write-Host "SQL script executed successfully."
        } else {
        $errorMessage = $process.StandardError.ReadToEnd()
        Write-Host "An error occurred while executing the SQL script: $errorMessage"
        throw "SQL script execution failed with exit code $exitCode"
        }



        Please make sure to replace domain\username with your actual domain and username.


        Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

        Kindest regards

        Leon Pavesic

Resources