SQL User Permissions
We are just starting to setup RDM within our organization and we are looking to use the Active Directory integration. We are using Version 9.2.10.0 64-bit.
When we have a new user setup a new data source, choose SQL type and then input the name/server info, check off the 'Integrated security (Active Directory' checkbox, a new user would be created in RDM. The issue was that this new RDM user was being created as an "Administrator", which is not something that we want.
After reading through your online help (Support/Resources->Tips and Tricks->SQL Server Data Source with Integrated Security), I noticed that there was this tip:
Any user (domain, local machine or SQL user) that has a server role of sysadminis automatically a administrator of Remote Desktop Manager.
Of course, we had all SQL users in that DB having the sysadmin permission. We attempted to removed that and the database connection failed. We tried adding back some permissions - dbcreator, which is supposed to be used to "create, alter, drop and restore any database", but the connection was still failing.
The specific message that we are receiving is:
"Unable to connect to the database!
Cannot open database "RemoteDesktopManager" requested by the login. The login failed. Login failed for user '<our user>'.
For someone that wants to use AD Integrated Security, what are the specific SQL permissions that you recommend we have setup in MSSQL such that users can connect to the DB and aren't added as 'Administrator' users in the RDM application?
Thanks in advance for any assistance you can provide!!
All Comments (3)
Hello,
Users should ideally be created through our administration screen, we will grant the necessary permissions.
For now, if you go in the user screen and create the user, you may get an error as to the pre-existing login, but we manage this accordingly and you can ignore the error.
Maurice
Thanks for the reply Maurice. While not ideal, I guess we can create ~150 users manually. What is the preferred/best practice setup for an organization that users Active Directory and wants to leverage your Integrated Security? We would like to have things setup so that we can be as hands off as possible and leverage all user changes via Active Directory.
I've poured through a number of your resources and didn't find any detailed information on how to set this up. Are you aware of any? Can you please let me know on the above?
Thanks,
Rob
Hi
Thats why we created the RDMS product, it assigns permission sets based on the AD group that a user belongs to.
For a SQL Data source there isn't much automation possible. We will soon create powershell cmdlets for administration purposes, but we dont have a timeline yet.
If you look at the RDMS product page, be aware that it will soon become an add-on that will not include RDM licences anymore, the price will therefore be cut significantly...
Maurice