[!INCLUDESQL Server 2019]

Whether or not the [!INCLUDEssbigdataclusters-ss-nover] is operating with Active Directory integration, AZDATA_PASSWORD is set during deployment. It provides a basic authentication to the cluster controller and master instance. This document describes how to manually update AZDATA_PASSWORD.

If the cluster is operating in non-Active Directory mode, update the Apache Knox Gateway password by doing the following steps:

1. Obtain the controller [!INCLUDEssNoVersion] credentials by running the following commands:

   a. Run this command as a Kubernetes administrator:

   kubectl get secret controller-sa-secret -n <cluster name> -o yaml | grep password

   b. Base64 decode the secret:

   echo <password from kubectl command>  | base64 --decode && echo

2. In a separate command window, expose the controller database server port:

   kubectl port-forward controldb-0 1433:1433 --address 0.0.0.0 -n <cluster name>

3. Use the system administrator password, which you just obtained, to connect to the controller database server from a SQL client tool.

4. Generate a new complex password for AZDATA_USERNAME to replace the existing AZDATA_PASSWORD.

   To simplify the example, the next steps use "newPassword" because the generated password is "newPassword".

5. Get hexsalt from the auth.users table:

   SELECT hexsalt FROM [auth].[users] WHERE username = '<username>'

   hexsalt returns a random hex string (for example, 64FC59DF31244FFEE02F457BC0750226).

6. Encrypt the new complex password by using hexsalt:

   For your convenience, we provide a pre-built tool pbkdf2 to encrypt the password. Download the platform-appropriate .NET Core app for pbkdf2.

   The app is self-contained and requires no prerequisites, such as .NET runtimes. To encrypt the password run:

   pbkdf2 <password> <hexsalt>
   J2y4E4dhlgwHOaRr3HKiiVAKBfjuGDyYmzn88VXmrzM=

7. Update the password in the auth.users table:

   UPDATE [auth].[users] SET password = 'J2y4E4dhlgwHOaRr3HKiiVAKBfjuGDyYmzn88VXmrzM=' WHERE username = '<username>'

1. Connect to the master SQL endpoint with any administrator user.

2. To change the password for the login credentials that you defined during deployment in the parameter AZDATA_USERNAME, run the following TSQL command:

   ALTER LOGIN <AZDATA_USERNAME> WITH PASSWORD = 'newPassword'

After following the steps to update AZDATA_PASSWORD, you will see that Grafana and Kibana still accept the old password. This is because Grafana and Kibana do not have visibility to the new Kubernetes secret. You must update the password for Grafana and Kibana separately.

Follow these options for manually updating the password for Grafana.

1. The htpasswd utility is required. You can install this on any client machine.

   For Ubuntu

   On Ubuntu Linux you can use the following:

   sudo apt install apache2-utils

   For RHEL

   On Red Hat Enterprise Linux you can use the following:

   sudo yum install httpd-tools

   ---

2. Generate the new password.

   htpasswd -nbs <username> <password>
   admin:{SHA}<secret>

   Replace values for <username>, <password>, <secret> as appropriate, for example:

   htpasswd -nbs admin Test@12345
   admin:{SHA}W/5VKRjIzjusUJ0ih0gHyEPjC/s=

3. Now encode the password:

   echo "admin:{SHA}W/5VKRjIzjusUJ0ih0gHyEPjC/s=" | base64

   Retain the output base64 string for later.

4. Next, edit the mgmtproxy-secret:

   kubectl edit secret -n mssql-cluster mgmtproxy-secret

5. Update the controller-login-htpasswd with the new encoded password base64 string generated above:

   # Please edit the object below. Lines beginning with a '#' will be ignored,
   # and an empty file will abort the edit. If an error occurs while saving this file will be
   # reopened with the relevant failures.
   #
   apiVersion: v1
   data:
      controller-login-htpasswd: <base64 string from before>
      mssql-internal-controller-password: <password>
      mssql-internal-controller-username: <username>

6. Identify and delete the mgmtproxy pod.

   If necessary, identify the name of your mgmtproxy prod.

   For Windows

   On a Windows server you can use the following script:

   kubectl get pods -n <namespace> -l app=mgmtproxy

   For Linux

   On Linux you can use the following script:

   kubectl get pods -n <namespace> | grep 'mgmtproxy'

   ---

   Remove the mgmtproxy pod:

   kubectl delete pod mgmtproxy-xxxxx -n mssql-clutser

7. Wait for the mgmtproxy pod to come online and Grafana Dashboard to start.

   The wait is not significant and the pod should be online within seconds. To check the status of the pod you can use the same get pods command as used in the previous step.

   If you see the mgmtproxy pod is not promptly returning to Ready status, use kubectl to describe the pod:

   kubectl describe pods mgmtproxy-xxxxx  -n <namespace>

   For troubleshooting and further log collection, use the Azure Data CLI azdata bdc debug copy-logs command.

8. Now, sign in to Grafana using new password.

Follow these options for manually updating the password for Kibana.

1. Open the Kibana URL.

   You can find the Kibana service endpoint URL from within Azure Data Studio, or use the following azdata command:

   azdata login
   azdata bdc endpoint list -e logsui -o table
   

   For example: https://11.111.111.111:30777/kibana/app/kibana#/discover

2. On the left side pane, select the Security option.

   

3. On the security page, under the heading Authentication Backends, select Internal User Database.

   

4. Now you will see the list of users under the heading Internal Users Database. Use this page to add, modify, and remove any users for Kibana endpoint access. For the user that needs the updated password, select Edit button on the row for the user.

   

5. Enter the new password twice and select Submit:

   

6. Close the browser and reconnect to the Kibana URL using updated password.

• azdata bdc (Azure Data CLI)
• Monitor applications with azdata and Grafana Dashboard
• Check out cluster logs with Kibana Dashboard