Discover mssql servers in the network
lazarus / 16-Databases

----------------------------------------------------------------------
juco · 2018-07-12 12:47

Hi guys!!!

How can I get a list of the mssql servers available in my network?

Regards

Juan

----------------------------------------------------------------------
Thaddy · 2018-07-12 13:36

In MY networks you can't..... Only the sysadmin knows.
If you mean as sysadmin? then it is possible..

----------------------------------------------------------------------
juco · 2018-07-12 13:53

Of course! I talk about "my" LAN, I know that using ODBC dlls is
achieved, but really now I do not remember how.
Using ADO you can too, but I do not have support for ADO in Lazarus

----------------------------------------------------------------------
440bx · 2018-07-12 14:44

Hello Juco,

One way you can do it is by creating a console process that runs a
powershell script that gets the information you want. Redirecting the
console's output to a file you subsequently read when the powershell
script is done.

If that is a solution for you, the link below will give you a lot of
information on how to do that with powershell.  Pick, choose and
implement based on your needs.

https://www.mssqltips.com/sqlservertip/1759/retrieve-a-list-of-sql-
server-databases-and-their-properties-using-powershell/

Hopefully, that will be helpful to you.

----------------------------------------------------------------------
Thaddy · 2018-07-12 15:31

Bit of nonsense again.
Just make sure ports and rights (I use certificates for traffic
between servers). As sysadmin you know the ports. They are in your
configuration.

----------------------------------------------------------------------
juco · 2018-07-12 15:52

thanks to everyone for the help, I found this code and it works, I
share it with you (http://coding.derkeiler.com/Archive/Delphi/borland.
public.delphi.database.ado/2004-04/0175.html)

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  unit
uGetServers; interface uses  Windows, Classes, SysUtils; //
broadcasted so that any single unit can expose method to handle
enumerating// their type of servertype TServerInfo101 = record   
platform_id: DWORD;    name: PWideChar;    version_major: DWORD;   
version_minor: DWORD;    server_type: DWORD;    comment: PWideChar; 
end;     PServerInfo101 = ^TServerInfo101; const  NERR_SUCCESS = 0; 
MAX_PREFERRED_LENGTH = DWORD(-1);  SV_TYPE_WORKSTATION = $00000001; 
SV_TYPE_SERVER = $00000002;  SV_TYPE_SQLSERVER = $00000004; 
SV_TYPE_DOMAIN_CTRL = $00000008;  SV_TYPE_DOMAIN_BAKCTRL = $00000010; 
SV_TYPE_TIME_SOURCE = $00000020;  SV_TYPE_AFP = $00000040; 
SV_TYPE_NOVELL = $00000080;  SV_TYPE_DOMAIN_MEMBER = $00000100; 
SV_TYPE_PRINTQ_SERVER = $00000200;  SV_TYPE_DIALIN_SERVER =
$00000400;  SV_TYPE_XENIX_SERVER = $00000800;  SV_TYPE_SERVER_UNIX =
SV_TYPE_XENIX_SERVER;  SV_TYPE_NT = $00001000;  SV_TYPE_WFW =
$00002000;  SV_TYPE_SERVER_MFPN = $00004000;  SV_TYPE_SERVER_NT =
$00008000;  SV_TYPE_POTENTIAL_BROWSER = $00010000; 
SV_TYPE_BACKUP_BROWSER = $00020000;  SV_TYPE_MASTER_BROWSER =
$00040000;  SV_TYPE_DOMAIN_MASTER = $00080000;  SV_TYPE_SERVER_OSF =
$00100000;  SV_TYPE_SERVER_VMS = $00200000;  SV_TYPE_WINDOWS =
$00400000; // Windows95 and above  SV_TYPE_DFS = $00800000; // Root of
a DFS tree  SV_TYPE_CLUSTER_NT = $01000000; // NT Cluster  SV_TYPE_DCE
= $10000000; // IBM DSS (Directory and Security Services) or
equivalent  SV_TYPE_ALTERNATE_XPORT = $20000000; // return list for
alternate transport  SV_TYPE_LOCAL_LIST_ONLY = $40000000; // Return
local list only  SV_TYPE_DOMAIN_ENUM = $80000000;  SV_TYPE_ALL =
$FFFFFFFF; // handy for NetServerEnum2 function NetServerEnum(const
ServerName: PWideString;                       level: DWORD;         
            var Buffer: pointer;                       PrefMaxLen:
