Problem : All SQL Server Instances in the Environment are not able to connect from central SQL Server management Studio.
Resolution: Below steps need to be followed to enable Remote Connection :
1) RDP to the SQL Server on which remote access needs to be enabled.
2) Open SQL Server Configuration manager (Start>All Programs>Microsoft SQL Server>Configuration Tools>Configuration manager)
3) Expand SQL Server Network Configuration, Select ‘Protocols’; right click on TCP/IP and click ‘Enable’.
4) If the instance in question is a Named Instance, then
a) Enable Browser Services
SQL Server Services>SQL Browser Services>Properties>Service and change ‘Start Mode’ to Automatic
b) Start Browser Services
SQL Server Services>SQL Browser Services>Properties>General and click on ‘Start’
5) Connect to Database Engine through
a) SSMS (if its installed on server)
check if remote access is configured or not :
Run sp_configure ‘remote access’
If ‘Run Value’ returns 1, then its OK else enable remote access:
Run sp_configure ‘remote access’,1
reconfigure
b) SQLCMD (if SSMS is not installed on server)
Open command prompt and type
SQLCMD –E –S <SQL Instance Name>
When connected, type
sp_configure ‘remote access’
If ‘Run Value’ returns 1, then its OK else enable remote access:
sp_configure ‘remote access’,1
Reconfigure
6) Restart SQL Services.
Now you can connect all SQL Instances from single SQL Management Studio.