Azure PowerShell to automate security setup for Azure API Management REST API

Looks like I haven't blogged here in quite a while! 

Hello there! 

Now I am back with the new stuff - I have developed an intense interest in Azure. 

So I will be adding a few blog posts related to my happy moments with Azure. 

In this post I am describing how to setup security required for the use of Azure Management REST API. I want to be able to start my Data Factory from my on-premises orchestration tool, and I am using Azure API Management REST APIs to do that. 

First of all, I need to set the security with the Security Principal in Azure to be able to login to Azure REST API with client secret. 

 See below the script that 

1.  Installs AzureAD module and connects to the tenant 

   
   Install-Module AzureAD
   

   
   Connect-AzureAD -TenantId "00000-0000-0000-000-0000"
   

2. Registers application in Azure AD 

   
   $subscriptionId = "00000-0000-0000-000-0000"
   $resourceGroup = "RG"
   $factory = "DF"
   $keyVaultName = "KV"
   
   $appName = "appreg"
   
   $appReg = New-AzureADApplication -DisplayName $appName -AvailableToOtherTenants $false
   
   $appReg
   

3. Creates a Service Principal for this application 

   
   New-AzADServicePrincipal -ApplicationId $appReg.AppId -Scope "/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.DataFactory/factories/$factory"
   

4. Creates a new client secret 

   
   $startDate = Get-Date
   $endDate = $startDate.AddYears(100)
   $aadAppsecret = New-AzureADApplicationPasswordCredential -ObjectId $appReg.ObjectId -CustomKeyIdentifier "SecretFactoryContributor" -StartDate $startDate -EndDate $endDate
   

5. Saves this new Client Secret and Client Id in the Key Vault for the use in Azure Management REST APIs

   
   $secret_value = $aadAppsecret.Value
   
   $Secret = ConvertTo-SecureString -String $secret_value -AsPlainText -Force
   Set-AzKeyVaultSecret -VaultName $keyVaultName -Name 'FactoryContributorClientSecret' -ContentType "Client Secret used to get token for Application $appName " -SecretValue $Secret
   
   $secret_value = $appReg.AppId
   
   $Secret = ConvertTo-SecureString -String $secret_value -AsPlainText -Force
   Set-AzKeyVaultSecret -VaultName $keyVaultName -Name 'FactoryContributorClientId' -ContentType "Client Id used to get token for Application $appName " -SecretValue $Secret
   
   

Having this script ready will save me some time when setting up new Azure environments and ensure that I have same settings everywhere. I am not a fan of manual setups!

SSRS Reporting Services - Site Settings security with PowerShell

Handy little PowerShell to automate adding a User or a Group to Site Settings of SSRS 2008 R2:

$rs = New-WebServiceProxy -uri "http://server/reportserver/ReportService2010.asmx?WSDL" -UseDefaultCredential -namespace "ReportingService2010" -class Reporting

$existingpolicies = $rs.GetSystemPolicies()
$Policy = New-Object -TypeName "ReportingService2010.Policy"
$Roles = @()
$Role = New-Object -TypeName "ReportingService2010.Role"
$Role.Name = "System User"
$Roles += $Role
$Policy.Roles = $Roles
$Policy.GroupUserName = "DOMAIN\USERORGROUP"

$PolicyAlreadyExists = $false

$existingpolicies | Foreach-Object{
if ($_.GroupUserName -eq "DOMAIN\USERORGROUP")
{$PolicyAlreadyExists = $true} }

$Policies = @($existingpolicies)


$Policies += $Policy


if(-not $PolicyAlreadyExists)
{
$rs.SetSystemPolicies($Policies)
}



based on https://social.technet.microsoft.com/Forums/sqlserver/en-US/ea8824fb-e1c4-4266-9e73-ef00a6c0a6cd/site-settings-security-using-rss-scripts?forum=sqlreportingservices

Capturing output from external commands in PowerShell

Great article on running external commands from PowerShell:

http://edgylogic.com/blog/powershell-and-external-commands-done-right/

Especially useful - information on capturing output from external commands in PowerShell:

for example:

& schtasks /END /TN $TaskName | Tee-Object -Variable scriptOutput | Out-Null Write-Host $scriptOutput

Powershell: Copy files from Network location

Copy-Item 

 -Path "Microsoft.PowerShell.Core\FileSystem::\\machine1\c$\log1.zip" 

 -Destination "c:\Logs\"

Capturing print output from SQL in PowerShell

Capturing verbose output through PowerShell object

PowerShell: Importing CSV into SQL Server

http://www.sqlteam.com/article/fast-csv-import-in-powershell-to-sql-server

I was working on the CSV import script in PowerShell, following the first example in the article above: Simple CSV Import using PowerShell.

I had to use Out-DataTable function  from here:
http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd#content

Simple CSV Import using PowerShell

Simple CSV Import using PowerShell

Simple CSV Import using PowerShell

to get DataTable from CSV file. This part worked very well.

But then I started getting an issue at the bulk insert - "The given value of type String from the data source cannot be converted to type int of the specified target column."

After some time of looking for the culprit data row, I realized that this error happens when we try to insert an empty value in the int column.

Thus, I had to change the Out-DataTable function in the following way:

 replace

     $DR.Item($property.Name) = $property.value

by

if($property.value -eq "")
{
        $DR.Item($property.Name) = $null
}
else
{
         $DR.Item($property.Name) = $property.value
}

now, Bulk Insert is satisfied and inserts NULLs when the data is not available in the CSV

Simple CSV Import using PowerShell

PowerShell: searching in the first 2 levels of the directory

Let's say you need to find a certain folder in the Directory, but you don't want to go deeper than 2 or 3 levels in your search?

Obviously, we will have to use get-childitem

However, -recurse option cannot help, since it has no stopping limit - it will recurse to the bottom of the directory

Here is a solution that I found that's short and simple, just the way I like it:



$search1 = "C:\temp\*"
$search2 = "C:\temp\*\*"
$search3 = "C:\temp\*\*\*"

$folders =  Get-ChildItem  -path $search1, $search2, $search3 | where-object {$_.PSIscontainer -and $_.name.StartsWith("Blue")}

...


Converting CSV files into XML files using PowerShell

At work I have to  resort sometimes to PowerShell to provide a quick solution to a problem.

For example, I had to do some CSV conversions and manipulations, which seemed to be easier done in PowerShell rather than in SQL Server.

1. I will document here a CSV to XML file conversion, which looked to me short and sweet:

Idea inspired from http://blogs.msdn.com/b/powershell/archive/2007/05/29/using-powershell-to-generate-xml-documents.aspx

#create xml file - export_xml 
$file = "C:\tmp\file.csv"
$fileoutxml = "C:\tmp\file.xml"
$xml=Import-Csv $file -Delimiter ";" -Header "c1","c2","c3" |
 foreach { " <row c1=`"{0}`" c2=`"{1}`" c3=`"{2}`"/>`n" -f
 $_.c1,$_.c2,$_.c3 } 
$xml="<export key_field=`"c1`">`n " + $xml + "</export>" | Out-File $fileoutxml -Encoding UTF8 

2. and a replace with Regular Expressions:
here, I am first replacing ; by | and trimming up the data at the same time
and then changing the swapping the order of the two last columns. it is important that $2$1 is surrounded by single quotes - script stops working if we use double quotes for some reason.

(Get-Content $file ) |
Foreach-Object {$_ -replace "(\s*;\s*)", -replace "(?<=^[^|]*\|[^|]*)(\|[^|]*)(\|[^|]*$)", '$2$1'; } |
Set-Content $fileout -Encoding Unicode