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.
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:
[sourcecode language=”sql”]
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
[/sourcecode]
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.
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.
Leave a Reply