2
\$\begingroup\$

I'm building an installer package for our software using Inno Setup. As a part of this, I'm also building a few different DLL's in Delphi XE2 to accommodate for some of the common tasks required by the installer. One of those is querying and manipulating settings on these instances. I intend to support SQL versions 2005 and above.

The things I need to query are:

  • List of all installed instances
  • Versions of all installed instances
  • Settings which I need to change (explained next)

The settings I need to change are:

  • Enable/Disable TCP/IP Protocol
  • Enable/Disable Named Pipes Protocol
  • Change Dynamic Ports of TCP/IP
  • Change Port Number of TCP/IP

What I understand is that this is all found in the registry, for example

SQL Server Config in Registry

I do know this can be done using WMI as well, but I'd rather avoid WMI if at all possible. I also know that I need to restart the instance to apply any changes.

SQLHelper.dll

library SQLHelper;

uses
  System.SysUtils,
  System.Classes,
  System.Win.Registry,
  WinApi.Windows;

{$R *.res}

const
  SQL_INSTANCES_KEY = 'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\';

  SQL_INST_ROOT_KEY = 'Software\Microsoft\Microsoft SQL Server\%s\MSSQLServer\';

type
  TStringArray = array of String;

function Is64BitOS: Bool; stdcall;
type
  TIsWow64Process = function(Handle:THandle; var IsWow64 : BOOL) : BOOL; stdcall;
var
  hKernel32 : Integer;
  IsWow64Process : TIsWow64Process;
  IsWow64 : BOOL;
begin
  Result := False;
  hKernel32 := LoadLibrary('kernel32.dll');
  try
    if (hKernel32 = 0) then RaiseLastOSError;
    @IsWow64Process := GetProcAddress(hkernel32, 'IsWow64Process');
    if Assigned(IsWow64Process) then begin
      IsWow64 := False;
      if (IsWow64Process(GetCurrentProcess, IsWow64)) then begin
        Result := IsWow64;
      end else RaiseLastOSError;
    end;
  finally
    FreeLibrary(hKernel32);
  end;
end;

function RegAccess: Cardinal; stdcall;
begin
  Result:= KEY_READ or KEY_WRITE;
  if Is64BitOS then
    Result:= Result or KEY_WOW64_64KEY;
end;

function GetSQLInstanceList(var A: TStringArray): Bool; stdcall;
var
  R: TRegistry;
  L: TStringList;
  X: Integer;
begin
  Result:= False;
  SetLength(A, 0);
  R:= TRegistry.Create(RegAccess);
  try
    R.RootKey:= HKEY_LOCAL_MACHINE;
    if R.KeyExists(SQL_INSTANCES_KEY) then begin
      if R.OpenKey(SQL_INSTANCES_KEY, False) then begin
        L:= TStringList.Create;
        try
          R.GetValueNames(L);
          SetLength(A, L.Count);
          for X := 0 to L.Count - 1 do begin
            A[X]:= L[X];
          end;
          Result:= True;
        finally
          L.Free;
        end;
        R.CloseKey
      end;
    end;
  finally
    R.Free;
  end;
end;

function GetSQLInstanceID(const Instance: WideString): WideString; stdcall;
var
  R: TRegistry;
begin
  Result:= '';
  R:= TRegistry.Create(RegAccess);
  try
    R.RootKey:= HKEY_LOCAL_MACHINE;
    if R.KeyExists(SQL_INSTANCES_KEY) then begin
      if R.OpenKey(SQL_INSTANCES_KEY, False) then begin
        if R.ValueExists(Instance) then begin
          Result:= R.ReadString(Instance);
        end;
        R.CloseKey
      end;
    end;
  finally
    R.Free;
  end;
end;

function GetSQLInstanceExists(const Instance: WideString): Bool; stdcall;
begin
  Result:= GetSQLInstanceID(Instance) <> '';
end;

function GetSQLInstanceRootKey(const Instance: WideString): WideString; stdcall;
begin
  Result:= Format(SQL_INST_ROOT_KEY, [GetSQLInstanceID(Instance)]);
end;

function GetSQLInstanceVerKey(const Instance: WideString): WideString; stdcall;
begin
  Result:= GetSQLInstanceRootKey(Instance)+'CurrentVersion\';
end;

function GetSQLInstanceConfigKey(const Instance: WideString): WideString; stdcall;
begin
  Result:= GetSQLInstanceRootKey(Instance)+'SuperSocketNetLib\';
end;

function GetSQLInstanceVer(const Instance: WideString): WideString; stdcall;
var
  R: TRegistry;
begin
  Result:= '';
  R:= TRegistry.Create(RegAccess);
  try
    R.RootKey:= HKEY_LOCAL_MACHINE;
    if R.KeyExists(GetSQLInstanceVerKey(Instance)) then begin
      if R.OpenKey(GetSQLInstanceVerKey(Instance), False) then begin
        if R.ValueExists('CurrentVersion') then begin
          Result:= R.ReadString('CurrentVersion');
        end;
        R.CloseKey;
      end;
    end;
  finally
    R.Free;
  end;
end;

function GetSQLInstanceProtocolEnabled(const Instance, Protocol: WideString): Bool; stdcall;
var
  R: TRegistry;
begin
  Result:= False;
  R:= TRegistry.Create(RegAccess);
  try
    R.RootKey:= HKEY_LOCAL_MACHINE;
    if R.KeyExists(GetSQLInstanceConfigKey(Instance)+Protocol) then begin
      if R.OpenKey(GetSQLInstanceConfigKey(Instance)+Protocol, False) then begin
        if R.ValueExists('Enabled') then begin
          Result:= R.ReadInteger('Enabled') = 1;
        end;
        R.CloseKey;
      end;
    end;
  finally
    R.Free;
  end;
