Calling sql script in Custom resource using different credential

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Daniel Krebs Daniel Krebs 1 month, 4 weeks ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #49187
    Profile photo of Gaurav Mishra
    Gaurav Mishra
    Participant

    I have a custom resource which runs a SQL script on SQL server. Script runs using machine account where I run DSC, I need to run this as a different account. I dont want to use sql authentication.
    Below is set function of my resource code. Right now this script runs as domain\MAchineName$ I need to run it as different user e.g domain\dvapp

    function Set-TargetResource
    {
    param([Parameter(Mandatory)]$ConnectionString,
    [Parameter(Mandatory)]$SqlScriptPath,
    [Parameter(Mandatory)]$DeploymentTimeStamp

    )
    $sql = [Io.File]::ReadAllText($SqlScriptPath)
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = $ConnectionString
    $SqlConnection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    # Set the SqlCommand's connection to the SqlConnection object above.
    $Command.Connection = $SqlConnection
    # Set the SqlCommand's command text to the query value passed in.
    $Command.CommandText = $sql
    # Execute the command against the database without returning results (NonQuery).
    $Command.ExecuteNonQuery()
    }

    #49208
    Profile photo of Daniel Krebs
    Daniel Krebs
    Participant

    Hi Gaurav,

    I believe your best and Microsoft supported option would be to use the PsDscRunAsCredential property introduced with WMF/PowerShell 5.0. The only caveat is you need to get all your DSC nodes updated to at least the Windows Management Framework (WMF) 5.0.

    https://msdn.microsoft.com/en-us/powershell/dsc/runasuser
    https://msdn.microsoft.com/en-us/powershell/dsc/configdatacredentials

    Best,
    Daniel

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.