SQL Database Overhaul and Decoupling

[LukeHodgson]

Hi all, great tool. Would like to get some involvement on the project and noticed the DB side of things could be greatly improve to aid with ease of use, maintenance and feature evolution etc

• Migrated the inline SQL scripts to DB projects for respective backend technologies.
• Apply NF standards.
• Utilization of stored procedures for security, cleanliness and decouples from the application code.
• Enables implementation of DB version migrations automatically during the installation process.

Just some thoughts seen a few comments in GitHub and externally arounds issues with the SQL side of things.

Feel that would help with breaking up the solution into more manageable chunks.

Thanks

[Kvarkas]

Hello and welcome, if you feel like could help with db side you are more than welcome, even if you are not c# coder, you could tell me how it should be so i could implement. Change ongoing is to use local db to store settings and all what is not sensitive data. Keep in mind what big companies (I am realy glad they trust us and use mR a lot) dont like to open gates to the cloud, however that could be an option, my vision mR should stay exactly as its named - manager of connections, and idealy dont include any db related stuff, however for proper work we still need db structure follow our schema. For such I am thinking to switch from xml format (perhaps) to json format, so that allow me to add validations as well and its will be external from the code - but where is a risk what not clever person amend what and then destroy db sturcture so need to be secure and maintanable at same way. Any idea around that is more than welcome!

[LukeHodgson]

Can help with the backend side and any refactoring needed with C# very familiar with it was my day job once upon a time.

With big companies using it I am assuming there is the need for proper RBAC on connections which are centrally stored so it's forms a central point of truth for the connections stored in a DB rather than having many DBs. Enabling teams to use it and admins to control access via roles to certain members etc. Having the connections stored in a centrally accessible manager helps this.

If you are wanting to move to JSON you can add verifications of the file hash (and other metadata) to see if it has been tampered with or not.

For local settings that are not sensitive could opt for SQLLite.

Sensitize details leverage suitable password vault tools with APIs or CLIs if they offer it that gives another aspect of flexibility.

[Kvarkas]

idea what i was plan to implement (amending today way we have) its on installation (or setup process) company provide elevated account what will be not saved but allow to created in empty db (they should provide connection string) all needed structure, or if they dont want such, they could grab our schema (or sql script) and made a structure, so we need to store only read only account credentials (and that should be happened securily on another local db perhaps) in global idea we want to allow users manage they secrets so we could integrate with any provider even with CyberArk :) but for home users we could store it localy even if that less secure. But main idea is what i dont want mR to manage security of the users data, only minimum nesesary like read only access. In more bigger view - on next release i will start implementing profiles, so we have 2 default identities: user and admin. By default admin. User who run an app will be automaticaly associated with default identitie. Then it could be setup for company needs (if so) so as an admin it could create many users profiles and many connection profiles - and that bit could be sotred internaly, externaly or in db. So once user run it its have access only to his profile (we need only read only access) so company admin could amend that profile or sotre it in Azure or esle where and do change on the fly, same for integrations with beyoundtrust, cyberakr and microsoft for credentials rotation etc.
At the moment i am still frustrating how to make localdb secure (I know how to encrypt it) but there tu put a password localy? :)

[LukeHodgson]

For local password storage you can leverage the windows credentials manager and I am sure the is a NuGet package from Microsoft on that or it's built into .NET.

Also the profiles idea some pretty much what I was on about with the RBAC, there are defaults as you mentioned but Admins can create more with the permissions to users they need.

Exciting, I will look at refactoring the backend side and decoupling it.

[Kvarkas]

let me know if need help with code, even its not writen by me, keep in mind also what in next release I will be migrating to .net 8 as well

[Kvarkas]

and if need chat we use this one: https://app.element.io/#/room/#mRemoteNG_DevChat:gitter.im