How to enable remote connection to SQL Instances from single SQL Server Management Studio?


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.