Giter Club home page Giter Club logo

Comments (22)

Splaxi avatar Splaxi commented on July 28, 2024 1

It gives the same end result.

But I believe we fixed it with a different testing / checking mechanism. Will update the cmdlet tomorrow with the latest and hope it works as planned 😊

from d365fo.tools.

Splaxi avatar Splaxi commented on July 28, 2024

Hi,

You shouldn't have to worry about that, if you run an elevated powershell console (Run As Administrator)

Is that not possible for your scenario?

from d365fo.tools.

skaue avatar skaue commented on July 28, 2024

Most, but not all. There are still a couple of MS hosted environments around. What is really nice with having no dependencies to those passwords is being able to quickly get going with some operations without having to go to LCS and gather the credentials. Sure, for most scenarios, you'll save your scripts for reuse, and credentials will be saved in your saved scripts. So two-fold, MS hosted VM support, and reducing the hazzle of the LCS detour. ;-)

from d365fo.tools.

Splaxi avatar Splaxi commented on July 28, 2024

We will give it a try. We have only seen tier 2 ms hosted where elevated wasn't a option. But we haven't seen all, that is why we need the feedback 😊

from d365fo.tools.

skaue avatar skaue commented on July 28, 2024

Currently, you need elevated to read out parts of the environment information, like the bin dir.
If find this approach to discover that information interesting:
https://gist.github.com/JohnLBevan/ad112826790fd2148bf43c3b5602ec4b

It can be done without elevation, however, I suspect it will not work for local VHDs.

from d365fo.tools.

Splaxi avatar Splaxi commented on July 28, 2024

Maybe some of the info is present in the registry. We'll see what we can do 😊

from d365fo.tools.

Splaxi avatar Splaxi commented on July 28, 2024

Okay - the facts are the following:

Get-D365EnvironmentSettings actually resolves like 99% of the important variables that we use when run as a non-elevated console. It is really only the SQL user / pass and a small portion of non-common objects that are missing.

First proposal

Test for OneBox - before testing for IsAdmin / -SQLPwd

For all cmdlets that we identify that most likely needs to run on OneBox, we simple change the first validation into testing for OneBox or not. If yes, we set a $UseTrustedConnection otherwise we fall back to the current testing.

This should give us the expected results of support a simple execution of cmdlets across several boxes, without breaking to much. It would ease the burden of implementing supporting / alternative cmdlets.

The main task is to identify all the cmdlet that should have this logic, so we only focus on them. We need to remember that we have New-D365Bacpac and Import-D365Bacpac that should work equally effective on either OneBox / Tier 2 environments. The sqluser and sqlpwd on Tier 2 environments are wrong when reading the (Get-D365EnvironmentSettings).DataAccess, which if why we have the current check in place. So we should be in the safe about those.

A small note - we plan on implementing storing details like sqluser / sqlpwd using the PSFramework. Read more about it in #23 :)

from d365fo.tools.

Splaxi avatar Splaxi commented on July 28, 2024

Please see if this could be a working solution. I'll test it my self tomorrow and get back with my results. Note that you might have to Install / Import-module PSFramework before running this. I you have the latest d365fo.tools installed you should be fine.

<#
.SYNOPSIS
Invoke the synchronization process used in Visual Studio

.DESCRIPTION
Uses the sync.exe (engine) to synchronize the database for the environment

.PARAMETER BinDirTools
Path to where the tools on the machine can be found

Default value is normally the AOS Service PackagesLocalDirectory\bin

.PARAMETER BinDir
Path to where the tools on the machine can be found

Default value is normally the AOS Service PackagesLocalDirectory

.PARAMETER LogPath
The path where the log file will be saved

.PARAMETER SyncMode
The sync mode the sync engine will use

.PARAMETER Verbosity
Parameter used to instruct the level of verbosity the sync engine has to report back

Default value is: "Normal"

.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.

.EXAMPLE
Invoke-D365DBSync

This will invoke the sync engine and have it work against the database

.EXAMPLE
Invoke-D365DBSync -Verbose

This will invoke the sync engine and have it work against the database. It will output the
same level of details that Visual Studio would normally do

