0

I have a Database folder in my Github repository which contains SQL scripts. I can access the repo only using GitHub secrets. I want to download the scripts using Invoke-WebRequest Powershell cmdlet and run those scripts using Invoke-Sqlcmd cmdlet in my local db.

I have made the following script :

 Add-PSSnapin SqlServerCmdletSnapin100
 Add-PSSnapin SqlServerProviderSnapin100
 $GitRepository = ("https://github.com/XYZ/ABC/tree/ABCD/XYZXYZ/Database/")
 $GitManifestArray = @("Database.sql","XYZ.sql","ABC.sql","SP.sql","RSP.sql","FN.sql","E.sql")
 $SQLInstances = ".\SQLEXPRESS"
 $DefaultDatabase = "MYDEFDATABASE"
 ForEach ($SQLInstance in $SQLInstances.Split(","))
  {
      echo "Running Scripts on " $SQLInstance
      #foreach ($File In $Manifest)
      ForEach ($file in $GitManifestArray)
      {
          $ScriptURL = ($GitRepository+$file.Replace("/blob/","/"))
         echo "Running " $ScriptURL
         $Headers = @{
            accept = "application/vnd.github.v3.raw"
            authorization = '${{secrets.ABC_XYZ_ABC_TOKEN}}'
            }
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls,  [Net.SecurityProtocolType]::Tls11, [Net.SecurityProtocolType]::Tls12, [Net.SecurityProtocolType]::Ssl3

          $ScriptFromGit = Invoke-WebRequest -Uri $ScriptURL  -Headers $Headers -UseBasicParsing
          Invoke-Sqlcmd -ServerInstance $SQLInstance  -Query $ScriptFromGit.Content
      }
  }

but I'm getting the following error :

  *Invoke-WebRequest : The request was aborted: The connection was closed unexpectedly.
  At line:18 char:27
  +          $ScriptFromGit = Invoke-WebRequest -Uri $ScriptURL  -Headers $Headers - ...*

I tried to put authorization as :

authorization = 'token ${{secrets.ABC_XYZ_ABC_TOKEN}}'

but still I'm getting the same error.

Hemant kumar
  • 11
  • 3
  • You are using SQLExpress which has two types of credentials 1) Windows 2) SQL. Most servers use Windows Credentials where connection string contains Integrated Security = TRUE instead of a username and password. Best way of checking is to use SQL Server Management Studio and check login window which I suspect says Windows Credentials. Than perform a query on database to make sure your account can query the database. You can check log file in SSMS in explorer under Management which will give more info on error when you attempted to connect from powershell. – jdweng Nov 14 '22 at 05:31
  • Wow, this would be sooo much easier if you install git (https://git-scm.com/downloads) then all you need is to clone the repo locally using CMD or PowerShell console (git clone https://github.com/username/reponame.git) No secret needed because the authentication is cached by the app. – PollusB Dec 07 '22 at 14:31

0 Answers0