end;

function SetSQLInstanceProtocolEnabled(const Instance, Protocol: WideString;
  const Enabled: Bool): Bool; stdcall;
var
  R: TRegistry;
  V: Integer;
begin
  Result:= False;
  R:= TRegistry.Create(RegAccess);
  try
    R.RootKey:= HKEY_LOCAL_MACHINE;
    if R.KeyExists(GetSQLInstanceConfigKey(Instance)+Protocol) then begin
      if R.OpenKey(GetSQLInstanceConfigKey(Instance)+Protocol, False) then begin
        if R.ValueExists('Enabled') then begin
          if Enabled then V:= 1 else V:= 0;
          R.WriteInteger('Enabled', V);
          Result:= True;
        end;
        R.CloseKey;
      end;
    end;
  finally
    R.Free;
  end;
end;

function GetSQLInstanceTCPPort(const Instance: WideString): Integer; stdcall;
var
  R: TRegistry;
begin
  Result:= 0;
  R:= TRegistry.Create(RegAccess);
  try
    R.RootKey:= HKEY_LOCAL_MACHINE;
    if R.KeyExists(GetSQLInstanceConfigKey(Instance)+'Tcp\IPAll\') then begin
      if R.OpenKey(GetSQLInstanceConfigKey(Instance)+'Tcp\IPAll\', False) then begin
        if R.ValueExists('TcpPort') then begin
          Result:= R.ReadInteger('TcpPort');
        end;
        R.CloseKey;
      end;
    end;
  finally
    R.Free;
  end;
end;

function SetSQLInstanceTCPPort(const Instance: WideString; const Port: Integer): Bool; stdcall;
var
  R: TRegistry;
begin
  Result:= False;
  R:= TRegistry.Create(RegAccess);
  try
    R.RootKey:= HKEY_LOCAL_MACHINE;
    if R.KeyExists(GetSQLInstanceConfigKey(Instance)+'Tcp\IPAll\') then begin
      if R.OpenKey(GetSQLInstanceConfigKey(Instance)+'Tcp\IPAll\', False) then begin
        if R.ValueExists('TcpPort') then begin
          R.WriteInteger('TcpPort', Port);
          Result:= True;
        end;
        R.CloseKey;
      end;
    end;
  finally
    R.Free;
  end;
end;

function GetSQLInstanceTCPDynamicPorts(const Instance: WideString): Integer; stdcall;
var
  R: TRegistry;
begin
  Result:= 0;
  R:= TRegistry.Create(RegAccess);
  try
    R.RootKey:= HKEY_LOCAL_MACHINE;
    if R.KeyExists(GetSQLInstanceConfigKey(Instance)+'Tcp\IPAll\') then begin
      if R.OpenKey(GetSQLInstanceConfigKey(Instance)+'Tcp\IPAll\', False) then begin
        if R.ValueExists('TcpDynamicPorts') then begin
          Result:= R.ReadInteger('TcpDynamicPorts');
        end;
        R.CloseKey;
      end;
    end;
  finally
    R.Free;
  end;
end;

function SetSQLInstanceTCPDynamicPorts(const Instance: WideString; const Port: Integer): Bool; stdcall;
var
  R: TRegistry;
begin
  Result:= False;
  R:= TRegistry.Create(RegAccess);
  try
    R.RootKey:= HKEY_LOCAL_MACHINE;
    if R.KeyExists(GetSQLInstanceConfigKey(Instance)+'Tcp\IPAll\') then begin
      if R.OpenKey(GetSQLInstanceConfigKey(Instance)+'Tcp\IPAll\', False) then begin
        if R.ValueExists('TcpDynamicPorts') then begin
          R.WriteInteger('TcpDynamicPorts', Port);
          Result:= True;
        end;
        R.CloseKey;
      end;
    end;
  finally
    R.Free;
  end;
end;




exports
  Is64BitOS,
  RegAccess,
  GetSQLInstanceList,
  GetSQLInstanceID,
  GetSQLInstanceExists,
  GetSQLInstanceRootKey,
  GetSQLInstanceVerKey,
  GetSQLInstanceConfigKey,
  GetSQLInstanceVer,
  GetSQLInstanceProtocolEnabled,
  SetSQLInstanceProtocolEnabled,
  GetSQLInstanceTCPPort,
  SetSQLInstanceTCPPort,
  GetSQLInstanceTCPDynamicPorts,
  SetSQLInstanceTCPDynamicPorts;

begin
end.
\$\endgroup\$
  • \$\begingroup\$ <Review>Although registry are tasty piece of cake, I would strongly recommend you to use WMI for your task. That's many times recommended suggestion.</Review> :) \$\endgroup\$ – TLama Feb 9 '14 at 20:40
  • \$\begingroup\$ <response>@TLama Indeed :-)</response> \$\endgroup\$ – Jerry Dodge Feb 9 '14 at 20:45
  • \$\begingroup\$ @TLama I've seen awkward occasions where clients disable WMI, IT people can enforce some strange rules \$\endgroup\$ – Jerry Dodge Feb 9 '14 at 21:08
  • \$\begingroup\$ It's upon you. You may continue relying on registy keys which may be suddenly changed with some SQL Server update. I wouldn't do that. Hence my short review... \$\endgroup\$ – TLama Feb 9 '14 at 21:15
  • \$\begingroup\$ I think we should migrate this to dba.stackexchange.com \$\endgroup\$ – Phrancis Jul 11 '14 at 4:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.