.NOTES
General notes
#>
function Invoke-D365DBSync {
    [CmdletBinding()]
    param (

        [Parameter(Mandatory = $false, Position = 0)]
        [string]$BinDirTools = $Script:BinDirTools,

        [Parameter(Mandatory = $false, Position = 1)]
        [Alias('MetadataDir')]        
        [string]$BinDir = $Script:BinDir,

        [Parameter(Mandatory = $false, Position = 2)]
        [string]$LogPath = "C:\temp\D365FO.Tools\Sync",

        [Parameter(Mandatory = $false, Position = 3)]
        #[ValidateSet('None', 'PartialList','InitialSchema','FullIds','PreTableViewSyncActions','FullTablesAndViews','PostTableViewSyncActions','KPIs','AnalysisEnums','DropTables','FullSecurity','PartialSecurity','CleanSecurity','ADEs','FullAll','Bootstrap','LegacyIds','Diag')]
        [string]$SyncMode = 'FullAll',
        
        [Parameter(Mandatory = $false, Position = 4)]
        [ValidateSet('Normal', 'Quiet', 'Minimal', 'Normal', 'Detailed', 'Diagnostic')]
        [string]$Verbosity = 'Normal',

        [Parameter(Mandatory = $false, Position = 5)]
        [string]$DatabaseServer = $Script:DatabaseServer,

        [Parameter(Mandatory = $false, Position = 6)]
        [string]$DatabaseName = $Script:DatabaseName,

        [Parameter(Mandatory = $false, Position = 7)]
        [string]$SqlUser = $Script:DatabaseUserName,

        [Parameter(Mandatory = $false, Position = 8)]
        [string]$SqlPwd = $Script:DatabaseUserPassword        
    )

    $TrustedConnection = "false"

    if ($Script:IsOnebox) {
        $TrustedConnection = "true"
    }
    elseif (!$script:IsAdminRuntime -and !($PSBoundParameters.ContainsKey("SqlPwd"))) {
        Write-Host "It seems that you ran this cmdlet non-elevated and without the -SqlPwd parameter. If you don't want to supply the -SqlPwd you must run the cmdlet elevated (Run As Administrator) or simply use the -SqlPwd parameter" -ForegroundColor Yellow
        Write-Error "Running non-elevated and without the -SqlPwd parameter. Please run elevated or supply the -SqlPwd parameter." -ErrorAction Stop
    }

    Write-PSFMessage -Level Verbose -Message "Testing if the path exists or not." -Target $command
    $command = Join-Path $BinDirTools "SyncEngine.exe"
    if ((Test-Path -Path $command -PathType Leaf) -eq $false) {
        Write-PSFMessage -Level Host -Message "Unable to locate the <c=`"red`">SyncEngine.exe</c> in the specified path. Please ensure that the path exists and you have permissions to access it."
            
        Stop-PSFFunction -Message "Stopping because unable to locate SyncEngine.exe" -Target $command
        return
    }

    Write-PSFMessage -Level Verbose -Message "Testing if the SyncEngine is already running."
    $syncEngine = Get-Process -Name "SyncEngine" -ErrorAction SilentlyContinue
    if ($null -ne $syncEngine) {
        Write-PSFMessage -Level Host -Message "A instance of SyncEngine is <c=`"red`">already running</c>. Please <c=`"red`">wait</c> for it to finish or <c=`"red`">kill it</c>."
            
        Stop-PSFFunction -Message "Stopping because SyncEngine.exe already running"
        return
    }

    Write-PSFMessage -Level Verbose -Message "Testing if the path exists or not." -Target $BinDir 
    if ((Test-Path -Path $BinDir -PathType Container) -eq $false) {
        Write-PSFMessage -Level Host -Message "Unable to locate the <c=`"red`">BinDir(metadatabinaries)</c> in the specified path. Please ensure that the path exists and you have permissions to access it."
            
        Stop-PSFFunction -Message "Stopping because unable to locate the BinDir path" -Target $BinDir
        return
    }
    
    Write-PSFMessage -Level Verbose -Message "Build the parameters for the command to execute."
    $param = " -syncmode=$($SyncMode.ToLower())"
    $param += " -verbosity=$($Verbosity.ToLower())"
    $param += " -metadatabinaries=`"$BinDir`""
    $param += " -connect=`"server=$DatabaseServer;Database=$DatabaseName;Trusted_Connection=$TrustedConnection; User Id=$DatabaseUserName;Password=$DatabaseUserPassword;`""

    Write-PSFMessage -Level Verbose -Message "Testing if the path exists or not." -Target $LogPath
    if ((Test-Path -Path $LogPath.Trim() -PathType Leaf) -eq $false) {
        Write-PSFMessage -Level Verbose -Message "Creating the path." -Target $LogPath
        $null = New-Item -Path $LogPath -ItemType directory -Force -ErrorAction Stop
    }

    Write-PSFMessage -Level Verbose -Message "Starting the SyncEngine with the parameters." -Target $param
    $process = Start-Process -FilePath $command -ArgumentList  $param -PassThru  -RedirectStandardOutput "$LogPath\output.log" -RedirectStandardError "$LogPath\error.log" -WindowStyle "Hidden"

    $lineTotalCount = 0
    $lineCount = 0
    Write-Verbose "Process Started"
    Write-Verbose $process

    $StartTime = Get-Date

    while ($process.HasExited -eq $false) {
        foreach ($line in Get-Content "$LogPath\output.log") {
            $lineCount++
            if ($lineCount -gt $lineTotalCount) {
                Write-Verbose $line
                $lineTotalCount++
            }
        }
        $lineCount = 0
        Start-Sleep -Seconds 2

    }

    foreach ($line in Get-Content "$LogPath\output.log") {
        $lineCount++
        if ($lineCount -gt $lineTotalCount) {
            Write-Verbose $line
            $lineTotalCount++
        }
    }

    foreach ($line in Get-Content "$LogPath\error.log") {        
        Write-PSFMessage -Level Critical -Message "Error: $line"
    }

    $EndTime = Get-Date

    $TimeSpan = New-TimeSpan -End $EndTime -Start $StartTime

    Write-PSFMessage -Level Verbose -Message "Total time for sync was $TimeSpan" -Target $TimeSpan
}

