Troubleshooting Database Connectivity from the Jamf Pro Server

Updated: 01 July 2021
Product: Jamf Pro

During startup, an error message appears indicating that Jamf Pro cannot establish a connection to the database.

This error occurs when the Jamf Pro server is unable to connect to the MySQL database when accessing the web interface. One or more of the following issues may explain the error:

  • The database connection details are not configured.

  • MySQL is not running on the database server.

  • The DataBase.xml file contains invalid MySQL server location details.

  • The database server is not accepting incoming connections.

  • The DataBase.xml file contains invalid user credentials.

  • The database user is not granted proper privileges to the database.

  • The database user password authentication plug-in is not properly configured.

Each of these potential issues are detailed below with steps to verify and resolve.
Note:

If the database connection error persists after trying these options to resolve, contact Jamf Support.

The Database Connection Details are Not Configured

Jamf Pro needs certain information to connect to the MySQL database, such as the MySQL user credentials and port number. If these or any other required details are missing from the DataBase.xml file, a database connection error will appear. You can view and configure the database connection details using Jamf Pro Server Tools. For instructions, see the Editing the Database Connection Using Jamf Pro Server Tools article.

MySQL is Not Running on the Database Server

The MySQL service must be running on the database server for Jamf Pro to establish a connection during startup. If MySQL is not running when Jamf Pro attempts to connect to the database, a database connection error will appear. Follow these steps to verify and resolve this issue.

Linux

  1. Log in to the server hosting the MySQL database.

  2. Verify that MySQL is running by executing one of the following commands:

    Red Hat Enterprise Linux:
    service mysqld status
    Ubuntu:
    service mysql status

    • If the command returns "Active: active (running)", this indicates that MySQL is currently running, and this is not the issue. Refer to the next possible issue to continue troubleshooting.

    • If the command returns "Active: inactive (dead)", this indicates that MySQL is not currently running. Continue following these steps to resolve.

  3. Execute the following command:

    Red Hat Enterprise Linux:
    service mysqld start
    Ubuntu:
    service mysql start

  4. After the service has started, reattempt connection to the database. If the database connection error persists, refer to the next possible issue in this article to continue troubleshooting.

Windows

  1. Log in to the server hosting the MySQL database.

  2. Open Services.

  3. Locate MySQL in the services list and verify that the service is running.

    • If Services reports that MySQL is Running, this is not the issue. Refer to the next possible issue in this article to continue troubleshooting.

    • If Services reports that MySQL is not running, continue following these steps to resolve.

  4. Click Start the service to start MySQL.

  5. Reattempt connection to the database. If the database connection error persists, refer to the next possible issue in this article to continue troubleshooting.

macOS

  1. Log in to the server hosting the MySQL database.

  2. Open System Preferences.

  3. Click MySQL. The status of MySQL is displayed.

  4. Verify that MySQL is running.

    • If the pane reports that the MySQL server instance is running, this is not the issue. Refer to the next possible issue in this article to continue troubleshooting.

    • If the pane reports that the MySQL server is not running, continue following these steps to resolve.

  5. Click Start MySQL to start the service. The displayed status of MySQL changes to "Running".

  6. On the Jamf Pro server, reattempt the connection to the database. If the database connection error persists, refer to the next possible issue in this article to continue troubleshooting.

The DataBase.xml File Contains Invalid MySQL Server Location Details

Jamf Pro uses information in the DataBase.xml file to locate the MySQL database. If the DataBase.xml file's server location details are incorrect, a database connection error will occur. Follow these steps to verify and resolve this issue.

  1. Log in to the server hosting Tomcat.
  2. Locate the DataBase.xml file, stored in one of the following locations:

    • Linux: /usr/local/jss/tomcat/webapps/ROOT/WEB-INF/xml/DataBase.xml

    • Windows: C:\Program Files\JSS\Tomcat\webapps\ROOT\WEB-INF\XML\DataBase.xml

    • Mac: /Library/JSS/Tomcat/webapps/ROOT/WEB-INF/xml/DataBase.xml

  3. Open the DataBase.xml file in a plain text editor and locate the following line:
    <ServerName>localhost</ServerName>
  4. Verify that the server location details are correct. If the database is hosted on the same server as the Tomcat server, the value should be set to "localhost", as shown in the above example. If the database is hosted on a separate server from the Tomcat server, the value should be either the fully qualified domain name or the IP address of the database server.
    1. If the server location details are correct, this is not the issue. Refer to the next possible issue in this article to continue troubleshooting.
    2. If the server location details are incorrect, continue following these steps to resolve.
  5. Edit the DataBase.xml file to reflect the correct server location details, and save the changes to the file.
  6. Restart Tomcat. For more information, see the Starting and Stopping Tomcat article.
  7. Reattempt connection to the database. If the database connection error persists, refer to the next possible issue in this article to continue troubleshooting.

