SQL Account Permissions
Hello,
I am working on setting up a shared data source (Azure SQL-based) for a team and am curious about the SQL account whose creds we must enter for connecting to the DB if we use the "custom auth model". I see that if we add an O365 user or database user, RDM auto-creates an underlying SQL DB user that essentially gets granted CONNECT, SELECT, INSERT, UPDATE and DELETE on the RDM DB and then gets DENY INSERT, UPDATE and DELETE on specific security-related tables. Therefore I presume, if I wanted to go the "custom auth" route so that my users don't have direct access to the DB - I would need to create a SQL user manually with those same permissions and then use that account as the underlying SQL creds to connect to the DB (and distribute to my users via a pre-created .rdd file)? Or is there some more restrictive set of permissions I should use (are the SQL permissions required for functionality officially documented anywhere)?
Any help is appreciated!
Best regards
David Willis
All Comments (5)
Hello David,
You would need to use a SQL login. All the information for the custom login can be found here: https://docs.devolutions.net/kb/remote-desktop-manager/how-to-articles/implement-custom-login-mode/
Let us know if you have any questions or issues!
Best regards,
Richard Boisvert
Thanks Richard! That article is great, lots of good info there.
The only remaining question I have is with the two methods the article outlines for creating the SQL user (either through RDM by creating a "database user" or through SSMS/directly through SQL) - those would produce two different results right? Because method one would also create a user at the RDM level (thus using one RDM license) whereas creating the SQL user directly would only create the user on the underlying database - is that correct or am I misunderstanding? Because in my scenario I just want to create a user that will be used for the underlying connection from RDM to the DB (not an account that will actually be utilized by a user).
David Willis
Hello David,
If you manually create the account, since it would have the db_owner role, it could be used to log into RDM. We recommend creating an admin account in RDM directly: https://docs.devolutions.net/kb/remote-desktop-manager/how-to-articles/implement-custom-login-mode/#method-using-remote-desktop-manager-recommended
You can always not grant an RDM license to that account as well.
Best regards,
Richard Boisvert
Ah I see, so if the manually-created account were used to login to RDM (which it could do because of its db_owner rights) then it would be automatically assigned a license and essentially be the same end result as if we had created the account through RDM?
EDIT: I think I understand now after playing around with it a little more - to reclaim the license if no user was actually going to be using that account we would just unassign the license through User Management, and that would still allow the account to be used for the underlying SQL connection without using a named user license.
Thanks for all your help!
David Willis
Hello David,
Exactly! Glad we could be of assistance.
Best regards,
Richard Boisvert