Calling sql script in Custom resource using different credential

Welcome Forums DSC (Desired State Configuration) Calling sql script in Custom resource using different credential

Viewing 1 reply thread
  • Author
    Posts
    • #49187
      Inactive
      Topics: 3
      Replies: 3
      Points: 0
      Rank: Member

      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
      Moderator
      Topics: 2
      Replies: 525
      Points: 24
      Team Member
      Rank: Member

      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 1 reply thread
  • The topic ‘Calling sql script in Custom resource using different credential’ is closed to new replies.