mssqltips logo

SQL Server Central Management Server Security

By:   |   Updated: 2011-06-20   |   Comments   |   Related: 1 | 2 | 3 | 4 | More > Central Management Servers

Problem

I have seen several tips on SQL Server Central Management Server and I want to implement the technology for my team. The only thing holding me back is security. Can you explain how security works with Central Management Server? What security access is needed to manage CMS? How about connecting to CMS? How about running a query using CMS? Check out this tip to answer these questions.

Solution

It's always important to understand the security of a new feature before you start using it in production. Today, we are going to walk through a demo to understand the security used behind the Central Management Server.

In this demo we will have CMS configured with five instances named: PBMDEMO, PBMDEMO\EXPRESS, PBMDEMO\SQL2000, PBMDEMO\SQL2008 and PBMDEMO\SQL2005. The basic configuration is shown below. If you need help creating a your Central Management Server look at this tip.


The table below gives you an overview of the security behind the Central Management Server:

Central Management Server Security

Security to connect to CMS The ServerGroupReaderRole database role in the MSDB database is used to give a user access to connect to a central management server.
Security to configure CMS The ServerGroupAdministratorRole database role in the MSDB database is used to manage the central management server. Grant a user access to this role to let them be a CMS Administrator.
Security to connect to instances Windows Authentication of the individual servers for the user connected to a central management server is to used to connect and execute t-sql and policies

Step 1: We will use the "PBMDEMO\PBMUser" Windows Authenticated account for this demo. The PBMUser account currently doesn't have access to CMS as it's not a member of the ServerGroupReaderRole or ServerGroupAdministratorRole database roles. You will see the following error message shown in the screen shot below.

sql server central management server

Step 2: In order to give the PBMUser account login access to CMS we need to track down the CMS database roles. To do this right click on the user under Logins node found under the Security node in SSMS and select properties. Next we will select the User Mappings tab. Click on the msdb database and notice that we now see the ServerGroupReaderRole and ServerGroupAdministrator database roles. These two roles are shown in the red box in the next screen shot below.

under login properties find the security node in ssms

Step 3: Now we will give the PBMUser access to CMS. This will be done by giving the account access to the ServerGroupReaderRole. If you wanted to make the user an CMS Administrator you could grant him ServerGroupAdministratorRole access.

giving the user access to cms

Step 4: Now that our user has access we will try to connect to CMS and run a query. When you right click on the CMS server and select "new query" you will see that you don't have access to all the servers in this example. In the bottom left corner of the query window you will see that we are only connected to two out of five instances. This is because Windows Authentication is used to connect to the instances of SQL Server and the user does not have access to connect.

grant the user access to sql server 2000, sql server 2005 and sql server 2008 instances

Step 5: We will run a very basic query to test the connection and prove that we only are connected to two instances. In this case we will use "SELECT @@VERSION." You will see that the PBMUser account doesn't have access to connect to the SQL2000, SQL2005 and SQL2008 instances. This can be fixed by granting the PBMUser account access to those instances.

you can automate a daily checklist with cms, t-sql and powershell

Step 6: To complete this example we will grant the user login access and the access needed to run the queries against CMS on the instances where the user doesn't have access. In this example we are going to give datareader access to MSDB because we are going to include more tips later to automate a daily checklist with CMS, T-SQL and PowerShell.

Note - In the real world you only want to grant the access needed for the query you want to execute against your SQL Server farm.

-- Grant PBMUser access to read all tables in MSDB
USE [master]
GO
EXEC master.dbo.sp_grantlogin @loginame = N'PBMDEMO\PBMUser'
EXEC master.dbo.sp_defaultdb @loginame = N'PBMDEMO\PBMUser', 
@defdb = N'tempdb'
EXEC master.dbo.sp_defaultlanguage @loginame = N'PBMDEMO\PBMUser'
GO
USE [tempdb]
GO
EXEC dbo.sp_grantdbaccess @loginame = N'PBMDEMO\PBMUser',
@name_in_db = N'PBMDEMO\PBMUser'
GO
USE [tempdb]
GO
EXEC sp_addrolemember N'db_datareader', N'PBMDEMO\PBMUser'
GO

give the datareader access to msdb

Step 7: Now we will rerun the exact same query and you should notice that you connect to the instances and see results for every instance.

results of sql server query

Next Steps


Last Updated: 2011-06-20


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools