Users get INSERT and UPDATE errors RDP with MSSQL Datasource
Hi,
We have just purchased a site license for the RDM Enterprise Edition.
We have setup a database on Microsoft SQL Server and given 2 active directory groups access (Users and Admin).
Users: DB_Reader, Public
Admins: DB_Owner, Public
Note: neither the Users or the Admins are database administrators. So they only have rights on the RDM database. Admins have logon rights to SQL Studio, but can only manage the actual RDM database.
RDM Versjon: All users are either on version11.6.2.0 or 11.7.2.0 and everyone is running Windows 8.1 x64 at a minimum.
RDM Data Source: The RDM data source is setup to use Integrated Active Directoy authentication
The initial setup went well and all admins were able to use the software as intended. Then we started adding users to the system slowly. It went well for a while then all of a sudden users started reporting that they were getting SQL errors in the aplpication:
See picture for a more detailed error message, but most are usually the following
INSERT permissions denied
UPDATE Permission Denied
ect...
I've made sure that I've set Add, Edit, Delete directly on the users account within the RDM application. In addition I've even tried to make them "Admin". I've also checked that Admin = 1 in the RDM database. But they still get the same error messages.
As i mentioned earlier, I'm not a Database administrator, so I havn't setup the actual database myself. It's possible that it is a misconfigured database. The documentation on the RDM support site suggests that DB_Reader is enough as the software runs GRANT statements to give users access when changes are done in the RDM admin panel.
Is there any way to check if these GRANT statements run without errors? Is DB_Owner enough permissions for the admin users to give access to other users?
Appreciate any help on the matter.
Regards
Jack.
insert denied.png
All Comments (9)
Could you try the "Fix SQL Login" action in the user management screen?
Test it with a problematic user to see if that solves your issue. If so, multi-select all the users and fix them all at once.
This we make sure all users have the minimum access rights to the database depending in each users assigned rights. (Can Add? Can Edit...)
Best regards,
Stéfane Lavergne
2016-10-24_15-24-46.png
Hi and thanks for the reply.
I'll give that a go.
Does that work when using AD Integrated Users? We are not using SQL User accounts.
Yes
Side note: make sure the user that is running the "fix SQL login" is "good". In other words, it has enough rights on the server to grant/deny/assign user rights, if not the fix will fail.
I possible workaround is to create an account for "sa" (or any other dbo user), make that user admin, log in to RDM using that new user and use the "fix SQL Login" to fix at least a single RDM admin account. You can then use that "fixed" admin account to fix the remaining users/admins.
Best regards,
Stéfane Lavergne
It was an SQL permission issue. Having DB_owner on the database isn't enough to run the GRANT statements.
On another note. How is the private vault secured in MSSQL? Can a database admin go into the raw database and read a users private vault data?
Users are working now?
The private vaults are encrypted with AES-256 bit unique key for each user.
Stéfane Lavergne
Hi,
Yes the users are working now. We needed an SQL admin with at least the SecurityAdmin server role to be able to run any commands that grant privileges (such as "Fix SQL")on the database tables.
Another question. The unique key for the private Vault. Where is that stored? Is it somehow attached to the users Windows Account in the case of AD authentication`?
The keys are in the database. It's a hash a few fields in the user table (won't list them for security reasons).
Stéfane Lavergne
Ok thanks for your help. Much appreciated.