CODEWITHSUNDEEP
sql-serverdelphiconfiguration
Jerry Dodge
Jerry Dodge

Reputation: 27276

Programatically configure SQL Server TCP settings

There's a manual process that we do whenever we install SQL Server 2008 R2 for our clients. We enable TCP/IP and Named Pipes protocols for the instance, set the TCP Dynamic Ports to 0, and set the TCP Port to a non-standard port number (Browser is deliberately disabled for security). Connections are always made with explicit port numbers (I.E. 192.168.1.100, 12345)

How can I configure these server TCP settings programatically from Delphi?

Upvotes: 0

Views: 685

Answers (1)

RRUZ
RRUZ

Reputation: 136391

You can use the ServerNetworkProtocol WMI class which is part of the WMI Provider for Configuration Management Classes.

To access this class you must connect to the proper namespace depending of your SQL Server version.

SQL Server 2005 - ComputerManagement
SQL Server 2008 - ComputerManagement10 
SQL Server 2012 - ComputerManagement11

Try this sample for SQL Server 2008

{$APPTYPE CONSOLE}

uses
  SysUtils,
  ActiveX,
  ComObj,
  Variants;


procedure  EnableSQLServerNetworkProtocol(Const Protocol : string; EnableProtocol: Boolean);
const
  WbemUser            ='';
  WbemPassword        ='';
  WbemComputer        ='localhost';
  wbemFlagForwardOnly = $00000020;
var
  FSWbemLocator : OLEVariant;
  FWMIService   : OLEVariant;
  FWbemObjectSet: OLEVariant;
  FWbemObject   : OLEVariant;
  oEnum         : IEnumvariant;
  iValue        : LongWord;
begin;
  FSWbemLocator := CreateOleObject('WbemScripting.SWbemLocator');
  FWMIService   := FSWbemLocator.ConnectServer(WbemComputer, 'root\Microsoft\SqlServer\ComputerManagement10', WbemUser, WbemPassword);
  FWbemObjectSet:= FWMIService.ExecQuery(Format('SELECT * FROM ServerNetworkProtocol Where ProtocolName="%s"', [Protocol]),'WQL',wbemFlagForwardOnly);
  oEnum         := IUnknown(FWbemObjectSet._NewEnum) as IEnumVariant;
  if oEnum.Next(1, FWbemObject, iValue) = 0 then
   //if FWbemObject.Enabled<>EnableProtocol then
      Writeln('Result '+VarToStr(FWbemObject.SetEnable(EnableProtocol))); // 0 means OK
end;


begin
 try
    CoInitialize(nil);
    try
      EnableSQLServerNetworkProtocol('Tcp', True);// TCP/IP
      EnableSQLServerNetworkProtocol('Np', True); // Named Pipes
    finally
      CoUninitialize;
    end;
 except
    on E:EOleException do
        Writeln(Format('EOleException %s %x', [E.Message,E.ErrorCode]));
    on E:Exception do
        Writeln(E.Classname, ':', E.Message);
 end;
 Writeln('Press Enter to exit');
 Readln;
end.

Note: this code must be executed in the same machine where is installed SQL Server, if you want do this task remotely you must provide the proper logon parameters (user, pass, server) to the WMI connection.

To set the IP address and port you can use the ServerNetworkProtocolIPAddress and ServerNetworkProtocolProperty classes.

Upvotes: 4

Related Questions