Problem with Windows authentication on SQL-database
Hello,
we want to use Remote Desktop Manager internally to let our internal users connect to our clients servers.
We would like to use Windows authentication to let our users connect to the central SQL Server database but for the initial setup these users need insert and update permissions on certain tables:
INSERT permission on object 'UserAccount'
INSERT permission on object 'UserSecurity'
UPDATE permission on object 'UserRole'
DELETE permission on object 'UserRole'
INSERT permission on object 'UserProfile'
DELETE permission on object 'UserGroupInfo'
INSERT permission on object 'ConnectionLog'
But then the user can connect to the database, for instance with SQL Management Studio and alter or delete certain information like logging, user permissions.
Our only option is to alter the rights on some tables after the initial setup of the user.
Is it possible to use Windows authentication without granting all these rights at initial setup and altering them after the setup ?
Kind regards,
Verheijden Thierry.
All Comments (2)
If the user is an administrator then they have "full'ish" rights to the database. For non-admins we DENY some tables & fields.
As with any Client-Server type applications if you use Integrated SQL Authentication or SQL Authentication there is not much you can do to prevent users from accessing the database directly and changing the raw data. RDM protects you as best it can with encryption of the underlying data but not all is encrypted.
So you have two choices. The first is the use the "Custom (Devolutions)" login type (see: https://help.remotedesktopmanager.com/index.html?administration_usermanagement.htm). This login type only works with RDM, users can't use those credentials in SSMS for example, putting an extra layer of security between the user and the database. Warning! If the user is an administrator of the network then the user might still have rights by virtue of Windows Groups and such. Your second option is to use DPS (Devolutions Password Server) as data source which offers an even better security model.
Stéfane Lavergne
Hello,
Sorry to jump in, but we have a blog on this subject that I think will interest you
https://blog.devolutions.net/2019/06/sql-server-with-integrated-security-thats-so-2015
On a side note, maybe the data report entry type could accomplish what you are looking for
https://help.remotedesktopmanager.com/sessions_datareport.htm
Best regards,
Jeff Dagenais