Als je Dynamics NAV/BC gebruikt met een SQL on Azure database, is die verbinding altijd opgebouwd door middel van een SQL login. Dat kan uiteraard ook op een on-premises omgeving. Dit script maakt die verbinding in orde, zowel on-premises als in de cloud.
Als je het prettiger vindt (of dit ergens anders wilt gebruiken) kun je de # voor param verwijderen, en de vaste variabelen ($password, $username, et cetera) mogen dan ook uit het script worden gehaald.
<#
This will setup a connection between a Dynamics NAV/BC service and the database, using SQL authentication.
$ServerInstance: Dynamics NAV/BC serverinstance name
$SQLDatabaseName: Name of the database
$SQLDatabaseServer: Name of the server where the database runs on (sqlserver\instance, SQLserver.database.windows.net)
$KeyLocation: Location of the encryptionkey. If this key does not exist, it will be created.
$Password: password used for the encryptionkey
$UserName: Username used for the connection to the database. If this is used, $Password will be used as the user's password.
If $UserName is not used, the script will ask for credentials
Remember to create the SQL account first, using
Create login [<username>] with password='password'
Create user [<username>] for login [<username>]
alter role db_owner add member [<username>]
#>
# param ($ServerInstance, $SQLDatabaseName, $SQLDatabaseServer, $KeyLocation, $Password, $UserName)
$password='password'
$userName='username'
$ServerInstance = "BC190"
$SQLDatabaseName = "Databasename"
$SQLDatabaseServer = "SQLServer\Instance"
$KeyLocation="c:\path\DynamicsNAV.key"
$ServiceLocation = (get-itemproperty -path HKLM:\SYSTEM\CurrentControlSet\Services\MicrosoftDynamicsNAVServer`$$serverinstance).ImagePath
if ($Servicelocation.indexof('"') -ge 0) {
$ServiceLocation = $ServiceLocation.split('"')[1]
}
$ServiceLocation = Split-path -path $ServiceLocation
if ([string]::IsNullOrWhitespace($ServiceLocation)) {
write-host "Could not locate $ServerInstance"
$ServerInstance = ""
read-host "Press enter to quit script"
return
}
get-childitem "$ServiceLocation\Microsoft.Dynamics.Nav.Management.dll" -recurse | import-module -verbose
get-childitem "$ServiceLocation\Microsoft.Dynamics.Nav.apps.Management.dll" -recurse | import-module -verbose
$DBServerInstance=$SQLDatabaseServer.split('\')[1]
$DBServerName=$SQLDatabaseServer.split('\')[0]
if (!([string]::IsNullOrEmpty($Username))) {
$Credentials = (New-Object PSCredential -ArgumentList $userName,(ConvertTo-SecureString -AsPlainText -Force $password))
} else {
$Credentials = Get-Credential
}
if ((Get-WmiObject -Class Win32_OperatingSystem -ComputerName localhost).Caption -like "*Server*") {
Install-WindowsFeature -Name NET-HTTP-Activation
}
if (!(test-path -path $KeyLocation -PathType Leaf)) {
new-item -path (split-path -path $KeyLocation) -ItemType "Directory" -ErrorAction SilentlyContinue
New-NAVEncryptionKey -KeyPath $KeyLocation -Password (ConvertTo-SecureString -AsPlainText -Force $password) -Force
}
Import-NAVEncryptionKey -ServerInstance $ServerInstance `
-ApplicationDatabaseServer $SQLDatabaseServer `
-ApplicationDatabaseCredentials $Credentials `
-ApplicationDatabaseName $SQLDatabaseName `
-KeyPath $KeyLocation `
-Password (ConvertTo-SecureString -AsPlainText -Force $password) `
-Force -Verbose
Set-NAVServerConfiguration -ServerInstance $ServerInstance -DatabaseCredentials $Credentials -Force
Set-NAVServerConfiguration -ServerInstance $ServerInstance -KeyName DatabaseServer -KeyValue $DBServerName -Force
Set-NavServerConfiguration -ServerInstance $ServerInstance -keyName DatabaseInstance -KeyValue $DBServerInstance
Set-NAVServerConfiguration -ServerInstance $ServerInstance -KeyName DatabaseName -KeyValue $SQLDatabaseName
Set-NAVServerConfiguration -ServerInstance $ServerInstance -KeyName EnableSqlConnectionEncryption -KeyValue true
if (!($DBServer | select-string -pattern "database.windows.net")) {
Set-NavServerConfiguration -ServerInstance $ServerInstance -KeyName TrustSQLServerCertificate -keyvalue true
}
write-host "(Re)starting $ServerInstance"
Set-NAVServerInstance -ServerInstance $ServerInstance -Restart