Lazarus

Programming => Databases => Topic started by: juco on July 12, 2018, 02:47:27 pm

Title: Discover mssql servers in the network
Post by: juco on July 12, 2018, 02:47:27 pm
Hi guys!!!

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

Regards

Juan

Title: Re: Discover mssql servers in the network
Post by: Thaddy on July 12, 2018, 03:36:53 pm
In MY networks you can't..... Only the sysadmin knows.
If you mean as sysadmin? then it is possible..
Title: Re: Discover mssql servers in the network
Post by: juco on July 12, 2018, 03:53:38 pm
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
Title: Re: Discover mssql servers in the network
Post by: 440bx on July 12, 2018, 04:44:55 pm
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.
Title: Re: Discover mssql servers in the network
Post by: Thaddy on July 12, 2018, 05:31:43 pm
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.
Title: Re: Discover mssql servers in the network
Post by: juco on July 12, 2018, 05:52:15 pm
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][+][-]
  1.  
  2. unit uGetServers;
  3.  
  4. interface
  5.  
  6. uses
  7.   Windows, Classes, SysUtils;
  8.  
  9. // broadcasted so that any single unit can expose method to handle enumerating
  10. // their type of server
  11. type TServerInfo101 = record
  12.     platform_id: DWORD;
  13.     name: PWideChar;
  14.     version_major: DWORD;
  15.     version_minor: DWORD;
  16.     server_type: DWORD;
  17.     comment: PWideChar;
  18.   end;
  19.  
  20.     PServerInfo101 = ^TServerInfo101;
  21.  
  22. const
  23.   NERR_SUCCESS = 0;
  24.   MAX_PREFERRED_LENGTH = DWORD(-1);
  25.   SV_TYPE_WORKSTATION = $00000001;
  26.   SV_TYPE_SERVER = $00000002;
  27.   SV_TYPE_SQLSERVER = $00000004;
  28.   SV_TYPE_DOMAIN_CTRL = $00000008;
  29.   SV_TYPE_DOMAIN_BAKCTRL = $00000010;
  30.   SV_TYPE_TIME_SOURCE = $00000020;
  31.   SV_TYPE_AFP = $00000040;
  32.   SV_TYPE_NOVELL = $00000080;
  33.   SV_TYPE_DOMAIN_MEMBER = $00000100;
  34.   SV_TYPE_PRINTQ_SERVER = $00000200;
  35.   SV_TYPE_DIALIN_SERVER = $00000400;
  36.   SV_TYPE_XENIX_SERVER = $00000800;
  37.   SV_TYPE_SERVER_UNIX = SV_TYPE_XENIX_SERVER;
  38.   SV_TYPE_NT = $00001000;
  39.   SV_TYPE_WFW = $00002000;
  40.   SV_TYPE_SERVER_MFPN = $00004000;
  41.   SV_TYPE_SERVER_NT = $00008000;
  42.   SV_TYPE_POTENTIAL_BROWSER = $00010000;
  43.   SV_TYPE_BACKUP_BROWSER = $00020000;
  44.   SV_TYPE_MASTER_BROWSER = $00040000;
  45.   SV_TYPE_DOMAIN_MASTER = $00080000;
  46.   SV_TYPE_SERVER_OSF = $00100000;
  47.   SV_TYPE_SERVER_VMS = $00200000;
  48.   SV_TYPE_WINDOWS = $00400000; // Windows95 and above
  49.   SV_TYPE_DFS = $00800000; // Root of a DFS tree
  50.   SV_TYPE_CLUSTER_NT = $01000000; // NT Cluster
  51.   SV_TYPE_DCE = $10000000; // IBM DSS (Directory and Security Services) or equivalent
  52.   SV_TYPE_ALTERNATE_XPORT = $20000000; // return list for alternate transport
  53.   SV_TYPE_LOCAL_LIST_ONLY = $40000000; // Return local list only
  54.   SV_TYPE_DOMAIN_ENUM = $80000000;
  55.   SV_TYPE_ALL = $FFFFFFFF; // handy for NetServerEnum2
  56.  
  57. function NetServerEnum(const ServerName: PWideString;
  58.                        level: DWORD;
  59.                        var Buffer: pointer;
  60.                        PrefMaxLen: DWORD;
  61.                        var EntriesRead: DWORD;
  62.                        var TotalEntries: DWORD;
  63.                        ServerType: DWORD;
  64.                        const Domain: PWideChar;
  65.                        var ResumeHandle: DWORD): DWORD; stdcall; external
  66. 'netapi32.dll';
  67.  
  68. function NetApiBufferFree(Buffer: pointer): DWORD; stdcall; external
  69. 'netapi32.dll';
  70.  
  71. function GetServerNames(const ServerType:DWORD):TStringList;
  72. implementation
  73.  
  74. function GetServerNames(const ServerType:DWORD):TStringList;
  75. var
  76.    Buffer: pointer;
  77.    EntriesRead,i,ErrCode,ResumeHandle,TotalEntries: DWORD;
  78.    PDomainUnicode: PWideChar;
  79.    ServerInfo: PServerInfo101;
  80.    slServerNames: TStringList;
  81. begin
  82.    slServerNames := TStringList.Create;
  83.    ResumeHandle := 0;
  84.  
  85.    PDomainUnicode:= nil;
  86.    errCode := NetServerEnum(nil, 101, Buffer, MAX_PREFERRED_LENGTH,
  87.                             EntriesRead, TotalEntries, ServerType,
  88.                                                         PDomainUnicode, ResumeHandle);
  89.  
  90.    if (errCode <> NERR_SUCCESS) then
  91.       slServerNames.Add('Can''t enumerate servers!')
  92.    else begin
  93.      try
  94.         ServerInfo := Buffer;
  95.         for i := 1 to EntriesRead do
  96.            begin
  97.            slServerNames.Add(ServerInfo^.name);
  98.            Inc(ServerInfo);
  99.            end;
  100.        if slServerNames.Count = 0 then
  101.           slServerNames.Add('No servers available!');
  102.      finally
  103.         NetApiBufferFree(Buffer);
  104.         Result := slServerNames;
  105.      end; // end of try finally
  106.    end;
  107. end;
  108.  
  109. end.    
  110.  
  111.  
  112.  
Title: Re: Discover mssql servers in the network
Post by: Thaddy on July 12, 2018, 06:14:31 pm
 :D If it works for you it's OK, but MY network won't allow that.
Title: Re: Discover mssql servers in the network
Post by: juco on July 12, 2018, 06:31:43 pm
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
Title: Re: Discover mssql servers in the network
Post by: Thaddy on July 12, 2018, 06:44:24 pm
ADO 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.
Title: Re: Discover mssql servers in the network
Post by: juco on July 12, 2018, 06:52:49 pm
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 :-)
Title: Re: Discover mssql servers in the network
Post by: Thaddy on July 12, 2018, 07:10:11 pm
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 :-)
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!
 
Title: Re: Discover mssql servers in the network
Post by: juco on July 12, 2018, 07:43:01 pm
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
TinyPortal © 2005-2018