locked
CRM 4.0 - SQLServer security? RRS feed

  • Question

  • Hi all,

     

    I did some simple security tests wich gave me strange results (tests have been done with the official crm 4.0 rtm vpc):

     

    my principal question is : Can a simple user (user only belongs to the crm security groups: usergroup and reportinggroup. This is the paulw login within the litwareinc domain) access sql server and the different tables within the mscrm config and ogranization db ?

     

    I opened the db within sql server management studio to check the security settings. Only administrators, administrator, reportinggroup, sqlaccessgroup and privreportinggroup can connect to the db engine. So there is no "logins" available directly for the user. Right this is nice.

     

    Lets check now each db instance:

     

    MSCRM_Config : normally no security settings allows the reportinggroup to access this db.... Nice Wink

    MicrosoftCRM_MSCRM: securable objects found for the reportinggroup that give only select and execute privileges for the reportinggroup on all FilteredXXXX Views and to some stored proc and functions used by those views. Still nice.

     

    Now what about "real life":

     

    Note : this test have been done "locally" within the vpc wich is a domain controller or server that "simple users" can normally not connect to.

     

    I connect to the db engine with sql server management studio with paulw (reportinggroup user). I can run queries over all objects/tables/views within both config and organization db !!!!!!!!

    Same happens if I open excel and connect to the db for importing external datas!!!!!!!

     

    So what if a simple user can access all account/leads or configuration settings from outside the CRM application ????

     

    Is this a security hole that can happen only if logged locally on the SQL Server or is it the same result from a domain computer? what is the difference? why security is not applied locally?

     

    I will really appreciate some explanations about this behavior. Is this behavior the same in a production environnement at my customer?

     

    Thanks to all Wink

     

    Tuesday, February 26, 2008 2:47 PM

Answers

  • It sounds like your paulw user has permissions through other SQL logins (which may be related to AD groups that the user is a member of), or modified the SQL permissions to the ReportingGroup or public in the MSCRM database.

     

    I've never had problems with SQL permissions on CRM

     

    Tuesday, February 26, 2008 3:53 PM
    Moderator
  • Hi david,

     

    Thanks, you were right Wink

     

    In fact, after executing the following command I discovered that the user was effectively part of the 'sysadmin' role...

     

    select is_srvrolemember('sysadmin') ==> return value: 1

     

    Thanks to Microsoft within their own vm that included the "domain users" as members of the "builtin\administrators" group wich has the sysadmin role. This only to allow the "domain users" to connect to the VM with remote desktop or locally on the server itself (this because by default domain users cannot connect to a server !!! if MS themselves do not respect their best security practices.... that's nice Wink) ..... They can add the "domain users" as member of the "Remote Desktop users" group instead. This will not compromise the CRM SQL Server security.

     

    Well everything is OK now....

     

     

     

     

     

     

    Thanks to Imran for your intersting suggestion too

    Wednesday, February 27, 2008 12:16 PM

All replies

  • It sounds like your paulw user has permissions through other SQL logins (which may be related to AD groups that the user is a member of), or modified the SQL permissions to the ReportingGroup or public in the MSCRM database.

     

    I've never had problems with SQL permissions on CRM

     

    Tuesday, February 26, 2008 3:53 PM
    Moderator
  • Had you check kerberos.

     

     

    http://www.microsoft.com/downloads/thankyou.aspx?familyId=51bf9f20-bd00-4759-8378-b38eefda7b99&displayLang=en
     
     
    An updated doc on Kerberos delegation published Jan 25th

     

     

    Also check this.

     

    http://www.sadev.co.za/node/155

     

     

    Regards,

    Imran

     

    http://microsoftcrm3.blogspot.com

     

    Wednesday, February 27, 2008 6:43 AM
    Moderator
  • Hi david,

     

    Thanks, you were right Wink

     

    In fact, after executing the following command I discovered that the user was effectively part of the 'sysadmin' role...

     

    select is_srvrolemember('sysadmin') ==> return value: 1

     

    Thanks to Microsoft within their own vm that included the "domain users" as members of the "builtin\administrators" group wich has the sysadmin role. This only to allow the "domain users" to connect to the VM with remote desktop or locally on the server itself (this because by default domain users cannot connect to a server !!! if MS themselves do not respect their best security practices.... that's nice Wink) ..... They can add the "domain users" as member of the "Remote Desktop users" group instead. This will not compromise the CRM SQL Server security.

     

    Well everything is OK now....

     

     

     

     

     

     

    Thanks to Imran for your intersting suggestion too

    Wednesday, February 27, 2008 12:16 PM