from d365fo.tools.

skaue avatar skaue commented on July 28, 2024

Having a OneBox check sounds like a nice way to go about this.
As for the connectionstring. You may need to omit credentials completely, not simply change the value of TrustedConnection. If it is false, credentials are needed. If it is true, you do not need them.

from d365fo.tools.

skaue avatar skaue commented on July 28, 2024

As for the bacpac story, it is true that you do not have the sqladmin password for the Tier2 environments, and we will not get any tooling to grab that from Microsofts own keyvault. We are then forced to make that password available somehow ourselves. It will require grabbing it manually from LCS.

from d365fo.tools.

Splaxi avatar Splaxi commented on July 28, 2024

If we are setting the TrustedConnection = true, all user / passwords in the connectionstring will be ignored. If we set the trustedconnection = true, we'll set the sqlpwd = "" (because the value we retrieve is 128 characters and that breaks stuff).

Then we avoid have to test further down the code whether to build a trusted connection string or not.

from d365fo.tools.

Splaxi avatar Splaxi commented on July 28, 2024

This version works as expected. Can you test it in your environment(s) before we push it?

<#
.SYNOPSIS
Invoke the synchronization process used in Visual Studio

.DESCRIPTION
Uses the sync.exe (engine) to synchronize the database for the environment

.PARAMETER BinDirTools
Path to where the tools on the machine can be found

Default value is normally the AOS Service PackagesLocalDirectory\bin

.PARAMETER BinDir
Path to where the tools on the machine can be found

Default value is normally the AOS Service PackagesLocalDirectory

.PARAMETER LogPath
The path where the log file will be saved

.PARAMETER SyncMode
The sync mode the sync engine will use

Default value is: "FullAll"
.PARAMETER Verbosity
Parameter used to instruct the level of verbosity the sync engine has to report back

Default value is: "Normal"

.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.

.EXAMPLE
Invoke-D365DBSync

This will invoke the sync engine and have it work against the database

.EXAMPLE
Invoke-D365DBSync -Verbose

This will invoke the sync engine and have it work against the database. It will output the
same level of details that Visual Studio would normally do

