1

The dbatools module's Find-DbaInstance can accept a computer name and return a list of instances on that machine. Is there an equivalent in the SqlServer module that does this? I tried Get-SqlInstance, but it seems to need actual instance names, as opposed to being able to use it for instance discovery. I'd prefer to use dbatools, but the SqlServer module is what I have consistent access to in the current environment.

Alex Pixley
  • 69
  • 6
  • 1
    Which type of scan do you want: WMI or SQL Browser or a specific TCP port or SPN lookup? – Charlieface May 30 '22 at 20:53
  • @Charlieface, WMI seems like it would be the most reliable (Browser might be disabled, the port *may* be something other than 1433, etc.). I am attempting to find every last 'rogue' install of SQL Server as those unknown instances are the most vulnerable (you can't patch them or retire them if you don't even know they exist). – Alex Pixley May 31 '22 at 13:27
  • Not sure about WMI, maybe look through the code https://github.com/dataplat/dbatools/blob/development/functions/Get-DbaService.ps1 Is there any reason you don't want to install DBA Tools, it's pretty easy – Charlieface May 31 '22 at 15:02
  • Using dbatools is the ultimate goal, but the rollout is only partially complete and our security software (after an upgrade) has suddenly started labeling dbatools as 'potentially malicious' and blocks it from running, so until that's remedied, it's not an option. – Alex Pixley May 31 '22 at 17:46

1 Answers1

1

There are many good results that dont even depend on the sqlserver module in this article

eg.

$SQLInstances = Invoke-Command -ComputerName "localhost" {
    (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
}

foreach ($sql in $SQLInstances) {
    [PSCustomObject]@{
        ServerName = $sql.PSComputerName
        InstanceName = $sql
    }
}
Otter
  • 1,086
  • 7
  • 18
  • Based on this [link](https://stackoverflow.com/questions/3570738/how-to-get-a-list-of-all-the-ms-sql-server-instances-on-the-local-machine), I have avoided the method you suggest. I am trying to find every last 'rogue' install of SQL Server as those unknown instances are the most vulnerable (you can't patch them or retire them if you don't even know they exist). – Alex Pixley May 31 '22 at 13:23
  • Have a look at the section labelled `Get-Service cmdlet` in the link I mentioned, that uses WMI to some extent, happy to edit my answer if that is your preferred method? – Otter May 31 '22 at 14:03
  • I worked through the different methods mentioned in the article. I was surprised at how well using `Get-ChildItem` addressed this problem and a couple of others I've been dealing with. For what I'm doing, It's even better than SMO. – Alex Pixley May 31 '22 at 19:04