The Database Server is Not Accepting Incoming Connections

Jamf Pro communicates with the database over port 3306. If the database server is not accepting traffic over port 3306, Jamf Pro will not be able to establish a connection, and a database connection error will occur. Follow these steps to verify and resolve this issue.
Note:

If MySQL is running on the same server as Tomcat, this is not the issue. Refer to the next possible issue in this article to continue troubleshooting.

  1. Work with your network administrator to verify that port 3306 is open both outbound on the Tomcat server and inbound on the database server.
    1. If port 3306 is open both outbound on the Tomcat server and inbound on the database server, this is not the issue. Refer to the next possible issue in this article to continue troubleshooting.
    2. If port 3306 is not open outbound on the Tomcat server, or if port 3306 is not open inbound on the database server, continue following these steps to resolve.
  2. Work with your network administrator to open port 3306 both outbound on the Tomcat server and inbound on the database server.
  3. Reattempt the connection to the database from the Jamf Pro server. If the database connection error persists, refer to the next possible issue in this article to continue troubleshooting.

The DataBase.xml File Contains Invalid MySQL User Credentials

Jamf Pro uses MySQL user information in the DataBase.xml file to login to MySQL. If the DataBase.xml file's MySQL user credentials are incorrect, a database connection error will occur. Follow these steps to verify and resolve this issue.

  1. Log in to the server hosting Tomcat.
  2. Locate the DataBase.xml file, stored in one of the following locations:

    • Linux: /usr/local/jss/tomcat/webapps/ROOT/WEB-INF/xml/DataBase.xml

    • Windows: C:\Program Files\JSS\Tomcat\webapps\ROOT\WEB-INF\XML\DataBase.xml

    • Mac: /Library/JSS/Tomcat/webapps/ROOT/WEB-INF/xml/DataBase.xml

  3. Open the DataBase.xml file in a plain text editor and locate the following lines:
    <DataBaseUser>database_username</DataBaseUser>
<DataBasePassword>database_password</DataBasePassword>

    These values are the MySQL user credentials Jamf Pro is using to attempt to access the Jamf Pro database.

  4. Verify that the credentials in the DataBase.xml file are valid by using them to attempt to log in to MySQL. On the database server, access the MySQL command line and log in with the user details identified in the previous step by executing one of the following:
    • Linux and Mac: 
      mysql -u database_username -p
      Note:

      Replace database_username in the above example with the database username identified in step 3.

    • Windows: 
      mysql.exe -u database_username -p
      Note:

      Replace database_username in the above example with the database username identified in step 3.

    1. If the login succeeds, you will see the MySQL command prompt: mysql>. This means that the MySQL user credentials contained in the DataBase.xml file are valid, and this is not your issue. Refer to the next possible issue in this article to continue troubleshooting.
    2. If the login fails, this indicates that the saved password for the database user is incorrect and needs to be changed. Continue following these steps to resolve.
  5. Change the password of the Jamf Pro database user. For instructions, see the "Changing the Password for the Jamf Pro MySQL Database" section of the Changing Specific Accounts in Jamf Pro and the MySQL Database article.
  6. Edit the DataBase.xml file to reflect the newly changed MySQL user credentials details. Save the changes to the file.
  7. Restart Tomcat. For more information, see the Starting and Stopping Tomcat article.
  8. Reattempt connection to the database. If the database connection error persists, refer to the next possible issue in this article to continue troubleshooting.

The Database User is Not Granted Proper Privileges to the Database

