Whenever you’re done configuring a new Database Server you might notice the SSMS Server Icon is still representing a white color rather than green. This is because you forgot to enable the remote admin setting on the server firewall. No worries on logging back into the server and setting it directly cause you can do this directly from the SSMS client (provided the SQL Service account has local rights in the OS).
use master; set nocount on exec master..sp_configure 'show advanced options', '1'; exec master..sp_configure 'xp_cmdshell', '1' reconfigure with override; exec master..xp_cmdshell 'netsh firewall set service RemoteAdmin enable'; --deprecated, but still works. exec master..xp_cmdshell 'netsh advfirewall firewall set rule group="remote administration" new enable=yes'; go
Categories: SQL Stuff