VN:F [1.7.9_1023]
Rating: 0.0/5 (0 votes cast)

Last week I was asked an interesting question: “How to check with T-SQL query if the TCP/IP protocol is enabled on a remote instance of SQL Server 2008 when you can’t use SQL Server Configuration Manager?”. The answer is pretty simple if you know that all the protocol settings are kept in the Windows registry on the server. And so, the query may look like:

DECLARE @key nvarchar(100);
SELECT @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\' +
  CASE @@SERVICENAME
    WHEN 'MSSQLSERVER' THEN 'MSSQLSERVER'
    ELSE 'MSSQL$' + @@SERVICENAME
  END + '\SuperSocketNetLib\Tcp';
DECLARE @test varchar(20);
EXEC master.dbo.xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @key,
  @value_name = 'Enabled',
  @value = @test OUTPUT;
SELECT CAST(@test AS binary(1));

If the result is binary 1, then the protocol is enabled. You can use regedit.exe to find out the paths to the keys in registry containing the settings for other protocols.

Also, you can check the static TCP port number using the same technique (I assumed the port is the same for all network interfaces):

DECLARE @key nvarchar(100);
SELECT @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\' +
  CASE @@SERVICENAME
    WHEN 'MSSQLSERVER' THEN 'MSSQLSERVER'
    ELSE 'MSSQL$' + @@SERVICENAME
  END + '\SuperSocketNetLib\Tcp\IpAll';
DECLARE @test varchar(20);
EXEC master.dbo.xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @key,
  @value_name = 'TcpPort',
  @value = @test OUTPUT;
SELECT @test;

Furthermore, you can even enable any protocol using xp_regwrite extended stored procedure (see the article for details on the procedure). Remember that after you make any changes to the protocol settings the SQL Server service has to be restarted (probably the best way is to create a .bat/.cmd batch at the remote server that uses net start and net stop commands to restart the SQL Server instance).

If you think this is something ugly – to play with the registry within T-SQL query – remember that the SQL Server service account has full rights to the registry keys that contain the SQL Server settings.