I want to emphasize that this blog post should ONLY be used with code that you are totally confident in, ideally your own code.
This is something I have been wanting to achieve for some time now. Over the last couple of months, I have been working a on project with a colleague where I need to combine PowerShell and SQL with each other. The real trick was not to combine the two languages with each other. – You can read about how to do it: https://www.christianfrohn.dk/2022/04/17/using-azure-service-principal-to-run-powershell-script-on-azure-sql-server-managed-instance/
The trick is my colleague needed to commit the code into a private GitHub repository and then I would have to copy that code into the PowerShell script that needed to run that SQL query in conjunction with the PowerShell code.
For the sake of the story, my colleague doesn’t have the permission to change my PowerShell code, so I’m the one that has to copy the SQL query into my own code – This can become tiresome, and it would add an inconvenience for me every time my colleague was asked to modify the SQL query, which was often requested by me. But with this trick I found a way to import the SQL query from a private GitHub repository into my PowerShell script.
The trick
What I wanted to achieve was to take a SQL query from a file in a GitHub repository and import it into a PowerShell script and then execute that SQL query on a SQL server.
In order to do this we first need to utilize the GitHub API – https://docs.github.com/en/rest?apiVersion=2022-11-28 – But first we need to create a Personal access token. You can click on this link: https://github.com/settings/tokens?type=beta this is our “password” into the GitHub repository.
Press Generate new token and then provide a meaning full name for the access token (and description).
Next step is to select the repository (We do not want this key to have access to all repositories.)
The permission level that we need to assign to our access token is the following:
Contents: Read-Only
Metadata: Read-Only
Press Generate token – You will be redirected to a new page where you can copy the Token. Make sure to save it in a safe place where you can read it again. You will not be able to get it from GitHub again.
Now that we have our access token for GitHub, we can get started with the PowerShell part.
This is the full script you need to run for this to work – I will explain below what the script does.
Link to full script: https://github.com/ChrFrohn/PowerShell/blob/master/ImportSQLqueryFromGitHubIntoPowerShell.ps1
First of in this PowerShell script you need to define your GitHub username (This can also be the name of an organization) and then you paste in your GitHub token from earlier.
Then you add the name of the GitHub repository and the file path and filename – You can copy that in the top of the file window in GitHub.
With those four things combined, the script will then use the GitHub API and then fill in username, repo, and file path for the API URL, and then next generate a header to be use for authentication in GitHub.
The content of the file will then be loaded into the response variable, but since the response is in base64 format. It then converts the content from JSON format to a PowerShell object, then decodes the base64 content to a string using the System.Text.Encoding.
Finally, the content of the file is readable in the $DecodeContent variable and that variable can now be used in an SQL query like this:
Notes:
If the file in the repo is Public, then you do not need to use an access token.
If want to use this within an organization, then you need to enable the token for SSO.
It’s also possible to use this with JSON & XML files in theory.