DWORD;                       var EntriesRead: DWORD;                 
    var TotalEntries: DWORD;                       ServerType: DWORD; 
                     const Domain: PWideChar;                     
 var ResumeHandle: DWORD): DWORD; stdcall;
external'netapi32.dll'; function NetApiBufferFree(Buffer: pointer):
DWORD; stdcall; external'netapi32.dll'; function GetServerNames(const
ServerType:DWORD):TStringList;implementation function
GetServerNames(const ServerType:DWORD):TStringList;var   Buffer:
pointer;   EntriesRead,i,ErrCode,ResumeHandle,TotalEntries: DWORD; 
 PDomainUnicode: PWideChar;   ServerInfo: PServerInfo101; 
 slServerNames: TStringList;begin   slServerNames :=
TStringList.Create;   ResumeHandle := 0;    PDomainUnicode:= nil; 
 errCode := NetServerEnum(nil, 101, Buffer, MAX_PREFERRED_LENGTH,    
                      EntriesRead, TotalEntries, ServerType,         
                                              PDomainUnicode,
ResumeHandle);    if (errCode <> NERR_SUCCESS) then     
slServerNames.Add('Can''t enumerate servers!')   else begin     try  
    ServerInfo := Buffer;        for i := 1 to EntriesRead do        
 begin           slServerNames.Add(ServerInfo^.name);         
 Inc(ServerInfo);           end;       if slServerNames.Count = 0
then          slServerNames.Add('No servers available!');     finally 
      NetApiBufferFree(Buffer);        Result := slServerNames;   
 end; // end of try finally   end;end; end.

----------------------------------------------------------------------
Thaddy · 2018-07-12 16:14

If it works for you it's OK, but MY network won't allow that.

----------------------------------------------------------------------
juco · 2018-07-12 16:31

if your problem is the ports and permissions, you can use sql-dmo that
connects directly to the MSSQL server, but with the inconvenience of
having to install in each workstation ... ADO would be another
solution, maybe with OLE you can achieve that

Juan

----------------------------------------------------------------------
Thaddy · 2018-07-12 16:44

Quote from: juco on July 12, 2018, 06:31:43 pmADO would be another
solution,
Has nothing to do with network security.... Oh, well....
Listen:
A network should simply drop a connection on ports that are not
available to other IP's than that you have specified.
A connection can be made if a certificate for that specific database
is in place.

If you do it any other way you WILL run into BIG trouble.

Nothing to do with databases as such, but with simple security on your
network.

----------------------------------------------------------------------
juco · 2018-07-12 16:52

It looks creepy so much internal security! Wow !, that is, if you do
not have the permission you can not access the SQL server ...  well,
it's better not to have problems :-)

----------------------------------------------------------------------
Thaddy · 2018-07-12 17:10

Quote from: juco on July 12, 2018, 06:52:49 pmIt looks creepy so much
internal security! Wow !, that is, if you do not have the permission
you can not access the SQL server ...  well, it's better not to have
problems :-)
We have a national kind of health database. Would you like a nurse to
be able to access information and medication you have shared in
private with your doctor?
Or your employer to have access to the same information?  Of course
not!
It is naive if you do not protect your databases BEYOND access. Costs
little,prevents mistakes.... (as in the above case: miss-use all over
the place....)
I know the architect. He knows he failed.
The cost of the operation to save the privacy of patients is 10 times
more than the original code (written -partly - in Delphi).
And I warned him!

----------------------------------------------------------------------
juco · 2018-07-12 17:43

Yes, I understand perfectly what you explain to me, and the problems
that cause that kind of error ... besides as you said: he was
warned....

I hope you get a solution

Regards

Juan