locked
SQL Server permissions

    Question

  • My DBA's are asking me what permissions are needed to setup the databses and what is needed post setup. The OCS databases will be installed on a corporate SQL server along with a few other databases so i cannot have administrator access. Any help would be appreciated.
    Tuesday, May 26, 2009 5:38 PM

All replies

  • Here is a screenshot of the correct (default) permissions and user mapping settings in SQL 2008 for our MSHxQ 2007 D2 databases for reference:  

      



    What you should notice here is that the specific groups are granted connect permission by the dbo (database owner).

    Also, the RTCAB database (new to OCS 2007 R2) includes a newly created server role with the “View Database State” permission granted by the dbo.

    The user mappings on the RTCHSUniversalServices group will look like this: 



    The RTCHSUniversalServices group is mapped to RTC, RTCConfig and RTCDYN. You cannot remove this group from ‘public’. The ServerRole and db_ddladmin memberships allow accounts in the RTCHSUniversalServices group to access the RTC database and execute SQL commands. The RTCDYN database has identical settings to these for the RTCHSUniversalServices group. 

    The RTCConfig mappings include the database role memberships ‘public’ and ‘ReadOnlyRole’.

    Next we will look at the mappings for the RTCComponentUniversalServices group. 

     

    It’s important to remember that this group is also mapped to the RTCAB and RTCConfig databases, with the ‘public’ and ‘ServerRole’ membership for the RTCAB database. Not mapping this group properly will ensure problematic behavior with your address book, conferencing, and A/V functionality on your front end servers. 



    Notice that this group has an additional role attached ‘AcdReadWriteRole’, along with the ‘public’ and ‘ReadOnlyRole’ memberships for the RTCConfig database.

    The AcdReadWriteRole is a new role created for our dial-in conferencing service, which happens to be a member of RTCComponentUniversalServices.

    We also have a new database created for this service, acddyn. The RTCComponentUniversalServices group is  mapped to this database with the ‘public’, ‘ReadOnlyRole’ and ‘ReadWriteRole’.



    Notice that ‘public’ is a role assigned to every group for every database that group is mapped to. You can not remove any of these groups from the ‘public’ role.

    The database journal has a great article on the public role. Most SQL experts will already be familiar with this, but any of us wondering can use this article as a reference or refresher. The article was written in 2001, but the concept I am told has not changed.
    http://www.databasejournal.com/features/mssql/article.php/1478701/SQL-Permissions-The-Public-Role.htm

    So, here we go. All of our role memberships, user mapping settings, and database permissions have been covered. So, my customers environment was actually setup exactly like this ---- with the default configuration intact when we reattached the SQL databases.

    The OCS 2007 R2 Front End services started, but we could not login to Communicator 2007 R2 and noticed this error in our event logs:

    Event Type:         Error

    Event Source:      OCS User Services

    Event Category:   (1006)

    Event ID:              30962

    Date:                    5/13/2009

    Time:                    9:12:44 PM

    User:                     N/A

    Computer:            MSHXSQ

    Description:

    Connection to back-end database succeeded, but failed to execute registration stored procedure on the back-end.  This error should not occur under normal operating conditions.  Contact product support.

    Back-end Server: wsqlrib00001111\t2x2ppr  Database: rtc  Sql native error: 916   Connection string of:

    driver={SQL Native Client};Trusted_Connection=yes;AutoTranslate=no;server=wsqlrib00001111\t2x2ppr;database=rtc;

    Cause: Possible issues with back-end database.

    Resolution:

    Ensure the back-end is functioning correctly.

    Judging from this error we know that we can start the services, connect to the RTC database, but we can’t execute any commands.

    There are two databases in SQL that we should then check. Most SQL folks will be familiar with these databases, but those of us in the UC space may not spend as much time dealing with these.

    master and msdb.

    The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.

    The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable. In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database .

    Our issue with the SQL Native Client 916 error on our OCS 2007 R2 front end can occur due to the following:

    (a) Guest account has been removed from the ‘master’ and ‘msdb’ databases


    (b) SQL server hardening: RTCHSUniversalServices and RTCComponentUniversalServices groups are denied access to the ‘msdb’ database with the functionality of the following roles: ‘public, SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole and TargetServerRole’

    Mapping the RTCHSUniversalServices and RTCComponentUniversalServices groups to the master and msdb databases with the public, SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole and TargetServerRole assignments is not recommended, unless strict server hardening standards are required and you must remove the 'Guest’ account from the master and msdb databases. Please note that this is not supported.

    After restoring the correct database permissions on the RTC, RTCDYN, RTCAB, and RTCConfig databases, and granting the ‘Guest’ account access to the ‘master’ and ‘msdb’ databases, your SQL Native client errors on your OCS 2007 R2 front end server should go away. 

    As  you can see, keeping your SQL permissions and database user mapping settings in tact when moving or making changes to your RTC databases is very important. You will likely see SQL Native Client errors on your OCS Front End in the Event Log should you make changes without keeping these default settings. Many SQL administrators may find themselves making changes for ‘security’ purposes on their SQL cluster, but you will want to keep the above in mind.

     

    Source

     


    Thanks,
    A.m.a.L
    .Net Goodies
    Remember to click "mark as answered" when you get a correct reply to your question
    Tuesday, May 26, 2009 6:27 PM