Is it possible to connect to a remote RDM server with RDM Powershell module

Hi JV,

If I understand correctly, you want to access your DVLS instance when executing your script from a batch server?
If so, the easiest way would be to create an app id and password on your DVLS instance (Administration -> Applications. Save the key and secret before saving).

Then in your script, you can do the following to set that DVLS instance as your data source

$dvlsKey = "your application key"
$dvlsSecret = "your application secret"
$ds = New-RDMDataSource -DVLS -Name 'DVLS' -Server 'DVLS instance address' -ScriptingTenantID $dvlsKey -ScriptingApplicationPassword $dvlsSecret
Set-RDMCurrentDataSource $ds

Alternatively, if you don't want to have your app key/secret stored in your script, you can create the data source and save it once in a PowerShell terminal like this

$dvlsKey = "your application key"
$dvlsSecret = "your application secret"
New-RDMDataSource -DVLS -Name 'DVLS' -Server 'DVLS instance address' -ScriptingTenantID $dvlsKey -ScriptingApplicationPassword $dvlsSecret -SetDatasource

and in your script load that data source

$ds = Get-RDMDataSource -Name 'DVLS'
Set-RDMCurrentDataSource $ds

Let me know if this helps.
Regards

Hello Jonathan,

Thank you for the answer.

My RDM Server is : Remote Desktop Manager Enterprise Edition 2022.1.27.0 64-Bits
The issue is when I go on the Administration tab, there is no "application" icons to create a key and a secret :

Maybe this is not available in my version?

On this server, here is the way I connect my powershell script:
$sqlinstance="SQLEXPRESS"
#start a connexion
Start-RDMInstance
#Connect the datasource
$RDMdatasource=Get-RDMDataSource -Name $sqlinstance
Set-RDMCurrentDataSource -DataSource $RDMdatasource

But as you suggested, I'm digging on the command New-RDMDataSource
New-RDMDataSource [-SQLServer] [-Database] <String> [[-IntegratedSecurity]] [-Server] <String> [-Name] <String> [[-SetDatasource]]
What I tried from my remote server :
Start-RDMInstance
New-RDMDataSource -SQLServer -Database 'SQLEXPRESS' -IntegratedSecurity -Server 'FQDNofmyserver' -Name 'SQLEXPRESS' -SetDatasource
$RDMdatasource=Get-RDMDataSource -Name 'SQLEXPRESS'
Set-RDMCurrentDataSource -DataSource $RDMdatasource

But here is my error now :
Unable to access the database. Verify if you are offline or if you have an opened VPN

Regards,

JV

Hi,

Your version of RDM is fine. I misunderstood what you meant and was referring to the 'Administration' section of another product.

So, your use case is connecting to your SQLExpress data source from another computer.
I think with default settings, SQL Express isn't configured to allow remote connections. Other than that, you script looks alright to me. You could even remove some lines.

$RDMdatasource = New-RDMDataSource -SQLServer -Database 'SQLEXPRESS' -IntegratedSecurity -Server 'FQDNofmyserver' -Name 'SQLEXPRESS' -SetDatasource
Set-RDMCurrentDataSource -DataSource $RDMdatasource

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-access-server-configuration-option?view=sql-server-ver16

It was an SQL connection issue about misconfiguration with dynamics ports instead of 1433 port.
Here is now my code :
Start-RDMInstance
RDMdatasource = New-RDMDataSource -SQLServer -Database $sqlinstance -IntegratedSecurity -Server $serverName -Name $sqlinstance -SetDatasource

I havenow a new issue with :
Set-RDMCurrentDataSource -DataSource $RDMdatasource
With the error
Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in t
ime. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=3; handshake=1;

But I know now it's a comunication issue with my SQL, I just need to find what :)

Thank you very much for your help.

Regards

It could be a TLS issue.
Could you try adding this at the beginning of your script?
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls -bor [Net.SecurityProtocolType]::Tls11 -bor [Net.SecurityProtocolType]::Tls12

Hi Jonathan.

I tried to add te line on my script but without success so I came back for test on my RDM Server.

For information, to let the users connect my RDM, I used a security provider with certificate for the database. I deployed it by GPO for any users in a special AD security group. When I'm on the RDM Server, I can see the user certificate well deployed and I can connect my RDM application without any issue.

For my powershell script on this RDM server :
#Connect the datasource
$RDMdatasource=Get-RDMDataSource -Name $sqlinstance
Set-RDMCurrentDataSource -DataSource $RDMdatasource
Get-RDMCurrentDataSource
ID : e9c9d556-83b2-4233-b824-0529fa471fd4
IsConnected : True
IsOffline : False
Name : SQLEXPRESS
Type : SQLServer

I can see it's connected

