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
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.
WMI
for your task. That's many times recommended suggestion.</Review> :) \$\endgroup\$ – TLama Feb 9 '14 at 20:40