Configuring Database

Configuring Database

In GUI Setups 

AssetExplorer is bundled with PostgreSQL database. You can also configure the application to set up MSSQL database.  

Configure PostgresSQL database

  1. Go to <AssetExplorer_Home>\bin directory in the command prompt and execute the changeDBServer.bat [changeDBServer.sh for Linux] file.
  2. This opens the Database Setup Wizard. Fill in the respective server details:
    1. Server Type: Select Postgres SQL server type from the drop-down.
    2. Host Name: The IP Address/hostname in which the database is available. The default hostname is localhost.
    3. Port: The default port is 65432 (can be changed as required).
    4. Database: The default database name is assetexplorer (can be changed as required).
    5. User Name: Specify the user name to login to the server (default user name is sdpadmin).
    6. Password: Enter password (default password is sdp@123).
      Note: This credential will give read and write access to users for the application's database alone.
  3. To check the availability of the connection, click the Test button. A window pops up showing Connection Established message.
  4. Click OK to proceed.
  5. Click Save.
Info
AssetExplorer  (AE) uses Endpoint Central agents from BUILD NO 6900 for scanning Windows, Linux, and Mac machines. If you are using Endpoint Central in your environment, then Endpoint Central will be installed with bundled PGSQL database by default. 

Auto-generation of default PostgreSQL database password for sdpadmin  

For users in AssetExplorer build 7310 and above, the default PostgreSQL database password will now be auto-generated for sdpadmin for security reasons.
 
When and how will the password be auto-generated?
  1. For existing setups, the password will be auto-generated during migration if the default password was not updated previously.
  2. For fresh setups, the default password will be auto-generated when the server is started for the first time.
  3. For setups that switch to PostgreSQL database after migrating to 7310 or later, the database password will be auto-generated when executing the changeDBServer.bat/changeDBServer.sh script.
Info
Recommendation for Connecting to AssetExplorer through external tools:
To connect to AssetExplorer through external servers, we recommend using rouser. For bundled Postgres database, the default password is "EdgarFCodd". For External Postgres/MSSQL database, refer here

Retrieve Postgres Password 

The user who installs AssetExplorer can view the auto-generated password by invoking the following script:
  1. Windows: decryptPostgresPassword.bat
  2. Linux: decryptPostgresPassword.sh
Info
The PostgreSQL password for sdpadmin cannot be retrieved after the database configuration is updated from bundled Postgres to external Postgres/MSSQL. Ensure the password is retrieved and stored in a secure location for future use.
 
If the sdpadmin password is reset to the default password, the application will not start.
 
While migrating from build 7300 to 7320 or later, the decryptPostgresPassword.bat/decryptPostgresPassword.sh file will not be available. Contact support to retrieve the sdpadmin password.

Configuring MSSQL database

  1. Go to <AssetExplorer_Home>\bin directory in the command prompt and execute the changeDBServer.bat [changeDBServer.sh for Linux] file.
  2. This opens the Database Setup Wizard. Fill in the respective server details:
    1. Server Type: Select MSSQL server type from the drop-down.
    2. Host Name: The IP Address/hostname in which the database is available. The default hostname is localhost.
    3. Port: The default port is 1433 (can be changed as required).
    4. Database: The default database name will be assetexplorer (can be changed as required).
    5. User Name: Provide username (default username is sdpadmin).
    6. Password: Enter password (default password is sdp@123).
    7. Master Key: Enter a Master Key password to encrypt the SCHAR columns in the database. Master Key password will be asked only during the creation of a database.
  3. Check the availability of the connection by clicking the Test button. A pop-up window showing Connection Established message appears.
  4. Click OK to proceed.
  5. Click Save.

Info
Only admin users can create a master key and it is not stored anywhere. To alter the master key password, refer here
AssetExplorer (AE) uses Endpoint Central agents from BUILD NO 6900 for scanning Windows, Linux, and Mac machines. If you are using Endpoint Central in your environment, then Endpoint Central will be installed with bundled PGSQL database by default. To learn how to change the DC database to MSSQL, click here


To create or alter the master key password manually, use the following queries.
  1. To create a master key password, use "create master key encryption by password='' ; . For altering a master key password use this query:  "alter master key encryption by password='' ;
  2. Internally, customer-config.xml will be saved with the encrypted master key password.
  3. To check the availability of the connection, click Test. A window pops up showing Connection Established message.
  4. Click OK to proceed.
  5. Click Save.

Info
You need full Admin rights to configure the MSSQL database for Windows with versions 2012 and above. Note that the standard user account is of the administrator.
The login account for creating the database requires SQL authentication and it should have the following roles enabled:
      * dbcreator
      * Public
      * Sysadmin

Configure Azure SQL database

For Azure SQL Manage Instance Database,
  1. Ensure that the machine in which the application is to be installed is connected to the same network that hosts the Manage Instance Server.
  2. If the application is to be installed in a Virtual Machine hosted in a different network, use V-Net peering to connect to the network that hosts Manage Instance Server.
  3. Follow the steps given to configure MSSQL Database to complete the configuration.
