While investigating alternative options a few weeks back, the only method that I have found to retrieve the status for each SSO user is to directly connect to the vCenter SSO Database. There are two specific tables of interest, one which provides the current password policy and the other providing the last password changed date for each SSO user:
Instead of having you manually dig around in the SSO Database, I have created a Perl script called getSSOUserExpiration.pl which can connect to either a MSSQL or vPostgress backend SSO database. The script which will automatically list out the current password policy as well as user accounts that will be expiring in N days, where N is input provided by the user. You also have the ability to configure the script to automatically email you the results which is nice for a daily or weekly report and can be setup using a cronjob or a scheduled task. There are several configuration variables that will need to be adjusted based on your environment and these are all located within the script itself. For more details on how to setup and use the script, please take a look at the Setup and Configuration section below.
Note: To reduce any negative impact to the vCenter SSO Database, you should add or ask your DBA's to create a limited read-only account and limit access to the following tables above. You may even be able to have your DBA's create a scheduled routine for the specific queries and have that emailed to you internally.
Here is a screenshot of connecting to a vPostgres backend Database:
Note: The email body should contain the specific vCenter SSO Database, but I am not sure why it is not showing up in Gmail, but it does work for other email clients.
Setup and Configuration
To connect to a vPostgres DB, you will need to install the following two perl packages: perl-DBI and perl-DBD-Pg. In this example, I am using the vMA appliance and the zypper package installer. For more details on how to add a SLES repo, please take a look at the following article. I also assume if you are connecting to a vPostgres DB, then you are using the VCSA (vCenter Server Appliance) and by default it does not accept remote connections. We will need to also make two configuration changes to the VCSA for our script to be able to connect to the database.
Step 1 - Run the following two commands to install both perl packages:
sudo zypper in perl-DBIStep 2 - SSH into your VCSA and in the following configuration file /storage/db/vpostgres/pg_hba.conf you will need to add the network in which you will be connecting from:
sudo zypper in perl-DBD-Pg
host all all 172.30.0.0/24 trustStep 3 - SSH into your VCSA and in the following configuration file /storage/db/vpostgres/postgresql.conf you will need to add the IP Address(s) that you want vPostgres to listen for remote connection. If you use "*", it will allow all addressees:
listen_addresses = '*'Step 4 - For the changes to go into effect, you will need to restart the vPostgres DB by running the following command:
service vmware-vpostgres restartStep 5 - Modify the getSSOUserExpiration.pl script and provide the credentials to your vCenter SSO DB. If you need help in identifying the vCenter SSO DB credentials, please refer to this article for the details.
To connect to an MSSQL DB, there are a few additional steps and packages that will be required. We will be using FreeTDS which provides libraries to connect to an MSSQL DB for UNIX/Linux platforms. There was a bit of trial and error in getting the MSSQL solution working and I would like to thank Reuben Stump for his assistance. The following article was used as a reference for the setup below.
Step 1 - Run the following two commands to install the required packages:
sudo zypper in perl-DBIStep 2 - Download and extract the contents of the FreeTDS package:
sudo zypper in gcc
wget ftp://ftp.astron.com/pub/freetds/stable/freetds-stable.tgzStep 3 - Compile and install FreeTDS under /usr/local/freetds:
tar -zxvf freetds-stable.tgz
export SYBASE=/usr/local/freetds/Step 4 - Add your vCenter SSO Server details into the FreeTDS configuration file located in /usr/local/freetds/etc/freetds.conf
sudo make install
[sso]In the example above, I named my database entry "sso" but you can use any name and this will be referenced when editing the script in step 5.
host = 172.30.0.239
port = 1433
tds version = 7.0
Step 5 - Modify the getSSOUserExpiration.pl script and provide the credentials to your vCenter SSO DB.