During startup, Jamf Pro logs in to MySQL as a specific database user. If the database user does not have proper permissions to access the Jamf Pro database, Jamf Pro will not be able to establish a connection and a database connection error will occur. Follow these steps to verify and resolve this issue.

  1. Log in to MySQL as the "root" user by executing one of the following:
    • Linux and Mac: 
      $mysql -u root -h localhost -p
    • Windows: 
      mysql.exe -h localhost --user=root -p
  2. Execute the following command:
    SHOW GRANTS for 'database_username'@'localhost';
    Note:

    Replace 'database_username'@'localhost' in the above example with your database username. in the above example with your database username.

    The command returns grant statements that have been made for the logged in database user, such as the following: 
    GRANT ALL PRIVILEGES ON .database_name.* TO 'database_username'@'localhost'
  3. Verify that the database user has proper privileges to the database.
    1. If the command returns a grant statement similar to the above example, this indicates the Jamf Pro database user has proper privileges to the Jamf Pro database. To continue troubleshooting the database connection error, contact Jamf Support.
    2. If the command does not return a grant statement similar to the above example, this indicates that the Jamf Pro user does not have proper permissions to the database. Continue following these steps to resolve.
  4. Execute the following command:
    GRANT ALL ON database_name.* TO 'database_username'@'localhost';
    Note:

    Replace database_name and 'database_username' with the database name and database username specific to your environment.

    Or, in the case of MySQL and Jamf Pro installed on different servers, execute something similar to the following: 
    GRANT ALL ON database_name.* TO 'database_username'@'123.456.78.910';
    Note:

    Replace database_name and 'database_username'@'123.456.78.910' with the database name and database username specific to your environment.

  5. On the Jamf Pro server, reattempt the connection to the database.

The Database User Password Authentication Plug-In is Not Properly Configured

Starting with MySQL 8.0, the MySQL database requires an upgraded default user password authentication plug-in to allow access. If this is not configured during installation, the connection will fail. Follow these steps to resolve this issue:

Linux

  1. Log in to MySQL as the "root" user by executing: 
    $mysql -u root -h localhost -p
  2. Execute a command similar to the following:
    alter user 'jamfsoftware'@'localhost' identified with mysql_native_password by 'jamfsw03';
    Note:

    Replace 'jamfsoftware'@'localhost' and 'jamfsw03' in the above command with the Jamf Pro database user credentials and location. These should match the credentials and location contained in the DataBase.xml file on the Jamf Pro server.

  3. Stop the MySQL server.

  4. Open the my.cnf file.

  5. Add the following entry:
    [mysqld] 
default-authentication-plugin=mysql_native_password

  6. Restart the MySQL server.

  7. Restart the Jamf Pro server.

  8. On the Jamf Pro Server, reattempt the connection to the database.

Windows

  1. Log in to MySQL as the "root" user by executing:
    mysql.exe -h localhost --user=root -p
  2. Execute a command similar to the following:
    alter user 'jamfsoftware'@'localhost' identified with mysql_native_password by 'jamfsw03';
    Note:

    Replace 'jamfsoftware'@'localhost' and 'jamfsw03' in the above command with the Jamf Pro database user credentials and location. These should match the credentials and location contained in the DataBase.xml file on the Jamf Pro server.

  3. Stop the MySQL server.

  4. Open the my.ini file in a text editor other than Notepad.

  5. Add the following entry:
    [mysqld]
default-authentication-plugin=mysql_native_password

  6. Restart the MySQL server.

  7. Restart the Jamf Pro server.

  8. On the Jamf Pro server, reattempt the connection to the database.

Mac

  1. Log in to MySQL as the "root" user by executing:
    $mysql -u root -h localhost -p
  2. Execute a command similar to the following:
    alter user 'jamfsoftware'@'localhost' identified with mysql_native_password by 'jamfsw03';
    Note:

    Replace 'jamfsoftware'@'localhost' and 'jamfsw03' in the above command with the Jamf Pro database user credentials and location. These should match the credentials and location contained in the DataBase.xml file on the Jamf Pro server.

  3. Open System Preferences.

  4. Click MySQL to open MySQL preferences.

  5. On the Instances tab and click Initialize Database.

  6. Select Use Legacy Password Encryption.

  7. When prompted, enter your "root" MySQL password.

  8. Click OK.

  9. Restart the Jamf Pro server.

  10. On the Jamf Pro server, reattempt the connection to the database.