Comments (22)
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.
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.
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.
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.
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.
Maybe some of the info is present in the registry. We'll see what we can do 😊
from d365fo.tools.
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.
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.
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.
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.
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.
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.
@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.
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.
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.
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.
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.
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.
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.
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.
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.
Should be fixed with 0.3.54. Open a new issue if there is any issues :)
from d365fo.tools.
Related Issues (20)
- 💚 Validation action stops with error "Register-AzModule : The type initializer for 'Microsoft.Azure.Commands.Common.AzModule' threw an exception." HOT 2
- Set-D365FavoriteBookmark doesn't work on Windows Server 2022 due to missing IE HOT 3
- Upload bacpac file to LCS asset library issue HOT 12
- :sparkles: new cmdlet New-D365EntraIntegration HOT 17
- :bug: Environment type for non-us/global regions are not correctly determined
- Move database from one env to another HOT 1
- :sparkles: add Entra integration app registration to wif.config HOT 1
- Understand the LCS cookie and how to work with it HOT 1
- :sparkles: Add Get-D365RsatSelfServiceCertificates cmdlet HOT 6
- Import-D365Bacpac cannot find SqlPackage HOT 11
- Invoke-D365DBSync / Catch error when synchronization went wrong on DevOps HOT 2
- Error importing Tier 2 database to Tier 1 VM - The permission 'KILL DATABASE CONNECTION' is not supported in this version of SQL Server. HOT 15
- D365LcsApiToken' command was found in the module 'd365fo.tools', but the module could not be loaded HOT 3
- Update "Import a bacpac file into a Tier1 environment" topic HOT 2
- variables.ps1 fails on detecting InstallationInfoDirectory on the new 10.0.39 VMs HOT 3
- Service Drive variable is missing on the new 10.0.39 VMs #829 HOT 3
- Retrieve metadata for on-prem environments HOT 3
- Update cmdlets for Entra integration HOT 1
- Clear-D365BacpacTableData : Exception calling ".ctor" with "2" argument(s): "Illegal characters in path." HOT 1
- File Description not correctly set in LCS Asset upload HOT 4
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
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.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from d365fo.tools.