Building inventory of sql servers in Active Directory

1

We need to scan our entire network to build the list of all machines where SQL Servers are found. Once we have the list we will be building the report with certain server settings for each SQL Server.

I tried SQLCMD -L but it list only some servers but not all, so it is probably not reliable.

All Other ways that I found on www include installing something on my machine.

Is there a way to do this without installing any additional tools on the machine I will be running this process from? I have admin privileges in the network.

2

Windows has a built-in utility, sc.exe, that can query/list the services running on a system:

https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-R2-and-2012/dd228922(v=ws.11)

https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-R2-and-2012/dd228921(v=ws.11)

Either option should work. If you generate a list of server names into a file (e.g. servers.txt) you can process them all from the command line:

for /F %s in (servers.txt) do sc %s query sqlsrvr

I can't remember the exact service name for SQL Server that sc reports, but once you find it on one it will be the same for others. This can also return SQL Server services that are installed but not running, if you use state= all in the query. Check the documentation for the correct syntax.

3

Thanks.
When running this command whether pre-creating the file or not i m getting error

H:>for /F %s in (c:\tempp\servers.txt) do sc %s query sqlsrvr
The system cannot find the file c:\tempp\servers.txt.

4

You need to pre-create the file, and put 1 server name per line. Double-check that your path and file name are correct.

5

That s the whole issue. WE DO NOT KNOW the server names. we need to discover them.

Listing the names of every sql server in the network.

6

We have used this in the last

7

If you have admin privileges on the network, you can get a list of all computers from Active Directory. My ds- util skills are a little rusty but this should work (from a domain controller or other machine with AD tools installed):

for /f %a in ('dsquery computer -name "*"') do dsget computer %a -samid >>servers.txt

You need dsget because dsquery only returns the full distinguished name. More info here:

https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-R2-and-2012/cc731950(v=ws.11)

https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-r2-and-2012/cc730720(v=ws.11)

1 Like