SQL Account Discovery

[NateG]

Hi all,

 

New Passwordstate user here, brand new install on build 8993. We're trying to set up a MS SQL account discovery jobs and are running into a few oddities.

 

1. First, there does not seem to be a way to define multiple SQL instances for a host without having to define multiple host entries (one for each instance). This is pretty clunky, and brings up 2:
2. There's no auto-discovery of SQL instances - why not leverage the functionality of the SQL Browser service to enumerate instances automagically, then tie each instance to a Privileged Account Credential?
3. After creating a second host and specifying a different instance (let's call this instance B), the discovery job "finds" accounts from instance A and either (I can't figure out what causes one to happen vs the other):
   - adds a duplicate record to the password list. In other words, we wind up with two entries: servername (instance A)\fred and servername (instance B)\fred  - even though fred isn't a user on instance B.
   - associates instance B's unique discovered users with instance A  
4. The account discovery job statistics are not updated as expected. The Last Discovery Took field is always 00:00:00, unless you run a job manually and don't navigate away from the page.
5. There is no way (that I've found) to have the instance name used as a variable. This means that the associated password list has identical Title and Description entries for each instance, leading to confusion.

 

Any help is appreciated!

[support]

Hi Nate,

 

Please see some comments below in Red.

1. First, there does not seem to be a way to define multiple SQL instances for a host without having to define multiple host entries (one for each instance). This is pretty clunky, and brings up 2: From a SQL Server perspective, these are different servers which is why we require separate host records for them. And so they also can be used with our Client Based Remote Session Launcher.
2. There's no auto-discovery of SQL instances - why not leverage the functionality of the SQL Browser service to enumerate instances automagically, then tie each instance to a Privileged Account Credential? We'd need to investigate if the Browser Service exposes the instances programatically, via PowerShell. If this is something you need, could you log a feature request on the following section on our forums - https://www.clickstudios.com.au/community/index.php?/forum/38-feature-requests/
3. After creating a second host and specifying a different instance (let's call this instance B), the discovery job "finds" accounts from instance A and either (I can't figure out what causes one to happen vs the other):
   - adds a duplicate record to the password list. In other words, we wind up with two entries: servername (instance A)\fred and servername (instance B)\fred  - even though fred isn't a user on instance B.
   - associates instance B's unique discovered users with instance A  We'll need to investigate this, as we've never had this reported to us before - we will let you know what we find
4. The account discovery job statistics are not updated as expected. The Last Discovery Took field is always 00:00:00, unless you run a job manually and don't navigate away from the page. Can you tell us how many SQL Servers you are querying here?
5. There is no way (that I've found) to have the instance name used as a variable. This means that the associated password list has identical Title and Description entries for each instance, leading to confusion.

 

Regards

Click Studios

[NateG]

Thanks - I logged the feature request here. I'm only querying 3 SQL instances right now, all on the same server. Let me know if you need me to provide anything for the other issues.

[support]

Hi Nate,

 

With your SQL instances, did you install these with either different port numbers, or different IP Addresses, or are each of your instances using the same port and IP Address?

Regards

Click Studios

[NateG]

They're using dynamic ports and listening on the same IP.

[support]

Hi NateG,

 

We've just been looking into this, and the Discovery Job is working for us if you specify the correct Instance Name and Port Number. When using Dynamic Ports, in our testing it's only dynamic initially when first set, so you can specify these ports on the Host records in Passwordstate. You just need to look them up using the SQL Server Configuration Manager tool, like in the screenshot below.

 

 

Regards

Click Studios

[NateG]

Well, the point of this is to not need to know the port, and there is no guarantee that the port number for an instance will not change. It's determined at startup: https://docs.microsoft.com/en-us/sql/tools/configuration-manager/tcp-ip-properties-ip-addresses-tab?view=sql-server-ver15#dynamic-ports

[support]

Hi Nate,

 

The reason we suggested to specify the ports is because during our testing, as long as we had different SQL Instance Names, we did not need to specify port numbers for the Hosts in Passwordstate - and we did not witness the wrong accounts being associated with the wrong host record.

 

In our experience the port did not change on startup, so possibly just specifying a port will help instead.

Regards

Click Studios