Workaround to Register an Instance Out of Your Domain in Central Management Server

It sucks that Central Management Server (CMS) doesn’t support registering an instance by SQL Server authentication (and unfortunately in very DBA’s inventory, there are always a few servers that are out your normal domain but you still have to manage).

Here is a workaround posted by David Samson on Brent Ozar’s blog post about how to use a local Windows account to “cheat” the Windows authentication mechanism. I list in the post the steps you have to follow:

1. Got to “Server Manager” and create, on the out-of-domain box e.g. “SERVER”, a Windows account “SERVER\nxu” in the case of my user name.
New_User
2. Make its password the same as your current AD account’s password, assuming my AD account is “DOMAIN\nxu”.

3. Add “SERVER\nxu” to sysadmin role on SERVER’s SQL Server instance. And you can achieve this by running T-SQL command on SERVER:

USE [master]
GO
CREATE LOGIN [SERVER\nxu] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N'SERVER\nxu', @rolename = N'sysadmin'
GO

4. Fire up CMS and register this SERVER as a sever to manage and even with “Authentication: Windows Authentication” drop-down menu grayed out, you’ll still see “The connection was tested successfully” message box pop up.
Test_Success
WARNING: this local Windows account password has to be updated once your AD account’s password is changed, which should already be scheduled  periodically by AD’s group policy.

Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in Database Connection, SQL Server and tagged , , . Bookmark the permalink.

2 Responses to Workaround to Register an Instance Out of Your Domain in Central Management Server

  1. Ali says:

    Hi XU,

    Can you please post this workaround by images, it will be easier for newbies like me to understand, I am having issues with connecting to different domain servers in to the CMS.

    Thanks
    Ali

    • Nick Xu says:

      Thanks for dropping by, Ali, I will update this post with nice screenshots probably on a rainy day 🙂 Or maybe Microsoft will support SQL Server authentication in its SQL2014 version’s CMS already.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s