.NOTES
#>
function Invoke-D365DBSync {
    [CmdletBinding()]
    param (

        [Parameter(Mandatory = $false, Position = 0)]
        [string]$BinDirTools = $Script:BinDirTools,

        [Parameter(Mandatory = $false, Position = 1)]
        [string]$MetadataDir = $Script:MetaDataDir,

        [Parameter(Mandatory = $false, Position = 2)]
        [string]$LogPath = "C:\temp\D365FO.Tools\Sync",

        [Parameter(Mandatory = $false, Position = 3)]
        #[ValidateSet('None', 'PartialList','InitialSchema','FullIds','PreTableViewSyncActions','FullTablesAndViews','PostTableViewSyncActions','KPIs','AnalysisEnums','DropTables','FullSecurity','PartialSecurity','CleanSecurity','ADEs','FullAll','Bootstrap','LegacyIds','Diag')]
        [string]$SyncMode = 'FullAll',
        
        [Parameter(Mandatory = $false, Position = 4)]
        [ValidateSet('Normal', 'Quiet', 'Minimal', 'Normal', 'Detailed', 'Diagnostic')]
        [string]$Verbosity = 'Normal',

        [Parameter(Mandatory = $false, Position = 5)]
        [string]$DatabaseServer = $Script:DatabaseServer,

        [Parameter(Mandatory = $false, Position = 6)]
        [string]$DatabaseName = $Script:DatabaseName,

        [Parameter(Mandatory = $false, Position = 7)]
        [string]$SqlUser = $Script:DatabaseUserName,

        [Parameter(Mandatory = $false, Position = 8)]
        [string]$SqlPwd = $Script:DatabaseUserPassword        
    )

    $TrustedConnection = "false"

    Write-PSFMessage -Level Debug -Message "Testing if run on onebox or running either elevated or with -SqlPwd set."
    if ($Script:IsOnebox) {
        $TrustedConnection = "true"
        #* We don't need the sqlpwd and without admin it will be to long
        $SqlPwd = ""
    }
    elseif (!$script:IsAdminRuntime -and !($PSBoundParameters.ContainsKey("SqlPwd"))) {
        Write-PSFMessage -Level Host -Message "It seems that you ran this cmdlet <c=`"red`">non-elevated</c> and without the <c=`"red`">-SqlPwd parameter</c>. If you don't want to supply the -SqlPwd you must run the cmdlet elevated (Run As Administrator) otherwise simply use the -SqlPwd parameter"
        Stop-PSFFunction -Message "Stopping because of missing parameters"
        return
    }

    Write-PSFMessage -Level Debug -Message "Testing if the path exists or not." -Target $command
    $command = Join-Path $BinDirTools "SyncEngine.exe"
    if ((Test-Path -Path $command -PathType Leaf) -eq $false) {
        Write-PSFMessage -Level Host -Message "Unable to locate the <c=`"red`">SyncEngine.exe</c> in the specified path. Please ensure that the path exists and you have permissions to access it."
            
        Stop-PSFFunction -Message "Stopping because unable to locate SyncEngine.exe" -Target $command
        return
    }

    Write-PSFMessage -Level Debug -Message "Testing if the SyncEngine is already running."
    $syncEngine = Get-Process -Name "SyncEngine" -ErrorAction SilentlyContinue
    if ($null -ne $syncEngine) {
        Write-PSFMessage -Level Host -Message "A instance of SyncEngine is <c=`"red`">already running</c>. Please <c=`"red`">wait</c> for it to finish or <c=`"red`">kill it</c>."
            
        Stop-PSFFunction -Message "Stopping because SyncEngine.exe already running"
        return
    }

    Write-PSFMessage -Level Debug -Message "Testing if the path exists or not." -Target $MetadataDir 
    if ((Test-Path -Path $MetadataDir -PathType Container) -eq $false) {
        Write-PSFMessage -Level Host -Message "Unable to locate the <c=`"red`">BinDir(metadatabinaries)</c> in the specified path. Please ensure that the path exists and you have permissions to access it."
            
        Stop-PSFFunction -Message "Stopping because unable to locate the BinDir path" -Target $MetadataDir
        return
    }
    
    Write-PSFMessage -Level Debug -Message "Build the parameters for the command to execute."
    $param = " -syncmode=$($SyncMode.ToLower())"
    $param += " -verbosity=$($Verbosity.ToLower())"
    $param += " -metadatabinaries=`"$MetadataDir`""
    $param += " -connect=`"server=$DatabaseServer;Database=$DatabaseName;Trusted_Connection=$TrustedConnection; User Id=$SqlUser;Password=$SqlPwd;`""

    Write-PSFMessage -Level Debug -Message "Testing if the path exists or not." -Target $LogPath
    if ((Test-Path -Path $LogPath.Trim() -PathType Leaf) -eq $false) {
        Write-PSFMessage -Level Debug -Message "Creating the path." -Target $LogPath
        $null = New-Item -Path $LogPath -ItemType directory -Force -ErrorAction Stop
    }

    Write-PSFMessage -Level Debug -Message "Starting the SyncEngine with the parameters." -Target $param
    $process = Start-Process -FilePath $command -ArgumentList  $param -PassThru  -RedirectStandardOutput "$LogPath\output.log" -RedirectStandardError "$LogPath\error.log" -WindowStyle "Hidden"

    $lineTotalCount = 0
    $lineCount = 0
    Write-Verbose "Process Started"
    Write-Verbose $process

    $StartTime = Get-Date

    while ($process.HasExited -eq $false) {
        foreach ($line in Get-Content "$LogPath\output.log") {
            $lineCount++
            if ($lineCount -gt $lineTotalCount) {
                Write-Verbose $line
                $lineTotalCount++
            }
        }
        $lineCount = 0
        Start-Sleep -Seconds 2

    }

    foreach ($line in Get-Content "$LogPath\output.log") {
        $lineCount++
        if ($lineCount -gt $lineTotalCount) {
            Write-Verbose $line
            $lineTotalCount++
        }
    }

    foreach ($line in Get-Content "$LogPath\error.log") {        
        Write-PSFMessage -Level Critical -Message "$line"
    }

    $EndTime = Get-Date

    $TimeSpan = New-TimeSpan -End $EndTime -Start $StartTime

    Write-PSFMessage -Level Debug -Message "Total time for sync was $TimeSpan" -Target $TimeSpan
}

from d365fo.tools.

Splaxi avatar Splaxi commented on July 28, 2024

@skaue Right now I have difficulties getting your sample to work on a 7.3 PU15 and 8.0 PU15 onebox, running under a non-elevated console.

  $command = "C:\AOSService\PackagesLocalDirectory\Bin\SyncEngine.exe"
    $connectionString = "Data Source=localhost;Integrated Security=True;Initial Catalog=AxDb"
    $packageDirectory = "C:\AOSService\PackagesLocalDirectory"
    $params = @(
        "-syncmode=`"fullall`""
        "-metadatabinaries=$packageDirectory"
        "-connect=`"$connectionString`""
    )
    & $command $params 2>&1 | Out-String

Could you test, confirm and share what environments you are able to get this to work?

from d365fo.tools.

Splaxi avatar Splaxi commented on July 28, 2024

I need to start SSMS with "Run As Administrator" on both machines for it to accept Windows Authentication.

So - what are we facing? Lack of understanding when the local windows user is recognized as being part of the administrator group could be part of the problem.

from d365fo.tools.

Splaxi avatar Splaxi commented on July 28, 2024

We found the issue.

Local VM / onebox isn't configured the same way as LCS deployed onebox. On the local the local administrator is only part of the local administrators group. When trying to authenticate against the SQL server, the user needs to run as elevated, otherwise will his user token not have the local administrators group with it and therefore the authentication fails.

We'll build a new cmdlet to fix this for others. First time they will have to run either with an elevated console or supply the SqlPwd themself. When the fix is applied trusted connection should would as expected across all scenarios.

from d365fo.tools.

skaue avatar skaue commented on July 28, 2024

Sorry for the delay. I've only tested on Azure hosted boxes. There is a difference in permissions between the admin user and the second regular user on the VMs provisioned through LCS. There are a couple of explicit permissions granted to the regular user, which should be implicit with the local admin user. You might be right about the local admin user needing to run certain things elevated still.

from d365fo.tools.

Splaxi avatar Splaxi commented on July 28, 2024

So you are saying that it should work if we were to use the "normal" user credentials that are listed on LCS? Hmm.

Will give it a look. Any idea on what permissions that differs?

from d365fo.tools.

Splaxi avatar Splaxi commented on July 28, 2024

Okay - now we know something more.

The non-admin user on a LCS deployed onebox owns the encryption key that is used for the config file. The FullSync method inside the SyncEngine.exe utilizes the built in dll files to get the needed information.

So the real issue is not that we have to support trusted connections. That is already solved with the above cmdlet. The real issue is that we tested as the administrator and he doesn't own the encryption key. For him to extract the data out of the dll files, he needs to run as elevated.

To summarize:
Local VM / onebox => You have to run it elevated (the user doesn't own the encryption key).
Azure hosted VM => You have to be logged in a the non-admin user to avoid elevated. Otherwise you have to run it elevated (the user doesn't own the encryption key)
MS hosted VM => You have to be logged in a the non-admin user to avoid elevated.

from d365fo.tools.

skaue avatar skaue commented on July 28, 2024

That turned out to be quite a lot of "ifs". So the mode OneBox can only take us thus far. There is also the EncryptionOwner mode that allows us to access the information necessary.

Nice investigation. I wasn't aware about the ownership of the encryption key. Handy...

from d365fo.tools.

Splaxi avatar Splaxi commented on July 28, 2024

We are guessing, until someone can point us in the right direction.

So for all non-local VMs, running without elevation is supported, if you use non-admin credentials. That solves 2 out of 3 scenarios.

Local VM is solved with elevated console when we are talking about dbsync. Other cmdlets should be able to utilize the soon to be storage of configuration.

from d365fo.tools.

skaue avatar skaue commented on July 28, 2024

So the Trusted Connection was a mistake on my end. What I actually meant was Integrated Security=True. Sorry, brain meltdown in my end... ;-)

from d365fo.tools.

Splaxi avatar Splaxi commented on July 28, 2024

Should be fixed with 0.3.54. Open a new issue if there is any issues :)

from d365fo.tools.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.