Info
AssetExplorer uses Endpoint Central agents from BUILD NO #### for scanning Windows, Linux, and Mac machines. If you are using Endpoint Central in your environment, EndpointCentral gets installed with bundled PGSQL database by default.

Non-GUI Setups

In Windows

Run the command changeDBServer.bat [changeDBServer.sh for Linux] in the command prompt by passing the given parameters:
  1. C:\[AssetExplorer Home]\bin>changeDBServer.bat --console
The Database Server information will be fetched from the console.
Info
AssetExplorer uses Endpoint Central agents from BUILD NO 6900 for scanning Windows, Linux, and Mac machines. If you are using Endpoint Central in your environment, Endpoint Central gets installed with bundled PGSQL database by default.

Connect to Postgres SQL Database

  1. Go to <AssetExplorer_Home> \ pgsql \ bin in the command prompt.
    1. cd ManageEngine\AssetExplorer\pgsql\bin
  2. Enter the command: psql.exe -U postgres -p 65432 assetexplorer
            C:\> cd ManageEngine\AssetExplorer\pgsql\bin> psql.exe -U postgres -p 65432 assetexplorer

                  For version higher ####, use the following command

            C:\> cd ManageEngine\AssetExplorer\pgsql\bin> psql.exe - U sdpadmin -p 65432 assetexplorer
      3. Enter the password when prompted. The default password is sdp@123.

Here, assetexplorer is the name of the database. 65432 is the port on which AssetExplorer database runs. AssetExplorer uses Postgres account to connect to the database and does not use any password. You can connect to PostgreSQL database only from the server console.

Connect to MS SQL Server

Use SQL studio to connect to MS SQL database.
Default Database name: assetexplorer

In Linux

  1. Install the PostgreSQL client which supports your Linux version.
  2. Make sure that the command "psql" is in path. (Most often, psql should be under path, if not, you may add the complete path to psql command under PATH variable in the bashrc file.
  3. Go to <AssetExplorer_Home>\ pgsql \ bin in the command prompt.
    # cd <AssetExplorer_Home>/pgsql/bin
  4. Enter the command: # ./psql -U sdpadmin -h localhost -p 65432 assetexplorer

Troubleshooting Tips 

PostgreSQL Connection Resolution  

  1. Check if PostgreSQL server is running.
  2. Check if the server name or the port number is misspelled or incorrect.
  3. If there is a firewall blocking the port on the server, you will not be able to connect to the server. To confirm the firewall block connect to TCP/IP use \"telnet<server_host><port number>\"\n to confirm the block.
  4. If the issue persists, contact your system administrator.

MS SQL Connection Resolution   

  1. If a connection is refused and an exception is thrown by SQL Server as 'Unable to connect the server, then the reason for this could be any of the following.
  2. The server name is misspelled or the port number is incorrect.
  3. The SQL server was not configured to use TCP/IP. In this case, enable TCP/IP from SQL servers network utility application.
  4. If there is a firewall blocking the port 65432 on the server, then you will not be able to connect to the server. To confirm the firewall block connect to TCP/IP use \"telnet<server_host>65432\"\n to confirm the block.
  5. SQL Server Instance is not currently supported by AssetExplorer and will be available in the feature release. You can also connect to SQL Server named instance once if you know the machine name and port of the named instance.
  6. Create a new user with full privileges as shown below:

  1. While configuring SQL server properties select the authentication type as SQL server as shown below. Windows authentication is not supported by AssetExplorer currently.

  1. If the issue persists, contact your system administrator.

    • Related Articles

    • Database Migration Tool

      Migrate data between two databases as well as between operating systems (Linux to Windows and vice versa). Possible database migrations Data can be migrated between two databases of the same installation as well as also different installation between ...
    • Configuring SAML in AssetExplorer

      Role Required: SDAdmin Go to Admin > Organizational Details > SAML Single Sign-On. In the configurations tab, you will find two sections: Service Provider Details and Configure Identity Provider Details. Service Provider Details Under the Service ...
    • Configuring ADFS 3.0 as the Identity Provider

      Role Required: SDAdmin Before you start the configuration process, make sure that the AssetExplorer application is running in the HTTPS mode. Then, configure AssetExplorer as a Relying Party Trust (RPT). This can be done either manually or using the ...
    • Configuring G Suite as the Identity Provider

      Role Required: SDAdmin Log in to Google Workspace. Go to Apps > Web and mobile apps. Click Add app > Add custom SAML app. Provide the App name and Description. Upload the App icon and click Continue. On the displayed page, download the certificate ...
    • FAQs Related to Database, HTTPS, and SSL

      How to change the Endpoint Central database from Postgres to MSSQL? By default, Endpoint Central agents come with bundled PGSQL but also supports MSSQL. Click here for a list of MSSQL versions supported by Endpoint Central. For detailed instructions ...