But if I try on the same server to modify $RDMdatasource=Get-RDMDataSource -Name $sqlinstance by:
$RDMdatasource = New-RDMDataSource -SQLServer -Database $sqlinstance -IntegratedSecurity -Server $serverName -Name $sqlinstance -SetDatasource
$RDMdatasource
ID : 975301ef-fd9c-4385-bbb4-d06e6727dc05
IsConnected : False
IsOffline : False
Name : SQLEXPRESS
Type : SQLServer

I can see it never connect.

That's why when I use the Set-RDMCurrentDataSource I receive the logn failed error.
Set-RDMCurrentDataSource -DataSource $RDMdatasource
Cannot open database "SQLEXPRESS" requested by the login. The login failed.
Login failed for user 'DOM\service_RDM_PSScript'.
Cannot open database "SQLEXPRESS" requested by the login. The login failed.
Login failed for user 'DOM\service_RDM_PSScript'.

Curently i can't explain why!

We could compare the settings to, hopefully, figure our why one works and not the other.
In RDM you should be able to see both data sources. Export them and compare the generated xml.
Anything stands out?

I think there is a misunderstood.
Sorry my english is maybe not good enough.

After a lot of test I saw, using the "New-RDMDataSource" command, It created me a new datasource in my RDM appliance. That is not what I want and also I think that's why I could never connect it.
I deleted it because I already have one datasource, and only one in RDM.
That's why after deletion, using "Get-RDMDataSource -Name $sqlinstance" from the rdm server, the result is :
ID : e9c9d556-83b2-4233-b824-0529fa471fd4
IsConnected : True
IsOffline : False
Name : SQLEXPRESS
Type : SQLServer
Which is fine now.

I just need to know now, which command to use from a remote server to connect this same remote RDMdatabase (and not create a new one).
Something like : Get-RDMDataSource -SQLServer -Database $databaseName -IntegratedSecurity -Server 'testjvesrdm' -Name $databaseName -SetDatasource (but of course this is not working)
Because now on the remote server, the issue is if I call Get-RDMDataSource -Name $sqlinstance, there are 6! I'm also searching a way to clean this.

Maybe an example of what I need from the remote server powershell script :

Start-RDMInstance
$RDMdatasource = find a way to connect the existing remote RDM server datasource
Set-RDMCurrentDataSource -DataSource $RDMdatasource
TotalRDMUsers=Get-RDMUser|select-object name -ExpandProperty name

I found how to remove them :
Get-RDMDataSource -Name $sqlinstance |Remove-RDMDataSource
Get-RDMDataSource|where-object {$_.Name -like "TEST_RDM_DB"}|Remove-RDMDataSource

OK, I think I'm gonna be crazy. It now works from the remote server...
$RDMdatasource = New-RDMDataSource -SQLServer -Database $databaseName -IntegratedSecurity -Server 'testjvesrdm' -Name $databaseName -SetDatasource
Set-RDMCurrentDataSource -DataSource $RDMdatasource
Get-RDMCurrentDataSource

Result :
ID : 3dca8e1c-0860-4a36-81de-0558dfc7a484
IsConnected : True
IsOffline : False
Name : TEST_RDM_DB
Type : SQLServer

And It seems to not create me a new database on my remote RDM

I don't know why it works now but... it seems to works :)

Thanks for your time.

After a while it recreated me a second database

I'm gonna cry :(

Another strange thing.

Running my remote script in powershell ISE with my service account creates the groups, create and remove the users, assign or remove the users to the good rdm role. That's perfect!

Running my remote script in scheduled task with highest privilege with my service account creates the groups, create and remove the users, but never assign or remove the users from any rdm role.

With default settings, both RDM and PowerShell will read the same config file.
Any data source configured in RDM or PowerShell will be visible to the other as long as the other is not running while the data source is created.
(Closing RDM after creating a data source while both were open will overwrite what you just did in PowerShell).

On a server running RDM, you have two options to access a data source from PowerShell.

1. Reuse the existing data source using Get-RDMDataSource -Name 'data source name'
2. Create a new one without saving it by calling New-RDMDataSource but make sure you don't have -SetDataSource in your command.

So a script you could run on both servers would look like

$RDMdatasource = New-RDMDataSource -SQLServer -Database $databaseName -IntegratedSecurity -Server 'testjvesrdm' -Name $databaseName 
Set-RDMCurrentDataSource -DataSource $RDMdatasource 

I hope this clears things up a little.

I believe you speak French natively? We could communicate privately to help communication if you would prefer.

Good morning Jonathan,

That's now clear for me about the behaviour of the datasource. As you said, removing -SetDatasource doesn't create now a second datasource in my RDM application. That's fine.

It remains for me to understand why the user assignement to the RDMrole is not working but only in scheduled task (RDM role and user creation and deletion is working). Using the same script with the same user account manually in PS ISE do all the actions fine.

Yes I'm French, it is so obvious? :)

Communicate privately is a good idea, thank you.
I'll try some tests again.

Regards,

JV