Joel Greijer

  $ErrorActionPreference = "stop" $server = "your-server.database.windows.net" $database = "your-database" $query = "select 1 from dbo.yourtable" Connect-AzAccount $token = ( Get-AzAccessToken -ResourceUrl "https://database.windows.net" ).Token Invoke-Sqlcmd -server $server -Database $database -Query $query -as dataset -AccessToken $token write-output "Done!"

This is how to grant user from system assigned identity (e.g. automation account) to allow to connect and query azure sql database without any use of keys, secrets, vaults or hard coded passwords. The app does not have to be contributor in azure sql instance for this to work Enable System Assigned Identity Navigate to Automation Account / Identity Set System Assigned: On Copy Object (Principal) Id guid Navigate to Azure Active Directory Search for the guid in "Search your tenant" box Find the "enterprise application" popping up Copy the Name this is what you use below [App_Name] Grant access to Azure SQL Database CREATE USER [App_Name] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo ALTER ROLE db_datareader ADD MEMBER [App_Name] ALTER ROLE db_datawriter ADD MEMBER [App_Name] How to use from runbook (powershell) $server  =  "your-sql-server-name.database.windows.net" $database  =  "your-database-name" $query  =  "sel...

Due to some nasty bugs in invoke-sqlcmd wrapper in powershell we must use the following workaround to be able to pass multiple parameters/variables to sqlcmd.exe from powershell $parameterizedCmd = 'sqlcmd -b -E -I -S . -d master -i "script.sql"' + ' -v Parameter1="' +$PsParameter1+ '"' + ' -v Parameter2="' +$PsParameter2+ '"' write-verbose $parameterizedCmd # invoke-expression and invoke-sqlcmd are insanely bugged # workaround by executing via cmd /c instead & cmd /c $parameterizedCmd

Setting up SSRS security is a two step process. Site security, then Folder security. This can be handled using the following scripts. Sources from  https://randypaulo.wordpress.com/2012/02/22/powershell-set-user-permission-in-ssrs-item-sql-reporting-server-using-powershell/ https://www.powershellgallery.com/packages/ReportingServicesTools/0.0.0.2/Content/Functions%5CSecurity%5CGrant-AccessToRS.ps1 Add-SsrsItemSecurity.ps1 <# .SYNOPSIS Set user permissions in SQL Reporting Services using Web Service .DESCRIPTION Set user permissions in SQL Reporting Services using Web Service .EXAMPLE Add-SsrsItemSecurity -url "http://[ServerName]/ReportServer/ReportService2010.asmx?WSDL" -itemPath "MyReportFolder" -u domain\user -r "Content Manager" -inherit $true #> param ( [ Parameter ( Mandatory = $true )][ Alias ( "url" )] [string] $webServiceUrl , [ Parameter ( Mandatory = $true )][ Alias ( "path" )] [stri...

When importing sqlps module implicitly you sometimes get the prompt changed to sqlserver:> in your powershell session. This can be hacked by explicitly loading sqlps in your script. Since this hogs your verbosity log you have the option to disable it with the output stream redirection hack used below. This script uses a defined path which is neccessary if running on teamteamcity where sqlps is not installed. # begin hack because in some cases invoke-sql is bugged, leaving prompt hanging in sqlserver:> try { write-verbose "Importing sqlps" $psmodulepath = join-path $PsScriptRoot MsBuild\Powershell\Modules\SQLPS Push-Location Import-Module -name $psmodulepath -DisableNameChecking 4>$null; #use line below to load where sqlps exists on executing machine: #Import-Module sqlps -DisableNameChecking 4>$null; Pop-Location #reset invalid prompt location } catch { write-error ( "There was an error importing sqlps: {0}" -f $_ .Except...

There are some hoops you need to jump through to get a multi-statement transaction committed via powershell. Default time-out is set to only one minute, so using the code below you can increase it. $timeout = New-Object System.TimeSpan -ArgumentList @ (5,0,0) # five hours $options = [System.Transactions.TransactionScopeOption] :: Required $scope = New-Object -TypeName System.Transactions.TransactionScope -ArgumentList @ ( $options , $timeout ) try { #Multiple queries, loops or whatever fun here, inside transaction Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName1 ; Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName2 ; Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName3 ; $scope .Complete() #we tell transactionscope to commit when using e...

Consider the following script: array as parameter.ps1 #requires -version 2.0 [CmdletBinding()] param ( [parameter(Mandatory=$false)] [string[]] $data ) write-host "items in data array:" $data.count write-host "first item in data array:" $data[0] When calling from command line cmd.exe using the following command powershell -file ".\array as parameter.ps1" -data "data1","data2" outputs items in data array: 1 first item in data array: data1,data2 But when calling using the folloing command powershell -command "&{.\'array as parameter.ps1' -data 'data1','data2'}" outputs items in data array: 2 first item in data array: data1

This script will remove items after being zipped. Filename is appended .zip Create two files from code templates below then edit and execute zipfiles.cmd zipfiles.ps1 [CmdletBinding()] Param( [Parameter(Mandatory=$True,Position=1)] [string]$sourceFolderPath, [Parameter(Mandatory=$True,Position=2)] [string]$filePattern, [Parameter(Mandatory=$True,Position=3)] [string]$7zipExe ) $sourceFilePattern= $sourceFolderPath +"\" + $filepattern $files=get-childItem $sourceFilePattern $ErrorActionPreference = "Stop" if ($files) { foreach ($file in $files) { $zipFileName=$file.fullname+".zip" $programArgs = "a", "-tzip", $zipFileName , $file.fullname write-host $programArgs Invoke-Command -ScriptBlock { & $7zipExe $programArgs } remove-item $file } } zipfiles.cmd powershell -file .\zipfiles.ps1 "c:\FolderWithFiles\ManyTextFiles" "*.txt" "c:\progr...

When accessing tables property of database in SMO, tables are indexed using name (excluding schema) This is problematic when using different schemas. To access a table using unique name use the following code $instance = $args[0]  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null $srv = new-Object Microsoft.SqlServer.Management.Smo.Server($instance) $db=$srv.Databases["ExampleDatabase"] $table = $db.Tables["ExampleTable"] # will get dbo schema, or random?   $table = $db.Tables | Where-Object {$_.Schema -eq "dbo" -and $_.Name -eq "ExampleTable"} #will get your unique table