commands.ps1
|
<# .SYNOPSIS Assign D365 Security configuration .DESCRIPTION Assign the same security configuration as the ADMIN user in the D365FO database .PARAMETER sqlCommand The SQL Command object that should be used when assigning the permissions .PARAMETER Id Id of the user inside the D365FO database .EXAMPLE PS C:\> $SqlParams = @{ DatabaseServer = "localhost" DatabaseName = "AXDB" SqlUser = "sqladmin" SqlPwd = "Pass@word1" TrustedConnection = $false } PS C:\> $SqlCommand = Get-SqlCommand @SqlParams PS C:\> Add-AadUserSecurity -SqlCommand $SqlCommand -Id "TestUser" This will create a new Sql Command object using the Get-SqlCommand cmdlet and the $SqlParams hashtable containing all the needed parameters. With the $SqlCommand in place it calls the Add-AadUserSecurity cmdlet and instructs it to update the "TestUser" to have the same security configuration as the ADMIN user. .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function Add-AadUserSecurity { [OutputType('System.Boolean')] param ( [Parameter(Mandatory = $true)] [System.Data.SqlClient.SqlCommand] $SqlCommand, [Parameter(Mandatory = $true)] [string] $Id ) $commandText = (Get-Content "$script:ModuleRoot\internal\sql\Set-AadUserSecurityInD365FO.sql") -join [Environment]::NewLine $sqlCommand.CommandText = $commandText $null = $sqlCommand.Parameters.Add("@Id", $Id) Write-PSFMessage -Level Verbose -Message "Setting security roles in D365FO database" Write-PSFMessage -Level InternalComment -Message "Executing a script against the database." -Target (Get-SqlString $SqlCommand) $differenceBetweenNewUserAndAdmin = $sqlCommand.ExecuteScalar() Write-PSFMessage -Level Verbose -Message "Difference between new user and admin security roles $differenceBetweenNewUserAndAdmin" -Target $differenceBetweenNewUserAndAdmin $SqlCommand.Parameters.Clear() $differenceBetweenNewUserAndAdmin -eq 0 } <# .SYNOPSIS Backup a file .DESCRIPTION Backup a file in the same directory as the original file with a suffix .PARAMETER File Path to the file that you want to backup .PARAMETER Suffix The suffix value that you want to append to the file name when backing it up .EXAMPLE PS C:\> Backup-File -File c:\temp\d365fo.tools\test.txt -Suffix "Original" This will backup the "test.txt" file as "test_Original.txt" inside "c:\temp\d365fo.tools\" .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function Backup-File { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [string] $File, [Parameter(Mandatory = $true)] [string] $Suffix ) $FileBackup = Get-BackupName $File $Suffix Write-PSFMessage -Level Verbose -Message "Backing up $File to $FileBackup" -Target (@($File, $FileBackup)) (Get-Content -Path $File) | Set-Content -path $FileBackup } <# .SYNOPSIS Complete the upload action in LCS .DESCRIPTION Signal to LCS that the upload of the blob has completed .PARAMETER Token The token to be used for the http request against the LCS API .PARAMETER ProjectId The project id for the Dynamics 365 for Finance & Operations project inside LCS .PARAMETER AssetId The unique id of the asset / file that you are trying to upload to LCS .PARAMETER LcsApiUri URI / URL to the LCS API you want to use Depending on whether your LCS project is located in europe or not, there is 2 valid URI's / URL's Valid options: "https://lcsapi.lcs.dynamics.com" "https://lcsapi.eu.lcs.dynamics.com" .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Complete-LcsUpload -Token "Bearer JldjfafLJdfjlfsalfd..." -ProjectId 123456789 -AssetId "958ae597-f089-4811-abbd-c1190917eaae" -LcsApiUri "https://lcsapi.lcs.dynamics.com" This will commit the upload process for the AssetId "958ae597-f089-4811-abbd-c1190917eaae" in the LCS project with Id 123456789. The http request will be using the "Bearer JldjfafLJdfjlfsalfd..." token for authentication against the LCS API. The http request will be going to the LcsApiUri "https://lcsapi.lcs.dynamics.com" (NON-EUROPE). .NOTES Tags: Environment, Url, Config, Configuration, LCS, Upload, Api, AAD, Token Author: M�tz Jensen (@Splaxi) #> function Complete-LcsUpload { [CmdletBinding()] [OutputType()] param( [Parameter(Mandatory = $true)] [string]$Token, [Parameter(Mandatory = $true)] [int]$ProjectId, [Parameter(Mandatory = $true)] [string]$AssetId, [Parameter(Mandatory = $false)] [string]$LcsApiUri, [switch] $EnableException ) Invoke-TimeSignal -Start $client = New-Object -TypeName System.Net.Http.HttpClient $client.DefaultRequestHeaders.Clear() $client.DefaultRequestHeaders.UserAgent.ParseAdd("d365fo.tools via PowerShell") $commitFileUri = "$LcsApiUri/box/fileasset/CommitFileAsset/$($ProjectId)?assetId=$AssetId" $request = New-JsonRequest -Uri $commitFileUri -Token $Token Write-PSFMessage -Level Verbose -Message "Sending the commit request against LCS" -Target $request try { $commitResult = Get-AsyncResult -Task $client.SendAsync($request) Write-PSFMessage -Level Verbose -Message "Parsing the commitResult for success" -Target $commitResult if (($commitResult.StatusCode -ne [System.Net.HttpStatusCode]::NoContent) -and ($commitResult.StatusCode -ne [System.Net.HttpStatusCode]::OK)) { Write-PSFMessage -Level Host -Message "The LCS API returned an http error code" -Exception $PSItem.Exception -Target $commitResult Stop-PSFFunction -Message "Stopping because of errors" -StepsUpward 1 } } catch { Write-PSFMessage -Level Host -Message "Something went wrong while working against the LCS API" -Exception $PSItem.Exception Stop-PSFFunction -Message "Stopping because of errors" -StepsUpward 1 return } Invoke-TimeSignal -End $commitResult } <# .SYNOPSIS Convert HashTable into an array .DESCRIPTION Convert HashTable with switches inside into an array of Key:Value .PARAMETER InputObject The HashTable object that you want to work against Shold only contain Key / Vaule, where value is $true or $false .PARAMETER KeyPrefix The prefix that you want to append to the key of the HashTable The default value is "-" .PARAMETER ValuePrefix The prefix that you want to append to the value of the HashTable The default value is ":" .PARAMETER KeepCase Instruct the cmdlet to keep the naming case of the properties from the hashtable Default value is: $true .EXAMPLE PS C:\> $params = @{NoPrompt = $true; CreateParents = $false} PS C:\> $arguments = Convert-HashToArgStringSwitch -Inputs $params This will convert the $params into an array of strings, each with the "-Key:Value" pattern. .EXAMPLE PS C:\> $params = @{NoPrompt = $true; CreateParents = $false} PS C:\> $arguments = Convert-HashToArgStringSwitch -InputObject $params -KeyPrefix "&" -ValuePrefix "=" This will convert the $params into an array of strings, each with the "&Key=Value" pattern. .NOTES Tags: HashTable, Arguments Author: M�tz Jensen (@Splaxi) #> function Convert-HashToArgStringSwitch { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidDefaultValueSwitchParameter", "")] [CmdletBinding()] [OutputType([System.String])] param ( [HashTable] $InputObject, [string] $KeyPrefix = "-", [string] $ValuePrefix = ":", [switch] $KeepCase = $true ) foreach ($key in $InputObject.Keys) { $value = "{0}" -f $InputObject.Item($key).ToString() if (-not $KeepCase) {$value = $value.ToLower()} "$KeyPrefix$($key)$ValuePrefix$($value)" } } <# .SYNOPSIS Convert an object to boolean .DESCRIPTION Convert an object to boolean or default it to the specified boolean value .PARAMETER Object Input object that you want to work against .PARAMETER Default The default boolean value you want returned if the convert / cast fails .EXAMPLE PS C:\> ConvertTo-BooleanOrDefault -Object "1" -Default $true This will try and convert the "1" value to a boolean value. If the convert would fail, it would return the default value $true. .NOTES Author: M�tz Jensen (@Splaxi) #> function ConvertTo-BooleanOrDefault { [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSAvoidUsingEmptyCatchBlock', '')] [CmdletBinding()] [OutputType('System.Boolean')] param ( [Object] $Object, [Boolean] $Default ) [boolean] $result = $Default; $stringTrue = @("yes", "true", "ok", "y") $stringFalse = @( "no", "false", "n") try { if (-not ($null -eq $Object) ) { switch ($Object.ToString().ToLower()) { {$stringTrue -contains $_} { $result = $true break } {$stringFalse -contains $_} { $result = $false break } default { $result = [System.Boolean]::Parser($Object.ToString()) break } } } } catch { } $result } <# .SYNOPSIS Convert an object into a HashTable .DESCRIPTION Convert an object into a HashTable, can be used with json objects to create a HashTable .PARAMETER InputObject The object you want to convert .EXAMPLE PS C:\> $jsonString = '{"Test1": "Test1","Test2": "Test2"}' PS C:\> $jsonString | ConvertFrom-Json | ConvertTo-Hashtable .NOTES Author: M�tz Jensen (@Splaxi) Original Author: Adam Bertram (@techsnips_io) Original blog post with the function explained: https://4sysops.com/archives/convert-json-to-a-powershell-hash-table/ #> function ConvertTo-Hashtable { [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseOutputTypeCorrectly', '')] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseCmdletCorrectly', '')] [CmdletBinding()] param ( [Parameter(ValueFromPipeline)] $InputObject ) process { ## Return null if the input is null. This can happen when calling the function ## recursively and a property is null if ($null -eq $InputObject) { return $null } ## Check if the input is an array or collection. If so, we also need to convert ## those types into hash tables as well. This function will convert all child ## objects into hash tables (if applicable) if ($InputObject -is [System.Collections.IEnumerable] -and $InputObject -isnot [string]) { $collection = @( foreach ($object in $InputObject) { ConvertTo-Hashtable -InputObject $object } ) ## Return the array but don't enumerate it because the object may be pretty complex Write-Output -NoEnumerate $collection } elseif ($InputObject -is [psobject]) { ## If the object has properties that need enumeration ## Convert it to its own hash table and return it $hash = @{} foreach ($property in $InputObject.PSObject.Properties) { $hash[$property.Name] = ConvertTo-Hashtable -InputObject $property.Value } $hash } else { ## If the object isn't an array, collection, or other object, it's already a hash table ## So just return it. $InputObject } } } <# .SYNOPSIS Convert a Hashtable into a PSCustomObject .DESCRIPTION Convert a Hashtable into a PSCustomObject .PARAMETER InputObject The hashtable you want to convert .EXAMPLE PS C:\> $params = @{SqlUser = ""; SqlPwd = ""} PS C:\> $params | ConvertTo-PsCustomObject This will create a hashtable with 2 properties. It will convert the hashtable into a PSCustomObject .NOTES Author: M�tz Jensen (@Splaxi) Original blog post with the function explained: https://blogs.msdn.microsoft.com/timid/2013/03/05/converting-pscustomobject-tofrom-hashtables/ #> function ConvertTo-PsCustomObject { [OutputType('[PsCustomObject]')] param ( [Parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true)] [object[]] $InputObject ) begin { $i = 0 } process { foreach ($myHashtable in $InputObject) { if ($myHashtable.GetType().Name -eq 'hashtable') { $output = New-Object -TypeName PsObject Add-Member -InputObject $output -MemberType ScriptMethod -Name AddNote -Value { Add-Member -InputObject $this -MemberType NoteProperty -Name $args[0] -Value $args[1] } $myHashtable.Keys | Sort-Object | ForEach-Object { $output.AddNote($_, $myHashtable.$_) } $output } elseif ($myHashtable.GetType().Name -eq 'OrderedDictionary') { $output = New-Object -TypeName PsObject Add-Member -InputObject $output -MemberType ScriptMethod -Name AddNote -Value { Add-Member -InputObject $this -MemberType NoteProperty -Name $args[0] -Value $args[1] } $myHashtable.Keys | ForEach-Object { $output.AddNote($_, $myHashtable.$_) } $output } else { Write-PSFMessage -Level Warning -Message "Index `$i is not of type [hashtable]" -Target $i } $i += 1 } } } <# .SYNOPSIS Copy local file to Azure Blob Storage .DESCRIPTION Copy local file to Azure Blob Storage that is used by LCS .PARAMETER FilePath Path to the file you want to upload to the Azure Blob storage .PARAMETER FullUri The full URI, including SAS token and Policy Permissions to the blob .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Copy-FileToLcsBlob -FilePath "C:\temp\d365fo.tools\GOLDEN.bacpac" -FullUri "https://uswedpl1catalog.blob.core.windows.net/...." This will upload the "C:\temp\d365fo.tools\GOLDEN.bacpac" to the "https://uswedpl1catalog.blob.core.windows.net/...." Blob Storage location. It is required that the FullUri contains all the needed SAS tokens and Policy Permissions for the upload to succeed. .NOTES Tags: Azure Blob, LCS, Upload Author: M�tz Jensen (@Splaxi) #> function Copy-FileToLcsBlob { [CmdletBinding()] [OutputType()] param( [Parameter(Mandatory = $true)] [string]$FilePath, [Parameter(Mandatory = $true)] [System.Uri]$FullUri, [switch] $EnableException ) Invoke-TimeSignal -Start Write-PSFMessage -Level Verbose -Message "Initializing the needed .net objects to work against Azure Blob." -Target $FullUri $cloudblob = New-Object -TypeName Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob -ArgumentList @($FullUri) try { $uploadResult = Get-AsyncResult -Task $cloudblob.UploadFromFileAsync([System.String]$FilePath) } catch { Write-PSFMessage -Level Host -Message "Something went wrong while uploading the desired file to Azure Blob." -Exception $PSItem.Exception -Target $FullUri Stop-PSFFunction -Message "Stopping because of errors" return } Invoke-TimeSignal -End $uploadResult } <# .SYNOPSIS Load all necessary information about the D365 instance .DESCRIPTION Load all servicing dll files from the D365 instance into memory .EXAMPLE PS C:\> Get-ApplicationEnvironment This will load all the different dll files into memory. .NOTES Author: M�tz Jensen (@Splaxi) #> function Get-ApplicationEnvironment { [System.Collections.ArrayList] $Files2Process = New-Object -TypeName "System.Collections.ArrayList" $AOSPath = Join-Path $script:ServiceDrive "\AOSService\webroot\bin" Write-PSFMessage -Level Verbose -Message "Testing if we are running on a AOS server or not." if (-not (Test-Path -Path $AOSPath -PathType Container)) { Write-PSFMessage -Level Verbose -Message "The machine is NOT an AOS server." $MRPath = Join-Path $script:ServiceDrive "MRProcessService\MRInstallDirectory\Server\Services" Write-PSFMessage -Level Verbose -Message "Testing if we are running on a BI / MR server or not." if (-not (Test-Path -Path $MRPath -PathType Container)) { Write-PSFMessage -Level Verbose -Message "It seems that you ran this cmdlet on a machine that doesn't have the assemblies needed to obtain system details. Most likely you ran it on a <c='em'>personal workstation / personal computer</c>." return } else { Write-PSFMessage -Level Verbose -Message "The machine is a BI / MR server." $BasePath = $MRPath $null = $Files2Process.Add((Join-Path $script:ServiceDrive "Monitoring\Instrumentation\Microsoft.Dynamics.AX.Authentication.Instrumentation.dll")) } } else { Write-PSFMessage -Level Verbose -Message "The machine is an AOS server." $BasePath = $AOSPath $null = $Files2Process.Add((Join-Path $BasePath "Microsoft.Dynamics.AX.Authentication.Instrumentation.dll")) } Write-PSFMessage -Level Verbose -Message "Shadow cloning all relevant assemblies to the Microsoft.Dynamics.ApplicationPlatform.Environment.dll to avoid locking issues. This enables us to install updates while having d365fo.tools loaded" $null = $Files2Process.Add((Join-Path $BasePath "Microsoft.Dynamics.AX.Configuration.Base.dll")) $null = $Files2Process.Add((Join-Path $BasePath "Microsoft.Dynamics.BusinessPlatform.SharedTypes.dll")) $null = $Files2Process.Add((Join-Path $BasePath "Microsoft.Dynamics.AX.Framework.EncryptionEngine.dll")) $null = $Files2Process.Add((Join-Path $BasePath "Microsoft.Dynamics.AX.Security.Instrumentation.dll")) $null = $Files2Process.Add((Join-Path $BasePath "Microsoft.Dynamics.ApplicationPlatform.Environment.dll")) Import-AssemblyFileIntoMemory -Path $($Files2Process.ToArray()) if (Test-PSFFunctionInterrupt) { return } Write-PSFMessage -Level Verbose -Message "All assemblies loaded. Getting environment details." $environment = [Microsoft.Dynamics.ApplicationPlatform.Environment.EnvironmentFactory]::GetApplicationEnvironment() $environment } <# .SYNOPSIS Simple abstraction to handle asynchronous executions .DESCRIPTION Simple abstraction to handle asynchronous executions for several other cmdlets .PARAMETER Task The task you want to work / wait for to complete .EXAMPLE PS C:\> $client = New-Object -TypeName System.Net.Http.HttpClient PS C:\> Get-AsyncResult -Task $client.SendAsync($request) This will take the client (http) and have it send a request using the asynchronous pattern. .NOTES Tags: Async, Waiter, Wait Author: M�tz Jensen (@Splaxi) #> function Get-AsyncResult { [CmdletBinding()] [OutputType('Object')] param ( [Parameter(Mandatory = $true, Position = 1)] [object] $Task ) Write-PSFMessage -Level Verbose -Message "Building the Task Waiter and start waiting." -Target $Task $Task.GetAwaiter().GetResult() } <# .SYNOPSIS Get the Azure Service Objectives .DESCRIPTION Get the current tiering details from the Azure SQL Database instance .PARAMETER DatabaseServer The name of the database server If on-premises or classic SQL Server, use either short name og Fully Qualified Domain Name (FQDN). If Azure use the full address to the database server, e.g. server.database.windows.net .PARAMETER DatabaseName The name of the database .PARAMETER SqlUser The login name for the SQL Server instance .PARAMETER SqlPwd The password for the SQL Server user .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Get-AzureServiceObjective -DatabaseServer dbserver1.database.windows.net -DatabaseName AxDB -SqlUser User123 -SqlPwd "Password123" This will get the Azure service objective details from the Azure SQL Database instance located at "dbserver1.database.windows.net" .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function Get-AzureServiceObjective { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [string] $DatabaseServer, [Parameter(Mandatory = $true)] [string] $DatabaseName, [Parameter(Mandatory = $true)] [string] $SqlUser, [Parameter(Mandatory = $true)] [string] $SqlPwd, [switch] $EnableException ) $sqlCommand = Get-SqlCommand @PsBoundParameters -TrustedConnection $false $commandText = (Get-Content "$script:ModuleRoot\internal\sql\get-azureserviceobjective.sql") -join [Environment]::NewLine $sqlCommand.CommandText = $commandText try { Write-PSFMessage -Level InternalComment -Message "Executing a script against the database." -Target (Get-SqlString $SqlCommand) $sqlCommand.Connection.Open() $reader = $sqlCommand.ExecuteReader() if ($reader.Read() -eq $true) { Write-PSFMessage -Level Verbose "Extracting details from the result retrieved from the Azure DB instance" $edition = $reader.GetString(1) $serviceObjective = $reader.GetString(2) $reader.close() $sqlCommand.Connection.Close() $sqlCommand.Dispose() [PSCustomObject]@{ DatabaseEdition = $edition DatabaseServiceObjective = $serviceObjective } } else { $messageString = "The query to detect <c='em'>edition</c> and <c='em'>service objectives</c> from the Azure DB instance <c='em'>failed</c>." Write-PSFMessage -Level Host -Message $messageString -Target (Get-SqlString $SqlCommand) Stop-PSFFunction -Message "Stopping because of errors." -Exception $([System.Exception]::new($($messageString -replace '<[^>]+>',''))) return } } catch { $messageString = "Something went wrong while working against the database." Write-PSFMessage -Level Host -Message $messageString -Exception $PSItem.Exception -Target (Get-SqlString $SqlCommand) Stop-PSFFunction -Message "Stopping because of errors." -Exception $([System.Exception]::new($($messageString -replace '<[^>]+>', ''))) -ErrorRecord $_ return } } <# .SYNOPSIS Get a backup name for the file .DESCRIPTION Generate a backup name for the file parsed .PARAMETER File Path to the file that you want a backup name for .PARAMETER Suffix The name that you want to put into the new backup file name .EXAMPLE PS C:\> Get-BackupName -File "C:\temp\d365do.tools\Test.txt" -Suffix "Original" The function will return "C:\temp\d365do.tools\Test_Original.txt" .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function Get-BackupName { [CmdletBinding()] [OutputType([System.String])] param ( [Parameter(Mandatory = $true)] [string] $File, [Parameter(Mandatory = $true)] [string] $Suffix ) Write-PSFMessage -Level Verbose -Message "Getting backup name for file: $File" -Tag $File $FileInfo = [System.IO.FileInfo]::new($File) $BackupName = "{0}{1}_{2}{3}" -f $FileInfo.Directory, $FileInfo.BaseName, $Suffix, $FileInfo.Extension Write-PSFMessage -Level Verbose -Message "Backup name for the file will be $BackupName" -Tag $BackupName $BackupName } <# .SYNOPSIS Load the Canonical Identity Provider .DESCRIPTION Load the necessary dll files from the D365 instance to get the Canonical Identity Provider object .EXAMPLE PS C:\> Get-CanonicalIdentityProvider This will get the Canonical Identity Provider from the D365 instance .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function Get-CanonicalIdentityProvider { [CmdletBinding()] param () try { Write-PSFMessage -Level Verbose "Loading dll files to do some work against the CanonicalIdentityProvider." Add-Type -Path "$Script:AOSPath\bin\Microsoft.Dynamics.AX.Framework.EncryptionEngine.dll" Add-Type -Path "$Script:AOSPath\bin\Microsoft.Dynamics.AX.Security.AuthenticationCommon.dll" Write-PSFMessage -Level Verbose "Executing the CanonicalIdentityProvider lookup logic." $Identity = [Microsoft.Dynamics.AX.Security.AuthenticationCommon.AadHelper]::GetIdentityProvider() $Provider = [Microsoft.Dynamics.AX.Security.AuthenticationCommon.AadHelper]::GetCanonicalIdentityProvider($Identity) Write-PSFMessage -Level Verbose "CanonicalIdentityProvider is: $Provider" -Tag $Provider return $Provider } catch { Write-PSFMessage -Level Host -Message "Something went wrong while working against the CanonicalIdentityProvider." -Exception $PSItem.Exception Stop-PSFFunction -Message "Stopping because of errors" return } } <# .SYNOPSIS Parse the compiler output .DESCRIPTION Parse the output log files from the compiler and show the number of warnings and errors .PARAMETER Path The path to where the compiler output log file is located .EXAMPLE PS C:\> Get-CompilerResult -Path c:\temp\d365fo.tools\Dynamics.AX.Custom.xppc.log This will analaze the Dynamics.AX.Custom.xppc.log compiler output file. Will create a summarize object with number of errors and warnings. .NOTES Author: M�tz Jensen (@Splaxi) This cmdlet is inspired by the work of "Vilmos Kintera" (twitter: @DAXRunBase) All credits goes to him for showing how to extract these information His blog can be found here: https://www.daxrunbase.com/blog/ The specific blog post that we based this cmdlet on can be found here: https://www.daxrunbase.com/2020/03/31/interpreting-compiler-results-in-d365fo-using-powershell/ The github repository containing the original scrips can be found here: https://github.com/DAXRunBase/PowerShell-and-Azure #> function Get-CompilerResult { [CmdletBinding()] [OutputType('[PsCustomObject]')] param ( [parameter(Mandatory = $true)] [string] $Path ) Invoke-TimeSignal -Start if (-not (Test-PathExists -Path $Path -Type Leaf)) { return } $errorText = Select-String -LiteralPath $Path -Pattern ^Errors: | ForEach-Object { $_.Line } $errorCount = [int]$errorText.Split()[-1] $warningText = Select-String -LiteralPath $Path -Pattern ^Warnings: | ForEach-Object { $_.Line } $warningCount = [int]$warningText.Split()[-1] [PsCustomObject][Ordered]@{ File = "$Path" Warnings = $warningCount Errors = $errorCount PSTypeName = 'D365FO.TOOLS.CompilerOutput' } } <# .SYNOPSIS Clone a hashtable .DESCRIPTION Create a deep clone of a hashtable for you to work on it without updating the original object .PARAMETER InputObject The hashtable you want to clone .EXAMPLE PS C:\> Get-DeepClone -InputObject $HashTable This will clone the $HashTable variable into a new object and return it to you. .NOTES Author: M�tz Jensen (@Splaxi) #> function Get-DeepClone { [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseOutputTypeCorrectly', '')] [CmdletBinding()] param( [parameter(Mandatory = $true)] $InputObject ) process { if($InputObject -is [hashtable]) { $clone = @{} foreach($key in $InputObject.keys) { if($key -eq "EnableException") {continue} $clone[$key] = Get-DeepClone $InputObject[$key] } $clone } else { $InputObject } } } <# .SYNOPSIS Get the file version details .DESCRIPTION Get the file version details for any given file .PARAMETER Path Path to the file that you want to extract the file version details from .EXAMPLE PS C:\> Get-FileVersion -Path "C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\Bin\AxServ32.exe" This will get the file version details for the AX AOS executable (AxServ32.exe). .NOTES Author: M�tz Jensen (@Splaxi) Inspired by https://blogs.technet.microsoft.com/askpfeplat/2014/12/07/how-to-correctly-check-file-versions-with-powershell/ #> function Get-FileVersion { [CmdletBinding()] Param( [Parameter(Mandatory = $true)] [string] $Path ) if (-not (Test-PathExists -Path $Path -Type Leaf)) { return } Write-PSFMessage -Level Verbose -Message "Extracting the file properties for: $Path" -Target $Path $Filepath = Get-Item -Path $Path [PSCustomObject]@{ FileVersion = $Filepath.VersionInfo.FileVersion ProductVersion = $Filepath.VersionInfo.ProductVersion FileVersionUpdated = "$($Filepath.VersionInfo.FileMajorPart).$($Filepath.VersionInfo.FileMinorPart).$($Filepath.VersionInfo.FileBuildPart).$($Filepath.VersionInfo.FilePrivatePart)" ProductVersionUpdated = "$($Filepath.VersionInfo.ProductMajorPart).$($Filepath.VersionInfo.ProductMinorPart).$($Filepath.VersionInfo.ProductBuildPart).$($Filepath.VersionInfo.ProductPrivatePart)" } } <# .SYNOPSIS Get the identity provider .DESCRIPTION Execute a web request to get the identity provider for the given email address .PARAMETER Email Email address on the account that you want to get the Identity Provider details about .EXAMPLE PS C:\> Get-IdentityProvider -Email "Claire@contoso.com" This will get the Identity Provider details for the user account with the email address "Claire@contoso.com" .NOTES Author : Rasmus Andersen (@ITRasmus) Author : M�tz Jensen (@splaxi) #> function Get-IdentityProvider { [CmdletBinding()] param( [Parameter(Mandatory = $true, Position = 1)] [string]$Email ) $tenant = Get-TenantFromEmail $Email try { $webRequest = New-WebRequest "https://login.windows.net/$tenant/.well-known/openid-configuration" $null "GET" $response = $WebRequest.GetResponse() if ($response.StatusCode -eq [System.Net.HttpStatusCode]::Ok) { $stream = $response.GetResponseStream() $streamReader = New-Object System.IO.StreamReader($stream); $openIdConfig = $streamReader.ReadToEnd() $streamReader.Close(); } else { $statusDescription = $response.StatusDescription throw "Https status code : $statusDescription" } $openIdConfigJSON = ConvertFrom-Json $openIdConfig $openIdConfigJSON.issuer } catch { Write-PSFMessage -Level Host -Message "Something went wrong while executing the web request" -Exception $PSItem.Exception Stop-PSFFunction -Message "Stopping because of errors" return } } <# .SYNOPSIS Get the instance provider from the D365FO instance .DESCRIPTION Get the instance provider from the dll files used for encryption and authentication for D365FO .EXAMPLE PS C:\> Get-InstanceIdentityProvider This will return the Instance Identity Provider based on the D365FO instance. .NOTES Author : Rasmus Andersen (@ITRasmus) Author : M�tz Jensen (@splaxi) #> function Get-InstanceIdentityProvider { [CmdletBinding()] [OutputType([System.String])] param() $files = @("$Script:AOSPath\bin\Microsoft.Dynamics.AX.Framework.EncryptionEngine.dll", "$Script:AOSPath\bin\Microsoft.Dynamics.AX.Security.AuthenticationCommon.dll") if (-not (Test-PathExists -Path $files -Type Leaf)) { return } try { Add-Type -Path $files $Identity = [Microsoft.Dynamics.AX.Security.AuthenticationCommon.AadHelper]::GetIdentityProvider() Write-PSFMessage -Level Verbose -Message "The found instance identity provider is: $Identity" -Target $Identity $Identity } catch { Write-PSFMessage -Level Host -Message "Something went wrong while working against the Identity provider" -Exception $PSItem.Exception Stop-PSFFunction -Message "Stopping because of errors" return } } <# .SYNOPSIS Get the Azure Database instance values .DESCRIPTION Extract the PlanId, TenantId and PlanCapability from the Azure Database instance .PARAMETER DatabaseServer The name of the database server If on-premises or classic SQL Server, use either short name og Fully Qualified Domain Name (FQDN). If Azure use the full address to the database server, e.g. server.database.windows.net .PARAMETER DatabaseName The name of the database .PARAMETER SqlUser The login name for the SQL Server instance .PARAMETER SqlPwd The password for the SQL Server user .PARAMETER TrustedConnection Should the connection use a Trusted Connection or not .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Get-InstanceValues -DatabaseServer SQLServer -DatabaseName AXDB -SqlUser "SqlAdmin" -SqlPwd "Pass@word1" This will extract the PlanId, TenantId and PlanCapability from the AXDB on the SQLServer, using the "SqlAdmin" credentials to do so. .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function Get-InstanceValues { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns", "")] [CmdletBinding()] [OutputType('System.Collections.Hashtable')] param ( [Parameter(Mandatory = $true)] [string] $DatabaseServer, [Parameter(Mandatory = $true)] [string] $DatabaseName, [Parameter(Mandatory = $false)] [string] $SqlUser, [Parameter(Mandatory = $false)] [string] $SqlPwd, [Parameter(Mandatory = $false)] [boolean] $TrustedConnection, [switch] $EnableException ) $sqlCommand = Get-SqlCommand @PsBoundParameters $commandText = (Get-Content "$script:ModuleRoot\internal\sql\get-instancevalues.sql") -join [Environment]::NewLine $sqlCommand.CommandText = $commandText try { Write-PSFMessage -Level InternalComment -Message "Executing a script against the database." -Target (Get-SqlString $SqlCommand) $sqlCommand.Connection.Open() $reader = $sqlCommand.ExecuteReader() if ($reader.Read() -eq $true) { Write-PSFMessage -Level Verbose "Extracting details from the result retrieved from the DB instance" $tenantId = $reader.GetString(0) $planId = $reader.GetGuid(1) $planCapability = $reader.GetString(2) @{ TenantId = $tenantId PlanId = $planId PlanCapability = $planCapability } } else { $messageString = "The query to detect <c='em'>TenantId</c>, <c='em'>PlanId</c> and <c='em'>PlanCapability</c> from the database <c='em'>failed</c>." Write-PSFMessage -Level Host -Message $messageString -Target (Get-SqlString $SqlCommand) Stop-PSFFunction -Message "Stopping because of missing parameters." -Exception $([System.Exception]::new($($messageString -replace '<[^>]+>',''))) return } } catch { $messageString = "Something went wrong while working against the database." Write-PSFMessage -Level Host -Message $messageString -Exception $PSItem.Exception -Target (Get-SqlString $SqlCommand) Stop-PSFFunction -Message "Stopping because of errors." -Exception $([System.Exception]::new($($messageString -replace '<[^>]+>', ''))) -ErrorRecord $_ return } finally { $reader.close() $sqlCommand.Connection.Close() $sqlCommand.Dispose() } } <# .SYNOPSIS Get file from the Asset library inside the LCS project .DESCRIPTION Get the available files from the Asset Library in LCS project .PARAMETER ProjectId The project id for the Dynamics 365 for Finance & Operations project inside LCS .PARAMETER FileType Type of file you want to upload Valid options: "Model" "Process Data Package" "Software Deployable Package" "GER Configuration" "Data Package" "PowerBI Report Model" "E-Commerce Package" "NuGet Package" "Retail Self-Service Package" "Commerce Cloud Scale Unit Extension" .PARAMETER BearerToken The token you want to use when working against the LCS api .PARAMETER LcsApiUri URI / URL to the LCS API you want to use Depending on whether your LCS project is located in europe or not, there is 2 valid URI's / URL's Valid options: "https://lcsapi.lcs.dynamics.com" "https://lcsapi.eu.lcs.dynamics.com" .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Get-LcsAssetFile -ProjectId 123456789 -FileType SoftwareDeployablePackage -BearerToken "JldjfafLJdfjlfsalfd..." -LcsApiUri "https://lcsapi.lcs.dynamics.com" This will get all software deployable packages from the Asset Library inside LCS. The LCS project is identified by the ProjectId 123456789, which can be obtained in the LCS portal. The FileType is Software Deployable Packages, with the FileType parameter. The request will authenticate with the BearerToken "JldjfafLJdfjlfsalfd...". The http request will be going to the LcsApiUri "https://lcsapi.lcs.dynamics.com" (NON-EUROPE). .NOTES Tags: Environment, LCS, Api, AAD, Token, Asset, File, Files Author: M�tz Jensen (@Splaxi) #> function Get-LcsAssetFile { [Cmdletbinding()] param( [Parameter(Mandatory = $true)] [int] $ProjectId, [LcsAssetFileType] $FileType, [Alias('Token')] [string] $BearerToken, [Parameter(Mandatory = $true)] [string] $LcsApiUri, [switch] $EnableException ) Invoke-TimeSignal -Start Write-PSFMessage -Level Verbose -Message "Json payload for LCS generated." -Target $jsonFile $client = New-Object -TypeName System.Net.Http.HttpClient $client.DefaultRequestHeaders.Clear() $client.DefaultRequestHeaders.UserAgent.ParseAdd("d365fo.tools via PowerShell") $fileTypeValue = [int]$FileType $lcsRequestUri = "$LcsApiUri/box/fileasset/GetAssets/$($ProjectId)?fileType=$($fileTypeValue)" $request = New-JsonRequest -Uri $lcsRequestUri -Token $BearerToken -HttpMethod "GET" try { Write-PSFMessage -Level Verbose -Message "Invoke LCS request." $result = Get-AsyncResult -task $client.SendAsync($request) Write-PSFMessage -Level Verbose -Message "Extracting the response received from LCS." $responseString = Get-AsyncResult -task $result.Content.ReadAsStringAsync() try { $lcsResponseObject = ConvertFrom-Json -InputObject $responseString -ErrorAction SilentlyContinue } catch { Write-PSFMessage -Level Critical -Message "$responseString" } Write-PSFMessage -Level Verbose -Message "Extracting the response received from LCS." -Target $lcsResponseObject if (-not ($result.StatusCode -eq [System.Net.HttpStatusCode]::OK)) { if (($lcsResponseObject) -and ($lcsResponseObject.Message)) { $errorText = "Error $( $lcsResponseObject.Message) in request for listing all files from the asset library of LCS: '$( $lcsResponseObject.Message)'" } else { $errorText = "API Call returned $($result.StatusCode): $($result.ReasonPhrase)" } Write-PSFMessage -Level Host -Message "Error listing bacpacs and backups from asset library." -Target $($lcsResponseObject.Message) Write-PSFMessage -Level Host -Message $errorText -Target $($result.ReasonPhrase) Stop-PSFFunction -Message "Stopping because of errors" -StepsUpward 1 } } catch { Write-PSFMessage -Level Host -Message "Something went wrong while working against the LCS API." -Exception $PSItem.Exception Stop-PSFFunction -Message "Stopping because of errors" -StepsUpward 1 return } Invoke-TimeSignal -End $lcsResponseObject } <# .SYNOPSIS Get the validation status from LCS .DESCRIPTION Get the validation status for a given file in the Asset Library in LCS .PARAMETER ProjectId The project id for the Dynamics 365 for Finance & Operations project inside LCS .PARAMETER BearerToken The token you want to use when working against the LCS api .PARAMETER AssetId The unique id of the asset / file that you are trying to deploy from LCS .PARAMETER LcsApiUri URI / URL to the LCS API you want to use Depending on whether your LCS project is located in europe or not, there is 2 valid URI's / URL's Valid options: "https://lcsapi.lcs.dynamics.com" "https://lcsapi.eu.lcs.dynamics.com" .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Get-LcsAssetValidationStatus -ProjectId 123456789 -BearerToken "JldjfafLJdfjlfsalfd..." -AssetId "958ae597-f089-4811-abbd-c1190917eaae" -LcsApiUri "https://lcsapi.lcs.dynamics.com" This will check the file with the AssetId "958ae597-f089-4811-abbd-c1190917eaae" in validated or not. It will test against the Asset Library located under the LCS project 123456789. The BearerToken "JldjfafLJdfjlfsalfd..." is used to authenticate against the LCS API endpoint. The file will be named "ReadyForTesting" inside the Asset Library in LCS. The file is validated against the NON-EUROPE LCS API. .NOTES Author: M�tz Jensen (@Splaxi) #> function Get-LcsAssetValidationStatus { [CmdletBinding()] [OutputType()] param ( [Parameter(Mandatory = $true, Position = 1)] [int] $ProjectId, [Parameter(Mandatory = $true, Position = 2)] [Alias('Token')] [string] $BearerToken, [Parameter(Mandatory = $true, Position = 3)] [string] $AssetId, [Parameter(Mandatory = $true, Position = 4)] [string] $LcsApiUri, [switch] $EnableException ) Invoke-TimeSignal -Start $client = New-Object -TypeName System.Net.Http.HttpClient $client.DefaultRequestHeaders.Clear() $client.DefaultRequestHeaders.UserAgent.ParseAdd("d365fo.tools via PowerShell") $checkUri = "$LcsApiUri/box/fileasset/GetFileAssetValidationStatus/$($ProjectId)?assetId=$AssetId" $request = New-JsonRequest -Uri $checkUri -Token $BearerToken -HttpMethod "GET" try { Write-PSFMessage -Level Verbose -Message "Invoke LCS request." -Target $request $result = Get-AsyncResult -task $client.SendAsync($request) Write-PSFMessage -Level Verbose -Message "Extracting the response received from LCS." $responseString = Get-AsyncResult -task $result.Content.ReadAsStringAsync() Write-PSFMessage -Level Verbose -Message "Extracting the response received from LCS." -Target $responseString try { $asset = ConvertFrom-Json -InputObject $responseString -ErrorAction SilentlyContinue } catch { Write-PSFMessage -Level Critical -Message "$responseString" } Write-PSFMessage -Level Verbose -Message "Extracting the asset json response received from LCS." -Target $asset if (-not ($result.StatusCode -eq [System.Net.HttpStatusCode]::OK)) { if (($asset) -and ($asset.Message)) { Write-PSFMessage -Level Host -Message "Error getting the validation status of the file asset." -Target $($asset.Message) Stop-PSFFunction -Message "Stopping because of errors" } else { Write-PSFMessage -Level Host -Message "API Call returned $($result.StatusCode)." -Target $($result.ReasonPhrase) Stop-PSFFunction -Message "Stopping because of errors" } } if (-not ($asset.Id)) { if ($asset.Message) { Write-PSFMessage -Level Host -Message "Error getting the validation status of the file asset." -Target $($asset.Message) Stop-PSFFunction -Message "Stopping because of errors" } else { Write-PSFMessage -Level Host -Message "Unknown error getting the validation status of the file asset." -Target $asset Stop-PSFFunction -Message "Stopping because of errors" } } } catch { Write-PSFMessage -Level Host -Message "Something went wrong while working against the LCS API." -Exception $PSItem.Exception Stop-PSFFunction -Message "Stopping because of errors" return } Invoke-TimeSignal -End $asset } <# .SYNOPSIS Get database backups from LCS project .DESCRIPTION Get the available database backups from the Asset Library in LCS project .PARAMETER ProjectId The project id for the Dynamics 365 for Finance & Operations project inside LCS .PARAMETER BearerToken The token you want to use when working against the LCS api .PARAMETER LcsApiUri URI / URL to the LCS API you want to use Depending on whether your LCS project is located in europe or not, there is 2 valid URI's / URL's Valid options: "https://lcsapi.lcs.dynamics.com" "https://lcsapi.eu.lcs.dynamics.com" .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Get-D365LcsDatabaseBackups -ProjectId 123456789 -BearerToken "JldjfafLJdfjlfsalfd..." -LcsApiUri "https://lcsapi.lcs.dynamics.com" This will get all available database backups from the Asset Library inside LCS. The LCS project is identified by the ProjectId 123456789, which can be obtained in the LCS portal. The request will authenticate with the BearerToken "JldjfafLJdfjlfsalfd...". The http request will be going to the LcsApiUri "https://lcsapi.lcs.dynamics.com" (NON-EUROPE). .NOTES Tags: Environment, LCS, Api, AAD, Token, Bacpac, Backup Author: M�tz Jensen (@Splaxi) #> function Get-LcsDatabaseBackups { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns", "")] [Cmdletbinding()] param( [Parameter(Mandatory = $true)] [int] $ProjectId, [Alias('Token')] [string] $BearerToken, [Parameter(Mandatory = $true)] [string] $LcsApiUri, [switch] $EnableException ) Invoke-TimeSignal -Start Write-PSFMessage -Level Verbose -Message "Json payload for LCS generated." -Target $jsonFile $client = New-Object -TypeName System.Net.Http.HttpClient $client.DefaultRequestHeaders.Clear() $client.DefaultRequestHeaders.UserAgent.ParseAdd("d365fo.tools via PowerShell") $deployStatusUri = "$LcsApiUri/databasemovement/v1/databases/project/$($ProjectId)" $request = New-JsonRequest -Uri $deployStatusUri -Token $BearerToken -HttpMethod "GET" try { Write-PSFMessage -Level Verbose -Message "Invoke LCS request." $result = Get-AsyncResult -task $client.SendAsync($request) Write-PSFMessage -Level Verbose -Message "Extracting the response received from LCS." $responseString = Get-AsyncResult -task $result.Content.ReadAsStringAsync() try { $databasesObject = ConvertFrom-Json -InputObject $responseString -ErrorAction SilentlyContinue } catch { Write-PSFMessage -Level Critical -Message "$responseString" } Write-PSFMessage -Level Verbose -Message "Extracting the response received from LCS." -Target $databasesObject if (-not ($result.StatusCode -eq [System.Net.HttpStatusCode]::OK)) { if (($databasesObject) -and ($databasesObject.ErrorMessage)) { $errorText = "" if ($databasesObject.OperationActivityId) { $errorText = "Error $( $databasesObject.ErrorMessage) in request for listing all bacpacs and backup from the asset library of LCS: '$( $databasesObject.ErrorMessage)' (Activity Id: '$( $databasesObject.OperationActivityId)')" } else { $errorText = "Error $( $databasesObject.ErrorMessage) in request for listing all bacpacs and backup from the asset library of LCS: '$( $databasesObject.ErrorMessage)'" } } elseif ($databasesObject.OperationActivityId) { $errorText = "API Call returned $($result.StatusCode): $($result.ReasonPhrase) (Activity Id: '$($databasesObject.OperationActivityId)')" } else { $errorText = "API Call returned $($result.StatusCode): $($result.ReasonPhrase)" } Write-PSFMessage -Level Host -Message "Error listing bacpacs and backups from asset library." -Target $($databasesObject.ErrorMessage) Write-PSFMessage -Level Host -Message $errorText -Target $($result.ReasonPhrase) Stop-PSFFunction -Message "Stopping because of errors" -StepsUpward 1 } if (-not ( $databasesObject.IsSuccess)) { if ( $databasesObject.ErrorMessage) { $errorText = "Error in request for listing all bacpacs and backup from the asset library of LCS: '$( $databasesObject.ErrorMessage)' (Activity Id: '$( $databasesObject.OperationActivityId)')" } elseif ( $databasesObject.OperationActivityId) { $errorText = "Error in request for listing all bacpacs and backup from the asset library of LCS. Activity Id: '$($activity.OperationActivityId)'" } else { $errorText = "Unknown error in request for listing all bacpacs and backup from the asset library of LCS" } Write-PSFMessage -Level Host -Message "Unknown error while listing all bacpacs and backups from asset library." -Target $databasesObject Write-PSFMessage -Level Host -Message $errorText -Target $($result.ReasonPhrase) Stop-PSFFunction -Message "Stopping because of errors" -StepsUpward 1 } } catch { Write-PSFMessage -Level Host -Message "Something went wrong while working against the LCS API." -Exception $PSItem.Exception Stop-PSFFunction -Message "Stopping because of errors" -StepsUpward 1 return } Invoke-TimeSignal -End $databasesObject } <# .SYNOPSIS Get the status of a LCS database operation .DESCRIPTION Get the database operation status for an environment in LCS .PARAMETER Token The token to be used for the http request against the LCS API .PARAMETER ProjectId The project id for the Dynamics 365 for Finance & Operations project inside LCS .PARAMETER BearerToken The token you want to use when working against the LCS api .PARAMETER OperationActivityId The unique id of the action you got from when starting the database operation against the environment .PARAMETER EnvironmentId The unique id of the environment that you want to work against The Id can be located inside the LCS portal .PARAMETER LcsApiUri URI / URL to the LCS API you want to use Depending on whether your LCS project is located in europe or not, there is 2 valid URI's / URL's Valid options: "https://lcsapi.lcs.dynamics.com" "https://lcsapi.eu.lcs.dynamics.com" .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Get-LcsDatabaseOperationStatus -ProjectId 123456789 -OperationActivityId 123456789 -EnvironmentId "13cc7700-c13b-4ea3-81cd-2d26fa72ec5e" -Token "JldjfafLJdfjlfsalfd..." -LcsApiUri "https://lcsapi.lcs.dynamics.com" This will check the database operation status of a specific OperationActivityId against an environment. The LCS project is identified by the ProjectId 123456789, which can be obtained in the LCS portal. The OperationActivityId is identified by the OperationActivityId 123456789, which is obtained from executing either the Invoke-D365LcsDatabaseExport or Invoke-D365LcsDatabaseRefresh cmdlets. The environment is identified by the EnvironmentId "13cc7700-c13b-4ea3-81cd-2d26fa72ec5e", which can be obtained in the LCS portal. The request will authenticate with the BearerToken "JldjfafLJdfjlfsalfd...". The http request will be going to the LcsApiUri "https://lcsapi.lcs.dynamics.com" (NON-EUROPE). .LINK Start-LcsDatabaseRefresh .NOTES Tags: Environment, Url, Config, Configuration, LCS, Upload, Api, AAD, Token, Deployment, Deployable Package Author: M�tz Jensen (@Splaxi) #> function Get-LcsDatabaseOperationStatus { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseShouldProcessForStateChangingFunctions", "")] [Cmdletbinding()] param( [Parameter(Mandatory = $true)] [int] $ProjectId, [Alias('Token')] [string] $BearerToken, [Parameter(Mandatory = $true)] [string] $OperationActivityId, [Parameter(Mandatory = $true)] [string] $EnvironmentId, [Parameter(Mandatory = $true)] [string] $LcsApiUri, [switch] $EnableException ) Invoke-TimeSignal -Start Write-PSFMessage -Level Verbose -Message "Json payload for LCS generated." -Target $jsonFile $client = New-Object -TypeName System.Net.Http.HttpClient $client.DefaultRequestHeaders.Clear() $client.DefaultRequestHeaders.UserAgent.ParseAdd("d365fo.tools via PowerShell") $databaseOperationStatusUri = "$LcsApiUri/databasemovement/v1/fetchstatus/project/$($ProjectId)/environment/$($EnvironmentId)/operationactivity/$($OperationActivityId)" $request = New-JsonRequest -Uri $databaseOperationStatusUri -Token $BearerToken -HttpMethod "GET" try { Write-PSFMessage -Level Verbose -Message "Invoke LCS request." $result = Get-AsyncResult -task $client.SendAsync($request) Write-PSFMessage -Level Verbose -Message "Extracting the response received from LCS." $responseString = Get-AsyncResult -task $result.Content.ReadAsStringAsync() try { $operationStatus = ConvertFrom-Json -InputObject $responseString -ErrorAction SilentlyContinue } catch { Write-PSFMessage -Level Critical -Message "$responseString" } Write-PSFMessage -Level Verbose -Message "Extracting the response received from LCS." -Target $operationStatus if (-not ($result.StatusCode -eq [System.Net.HttpStatusCode]::OK)) { if (($operationStatus) -and ($operationStatus.ErrorMessage)) { $errorText = "" if ($operationStatus.OperationActivityId) { $errorText = "Error in request for database refresh status of environment: '$( $operationStatus.ErrorMessage)' (Activity Id: '$( $operationStatus.OperationActivityId)')" } else { $errorText = "Error in request for database refresh status of environment: '$( $operationStatus.ErrorMessage)'" } } elseif ($operationStatus.OperationActivityId) { $errorText = "API Call returned $($result.StatusCode): $($result.ReasonPhrase) (Activity Id: '$($operationStatus.OperationActivityId)')" } else { $errorText = "API Call returned $($result.StatusCode): $($result.ReasonPhrase)" } Write-PSFMessage -Level Host -Message "Error getting database refresh status." -Target $($operationStatus.ErrorMessage) Write-PSFMessage -Level Host -Message $errorText -Target $($result.ReasonPhrase) Stop-PSFFunction -Message "Stopping because of errors" -StepsUpward 1 } if (-not ($operationStatus.IsSuccess)) { if ($operationStatus.ErrorMessage) { $errorText = "Error in request for database refresh status of environment: '$( $operationStatus.ErrorMessage)' (Activity Id: '$( $operationStatus.OperationActivityId)')" } elseif ( $operationStatus.OperationActivityId) { $errorText = "Error in request for database refresh status of environment. Activity Id: '$($activity.OperationActivityId)'" } else { $errorText = "Unknown error in request for database refresh status." } Write-PSFMessage -Level Host -Message "Unknown error requesting database refresh status." -Target $operationStatus Write-PSFMessage -Level Host -Message $errorText -Target $($result.ReasonPhrase) Stop-PSFFunction -Message "Stopping because of errors" -StepsUpward 1 } } catch { Write-PSFMessage -Level Host -Message "Something went wrong while working against the LCS API." -Exception $PSItem.Exception Stop-PSFFunction -Message "Stopping because of errors" -StepsUpward 1 return } Invoke-TimeSignal -End $operationStatus } <# .SYNOPSIS Get the status of a LCS deployment .DESCRIPTION Get the deployment status for an environment in LCS .PARAMETER Token The token to be used for the http request against the LCS API .PARAMETER ProjectId The project id for the Dynamics 365 for Finance & Operations project inside LCS .PARAMETER BearerToken The token you want to use when working against the LCS api .PARAMETER ActivityId The unique id of the action you got from when starting the deployment to the environment .PARAMETER EnvironmentId The unique id of the environment that you want to work against The Id can be located inside the LCS portal .PARAMETER LcsApiUri URI / URL to the LCS API you want to use Depending on whether your LCS project is located in europe or not, there is 2 valid URI's / URL's Valid options: "https://lcsapi.lcs.dynamics.com" "https://lcsapi.eu.lcs.dynamics.com" .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Get-LcslcsResponseObject -Token "Bearer JldjfafLJdfjlfsalfd..." -ProjectId 123456789 -ActivityId 123456789 -EnvironmentId "13cc7700-c13b-4ea3-81cd-2d26fa72ec5e" -LcsApiUri "https://lcsapi.lcs.dynamics.com" This will start the deployment of the file located in the Asset Library with the AssetId "958ae597-f089-4811-abbd-c1190917eaae" in the LCS project with Id 123456789. The http request will be using the "Bearer JldjfafLJdfjlfsalfd..." token for authentication against the LCS API. The http request will be going to the LcsApiUri "https://lcsapi.lcs.dynamics.com" (NON-EUROPE). .LINK Start-LcsDeployment .NOTES Tags: Environment, Url, Config, Configuration, LCS, Upload, Api, AAD, Token, Deployment, Deployable Package Author: M�tz Jensen (@Splaxi) #> function Get-LcsDeploymentStatus { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseShouldProcessForStateChangingFunctions", "")] [Cmdletbinding()] param( [Parameter(Mandatory = $true)] [int] $ProjectId, [Alias('Token')] [string] $BearerToken, [Parameter(Mandatory = $true)] [string] $ActivityId, [Parameter(Mandatory = $true)] [string] $EnvironmentId, [Parameter(Mandatory = $true)] [string] $LcsApiUri, [switch] $EnableException ) Invoke-TimeSignal -Start Write-PSFMessage -Level Verbose -Message "Json payload for LCS generated." -Target $jsonFile $client = New-Object -TypeName System.Net.Http.HttpClient $client.DefaultRequestHeaders.Clear() $client.DefaultRequestHeaders.UserAgent.ParseAdd("d365fo.tools via PowerShell") $lcsRequestUri = "$LcsApiUri/environment/v2/fetchstatus/project/$($ProjectId)/environment/$($EnvironmentId)/operationactivity/$($ActivityId)" $request = New-JsonRequest -Uri $lcsRequestUri -Token $BearerToken -HttpMethod "GET" try { Write-PSFMessage -Level Verbose -Message "Invoke LCS request." $result = Get-AsyncResult -task $client.SendAsync($request) Write-PSFMessage -Level Verbose -Message "Extracting the response received from LCS." $responseString = Get-AsyncResult -task $result.Content.ReadAsStringAsync() try { $lcsResponseObject = ConvertFrom-Json -InputObject $responseString -ErrorAction SilentlyContinue } catch { Write-PSFMessage -Level Critical -Message "$responseString" } Write-PSFMessage -Level Verbose -Message "Extracting the response received from LCS." -Target $lcsResponseObject #This IF block might be obsolute based on the V2 implementation if (-not ($result.StatusCode -eq [System.Net.HttpStatusCode]::OK)) { if ($lcsResponseObject) { $errorText = "" if ($lcsResponseObject.ActivityId) { $errorText = "Error $( $lcsResponseObject.ErrorMessage) in request for status of environment servicing action: '$($lcsResponseObject.ErrorMessage)' (Activity Id: '$($lcsResponseObject.ActivityId)')" } else { $errorText = "Error $( $lcsResponseObject.ErrorMessage) in request for status of environment servicing action: '$($lcsResponseObject.ErrorMessage)'" } } elseif ($lcsResponseObject.ActivityId) { $errorText = "API Call returned $($result.StatusCode): $($result.ReasonPhrase) (Activity Id: '$($lcsResponseObject.ActivityId)')" } else { $errorText = "API Call returned $($result.StatusCode): $($result.ReasonPhrase)" } Write-PSFMessage -Level Host -Message "Error fetching environment servicing status." -Target $($lcsResponseObject.Message) Write-PSFMessage -Level Host -Message $errorText -Target $($result.ReasonPhrase) Stop-PSFFunction -Message "Stopping because of errors" } if (-not ($lcsResponseObject.OperationStatus)) { if ($lcsResponseObject.Message) { $errorText = "Error in request for status of environment servicing action: '$($lcsResponseObject.Message)')" } elseif ($lcsResponseObject.ErrorMessage) { $errorText = "Error in request for status of environment servicing action: '$($lcsResponseObject.ErrorMessage)' (ActivityId: '$($lcsResponseObject.ActivityId)' - OperationActivityId: '$($lcsResponseObject.OperationActivityId)')" } elseif ($lcsResponseObject.OperationActivityId -or $lcsResponseObject.ActivityId) { $errorText = "Error in request for status of environment servicing action. (ActivityId: '$($lcsResponseObject.ActivityId)' - OperationActivityId: '$($lcsResponseObject.OperationActivityId)')" } else { $errorText = "Unknown error in request for status of environment servicing action" } Write-PSFMessage -Level Host -Message "Unknown error fetching environment servicing status." -Target $lcsResponseObject Write-PSFMessage -Level Host -Message $errorText -Target $($result.ReasonPhrase) Stop-PSFFunction -Message "Stopping because of errors" } } catch { Write-PSFMessage -Level Host -Message "Something went wrong while working against the LCS API." -Exception $PSItem.Exception Stop-PSFFunction -Message "Stopping because of errors" return } Invoke-TimeSignal -End $lcsResponseObject } <# .SYNOPSIS Get the login name from the e-mail address .DESCRIPTION Extract the login name from the e-mail address by substring everything before the @ character .PARAMETER Email The e-mail address that you want to get the login name from .EXAMPLE PS C:\> Get-LoginFromEmail -Email Claire@contoso.com This will substring the e-mail address and return "Claire" as the result .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function Get-LoginFromEmail { [CmdletBinding()] [OutputType('System.String')] param ( [string]$Email ) $email.Substring(0, $Email.LastIndexOf('@')).Trim() } <# .SYNOPSIS Get the network domain from the e-mail .DESCRIPTION Get the network domain provider (Azure) for the e-mail / user .PARAMETER Email The e-mail that you want to retrieve the provider for .EXAMPLE PS C:\> Get-NetworkDomain -Email "Claire@contoso.com" This will return the provider registered with the "Claire@contoso.com" e-mail address. .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function Get-NetworkDomain { [CmdletBinding()] [OutputType('System.String')] param( [Parameter(Mandatory = $true, Position = 1)] [string]$Email ) $tenant = Get-TenantFromEmail $Email $provider = Get-InstanceIdentityProvider $canonicalIdentityProvider = Get-CanonicalIdentityProvider if ($Provider.ToLower().Contains($Tenant.ToLower()) -eq $True) { $canonicalIdentityProvider } else { "$canonicalIdentityProvider$Tenant" } } <# .SYNOPSIS Get the product information .DESCRIPTION Get the product information object from the environment .EXAMPLE PS C:\> Get-ProductInfoProvider This will get the product information object and return it .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function Get-ProductInfoProvider { Add-Type -Path "$Script:AOSPath\bin\Microsoft.Dynamics.BusinessPlatform.ProductInformation.Provider.dll" [Microsoft.Dynamics.BusinessPlatform.ProductInformation.Provider.ProductInfoProvider]::get_Provider() } <# .SYNOPSIS Get the list of Dynamics 365 services .DESCRIPTION Get the list of Dynamics 365 service names based on the parameters .PARAMETER All Switch to instruct the cmdlet to output all service names .PARAMETER Aos Switch to instruct the cmdlet to output the aos service name .PARAMETER Batch Switch to instruct the cmdlet to output the batch service name .PARAMETER FinancialReporter Switch to instruct the cmdlet to output the financial reporter service name .PARAMETER DMF Switch to instruct the cmdlet to output the data management service name .EXAMPLE PS C:\> Get-ServiceList -All This will return all services for an D365 environment .NOTES Author: M�tz Jensen (@Splaxi) #> Function Get-ServiceList { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidDefaultValueSwitchParameter", "")] [CmdletBinding(DefaultParameterSetName = 'Default')] param ( [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 2 )] [switch] $All = $true, [Parameter(Mandatory = $false, ParameterSetName = 'Specific', Position = 2 )] [switch] $Aos, [Parameter(Mandatory = $false, ParameterSetName = 'Specific', Position = 3 )] [switch] $Batch, [Parameter(Mandatory = $false, ParameterSetName = 'Specific', Position = 4 )] [switch] $FinancialReporter, [Parameter(Mandatory = $false, ParameterSetName = 'Specific', Position = 5 )] [switch] $DMF ) if ($PSCmdlet.ParameterSetName -eq "Specific") { $All = $false } Write-PSFMessage -Level Verbose -Message "The PSBoundParameters was" -Target $PSBoundParameters $aosname = "w3svc" $batchname = "DynamicsAxBatch" $financialname = "MR2012ProcessService" $dmfname = "Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe" [System.Collections.ArrayList]$Services = New-Object -TypeName "System.Collections.ArrayList" if ($All) { $null = $Services.AddRange(@($aosname, $batchname, $financialname, $dmfname)) } else { if ($Aos) { $null = $Services.Add($aosname) } if ($Batch) { $null = $Services.Add($batchname) } if ($FinancialReporter) { $null = $Services.Add($financialname) } if ($DMF) { $null = $Services.Add($dmfname) } } $Services.ToArray() } <# .SYNOPSIS Get a SqlCommand object .DESCRIPTION Get a SqlCommand object initialized with the passed parameters .PARAMETER DatabaseServer The name of the database server If on-premises or classic SQL Server, use either short name og Fully Qualified Domain Name (FQDN). If Azure use the full address to the database server, e.g. server.database.windows.net .PARAMETER DatabaseName The name of the database .PARAMETER SqlUser The login name for the SQL Server instance .PARAMETER SqlPwd The password for the SQL Server user .PARAMETER TrustedConnection Should the connection use a Trusted Connection or not .EXAMPLE PS C:\> Get-SqlCommand -DatabaseServer localhost -DatabaseName AxDB -SqlUser User123 -SqlPwd "Password123" -TrustedConnection $true This will initialize a new SqlCommand object (.NET type) with localhost as the server name, AxDB as the database and the User123 sql credentials. .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function Get-SQLCommand { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [string] $DatabaseServer, [Parameter(Mandatory = $true)] [string] $DatabaseName, [Parameter(Mandatory = $false)] [string] $SqlUser, [Parameter(Mandatory = $false)] [string] $SqlPwd, [Parameter(Mandatory = $false)] [boolean] $TrustedConnection ) Write-PSFMessage -Level Debug -Message "Writing the bound parameters" -Target $PsBoundParameters [System.Collections.ArrayList]$Params = New-Object -TypeName "System.Collections.ArrayList" $null = $Params.Add("Server='$DatabaseServer';") $null = $Params.Add("Database='$DatabaseName';") if ($null -eq $TrustedConnection -or (-not $TrustedConnection)) { $null = $Params.Add("User='$SqlUser';") $null = $Params.Add("Password='$SqlPwd';") } else { $null = $Params.Add("Integrated Security='SSPI';") } $null = $Params.Add("Application Name='d365fo.tools'") Write-PSFMessage -Level Verbose -Message "Building the SQL connection string." -Target ($Params -join ",") $sqlConnection = New-Object System.Data.SqlClient.SqlConnection try { $sqlConnection.ConnectionString = ($Params -join "") $sqlCommand = New-Object System.Data.SqlClient.SqlCommand $sqlCommand.Connection = $sqlConnection $sqlCommand.CommandTimeout = 0 } catch { Write-PSFMessage -Level Host -Message "Something went wrong while working with the sql server connection objects" -Exception $PSItem.Exception Stop-PSFFunction -Message "Stopping because of errors" return } $sqlCommand } <# .SYNOPSIS Get the size from the parameter .DESCRIPTION Get the size from the parameter based on its datatype and value .PARAMETER SqlParameter The SqlParameter object that you want to get the size from .EXAMPLE PS C:\> $SqlCmd = New-Object System.Data.SqlClient.SqlCommand PS C:\> $SqlCmd.Parameters.AddWithValue("@Parm1", "1234") PS C:\> Get-SqlParameterSize -SqlParameter $SqlCmd.Parameters[0] This will extract the size from the first parameter from the SqlCommand object and return it as a formatted string. .NOTES Author: M�tz Jensen (@Splaxi) #> function Get-SqlParameterSize { [CmdletBinding()] [OutputType('System.String')] param ( [System.Data.SqlClient.SqlParameter] $SqlParameter ) $res = "" $stringSizeTypes = @( [System.Data.SqlDbType]::Char, [System.Data.SqlDbType]::NChar, [System.Data.SqlDbType]::NText, [System.Data.SqlDbType]::NVarChar, [System.Data.SqlDbType]::Text, [System.Data.SqlDbType]::VarChar ) if ( $stringSizeTypes -contains $SqlParameter.SqlDbType) { $res = "($($SqlParameter.Size))" } $res } <# .SYNOPSIS Get the value from the parameter .DESCRIPTION Get the value that is assigned to the SqlParameter object .PARAMETER SqlParameter The SqlParameter object that you want to work against .EXAMPLE PS C:\> $SqlCmd = New-Object System.Data.SqlClient.SqlCommand PS C:\> $SqlCmd.Parameters.AddWithValue("@Parm1", "1234") PS C:\> Get-SqlParameterValue -SqlParameter $SqlCmd.Parameters[0] This will extract the value from the first parameter from the SqlCommand object. .NOTES Author: M�tz Jensen (@Splaxi) #> function Get-SqlParameterValue { [CmdletBinding()] [OutputType('System.String')] param ( [System.Data.SqlClient.SqlParameter] $SqlParameter ) $result = $null $stringEscaped = @( [System.Data.SqlDbType]::Char, [System.Data.SqlDbType]::DateTime, [System.Data.SqlDbType]::NChar, [System.Data.SqlDbType]::NText, [System.Data.SqlDbType]::NVarChar, [System.Data.SqlDbType]::Text, [System.Data.SqlDbType]::VarChar, [System.Data.SqlDbType]::Xml, [System.Data.SqlDbType]::Date, [System.Data.SqlDbType]::Time, [System.Data.SqlDbType]::DateTime2, [System.Data.SqlDbType]::DateTimeOffset ) $stringNumbers = @([System.Data.SqlDbType]::Float, [System.Data.SqlDbType]::Decimal) switch ($SqlParameter.SqlDbType) { { $stringEscaped -contains $_ } { $result = "'{0}'" -f $SqlParameter.Value.ToString().Replace("'", "''") break } { [System.Data.SqlDbType]::Bit } { if ((ConvertTo-BooleanOrDefault -Object $SqlParameter.Value.ToString() -Default $true)) { $result = '1' } else { $result = '0' } break } { $stringNumbers -contains $_ } { $SqlParameter.Value $result = ([System.Double]$SqlParameter.Value).ToString([System.Globalization.CultureInfo]::InvariantCulture).Replace("'", "''") break } default { $result = $SqlParameter.Value.ToString().Replace("'", "''") break } } $result } <# .SYNOPSIS Get an executable string from a SqlCommand object .DESCRIPTION Get an formatted and valid string from a SqlCommand object that contains all variables .PARAMETER SqlCommand The SqlCommand object that you want to retrieve the string from .EXAMPLE PS C:\> $SqlCmd = New-Object System.Data.SqlClient.SqlCommand PS C:\> $SqlCmd.CommandText = "SELECT * FROM Table WHERE Column = @Parm1" PS C:\> $SqlCmd.Parameters.AddWithValue("@Parm1", "1234") PS C:\> Get-SqlString -SqlCommand $SqlCmd .NOTES Author: M�tz Jensen (@Splaxi) #> function Get-SqlString { [CmdletBinding()] [OutputType('System.String')] param ( [System.Data.SqlClient.SqlCommand] $SqlCommand ) $sbDeclare = [System.Text.StringBuilder]::new() $sbAssignment = [System.Text.StringBuilder]::new() $sbRes = [System.Text.StringBuilder]::new() if ($SqlCommand.CommandType -eq [System.Data.CommandType]::Text) { if (-not ($null -eq $SqlCommand.Connection)) { $null = $sbDeclare.Append("USE [").Append($SqlCommand.Connection.Database).AppendLine("]") } foreach ($parameter in $SqlCommand.Parameters) { if ($parameter.Direction -eq [System.Data.ParameterDirection]::Input) { $null = $sbDeclare.Append("DECLARE ").Append($parameter.ParameterName).Append("`t") $null = $sbDeclare.Append($parameter.SqlDbType.ToString().ToUpper()) $null = $sbDeclare.AppendLine((Get-SqlParameterSize -SqlParameter $parameter)) $null = $sbAssignment.Append("SET ").Append($parameter.ParameterName).Append(" = ").AppendLine((Get-SqlParameterValue -SqlParameter $parameter)) } } $null = $sbRes.AppendLine($sbDeclare.ToString()) $null = $sbRes.AppendLine($sbAssignment.ToString()) $null = $sbRes.AppendLine($SqlCommand.CommandText) } $sbRes.ToString() } <# .SYNOPSIS Retrieve sync base and extension elements based on a modulename .DESCRIPTION Retrieve the list of installed packages / modules where the name fits the ModuleName parameter. For every model retrieved: collect all base sync and extension sync elements. .PARAMETER ModuleName Name of the module that you are looking for Accepts wildcards for searching. E.g. -Name "Application*Adaptor" Default value is "*" which will search for all modules .EXAMPLE PS C:\> Get-SyncElements -ModuleName "Application*Adaptor" Retrieve the list of installed packages / modules where the name fits the search "Application*Adaptor". For every model retrieved: collect all base sync and extension sync elements. .NOTES Tags: Database Author: Jasper Callens - Cegeka #> function Get-SyncElements { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns", "")] [CmdletBinding()] Param( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [string] $ModuleName ) begin { $assemblies2Process = New-Object -TypeName "System.Collections.ArrayList" $null = $assemblies2Process.Add((Join-Path $BinDirTools "Microsoft.Dynamics.AX.Metadata.dll")) $null = $assemblies2Process.Add((Join-Path $BinDirTools "Microsoft.Dynamics.AX.Metadata.Core.dll")) $null = $assemblies2Process.Add((Join-Path $BinDirTools "Microsoft.Dynamics.AX.Metadata.Storage.dll")) $null = $assemblies2Process.Add((Join-Path $BinDirTools "Microsoft.Dynamics.ApplicationPlatform.XppServices.Instrumentation.dll")) $null = $assemblies2Process.Add((Join-Path $BinDirTools "Microsoft.Dynamics.AX.Metadata.Management.Delta.dll")) $null = $assemblies2Process.Add((Join-Path $BinDirTools "Microsoft.Dynamics.AX.Metadata.Management.Core.dll")) $null = $assemblies2Process.Add((Join-Path $BinDirTools "Microsoft.Dynamics.AX.Metadata.Management.Merge.dll")) $null = $assemblies2Process.Add((Join-Path $BinDirTools "Microsoft.Dynamics.AX.Metadata.Management.Diff.dll")) Import-AssemblyFileIntoMemory -Path $($assemblies2Process.ToArray()) $diskMetadataProvider = (New-Object Microsoft.Dynamics.AX.Metadata.Storage.MetadataProviderFactory).CreateDiskProvider($Script:PackageDirectory) $baseSyncElements = New-Object -TypeName "System.Collections.ArrayList" $extensionSyncElements = New-Object -TypeName "System.Collections.ArrayList" $extensionToBaseSyncElements = New-Object -TypeName "System.Collections.ArrayList" } process { Write-PSFMessage -Level Debug -Message "Collecting $ModuleName AOT elements to sync" $baseSyncElements.AddRange($diskMetadataProvider.Tables.ListObjects($ModuleName)); $baseSyncElements.AddRange($diskMetadataProvider.Views.ListObjects($ModuleName)); $baseSyncElements.AddRange($diskMetadataProvider.DataEntityViews.ListObjects($ModuleName)); $extensionSyncElements.AddRange($diskMetadataProvider.TableExtensions.ListObjects($ModuleName)); # Some Extension elements have to be 'converted' to their base element that has to be passed to the SyncList of the syncengine # Add these elements to an ArrayList $extensionToBaseSyncElements.AddRange($diskMetadataProvider.ViewExtensions.ListObjects($ModuleName)); $extensionToBaseSyncElements.AddRange($diskMetadataProvider.DataEntityViewExtensions.ListObjects($ModuleName)); } end { # Loop every extension element, convert it to its base element and add the base element to another list Foreach ($extElement in $extensionToBaseSyncElements) { $null = $baseSyncElements.Add($extElement.Substring(0, $extElement.IndexOf('.'))) } Write-PSFMessage -Level Debug -Message "Elements from $ModuleName retrieved: $(($baseSyncElements + $extensionToBaseSyncElements) -join ",")" [PSCustomObject]@{ BaseSyncElements = $baseSyncElements.ToArray(); ExtensionSyncElements = $extensionSyncElements.ToArray(); } } } <# .SYNOPSIS Get the tenant from e-mail address .DESCRIPTION Get the tenant (domain) from an e-mail address .PARAMETER Email The e-mail address you want to get the tenant from .EXAMPLE PS C:\> Get-TenantFromEmail -Email "Claire@contoso.com" This will return the tenant (domain) from the "Claire@contoso.com" e-mail address. .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function Get-TenantFromEmail { [CmdletBinding()] [OutputType('System.String')] param ( [string] $email ) $email.Substring($email.LastIndexOf('@') + 1).Trim(); } <# .SYNOPSIS Get time zone .DESCRIPTION Extract the time zone object from the supplied parameter Uses regex to determine whether or not the parameter is the ID or the DisplayName of a time zone .PARAMETER InputObject String value that you want converted into a time zone object .EXAMPLE PS C:\> Get-TimeZone -InputObject "UTC" This will return the time zone object based on the UTC id. .NOTES Tag: Time, TimeZone, Author: M�tz Jensen (@Splaxi) #> function Get-TimeZone { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidOverwritingBuiltInCmdlets", "")] [CmdletBinding()] [OutputType('System.TimeZoneInfo')] param ( [Parameter(Mandatory = $true, Position = 1)] [string] $InputObject ) if ($InputObject -match "\s\-\s\[") { $search = [regex]::Split($InputObject, "\s\-\s\[")[0] [System.TimeZoneInfo]::GetSystemTimeZones() | Where-Object {$PSItem.DisplayName -eq $search} | Select-Object -First 1 } else { try { [System.TimeZoneInfo]::FindSystemTimeZoneById($InputObject) } catch { Write-PSFMessage -Level Host -Message "Unable to translate the <c='em'>$InputObject</c> to a known .NET timezone value. Please make sure you filled in a valid timezone." Stop-PSFFunction -Message "Stopping because timezone wasn't found." -StepsUpward 1 return } } } <# .SYNOPSIS Get the SID from an Azure Active Directory (AAD) user .DESCRIPTION Get the generated SID that an Azure Active Directory (AAD) user will get in relation to Dynamics 365 Finance & Operations environment .PARAMETER SignInName The sign in name (email address) for the user that you want the SID from .PARAMETER Provider The provider connected to the sign in name .EXAMPLE PS C:\> Get-UserSIDFromAad -SignInName "Claire@contoso.com" -Provider "ZXY" This will get the SID for Azure Active Directory user "Claire@contoso.com" .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function Get-UserSIDFromAad { [CmdletBinding()] [OutputType('System.String')] param ( [string] $SignInName, [string] $Provider ) try { $productDetails = Get-ProductInfoProvider Add-Type -Path "$Script:AOSPath\bin\Microsoft.Dynamics.BusinessPlatform.SharedTypes.dll" Add-Type -Path "$Script:AOSPath\bin\Microsoft.Dynamics.ApplicationPlatform.PerformanceCounters.dll" Add-Type -Path "$Script:AOSPath\bin\Microsoft.Dynamics.ApplicationPlatform.XppServices.Instrumentation.dll" Add-Type -Path "$Script:AOSPath\bin\Microsoft.Dynamics.AX.Security.SidGenerator.dll" if ($([Version]$productDetails.ApplicationVersion) -ge $([Version]"10.0.13")) { $SID = [Microsoft.Dynamics.Ax.Security.SidGenerator]::Generate($SignInName, $Provider, [Microsoft.Dynamics.Ax.Security.SidGenerator+SidAlgorithm]::Sha1) } else { $SID = [Microsoft.Dynamics.Ax.Security.SidGenerator]::Generate($SignInName, $Provider) } Write-PSFMessage -Level Verbose -Message "Generated SID: $SID" -Target $SID $SID } catch { Write-PSFMessage -Level Host -Message "Something went wrong while working against the database" -Exception $PSItem.Exception Stop-PSFFunction -Message "Stopping because of errors" return } } <# .SYNOPSIS Get Windows Defender Status .DESCRIPTION Will get the current status of the Windows Defender .PARAMETER Silent Instruct the cmdlet to silence the output written to the console If set the output will be silenced, if not set, the output will be written to the console .EXAMPLE PS C:\> Get-WindowsDefenderStatus This will get the status of Windows Defender. It will write the output to the console. .EXAMPLE PS C:\> Get-WindowsDefenderStatus -Silent This will get the status of Windows Defender. All outputs will be silenced. .NOTES Inspired by https://gallery.technet.microsoft.com/scriptcenter/PowerShell-to-Check-if-811b83bc Author: Robin Kretzschmar (@darksmile92) Author: M�tz Jensen (@Splaxi) #> function Get-WindowsDefenderStatus { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseShouldProcessForStateChangingFunctions", "")] [CmdletBinding()] [OutputType('System.Boolean')] param ( [switch] $Silent ) try { $defenderOptions = Get-MpComputerStatus if ([string]::IsNullOrEmpty($defenderOptions)) { if ($Silent -eq $false) { Write-PSFMessage -Level Host -Message "Windows Defender was not found running on the Server: $($env:computername)" } $false } else { if ($Silent -eq $false) { Write-PSFHostColor -DefaultColor "Cyan" -String "Windows Defender was found on the Server: $($env:computername)" Write-PSFHostColor -DefaultColor "Yellow" -String " Is Windows Defender Enabled? $($defenderOptions.AntivirusEnabled)" Write-PSFHostColor -DefaultColor "Yellow" -String " Is Windows Defender Service Enabled? $($defenderOptions.AMServiceEnabled)" Write-PSFHostColor -DefaultColor "Yellow" -String " Is Windows Defender Antispyware Enabled? $($defenderOptions.AntispywareEnabled)" Write-PSFHostColor -DefaultColor "Yellow" -String " Is Windows Defender OnAccessProtection Enabled? $($defenderOptions.OnAccessProtectionEnabled)" Write-PSFHostColor -DefaultColor "Yellow" -String " Is Windows Defender RealTimeProtection Enabled? $($defenderOptions.RealTimeProtectionEnabled)" } if ($defenderOptions.AntivirusEnabled -eq $true) { $true } else { $false } } } catch { if ($Silent -eq $false) { Write-PSFMessage -Level Host -Message "Windows Defender was not found running on the Server: $($env:computername)" } $false } } <# .SYNOPSIS Import an Azure Active Directory (AAD) application .DESCRIPTION Import an Azure Active Directory (AAD) application into a Dynamics 365 for Finance & Operations environment .PARAMETER SqlCommand The SQL Command object that should be used when importing the AAD application .PARAMETER Name The name that the imported application should have inside the D365FO environment .PARAMETER UserId The id of the user linked to the application inside the D365FO environment .PARAMETER ClientId The Client ID that the imported application should use inside the D365FO environment .EXAMPLE PS C:\> $SqlCommand = Get-SqlCommand -DatabaseServer localhost -DatabaseName AxDB -SqlUser User123 -SqlPwd "Password123" PS C:\> Import-AadApplicationIntoD365FO -SqlCommand $SqlCommand -Name "Application1" -UserId "admin" -ClientId "aef2e67c-64a3-4c72-9294-d288c5bf503d" This will get a SqlCommand object that will connect to the localhost server and the AXDB database, with the sql credential "User123". The SqlCommand object is passed to the Import-AadApplicationIntoD365FO along with all the necessary details for importing Application1 as an application linked to user admin into the D365FO environment. .NOTES Author: Gert Van Der Heyden (@gertvdheyden) #> function Import-AadApplicationIntoD365FO { [CmdletBinding()] param ( [System.Data.SqlClient.SqlCommand] $SqlCommand, [string] $Name, [string] $UserId, [string] $ClientId ) Write-PSFMessage -Level Verbose -Message "Testing the userid $UserId" $idExists = Test-AadUserIdInD365FO $sqlCommand $UserId if ($idExists -eq $true) { New-D365FOAadApplication $sqlCommand $Name $UserId $ClientId Write-PSFMessage -Level Host -Message "Application $Name for user $UserId added to D365FO" } else { Write-PSFMessage -Level Host -Message "An User with ID = '$UserId' does not exists" } } <# .SYNOPSIS Import an Azure Active Directory (AAD) user .DESCRIPTION Import an Azure Active Directory (AAD) user into a Dynamics 365 for Finance & Operations environment .PARAMETER SqlCommand The SQL Command object that should be used when importing the AAD user .PARAMETER SignInName The sign in name (email address) for the user that you want to import .PARAMETER Name The name that the imported user should have inside the D365FO environment .PARAMETER Id The ID that the imported user should have inside the D365FO environment .PARAMETER SID The SID that correlates to the imported user inside the D365FO environment .PARAMETER StartUpCompany The default company (legal entity) for the imported user .PARAMETER IdentityProvider The provider for the imported to validated against .PARAMETER NetworkDomain The network domain of the imported user .PARAMETER ObjectId The Azure Active Directory object id for the imported user .PARAMETER Language Language that should be configured for the user, for when they sign-in to the D365 environment .EXAMPLE PS C:\> $SqlCommand = Get-SqlCommand -DatabaseServer localhost -DatabaseName AxDB -SqlUser User123 -SqlPwd "Password123" PS C:\> Import-AadUserIntoD365FO -SqlCommand $SqlCommand -SignInName "Claire@contoso.com" -Name "Claire" -Id "claire" -SID "123XYZ" -StartupCompany "DAT" -IdentityProvider "XYZ" -NetworkDomain "Contoso.com" -ObjectId "123XYZ" This will get a SqlCommand object that will connect to the localhost server and the AXDB database, with the sql credential "User123". The SqlCommand object is passed to the Import-AadUserIntoD365FO along with all the necessary details for importing Claire@contoso.com as an user into the D365FO environment. .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function Import-AadUserIntoD365FO { [CmdletBinding()] param ( [System.Data.SqlClient.SqlCommand] $SqlCommand, [string] $SignInName, [string] $Name, [string] $Id, [string] $SID, [string] $StartUpCompany, [string] $IdentityProvider, [string] $NetworkDomain, [string] $ObjectId, [string] $Language ) Write-PSFMessage -Level Verbose -Message "Testing the Email $signInName" -Target $signInName $UserFound = Test-AadUserInD365FO $sqlCommand $SignInName if ($UserFound -eq $false) { Write-PSFMessage -Level Verbose -Message "Testing the userid $Id" -Target $Id $idTaken = Test-AadUserIdInD365FO $sqlCommand $id if (Test-PSFFunctionInterrupt) { return } if ($idTaken -eq $false) { $userAdded = New-D365FOUser $sqlCommand $SignInName $Name $Id $Sid $StartUpCompany $IdentityProvider $NetworkDomain $ObjectId $Language if ($userAdded -eq $true) { $securityAdded = Add-AadUserSecurity $sqlCommand $Id Write-PSFMessage -Level Host -Message "User $SignInName Imported" if ($securityAdded -eq $false) { Write-PSFMessage -Level Host -Message "User $SignInName did not get securityRoles" #Stop-PSFFunction -Message "Stopping because of errors" -StepsUpward 1 #return } } else { Write-PSFMessage -Level Host -Message "User $SignInName, not added to D365FO" #Stop-PSFFunction -Message "Stopping because of errors" -StepsUpward 1 #return } } else { Write-PSFMessage -Level Host -Message "An User with ID = '$ID' already exists" #Stop-PSFFunction -Message "Stopping because of errors" -StepsUpward 1 #return } } else { Write-PSFMessage -Level Host -Message "An User with Email $SignInName already exists in D365FO" #Stop-PSFFunction -Message "Stopping because of errors" -StepsUpward 1 #return } } <# .SYNOPSIS Imports a .NET dll file into memory .DESCRIPTION Imports a .NET dll file into memory, by creating a copy (temporary file) and imports it using reflection .PARAMETER Path Path to the dll file you want to import Accepts an array of strings .EXAMPLE PS C:\> Import-AssemblyFileIntoMemory -Path "C:\AOSService\PackagesLocalDirectory\Bin\Microsoft.Dynamics.BusinessPlatform.ProductInformation.Framework.dll" This will create an new file named "C:\AOSService\PackagesLocalDirectory\Bin\Microsoft.Dynamics.BusinessPlatform.ProductInformation.Framework.dll_shawdow.dll" The new file is then imported into memory using .NET Reflection. After the file has been imported, it will be deleted from disk. .NOTES Author: M�tz Jensen (@Splaxi) #> function Import-AssemblyFileIntoMemory { [CmdletBinding()] [OutputType()] param ( [Parameter(Mandatory = $true, Position = 1)] [string[]] $Path ) if (-not (Test-PathExists -Path $Path -Type Leaf)) { Stop-PSFFunction -Message "Stopping because unable to locate file." -StepsUpward 1 return } Invoke-TimeSignal -Start foreach ($itemPath in $Path) { $shadowClonePath = "$itemPath`_shadow.dll" try { Write-PSFMessage -Level Verbose -Message "Cloning $itemPath to $shadowClonePath" Copy-Item -Path $itemPath -Destination $shadowClonePath -Force Write-PSFMessage -Level Verbose -Message "Loading $shadowClonePath into memory" $null = [AppDomain]::CurrentDomain.Load(([System.IO.File]::ReadAllBytes($shadowClonePath))) } catch { Write-PSFMessage -Level Host -Message "Something went wrong while working against the database" -Exception $PSItem.Exception Stop-PSFFunction -Message "Stopping because of errors" return } finally { Write-PSFMessage -Level Verbose -Message "Removing $shadowClonePath" Remove-Item -Path $shadowClonePath -Force -ErrorAction SilentlyContinue } } Invoke-TimeSignal -End } <# .SYNOPSIS Create a database copy in Azure SQL Database instance .DESCRIPTION Create a new database by cloning a database in Azure SQL Database instance .PARAMETER DatabaseServer The name of the database server If on-premises or classic SQL Server, use either short name og Fully Qualified Domain Name (FQDN) If Azure use the full address to the database server, e.g. server.database.windows.net .PARAMETER DatabaseName The name of the database .PARAMETER SqlUser The login name for the SQL Server instance .PARAMETER SqlPwd The password for the SQL Server user .PARAMETER NewDatabaseName Name of the new / cloned database in the Azure SQL Database instance .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Invoke-AzureBackupRestore -DatabaseServer TestServer.database.windows.net -DatabaseName AxDB -SqlUser User123 -SqlPwd "Password123" -NewDatabaseName ExportClone This will create a database named "ExportClone" in the "TestServer.database.windows.net" Azure SQL Database instance. It uses the SQL credential "User123" to preform the needed actions. .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> Function Invoke-AzureBackupRestore { [CmdletBinding()] [OutputType('System.Boolean')] param ( [Parameter(Mandatory = $true)] [string] $DatabaseServer, [Parameter(Mandatory = $true)] [string] $DatabaseName, [Parameter(Mandatory = $true)] [string] $SqlUser, [Parameter(Mandatory = $true)] [string] $SqlPwd, [Parameter(Mandatory = $true)] [string] $NewDatabaseName, [switch] $EnableException ) Invoke-TimeSignal -Start $StartTime = Get-Date $SqlConParams = @{DatabaseServer = $DatabaseServer; SqlUser = $SqlUser; SqlPwd = $SqlPwd; TrustedConnection = $false} $sqlCommand = Get-SqlCommand @SqlConParams -DatabaseName $DatabaseName $commandText = (Get-Content "$script:ModuleRoot\internal\sql\newazuredbfromcopy.sql") -join [Environment]::NewLine $commandText = $commandText.Replace('@CurrentDatabase', $DatabaseName) $commandText = $commandText.Replace('@NewName', $NewDatabaseName) $sqlCommand.CommandText = $commandText try { Write-PSFMessage -Level InternalComment -Message "Executing a script against the database." -Target (Get-SqlString $SqlCommand) Write-PSFMessage -Level Verbose -Message "Starting the cloning process of the Azure DB." -Target (Get-SqlString $SqlCommand) $sqlCommand.Connection.Open() $null = $sqlCommand.ExecuteNonQuery() } catch { $messageString = "Something went wrong while <c='em'>cloning</c> the Azure DB database." Write-PSFMessage -Level Host -Message $messageString -Exception $PSItem.Exception -Target (Get-SqlString $SqlCommand) Stop-PSFFunction -Message "Stopping because of errors." -Exception $([System.Exception]::new($($messageString -replace '<[^>]+>', ''))) -ErrorRecord $_ -StepsUpward 1 return } finally { if ($sqlCommand.Connection.State -ne [System.Data.ConnectionState]::Closed) { $sqlCommand.Connection.Close() } $sqlCommand.Dispose() } $sqlCommand = Get-SqlCommand @SqlConParams -DatabaseName "master" $commandText = (Get-Content "$script:ModuleRoot\internal\sql\checkfornewazuredb.sql") -join [Environment]::NewLine $sqlCommand.CommandText = $commandText $null = $sqlCommand.Parameters.Add("@NewName", $NewDatabaseName) $null = $sqlCommand.Parameters.Add("@Time", $StartTime) try { Write-PSFMessage -Level InternalComment -Message "Executing a script against the database." -Target (Get-SqlString $SqlCommand) Write-PSFMessage -Level Verbose -Message "Start to wait for the cloning process of the Azure DB to complete." $sqlCommand.Connection.Open() $operation_row_count = 0 #Loop every minute until we get a row, if we get a row copy is done while ($operation_row_count -eq 0) { $Reader = $sqlCommand.ExecuteReader() $Datatable = New-Object System.Data.DataTable $Datatable.Load($Reader) $operation_row_count = $Datatable.Rows.Count $time = (Get-Date).ToString("HH:mm:ss") Write-PSFMessage -Level Verbose -Message "Cloning not complete Sleeping for 60 seconds. [$time]" Start-Sleep -s 60 } $true } catch { $messageString = "Something went wrong while <c='em'>waiting</c> for the clone process of the Azure DB database to complete." Write-PSFMessage -Level Host -Message $messageString -Exception $PSItem.Exception -Target (Get-SqlString $SqlCommand) Stop-PSFFunction -Message "Stopping because of errors." -Exception $([System.Exception]::new($($messageString -replace '<[^>]+>', ''))) -ErrorRecord $_ -StepsUpward 1 return } finally { $Reader.close() if ($sqlCommand.Connection.State -ne [System.Data.ConnectionState]::Closed) { $sqlCommand.Connection.Close() } $sqlCommand.Dispose() $Datatable.Dispose() } Invoke-TimeSignal -End } <# .SYNOPSIS Clear Azure SQL Database specific objects .DESCRIPTION Clears all the objects that can only exists inside an Azure SQL Database instance or disable things that will require rebuilding on the receiving system .PARAMETER DatabaseServer The name of the database server If on-premises or classic SQL Server, use either short name og Fully Qualified Domain Name (FQDN) If Azure use the full address to the database server, e.g. server.database.windows.net .PARAMETER DatabaseName The name of the database .PARAMETER SqlUser The login name for the SQL Server instance .PARAMETER SqlPwd The password for the SQL Server user .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Invoke-ClearAzureSpecificObjects -DatabaseServer TestServer.database.windows.net -DatabaseName ExportClone -SqlUser User123 -SqlPwd "Password123" This will execute all necessary scripts against the "ExportClone" database that exists in the "TestServer.database.windows.net" Azure SQL Database instance. It uses the SQL credential "User123" to preform the needed actions. .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> Function Invoke-ClearAzureSpecificObjects { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns", "")] [CmdletBinding()] [OutputType([System.Boolean])] param ( [Parameter(Mandatory = $true)] [string] $DatabaseServer, [Parameter(Mandatory = $true)] [string] $DatabaseName, [Parameter(Mandatory = $true)] [string] $SqlUser, [Parameter(Mandatory = $true)] [string] $SqlPwd, [switch] $EnableException ) $sqlCommand = Get-SQLCommand @PsBoundParameters -TrustedConnection $false $commandText = (Get-Content "$script:ModuleRoot\internal\sql\clear-azurebacpacdatabase.sql") -join [Environment]::NewLine $commandText = $commandText.Replace("@NewDatabase", $DatabaseName) $sqlCommand.CommandText = $commandText try { Write-PSFMessage -Level InternalComment -Message "Executing a script against the database." -Target (Get-SqlString $SqlCommand) $sqlCommand.Connection.Open() $null = $sqlCommand.ExecuteNonQuery() $true } catch { $messageString = "Something went wrong while <c='em'>clearing</c> the <c='em'>Azure</c> specific objects in the database." Write-PSFMessage -Level Host -Message $messageString -Exception $PSItem.Exception -Target (Get-SqlString $SqlCommand) Stop-PSFFunction -Message "Stopping because of errors." -Exception $([System.Exception]::new($($messageString -replace '<[^>]+>', ''))) -ErrorRecord $_ -StepsUpward 1 return } finally { if ($sqlCommand.Connection.State -ne [System.Data.ConnectionState]::Closed) { $sqlCommand.Connection.Close() } $sqlCommand.Dispose() } } <# .SYNOPSIS Clear SQL Server (on-premises) specific objects .DESCRIPTION Clears all the objects that can only exists inside a SQL Server (on-premises) instance or disable things that will require rebuilding on the receiving system .PARAMETER DatabaseServer The name of the database server If on-premises or classic SQL Server, use either short name og Fully Qualified Domain Name (FQDN) If Azure use the full address to the database server, e.g. server.database.windows.net .PARAMETER DatabaseName The name of the database .PARAMETER SqlUser The login name for the SQL Server instance .PARAMETER SqlPwd The password for the SQL Server user .PARAMETER TrustedConnection Should the connection use a Trusted Connection or not .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Invoke-ClearSqlSpecificObjects -DatabaseServer localhost -DatabaseName ExportClone -SqlUser User123 -SqlPwd "Password123" This will execute all necessary scripts against the "ExportClone" database that exists in the localhost SQL Server instance. It uses the SQL credential "User123" to preform the needed actions. .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> Function Invoke-ClearSqlSpecificObjects { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns", "")] [CmdletBinding()] [OutputType([System.Boolean])] param ( [Parameter(Mandatory = $true)] [string] $DatabaseServer, [Parameter(Mandatory = $true)] [string] $DatabaseName, [Parameter(Mandatory = $false)] [string] $SqlUser, [Parameter(Mandatory = $false)] [string] $SqlPwd, [Parameter(Mandatory = $false)] [boolean] $TrustedConnection, [switch] $EnableException ) $sqlCommand = Get-SQLCommand @PsBoundParameters $commandText = (Get-Content "$script:ModuleRoot\internal\sql\clear-sqlbacpacdatabase.sql") -join [Environment]::NewLine $sqlCommand.CommandText = $commandText try { Write-PSFMessage -Level InternalComment -Message "Executing a script against the database." -Target (Get-SqlString $SqlCommand) $sqlCommand.Connection.Open() $null = $sqlCommand.ExecuteNonQuery() $true } catch { $messageString = "Something went wrong while <c='em'>clearing</c> the <c='em'>SQL</c> specific objects in the database." Write-PSFMessage -Level Host -Message $messageString -Exception $PSItem.Exception -Target (Get-SqlString $SqlCommand) Stop-PSFFunction -Message "Stopping because of errors." -Exception $([System.Exception]::new($($messageString -replace '<[^>]+>', ''))) -ErrorRecord $_ -StepsUpward 1 return } finally { if ($sqlCommand.Connection.State -ne [System.Data.ConnectionState]::Closed) { $sqlCommand.Connection.Close() } $sqlCommand.Dispose() } } <# .SYNOPSIS Analyze the compiler output log .DESCRIPTION Analyze the compiler output log and generate an excel file contain worksheets per type: Errors, Warnings, Tasks It could be a Visual Studio compiler log or it could be a Invoke-D365ModuleCompile log you want analyzed .PARAMETER Path Path to the compiler log file that you want to work against A BuildModelResult.log or a Dynamics.AX.*.xppc.log file will both work .PARAMETER Identifier Identifier used to name the error output when hitting parsing errors .PARAMETER OutputPath Path where you want the excel file (xlsx-file) saved to .PARAMETER SkipWarnings Instructs the cmdlet to skip warnings while analyzing the compiler output log file .PARAMETER SkipTasks Instructs the cmdlet to skip tasks while analyzing the compiler output log file .PARAMETER PackageDirectory Path to the directory containing the installed package / module .EXAMPLE PS C:\> Invoke-CompilerResultAnalyzer -Path "c:\temp\d365fo.tools\Custom\Dynamics.AX.Custom.xppc.log" -Identifier "Custom" -OutputPath "C:\Temp\d365fo.tools\custom-CompilerResults.xslx" -PackageDirectory "J:\AOSService\PackagesLocalDirectory" This will analyze the compiler log file and generate a compiler result excel file. .NOTES Tags: Compiler, Build, Errors, Warnings, Tasks Author: M�tz Jensen (@Splaxi) This cmdlet is inspired by the work of "Vilmos Kintera" (twitter: @DAXRunBase) All credits goes to him for showing how to extract these information His blog can be found here: https://www.daxrunbase.com/blog/ The specific blog post that we based this cmdlet on can be found here: https://www.daxrunbase.com/2020/03/31/interpreting-compiler-results-in-d365fo-using-powershell/ The github repository containing the original scrips can be found here: https://github.com/DAXRunBase/PowerShell-and-Azure #> function Invoke-CompilerResultAnalyzer { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidAssignmentToAutomaticVariable", "")] [CmdletBinding()] [OutputType('')] param ( [string] $Path, [string] $Identifier, [string] $OutputPath, [switch] $SkipWarnings, [switch] $SkipTasks, [string] $PackageDirectory ) Invoke-TimeSignal -Start if (-not (Test-PathExists -Path $PackageDirectory -Type Container)) { return } $positionRegex = '(?=\[\().*(?=\)\])' $positionSplitRegex = '(.*)(?=\[\().*(?:\)\]: )(.*)' $warningRegex = '(?:Compile Fatal|MetadataProvider|Metadata|Compile|Unspecified|Generation|ExternalReference|BestPractices) (Warning): (Query Method|Interface Method|Form Method LocalFunction|Form Control Method|Form Datasource Method|Form DataSource Method|Form DataSource DataField Method|Form Method|Map Method|Class Delegate|Table Method LocalFunction|Class Method LocalFunction|Table Method|Class Method|Table|Class|View|Form|)(?: |)(?:dynamics:|)(.*)(?:: )(.*)' $taskRegex = '(TaskListItem Information): (Query Method|Interface Method|Form Method LocalFunction|Form Control Method|Form Datasource Method|Form DataSource Method|Form DataSource DataField Method|Form Method|Map Method|Class Delegate|Table Method LocalFunction|Class Method LocalFunction|Table Method|Class Method|Table|Class|View|Form|)(?: |)(?:dynamics:|)(.*)(?:: )(.*)' $errorRegex = '(?:Compile Fatal|MetadataProvider|Metadata|Compile|Unspecified|Generation) (Error): (Query Method|Interface Method|Form Method LocalFunction|Form Control Method|Form Datasource Method|Form DataSource Method|Form DataSource DataField Method|Form Method|Map Method|Class Delegate|Table Method LocalFunction|Class Method LocalFunction|Table Method|Class Method|Table|Class|View|Form|)(?: |)(?:dynamics:|)(.*)(?:: )(.*)' $warningObjects = New-Object System.Collections.Generic.List[System.Object] $errorObjects = New-Object System.Collections.Generic.List[System.Object] $taskObjects = New-Object System.Collections.Generic.List[System.Object] if (-not $SkipWarnings) { Write-PSFMessage -Level Verbose -Message "Will analyze for warnings in the log file." -Target $SkipWarnings try { $warningText = Select-String -LiteralPath $Path -Pattern '(^.*) Warning: (.*)' | ForEach-Object { $_.Line } # Skip modules that do not have warnings if ($warningText) { Write-PSFMessage -Level Verbose -Message "Found warning lines in the log file." foreach ($line in $warningText) { $lineLocal = $line # Remove positioning text in the format of "[(5,5),(5,39)]: " for methods if ($lineLocal -match $positionRegex) { Write-PSFMessage -Level Verbose -Message "Position notation was found in the warning line. Will remove it." $lineReplaced = [regex]::Split($lineLocal, $positionSplitRegex) $lineLocal = $lineReplaced[1] + $lineReplaced[2] } try { Write-PSFMessage -Level Verbose -Message "Will split the warning line, and create result object." # Regular expression matching to split line details into groups $Matches = [regex]::split($lineLocal, $warningRegex) $object = [PSCustomObject]@{ OutputType = $Matches[1].trim() ObjectType = $Matches[2].trim() Path = $Matches[3].trim() Text = $Matches[4].trim() } $warningObjects.Add($object) } catch { Write-PSFHostColor -Level Host "<c='Yellow'>($Identifier) Error during processing line for warnings <</c><c='Red'>$line</c><c='Yellow'>></c>" } } } } catch { Write-PSFMessage -Level Host "Error while processing warnings" } } if (-not $SkipTasks) { Write-PSFMessage -Level Verbose -Message "Will analyze for tasks in the log file." -Target $SkipTasks try { $taskText = Select-String -LiteralPath $Path -Pattern '(^.*)TaskListItem Information: (.*)' | ForEach-Object { $_.Line } # Skip modules that do not have tasks if ($taskText) { Write-PSFMessage -Level Verbose -Message "Found task lines in the log file." foreach ($line in $taskText) { $lineLocal = $line # Remove positioning text in the format of "[(5,5),(5,39)]: " for methods if ($lineLocal -match $positionRegex) { Write-PSFMessage -Level Verbose -Message "Position notation was found in the task line. Will remove it." $lineReplaced = [regex]::Split($lineLocal, $positionSplitRegex) $lineLocal = $lineReplaced[1] + $lineReplaced[2] } # Remove TODO part if ($lineLocal -match '(?:TODO :|TODO:|TODO)') { Write-PSFMessage -Level Verbose -Message "TODO prefix string value was found in the line. Will remove it." $lineReplaced = [regex]::Split($lineLocal, '(.*)(?:TODO :|TODO:|TODO)(.*)', [System.Text.RegularExpressions.RegexOptions]::IgnoreCase) $lineLocal = $lineReplaced[1] + $lineReplaced[2] } try { Write-PSFMessage -Level Verbose -Message "Will split the task line, and create result object." # Regular expression matching to split line details into groups $Matches = [regex]::split($lineLocal, $taskRegex) $object = [PSCustomObject]@{ OutputType = $Matches[1].trim() ObjectType = $Matches[2].trim() Path = $Matches[3].trim() Text = $Matches[4].trim() } $taskObjects.Add($object) } catch { Write-PSFHostColor -Level Host "<c='Yellow'>($Identifier) Error during processing line for tasks <</c><c='Red'>$line</c><c='Yellow'>></c>" } } } } catch { Write-PSFMessage -Level Host -Message "Error during processing tasks" } } try { $errorText = Select-String -LiteralPath $Path -Pattern '(^.*) Error: (.*)' | ForEach-Object { $_.Line } # Skip modules that do not have errors if ($errorText) { foreach ($line in $errorText) { $lineLocal = $line # Remove positioning text in the format of "[(5,5),(5,39)]: " for methods if ($lineLocal -match $positionRegex) { Write-PSFMessage -Level Verbose -Message "Position notation was found in the error line. Will remove it." $lineReplaced = [regex]::Split($lineLocal, $positionSplitRegex) $lineLocal = $lineReplaced[1] + $lineReplaced[2] } try { Write-PSFMessage -Level Verbose -Message "Will split the error line, and create result object." # Regular expression matching to split line details into groups $Matches = [regex]::split($lineLocal, $errorRegex) $object = [PSCustomObject]@{ ErrorType = $Matches[1].trim() ObjectType = $Matches[2].trim() Path = $Matches[3].trim() Text = $Matches[4].trim() } $errorObjects.Add($object) } catch { Write-PSFHostColor -Level Host "<c='Yellow'>($Identifier) Error during processing line for errors <</c><c='Red'>$line</c><c='Yellow'>></c>" } } } } catch { Write-PSFMessage -Level Host -Message "Error during processing errors" } Write-PSFMessage -Level Verbose -Message "Will start exporting the details to the excel file." -Target $OutputPath $errorObjects.ToArray() | Export-Excel -Path $OutputPath -WorksheetName "Errors" -ClearSheet -AutoFilter -AutoSize -BoldTopRow $groupErrorTexts = $errorObjects.ToArray() | Group-Object -Property Text | Sort-Object -Property "Count" -Descending | Select-PSFObject Count, "Name as DistinctErrorText" $groupErrorTexts | Export-Excel -Path $OutputPath -WorksheetName "Errors-Summary" -ClearSheet -AutoFilter -AutoSize -BoldTopRow if (-not $SkipWarnings) { Write-PSFMessage -Level Verbose -Message "Building the warning details and saving them to the excel file." -Target $SkipWarnings $warningObjects.ToArray() | Export-Excel -Path $OutputPath -WorksheetName "Warnings" -ClearSheet -AutoFilter -AutoSize -BoldTopRow $groupWarningTexts = $warningObjects.ToArray() | Group-Object -Property Text | Sort-Object -Property "Count" -Descending | Select-PSFObject Count, "Name as DistinctWarningText" $groupWarningTexts | Export-Excel -Path $OutputPath -WorksheetName "Warnings-Summary" -ClearSheet -AutoFilter -AutoSize -BoldTopRow } else { Remove-Worksheet -Path $OutputPath -WorksheetName "Warnings" Remove-Worksheet -Path $OutputPath -WorksheetName "Warnings-Summary" } if (-not $SkipTasks) { Write-PSFMessage -Level Verbose -Message "Building the task details and saving them to the excel file." -Target $SkipTasks $taskObjects.ToArray() | Export-Excel -Path $OutputPath -WorksheetName "Tasks" -ClearSheet -AutoFilter -AutoSize -BoldTopRow $groupTaskTexts = $taskObjects.ToArray() | Group-Object -Property Text | Sort-Object -Property "Count" -Descending | Select-PSFObject Count, "Name as DistinctTaskText" $groupTaskTexts | Export-Excel -Path $OutputPath -WorksheetName "Tasks-Summary" -ClearSheet -AutoFilter -AutoSize -BoldTopRow } else { Remove-Worksheet -Path $OutputPath -WorksheetName "Tasks" Remove-Worksheet -Path $OutputPath -WorksheetName "Tasks-Summary" } [PSCustomObject]@{ File = $OutputPath Filename = $(Split-Path -Path $OutputPath -Leaf) } Invoke-TimeSignal -End } <# .SYNOPSIS Invoke the ModelUtil.exe .DESCRIPTION A cmdlet that wraps some of the cumbersome work into a streamlined process .PARAMETER Command Instruct the cmdlet to what process you want to execute against the ModelUtil tool Valid options: Import Export Delete Replace .PARAMETER Path Used for import to point where to import from Used for export to point where to export the model to The cmdlet only supports an already extracted ".axmodel" file .PARAMETER Model Name of the model that you want to work against Used for export to select the model that you want to export Used for delete to select the model that you want to delete .PARAMETER BinDir The path to the bin directory for the environment Default path is the same as the AOS service PackagesLocalDirectory\bin Default value is fetched from the current configuration on the machine .PARAMETER MetaDataDir The path to the meta data directory for the environment Default path is the same as the aos service PackagesLocalDirectory .PARAMETER LogPath The path where the log file(s) will be saved .PARAMETER ShowOriginalProgress Instruct the cmdlet to show the standard output in the console Default is $false which will silence the standard output .PARAMETER OutputCommandOnly Instruct the cmdlet to only output the command that you would have to execute by hand Will include full path to the executable and the needed parameters based on your selection .EXAMPLE PS C:\> Invoke-ModelUtil -Command Import -Path "c:\temp\d365fo.tools\CustomModel.axmodel" This will execute the import functionality of ModelUtil.exe and have it import the "CustomModel.axmodel" file. .EXAMPLE PS C:\> Invoke-ModelUtil -Command Export -Path "c:\temp\d365fo.tools" -Model CustomModel This will execute the export functionality of ModelUtil.exe and have it export the "CustomModel" model. The file will be placed in "c:\temp\d365fo.tools". .EXAMPLE PS C:\> Invoke-ModelUtil -Command Delete -Model CustomModel This will execute the delete functionality of ModelUtil.exe and have it delete the "CustomModel" model. The folders in PackagesLocalDirectory for the "CustomModel" will NOT be deleted .EXAMPLE PS C:\> Invoke-ModelUtil -Command Replace -Path "c:\temp\d365fo.tools\CustomModel.axmodel" This will execute the replace functionality of ModelUtil.exe and have it replace the "CustomModel" model. .NOTES Tags: AXModel, Model, ModelUtil, Servicing, Import, Export, Delete, Replace Author: M�tz Jensen (@Splaxi) #> function Invoke-ModelUtil { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidDefaultValueSwitchParameter", "")] [CmdletBinding(DefaultParameterSetName = 'Default')] param ( [Parameter(Mandatory = $true)] [ValidateSet('Import', 'Export', 'Delete', 'Replace')] [string] $Command, [Parameter(Mandatory = $True, ParameterSetName = 'Import', Position = 1 )] [Parameter(Mandatory = $True, ParameterSetName = 'Export', Position = 1 )] [Alias('File')] [string] $Path, [Parameter(Mandatory = $True, ParameterSetName = 'Export', Position = 2 )] [Parameter(Mandatory = $True, ParameterSetName = 'Delete', Position = 1 )] [string] $Model, [string] $BinDir = "$Script:PackageDirectory\bin", [string] $MetaDataDir = "$Script:MetaDataDir", [string] $LogPath, [switch] $ShowOriginalProgress, [switch] $OutputCommandOnly ) Invoke-TimeSignal -Start if (-not (Test-PathExists -Path $MetaDataDir, $BinDir -Type Container)) { Stop-PSFFunction -Message "Stopping because of missing paths." -StepsUpward 1 } $executable = Join-Path -Path $BinDir -ChildPath "ModelUtil.exe" if (-not (Test-PathExists -Path $executable -Type Leaf)) { Stop-PSFFunction -Message "Stopping because of missing paths." -StepsUpward 1 } $params = New-Object System.Collections.Generic.List[string] Write-PSFMessage -Level Verbose -Message "Building the parameter options." switch ($Command.ToLowerInvariant()) { 'import' { if (-not (Test-PathExists -Path $Path -Type Leaf)) { Stop-PSFFunction -Message "Stopping because of missing paths." -StepsUpward 1 } $params.Add("-import") $params.Add("-metadatastorepath=`"$MetaDataDir`"") $params.Add("-file=`"$Path`"") } 'export' { $params.Add("-export") $params.Add("-metadatastorepath=`"$MetaDataDir`"") $params.Add("-outputpath=`"$Path`"") $params.Add("-modelname=`"$Model`"") } 'delete' { $params.Add("-delete") $params.Add("-metadatastorepath=`"$MetaDataDir`"") $params.Add("-modelname=`"$Model`"") } 'replace' { if (-not (Test-PathExists -Path $Path -Type Leaf)) { Stop-PSFFunction -Message "Stopping because of missing paths." -StepsUpward 1 } $params.Add("-replace") $params.Add("-metadatastorepath=`"$MetaDataDir`"") $params.Add("-file=`"$Path`"") } } Write-PSFMessage -Level Verbose -Message "Starting the $executable with the parameter options." -Target $($params.ToArray() -join " ") Invoke-Process -Executable $executable -Params $params.ToArray() -ShowOriginalProgress:$ShowOriginalProgress -OutputCommandOnly:$OutputCommandOnly -LogPath $LogPath if (Test-PSFFunctionInterrupt) { Stop-PSFFunction -Message "Stopping because of 'ModelUtil.exe' failed its execution." -StepsUpward 1 return } Invoke-TimeSignal -End } <# .SYNOPSIS Invoke a process .DESCRIPTION Invoke a process and pass the needed parameters to it .PARAMETER Path Path to the program / executable that you want to start .PARAMETER Params Array of string parameters that you want to pass to the executable .PARAMETER LogPath The path where the log file(s) will be saved .PARAMETER ShowOriginalProgress Instruct the cmdlet to show the standard output in the console Default is $false which will silence the standard output .PARAMETER OutputCommandOnly Instruct the cmdlet to only output the command that you would have to execute by hand Will include full path to the executable and the needed parameters based on your selection .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Invoke-Process -Path "C:\AOSService\PackagesLocalDirectory\Bin\xppc.exe" -Params "-metadata=`"C:\AOSService\PackagesLocalDirectory\Bin`"", "-modelmodule=`"ApplicationSuite`"", "-output=`"C:\AOSService\PackagesLocalDirectory\Bin`"", "-referencefolder=`"C:\AOSService\PackagesLocalDirectory\Bin`"", "-log=`"C:\temp\d365fo.tools\ApplicationSuite\Dynamics.AX.$Module.xppc.log`"", "-xmlLog=`"C:\temp\d365fo.tools\ApplicationSuite\Dynamics.AX.ApplicationSuite.xppc.xml`"", "-verbose" This will invoke the "C:\AOSService\PackagesLocalDirectory\Bin\xppc.exe" executable. All parameters will be passed to it. The standard output will be redirected to a local variable. The error output will be redirected to a local variable. The standard output will be written to the verbose stream before exiting. If an error should occur, both the standard output and error output will be written to the console / host. .EXAMPLE PS C:\> Invoke-Process -ShowOriginalProgress -Path "C:\AOSService\PackagesLocalDirectory\Bin\xppc.exe" -Params "-metadata=`"C:\AOSService\PackagesLocalDirectory\Bin`"", "-modelmodule=`"ApplicationSuite`"", "-output=`"C:\AOSService\PackagesLocalDirectory\Bin`"", "-referencefolder=`"C:\AOSService\PackagesLocalDirectory\Bin`"", "-log=`"C:\temp\d365fo.tools\ApplicationSuite\Dynamics.AX.$Module.xppc.log`"", "-xmlLog=`"C:\temp\d365fo.tools\ApplicationSuite\Dynamics.AX.ApplicationSuite.xppc.xml`"", "-verbose" This will invoke the "C:\AOSService\PackagesLocalDirectory\Bin\xppc.exe" executable. All parameters will be passed to it. The standard output will be outputted directly to the console / host. The error output will be outputted directly to the console / host. .NOTES Author: M�tz Jensen (@Splaxi) #> function Invoke-Process { [CmdletBinding()] [OutputType()] param ( [Parameter(Mandatory = $true)] [Alias('Executable')] [string] $Path, [Parameter(Mandatory = $true)] [string[]] $Params, [string] $LogPath, [switch] $ShowOriginalProgress, [switch] $OutputCommandOnly, [switch] $EnableException ) Invoke-TimeSignal -Start if (-not (Test-PathExists -Path $Path -Type Leaf)) { return } if (Test-PSFFunctionInterrupt) { return } $tool = Split-Path -Path $Path -Leaf $pinfo = New-Object System.Diagnostics.ProcessStartInfo $pinfo.FileName = "$Path" $pinfo.WorkingDirectory = Split-Path -Path $Path -Parent if (-not $ShowOriginalProgress) { Write-PSFMessage -Level Verbose "Output and Error streams will be redirected (silence mode)" $pinfo.RedirectStandardError = $true $pinfo.RedirectStandardOutput = $true } $pinfo.UseShellExecute = $false $pinfo.Arguments = "$($Params -join " ")" $p = New-Object System.Diagnostics.Process $p.StartInfo = $pinfo Write-PSFMessage -Level Verbose "Starting the $tool" -Target "$($params -join " ")" if ($OutputCommandOnly) { Write-PSFMessage -Level Host "$Path $($pinfo.Arguments)" return } $p.Start() | Out-Null if (-not $ShowOriginalProgress) { $stdout = $p.StandardOutput.ReadToEnd() $stderr = $p.StandardError.ReadToEnd() } Write-PSFMessage -Level Verbose "Waiting for the $tool to complete" $p.WaitForExit() if ($p.ExitCode -ne 0 -and (-not $ShowOriginalProgress)) { Write-PSFMessage -Level Host "Exit code from $tool indicated an error happened. Will output both standard stream and error stream." Write-PSFMessage -Level Host "Standard output was: \r\n $stdout" Write-PSFMessage -Level Host "Error output was: \r\n $stderr" $messageString = "Stopping because an Exit Code from $tool wasn't 0 (zero) like expected." Stop-PSFFunction -Message "Stopping because of Exit Code." -Exception $([System.Exception]::new($($messageString -replace '<[^>]+>', ''))) -StepsUpward 1 return } else { Write-PSFMessage -Level Verbose "Standard output was: \r\n $stdout" } if ((-not $ShowOriginalProgress) -and (-not ([string]::IsNullOrEmpty($LogPath)))) { if (-not (Test-PathExists -Path $LogPath -Type Container -Create)) { return } $stdOutputPath = Join-Path -Path $LogPath -ChildPath "$tool`_StdOutput.log" $errOutputPath = Join-Path -Path $LogPath -ChildPath "$tool`_ErrOutput.log" $stdout | Out-File -FilePath $stdOutputPath -Encoding utf8 -Force $stderr | Out-File -FilePath $errOutputPath -Encoding utf8 -Force } Invoke-TimeSignal -End } <# .SYNOPSIS Backup & Restore SQL Server database .DESCRIPTION Backup a database and restore it back into the SQL Server .PARAMETER DatabaseServer The name of the database server If on-premises or classic SQL Server, use either short name og Fully Qualified Domain Name (FQDN). If Azure use the full address to the database server, e.g. server.database.windows.net .PARAMETER DatabaseName The name of the database .PARAMETER SqlUser The login name for the SQL Server instance .PARAMETER SqlPwd The password for the SQL Server user .PARAMETER TrustedConnection Should the connection use a Trusted Connection or not .PARAMETER NewDatabaseName Name of the new (restored) database .PARAMETER BackupDirectory Path to a directory that can store the backup file .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Invoke-SqlBackupRestore -DatabaseServer localhost -DatabaseName AxDB -SqlUser User123 -SqlPwd "Password123" -NewDatabaseName "ExportClone" -BackupDirectory "C:\temp\d365fo.tools\sqlbackup" This will backup the AxDB database and place the backup file inside the "c:\temp\d365fo.tools\sqlbackup" directory. The backup file will the be used to restore into a new database named "ExportClone". .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> Function Invoke-SqlBackupRestore { [CmdletBinding()] [OutputType('System.Boolean')] param ( [Parameter(Mandatory = $true)] [string] $DatabaseServer, [Parameter(Mandatory = $true)] [string] $DatabaseName, [Parameter(Mandatory = $false)] [string] $SqlUser, [Parameter(Mandatory = $false)] [string] $SqlPwd, [Parameter(Mandatory = $false)] [boolean] $TrustedConnection, [Parameter(Mandatory = $true)] [string] $NewDatabaseName, [Parameter(Mandatory = $true)] [string] $BackupDirectory, [switch] $EnableException ) Invoke-TimeSignal -Start $Params = @{DatabaseServer = $DatabaseServer; DatabaseName = $DatabaseName; SqlUser = $SqlUser; SqlPwd = $SqlPwd; TrustedConnection = $TrustedConnection; } $sqlCommand = Get-SQLCommand @Params $sqlCommand.CommandText = (Get-Content "$script:ModuleRoot\internal\sql\backuprestoredb.sql") -join [Environment]::NewLine $null = $sqlCommand.Parameters.Add("@CurrentDatabase", $DatabaseName) $null = $sqlCommand.Parameters.Add("@NewName", $NewDatabaseName) $null = $sqlCommand.Parameters.Add("@BackupDirectory", $BackupDirectory) try { Write-PSFMessage -Level InternalComment -Message "Executing a script against the database." -Target (Get-SqlString $SqlCommand) $sqlCommand.Connection.Open() $null = $sqlCommand.ExecuteNonQuery() $true } catch { $messageString = "Something went wrong while doing <c='em'>backup / restore</c> against the database." Write-PSFMessage -Level Host -Message $messageString -Exception $PSItem.Exception -Target (Get-SqlString $SqlCommand) Stop-PSFFunction -Message "Stopping because of errors." -Exception $([System.Exception]::new($($messageString -replace '<[^>]+>', ''))) -ErrorRecord $_ return } finally { $sqlCommand.Connection.Close() $sqlCommand.Dispose() } Invoke-TimeSignal -End } <# .SYNOPSIS Invoke the sqlpackage executable .DESCRIPTION Invoke the sqlpackage executable and pass the necessary parameters to it .PARAMETER Action Can either be import or export .PARAMETER DatabaseServer The name of the database server If on-premises or classic SQL Server, use either short name og Fully Qualified Domain Name (FQDN). If Azure use the full address to the database server, e.g. server.database.windows.net .PARAMETER DatabaseName The name of the database .PARAMETER SqlUser The login name for the SQL Server instance .PARAMETER SqlPwd The password for the SQL Server user .PARAMETER TrustedConnection Should the sqlpackage work with TrustedConnection or not .PARAMETER FilePath Path to the file, used for either import or export .PARAMETER Properties Array of all the properties that needs to be parsed to the sqlpackage.exe .PARAMETER DiagnosticFile Path to where you want the SqlPackage to output a diagnostics file to assist you in troubleshooting .PARAMETER ModelFile Path to the model file that you want the SqlPackage.exe to use instead the one being part of the bacpac file This is used to override SQL Server options, like collation and etc .PARAMETER MaxParallelism Sets SqlPackage.exe's degree of parallelism for concurrent operations running against a database. The default value is 8. .PARAMETER LogPath The path where the log file(s) will be saved .PARAMETER ShowOriginalProgress Instruct the cmdlet to show the standard output in the console Default is $false which will silence the standard output .PARAMETER OutputCommandOnly Instruct the cmdlet to only output the command that you would have to execute by hand Will include full path to the executable and the needed parameters based on your selection .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> $BaseParams = @{ DatabaseServer = $DatabaseServer DatabaseName = $DatabaseName SqlUser = $SqlUser SqlPwd = $SqlPwd } PS C:\> $ImportParams = @{ Action = "import" FilePath = $BacpacFile } PS C:\> Invoke-SqlPackage @BaseParams @ImportParams This will start the sqlpackage.exe file and pass all the needed parameters. .NOTES Author: M�tz Jensen (@splaxi) #> function Invoke-SqlPackage { [CmdletBinding()] [OutputType([System.Boolean])] param ( [ValidateSet('Import', 'Export')] [string] $Action, [string] $DatabaseServer, [string] $DatabaseName, [string] $SqlUser, [string] $SqlPwd, [string] $TrustedConnection, [string] $FilePath, [string[]] $Properties, [string] $DiagnosticFile, [string] $ModelFile, [string] $MaxParallelism, [string] $LogPath, [switch] $ShowOriginalProgress, [switch] $OutputCommandOnly, [switch] $EnableException ) $executable = $Script:SqlPackagePath Invoke-TimeSignal -Start if (!(Test-PathExists -Path $executable -Type Leaf)) { return } Write-PSFMessage -Level Verbose -Message "Starting to prepare the parameters for sqlpackage.exe" [System.Collections.ArrayList]$Params = New-Object -TypeName "System.Collections.ArrayList" if ($Action -eq "export") { $null = $Params.Add("/Action:export") $null = $Params.Add("/SourceServerName:$DatabaseServer") $null = $Params.Add("/SourceDatabaseName:$DatabaseName") $null = $Params.Add("/TargetFile:`"$FilePath`"") $null = $Params.Add("/Properties:CommandTimeout=0") if (!$UseTrustedConnection) { $null = $Params.Add("/SourceUser:$SqlUser") $null = $Params.Add("/SourcePassword:$SqlPwd") } Remove-Item -Path $FilePath -ErrorAction SilentlyContinue -Force } else { $null = $Params.Add("/Action:import") $null = $Params.Add("/TargetServerName:$DatabaseServer") $null = $Params.Add("/TargetDatabaseName:$DatabaseName") $null = $Params.Add("/SourceFile:`"$FilePath`"") $null = $Params.Add("/Properties:CommandTimeout=0") if (!$UseTrustedConnection) { $null = $Params.Add("/TargetUser:$SqlUser") $null = $Params.Add("/TargetPassword:$SqlPwd") } } foreach ($item in $Properties) { $null = $Params.Add("/Properties:$item") } if (-not [system.string]::IsNullOrEmpty($DiagnosticFile)) { $null = $Params.Add("/Diagnostics:true") $null = $Params.Add("/DiagnosticsFile:`"$DiagnosticFile`"") } if (-not [system.string]::IsNullOrEmpty($ModelFile)) { $null = $Params.Add("/ModelFilePath:`"$ModelFile`"") } if (-not [system.string]::IsNullOrEmpty($MaxParallelism)) { $null = $Params.Add("/mp:`"$MaxParallelism`"") } Invoke-Process -Executable $executable -Params $params -ShowOriginalProgress:$ShowOriginalProgress -OutputCommandOnly:$OutputCommandOnly -LogPath $LogPath if (Test-PSFFunctionInterrupt) { Write-PSFMessage -Level Critical -Message "The SqlPackage.exe exited with an error." Stop-PSFFunction -Message "Stopping because of errors." -StepsUpward 1 return } Invoke-TimeSignal -End } <# .SYNOPSIS Handle time measurement .DESCRIPTION Handle time measurement from when a cmdlet / function starts and ends Will write the output to the verbose stream (Write-PSFMessage -Level Verbose) .PARAMETER Start Switch to instruct the cmdlet that a start time registration needs to take place .PARAMETER End Switch to instruct the cmdlet that a time registration has come to its end and it needs to do the calculation .EXAMPLE PS C:\> Invoke-TimeSignal -Start This will start the time measurement for any given cmdlet / function .EXAMPLE PS C:\> Invoke-TimeSignal -End This will end the time measurement for any given cmdlet / function. The output will go into the verbose stream. .NOTES Author: M�tz Jensen (@Splaxi) #> function Invoke-TimeSignal { [CmdletBinding(DefaultParameterSetName = 'Start')] param ( [Parameter(Mandatory = $true, ParameterSetName = 'Start', Position = 1 )] [switch] $Start, [Parameter(Mandatory = $True, ParameterSetName = 'End', Position = 2 )] [switch] $End ) $Time = (Get-Date) $Command = (Get-PSCallStack)[1].Command if ($Start) { if ($Script:TimeSignals.ContainsKey($Command)) { Write-PSFMessage -Level Verbose -Message "The command '$Command' was already taking part in time measurement. The entry has been update with current date and time." $Script:TimeSignals[$Command] = $Time } else { $Script:TimeSignals.Add($Command, $Time) } } else { if ($Script:TimeSignals.ContainsKey($Command)) { $TimeSpan = New-TimeSpan -End $Time -Start (($Script:TimeSignals)[$Command]) Write-PSFMessage -Level Verbose -Message "Total time spent inside the function was $TimeSpan" -Target $TimeSpan -FunctionName $Command -Tag "TimeSignal" $null = $Script:TimeSignals.Remove($Command) } else { Write-PSFMessage -Level Verbose -Message "The command '$Command' was never started to take part in time measurement." } } } <# .SYNOPSIS Creates a new Azure Active Directory (AAD) application .DESCRIPTION Creates a new Azure Active Directory (AAD) application in a Dynamics 365 for Finance & Operations instance .PARAMETER sqlCommand The SQL Command object that should be used when creating the new application .PARAMETER Name The name that the imported application should have inside the D365FO environment .PARAMETER UserId The id of the user linked to the application inside the D365FO environment .PARAMETER ClientId The Client ID that the imported application should use inside the D365FO environment .EXAMPLE PS C:\> $SqlCommand = Get-SqlCommand -DatabaseServer localhost -DatabaseName AxDB -SqlUser User123 -SqlPwd "Password123" PS C:\> New-D365FOAadApplication -SqlCommand $SqlCommand -Name "Application1" -UserId "admin" -ClientId "aef2e67c-64a3-4c72-9294-d288c5bf503d" This will get a SqlCommand object that will connect to the localhost server and the AXDB database, with the sql credential "User123". The SqlCommand object is passed to the New-D365FOAadApplication along with all the necessary details for importing Application1 as an application linked to user admin into the D365FO environment. .NOTES Author: Gert Van Der Heyden (@gertvdheyden) #> function New-D365FOAadApplication { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseShouldProcessForStateChangingFunctions", "")] Param ( [System.Data.SqlClient.SqlCommand] $SqlCommand, [string] $Name, [string] $UserId, [string] $ClientId ) $sqlCommand.CommandText = (Get-Content "$script:ModuleRoot\internal\sql\Add-AadApplicationIntoD365FO.sql") -join [Environment]::NewLine Write-PSFMessage -Level Verbose -Message "Adding Application : $Name,$UserId,$ClientId" $null = $sqlCommand.Parameters.Add("@Name", $Name) $null = $sqlCommand.Parameters.Add("@UserId", $UserId) $null = $sqlCommand.Parameters.Add("@ClientId", $ClientId) Write-PSFMessage -Level Verbose -Message "Creating the application in database" Write-PSFMessage -Level InternalComment -Message "Executing a script against the database." -Target (Get-SqlString $SqlCommand) $null = $sqlCommand.ExecuteNonQuery() Write-PSFMessage -Level Verbose -Message "Added application" } <# .SYNOPSIS Creates a new user .DESCRIPTION Creates a new user in a Dynamics 365 for Finance & Operations instance .PARAMETER sqlCommand The SQL Command object that should be used when creating the new user .PARAMETER SignInName The sign in name (email address) for the user that you want the SID from .PARAMETER Name The name that the imported user should have inside the D365FO environment .PARAMETER Id The ID that the imported user should have inside the D365FO environment .PARAMETER SID The SID that correlates to the imported user inside the D365FO environment .PARAMETER StartUpCompany The default company (legal entity) for the imported user .PARAMETER IdentityProvider The provider for the imported to validated against .PARAMETER NetworkDomain The network domain of the imported user .PARAMETER ObjectId The Azure Active Directory object id for the imported user .PARAMETER Language Language that should be configured for the user, for when they sign-in to the D365 environment .EXAMPLE PS C:\> $SqlCommand = Get-SqlCommand -DatabaseServer localhost -DatabaseName AxDB -SqlUser User123 -SqlPwd "Password123" PS C:\> New-D365FOUser -SqlCommand $SqlCommand -SignInName "Claire@contoso.com" -Name "Claire" -Id "claire" -SID "123XYZ" -StartupCompany "DAT" -IdentityProvider "XYZ" -NetworkDomain "Contoso.com" -ObjectId "123XYZ" This will get a SqlCommand object that will connect to the localhost server and the AXDB databae, with the sql credential "User123". The SqlCommand object is passed to the Import-AadUserIntoD365FO along with all the necessary details for importing Claire@contoso.com as an user into the D365FO environment. .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function New-D365FOUser { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseShouldProcessForStateChangingFunctions", "")] Param ( [System.Data.SqlClient.SqlCommand] $SqlCommand, [string] $SignInName, [string] $Name, [string] $Id, [string] $SID, [string] $StartUpCompany, [string] $IdentityProvider, [string] $NetworkDomain, [string] $ObjectId, [string] $Language ) $sqlCommand.CommandText = (Get-Content "$script:ModuleRoot\internal\sql\Add-AadUserIntoD365FO.sql") -join [Environment]::NewLine Write-PSFMessage -Level Verbose -Message "Adding User : $SignInName,$Name,$Id,$SID,$StartUpCompany,$IdentityProvider,$NetworkDomain" $null = $sqlCommand.Parameters.Add("@SignInName", $SignInName) $null = $sqlCommand.Parameters.Add("@Name", $Name) $null = $sqlCommand.Parameters.Add("@SID", $SID) $null = $sqlCommand.Parameters.Add("@NetworkDomain", $NetworkDomain) $null = $sqlCommand.Parameters.Add("@IdentityProvider", $IdentityProvider) $null = $sqlCommand.Parameters.Add("@StartUpCompany", $StartUpCompany) $null = $sqlCommand.Parameters.Add("@Id", $Id) $null = $sqlCommand.Parameters.Add("@ObjectId", $ObjectId) $null = $sqlCommand.Parameters.Add("@Language", $Language) Write-PSFMessage -Level Verbose -Message "Creating the user in database" Write-PSFMessage -Level InternalComment -Message "Executing a script against the database." -Target (Get-SqlString $SqlCommand) $rowsCreated = $sqlCommand.ExecuteScalar() Write-PSFMessage -Level Verbose -Message "Rows inserted $rowsCreated for user $SignInName" $SqlCommand.Parameters.Clear() $rowsCreated -eq 1 } <# .SYNOPSIS Create a new self signed certificate .DESCRIPTION Create a new self signed certificate and have it password protected .PARAMETER CertificateFileName Path to the location where you want to store the CER file for the certificate .PARAMETER PrivateKeyFileName Path to the location where you want to store the PFX file for the certificate .PARAMETER Password The password that you want to use to protect your different certificates with .EXAMPLE PS C:\> New-D365SelfSignedCertificate -CertificateFileName "C:\temp\d365fo.tools\TestAuth.cer" -PrivateKeyFileName "C:\temp\d365fo.tools\TestAuth.pfx" -Password (ConvertTo-SecureString -String "pass@word1" -Force -AsPlainText) This will generate a new CER certificate that is stored at "C:\temp\d365fo.tools\TestAuth.cer". This will generate a new PFX certificate that is stored at "C:\temp\d365fo.tools\TestAuth.pfx". Both certificates will be password protected with "pass@word1". .NOTES Author: Kenny Saelen (@kennysaelen) Author: M�tz Jensen (@Splaxi) #> function New-D365SelfSignedCertificate { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidUsingConvertToSecureStringWithPlainText", "")] [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseShouldProcessForStateChangingFunctions", "")] [CmdletBinding()] param ( [Parameter(Mandatory = $false, Position = 1)] [string] $CertificateFileName = (Join-Path $env:TEMP "TestAuthCert.cer"), [Parameter(Mandatory = $false, Position = 2)] [string] $PrivateKeyFileName = (Join-Path $env:TEMP "TestAuthCert.pfx"), [Parameter(Mandatory = $false, Position = 3)] [Security.SecureString] $Password = (ConvertTo-SecureString -String "Password1" -Force -AsPlainText) ) try { # First generate a self-signed certificate and place it in the local store on the machine $certificate = New-SelfSignedCertificate -dnsname 127.0.0.1 -CertStoreLocation cert:\LocalMachine\My -FriendlyName "D365 Automated testing certificate" -Provider "Microsoft Strong Cryptographic Provider" $certificatePath = 'cert:\localMachine\my\' + $certificate.Thumbprint # Export the private key Export-PfxCertificate -cert $certificatePath -FilePath $PrivateKeyFileName -Password $Password # Import the certificate into the local machine's trusted root certificates store $importedCertificate = Import-PfxCertificate -FilePath $PrivateKeyFileName -CertStoreLocation Cert:\LocalMachine\Root -Password $Password } catch { Write-PSFMessage -Level Host -Message "Something went wrong while generating the self-signed certificate and installing it into the local machine's trusted root certificates store." -Exception $PSItem.Exception Stop-PSFFunction -Message "Stopping because of errors" -StepsUpward 1 return } return $importedCertificate } <# .SYNOPSIS Decrypt web.config file .DESCRIPTION Utilize the built in encryptor utility to decrypt the web.config file from inside the AOS .PARAMETER File Path to the file that you want to work against Please be careful not to point to the original file from inside the AOS directory .PARAMETER DropPath Path to the directory where you want save the file after decryption is completed .EXAMPLE PS C:\> New-DecryptedFile -File "C:\temp\d365fo.tools\web.config" -DropPath "c:\temp\d365fo.tools\decrypted.config" This will take the "C:\temp\d365fo.tools\web.config" and decrypt it. After decryption the output file will be stored in "c:\temp\d365fo.tools\decrypted.config". .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function New-DecryptedFile { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseShouldProcessForStateChangingFunctions", "")] Param ( [string] $File, [string] $DropPath ) $Decrypter = Join-Path $AosServiceWebRootPath -ChildPath "bin\Microsoft.Dynamics.AX.Framework.ConfigEncryptor.exe" if (-not (Test-PathExists -Path $Decrypter -Type Leaf)) { return } $fileInfo = [System.IO.FileInfo]::new($File) $DropFile = Join-Path $DropPath $FileInfo.Name Write-PSFMessage -Level Verbose -Message "Extracted file path is: $DropFile" -Target $DropFile Copy-Item $File $DropFile -Force -ErrorAction Stop if (-not (Test-PathExists -Path $DropFile -Type Leaf)) { return } & $Decrypter -decrypt $DropFile } <# .SYNOPSIS Create a new Json HttpRequestMessage .DESCRIPTION Create a new HttpRequestMessage with the ContentType = application/json .PARAMETER Uri The URI / URL for the web site you want to work against .PARAMETER Token The token that contains the needed authorization permission .PARAMETER Content The content that you want to include in the HttpRequestMessage .PARAMETER HttpMethod The method of the HTTP request you wanne make Valid options are: GET POST .EXAMPLE PS C:\> New-JsonRequest -Token "Bearer JldjfafLJdfjlfsalfd..." -Uri "https://lcsapi.lcs.dynamics.com/box/fileasset/CommitFileAsset/123456789?assetId=958ae597-f089-4811-abbd-c1190917eaae" This will create a new HttpRequestMessage what will work against the "https://lcsapi.lcs.dynamics.com/box/fileasset/CommitFileAsset/123456789?assetId=958ae597-f089-4811-abbd-c1190917eaae". It attaches the Token "Bearer JldjfafLJdfjlfsalfd..." to the request. .NOTES Tags: Json, Http, HttpRequestMessage, POST Author: M�tz Jensen (@Splaxi) #> function New-JsonRequest { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseShouldProcessForStateChangingFunctions", "")] [CmdletBinding()] [OutputType()] param ( [Parameter(Mandatory = $true)] [string] $Uri, [Parameter(Mandatory = $true)] [string] $Token, [Parameter(Mandatory = $false)] [string] $Content, [Parameter(Mandatory = $false)] [ValidateSet('POST', 'GET')] [string] $HttpMethod = "POST" ) $httpMethodObject = [System.Net.Http.HttpMethod]::New($HttpMethod) Write-PSFMessage -Level Verbose -Message "Building a HttpRequestMessage." -Target $Uri $request = New-Object -TypeName System.Net.Http.HttpRequestMessage -ArgumentList @($httpMethodObject, $Uri) if (-not ($Content -eq "")) { Write-PSFMessage -Level Verbose -Message "Adding content to the HttpRequestMessage." -Target $Content $request.Content = New-Object -TypeName System.Net.Http.StringContent -ArgumentList @($Content, [System.Text.Encoding]::UTF8, "application/json") } Write-PSFMessage -Level Verbose -Message "Adding Authorization token to the HttpRequestMessage." -Target $Token $request.Headers.Authorization = $Token $request } <# .SYNOPSIS Get a web request object .DESCRIPTION Get a prepared web request object with all necessary headers and tokens in place .PARAMETER RequestUrl The URL you want to work against .PARAMETER AuthorizationHeader The Authorization Header object that you want to use for you web request .PARAMETER Action The HTTP action you want to preform .EXAMPLE PS C:\> New-WebRequest -RequestUrl "https://login.windows.net/contoso/.well-known/openid-configuration" -AuthorizationHeader $null -Action GET This will create a new web request object that will work against the "https://login.windows.net/contoso/.well-known/openid-configuration" URL. The HTTP action is GET and in this case we don't need an Authorization Header in place. .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function New-WebRequest { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseShouldProcessForStateChangingFunctions", "")] param ( $RequestUrl, $AuthorizationHeader, $Action ) Write-PSFMessage -Level Verbose -Message "New Request $RequestUrl, $Action" $request = [System.Net.WebRequest]::Create($RequestUrl) if ($null -ne $AuthorizationHeader) { $request.Headers["Authorization"] = $AuthorizationHeader.CreateAuthorizationHeader() } $request.Method = $Action $request } <# .SYNOPSIS Rename the value in the web.config file .DESCRIPTION Replace the old value with the new value inside a web.config file .PARAMETER File Path to the file that you want to update/rename/replace .PARAMETER NewValue The new value that replaces the old value .PARAMETER OldValue The old value that needs to be replaced .EXAMPLE PS C:\> Rename-ConfigValue -File "C:\temp\d365fo.tools\web.config" -NewValue "Demo-8.1" -OldValue "usnconeboxax1aos" This will open the "C:\temp\d365fo.tools\web.config" file and replace all "usnconeboxax1aos" entries with "Demo-8.1" .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function Rename-ConfigValue { param ( [string] $File, [string] $NewValue, [string] $OldValue ) Write-PSFMessage -Level Verbose -Message "Replace content from $File. Old value is $OldValue. New value is $NewValue." -Target (@($File, $OldValue, $NewValue)) (Get-Content $File).replace($OldValue, $NewValue) | Set-Content $File } <# .SYNOPSIS Short description .DESCRIPTION Long description .PARAMETER InputObject Parameter description .PARAMETER Property Parameter description .PARAMETER ExcludeProperty Parameter description .PARAMETER TypeName Parameter description .EXAMPLE PS C:\> Select-DefaultView -InputObject $result -Property CommandName, Synopsis This will help you do it right. .NOTES Author: M�tz Jensen (@Splaxi) #> function Select-DefaultView { <# This command enables us to send full on objects to the pipeline without the user seeing it a lot of this is from boe, thanks boe! https://learn-powershell.net/2013/08/03/quick-hits-set-the-default-property-display-in-powershell-on-custom-objects/ TypeName creates a new type so that we can use ps1xml to modify the output #> [CmdletBinding()] param ( [parameter(ValueFromPipeline)] [object] $InputObject, [string[]] $Property, [string[]] $ExcludeProperty, [string] $TypeName ) process { if ($null -eq $InputObject) { return } if ($TypeName) { $InputObject.PSObject.TypeNames.Insert(0, "d365fo.tools.$TypeName") } if ($ExcludeProperty) { if ($InputObject.GetType().Name.ToString() -eq 'DataRow') { $ExcludeProperty += 'Item', 'RowError', 'RowState', 'Table', 'ItemArray', 'HasErrors' } $props = ($InputObject | Get-Member | Where-Object MemberType -in 'Property', 'NoteProperty', 'AliasProperty' | Where-Object { $_.Name -notin $ExcludeProperty }).Name $defaultset = New-Object System.Management.Automation.PSPropertySet('DefaultDisplayPropertySet', [string[]]$props) } else { # property needs to be string if ("$property" -like "* as *") { $newproperty = @() foreach ($p in $property) { if ($p -like "* as *") { $old, $new = $p -isplit " as " # Do not be tempted to not pipe here $inputobject | Add-Member -Force -MemberType AliasProperty -Name $new -Value $old -ErrorAction SilentlyContinue $newproperty += $new } else { $newproperty += $p } } $property = $newproperty } $defaultset = New-Object System.Management.Automation.PSPropertySet('DefaultDisplayPropertySet', [string[]]$Property) } $standardmembers = [System.Management.Automation.PSMemberInfo[]]@($defaultset) # Do not be tempted to not pipe here $inputobject | Add-Member -Force -MemberType MemberSet -Name PSStandardMembers -Value $standardmembers -ErrorAction SilentlyContinue $inputobject } } <# .SYNOPSIS Provision an user to be the administrator of a Dynamics 365 for Finance & Operations environment .DESCRIPTION Provision an user to be the administrator by using the supplied tools from Microsoft (AdminUserProvisioning.exe) .PARAMETER SignInName The sign in name (email address) for the user that you want to be the administrator .PARAMETER DatabaseServer The name of the database server If on-premises or classic SQL Server, use either short name og Fully Qualified Domain Name (FQDN). If Azure use the full address to the database server, e.g. server.database.windows.net .PARAMETER DatabaseName The name of the database .PARAMETER SqlUser The login name for the SQL Server instance .PARAMETER SqlPwd The password for the SQL Server user .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Set-AdminUser -SignInName "Claire@contoso.com" -DatabaseServer localhost -DatabaseName AxDB -SqlUser User123 -SqlPwd "Password123" This will provision the user with the e-mail "Claire@contoso.com" to be the administrator of the D365 for Finance & Operations instance. It will handle if the tenant is switching also, and update the necessary details. .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) Author: Mark Furrer (@devax_mf) #> function Set-AdminUser { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseShouldProcessForStateChangingFunctions", "")] Param ( [string] $SignInName, [string] $DatabaseServer, [string] $DatabaseName, [string] $SqlUser, [string] $SqlPwd, [switch] $EnableException ) $WebConfigFile = Join-Path $Script:AOSPath $Script:WebConfig $MetaDataNode = Select-Xml -XPath "/configuration/appSettings/add[@key='Aos.MetadataDirectory']/@value" -Path $WebConfigFile $MetaDataNodeDirectory = $MetaDataNode.Node.Value Write-PSFMessage -Level Verbose -Message "MetaDataDirectory: $MetaDataNodeDirectory" -Target $MetaDataNodeDirectory $AdminFileLocationPu29AndUp = "$MetaDataNodeDirectory\Bin\Microsoft.Dynamics.AdminUserProvisioningLib.dll" $AdminFileLocationBeforePu29 = "$MetaDataNodeDirectory\Bin\AdminUserProvisioning.exe" if ( Test-Path -Path $AdminFileLocationPu29AndUp -PathType Leaf ) { $AdminFile = $AdminFileLocationPu29AndUp $AdminLibNameSpace = "Microsoft.Dynamics.AdminUserProvisioningLib" } else { $AdminFile = $AdminFileLocationBeforePu29 $AdminLibNameSpace = "Microsoft.Dynamics.AdminUserProvisioning" } Write-PSFMessage -Level Verbose -Message "Path to AdminFile: $AdminFile" $TempFileName = New-TemporaryFile $TempFileName = $TempFileName.BaseName $AdminDll = "$env:TEMP\$TempFileName.dll" copy-item -Path $AdminFile -Destination $AdminDll $adminAssembly = [System.Reflection.Assembly]::LoadFile($AdminDll) $AdminUserUpdater = $adminAssembly.GetType("$AdminLibNameSpace.AdminUserUpdater") $PublicBinding = [System.Reflection.BindingFlags]::Public $StaticBinding = [System.Reflection.BindingFlags]::Static $CombinedBinding = $PublicBinding -bor $StaticBinding $UpdateAdminUser = $AdminUserUpdater.GetMethod("UpdateAdminUser", $CombinedBinding) Write-PSFMessage -Level Verbose -Message "Adjusting parameter set to the PU that is in use in this environment." if((($UpdateAdminUser.GetParameters()).Name) -contains "hostUrl") { Write-PSFMessage -Level Verbose -Message "PU29 or higher found. Will adjust parameters." $params = $SignInName, "AAD-Global", $null, $null, $DatabaseServer, $DatabaseName, $SqlUser, $SqlPwd, "$Script:AOSPath\", $Script:Url } elseif((($UpdateAdminUser.GetParameters()).Name) -contains "providerName") { Write-PSFMessage -Level Verbose -Message "PU26/27/28 found. Will adjust parameters." $params = $SignInName, "AAD-Global", $null, $null, $DatabaseServer, $DatabaseName, $SqlUser, $SqlPwd } else { Write-PSFMessage -Level Verbose -Message "PU below PU26 found. Will adjust parameters." $params = $SignInName, $null, $null, $DatabaseServer, $DatabaseName, $SqlUser, $SqlPwd } try { $paramsString = $params -join ", " Write-PSFMessage -Level Verbose -Message "Updating Admin using the values $paramsString" $UpdateAdminUser.Invoke($null, $params) } catch { $messageString = "Something went wrong while <c='em'>provisioning</c> the environment to the new administrator: $SignInName." Write-PSFMessage -Level Host -Message $messageString -Exception $PSItem.Exception -Target $SignInName Stop-PSFFunction -Message "Stopping because of errors." -Exception $([System.Exception]::new($($messageString -replace '<[^>]+>', ''))) -ErrorRecord $_ -StepsUpward 1 return } } <# .SYNOPSIS Change the different Azure SQL Database details .DESCRIPTION When preparing an Azure SQL Database to be the new database for an Tier 2+ environment you need to set different details .PARAMETER DatabaseServer The name of the database server If on-premises or classic SQL Server, use either short name og Fully Qualified Domain Name (FQDN). If Azure use the full address to the database server, e.g. server.database.windows.net .PARAMETER DatabaseName The name of the database .PARAMETER SqlUser The login name for the SQL Server instance .PARAMETER SqlPwd The password for the SQL Server user .PARAMETER AxDeployExtUserPwd Password obtained from LCS .PARAMETER AxDbAdminPwd Password obtained from LCS .PARAMETER AxRuntimeUserPwd Password obtained from LCS .PARAMETER AxMrRuntimeUserPwd Password obtained from LCS .PARAMETER AxRetailRuntimeUserPwd Password obtained from LCS .PARAMETER AxRetailDataSyncUserPwd Password obtained from LCS .PARAMETER AxDbReadonlyUserPwd Password obtained from LCS .PARAMETER TenantId The ID of tenant that the Azure SQL Database instance is going to be run under .PARAMETER PlanId The ID of the type of plan that the Azure SQL Database is going to be using .PARAMETER PlanCapability The capabilities that the Azure SQL Database instance will be running with .PARAMETER EnableException This parameters disables user-friendly warnings and enables the throwing of exceptions This is less user friendly, but allows catching exceptions in calling scripts .EXAMPLE PS C:\> Set-AzureBacpacValues -DatabaseServer dbserver1.database.windows.net -DatabaseName Import -SqlUser User123 -SqlPwd "Password123" -AxDeployExtUserPwd "Password123" -AxDbAdminPwd "Password123" -AxRuntimeUserPwd "Password123" -AxMrRuntimeUserPwd "Password123" -AxRetailRuntimeUserPwd "Password123" -AxRetailDataSyncUserPwd "Password123" -AxDbReadonlyUserPwd "Password123" -TenantId "TenantIdFromAzure" -PlanId "PlanIdFromAzure" -PlanCapability "Capabilities" This will set all the needed details inside the "Import" database that is located in the "dbserver1.database.windows.net" Azure SQL Database instance. All service accounts and their passwords will be updated accordingly. .NOTES Author: Rasmus Andersen (@ITRasmus) Author: M�tz Jensen (@Splaxi) #> function Set-AzureBacpacValues { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseShouldProcessForStateChangingFunctions", "")] [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns", "")] [CmdletBinding()] [OutputType([System.Boolean])] param ( [Parameter(Mandatory = $true)] [string] $DatabaseServer, [Parameter(Mandatory = $true)] [string] $DatabaseName, [Parameter(Mandatory = $true)] [string] $SqlUser, [Parameter(Mandatory = $true)] [string] $SqlPwd, [Parameter(Mandatory = $true)] [string] $AxDeployExtUserPwd, [Parameter(Mandatory = $true)] [string] $AxDbAdminPwd, [Parameter(Mandatory = $true)] [string] $AxRuntimeUserPwd, [Parameter(Mandatory = $true)] [string] $AxMrRuntimeUserPwd, [Parameter(Mandatory = $true)] [string] $AxRetailRuntimeUserPwd, [Parameter(Mandatory = $true)] [string] $AxRetailDataSyncUserPwd, [Parameter(Mandatory = $true)] [string] $AxDbReadonlyUserPwd, [Parameter(Mandatory = $true)] [string] $TenantId, [Parameter(Mandatory = $true)] [string] $PlanId, [Parameter(Mandatory = $true)] [string] $PlanCapability, [switch] $EnableException ) $sqlCommand = Get-SQLCommand -DatabaseServer $DatabaseServer -DatabaseName $DatabaseName -SqlUser $SqlUser -SqlPwd $SqlPwd -TrustedConnection $false $commandText = (Get-Content "$script:ModuleRoot\internal\sql\set-bacpacvaluesazure.sql") -join [Environment]::NewLine $commandText = $commandText.Replace('@axdeployextuser', $AxDeployExtUserPwd) $commandText = $commandText.Replace('@axdbadmin', $AxDbAdminPwd) $commandText = $commandText.Replace('@axruntimeuser', $AxRuntimeUserPwd) $commandText = $commandText.Replace('@axmrruntimeuser', $AxMrRuntimeUserPwd) $commandText = $commandText.Replace('@axretailruntimeuser', $AxRetailRuntimeUserPwd) $commandText = $commandText.Replace('@axretaildatasyncuser', $AxRetailDataSyncUserPwd) $commandText = $commandText.Replace('@axdbreadonlyuser', $AxDbReadonlyUserPwd) $sqlCommand.CommandText = $commandText $null = $sqlCommand.Parameters.Add("@TenantId", $TenantId) $null = $sqlCommand.Parameters.Add("@PlanId", $PlanId) $null = $sqlCommand.Parameters.Add("@PlanCapability ", $PlanCapability) try { Write-PSFMessage -Level InternalComment -Message "Executing a script against the database." -Target (Get-SqlString $SqlCommand) $sqlCommand.Connection.Open() $null = $sqlCommand.ExecuteNonQuery() $true } catch { $messageString = "Something went wrong while working against the database." Write-PSFMessage -Level Host -Message $messageString -Exception $PSItem.Exception -Target (Get-SqlString $SqlCommand) Stop-PSFFunction -Message "Stopping because of errors." -Exception $([System.Exception]::new($($messageString -replace '<[^>]+>', ''))) -ErrorRecord $_ return } finally { if ($sqlCommand.Connection.State -ne [System.Data.ConnectionState]::Closed) { $sqlCommand.Connection.Close() } $sqlCommand.Dispose() } } <# .SYNOPSIS & |