{"cells":[{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["SOP017 - Add app-deploy AD group\n","================================\n","\n","Description\n","-----------\n","\n","If the Big Data Cluster was installed without an Active Directory group,\n","you can add one post install using this notebook.\n","\n","### Steps\n","\n","### Parameters"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":["parameters"]},"outputs":[],"source":["user_or_group_name = \"\u003cINSERT USER/GROUP NAME\u003e\"\n","realm = \"\u003cINSERT REALM\u003e\" # Upper case\n","sid = \"\" # To find the SID of the user or the group being added, you can use Get-ADUser or Get-ADGroup PowerShell commands.\n","role = \"appReader\""]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Instantiate Kubernetes client"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":["hide_input"]},"outputs":[],"source":["# Instantiate the Python Kubernetes client into 'api' variable\n","\n","import os\n","from IPython.display import Markdown\n","\n","try:\n","    from kubernetes import client, config\n","    from kubernetes.stream import stream\n","except ImportError: \n","\n","    # Install the Kubernetes module\n","    import sys\n","    !{sys.executable} -m pip install kubernetes    \n","    \n","    try:\n","        from kubernetes import client, config\n","        from kubernetes.stream import stream\n","    except ImportError:\n","        display(Markdown(f'HINT: Use [SOP059 - Install Kubernetes Python module](../install/sop059-install-kubernetes-module.ipynb) to resolve this issue.'))\n","        raise\n","\n","if \"KUBERNETES_SERVICE_PORT\" in os.environ and \"KUBERNETES_SERVICE_HOST\" in os.environ:\n","    config.load_incluster_config()\n","else:\n","    try:\n","        config.load_kube_config()\n","    except:\n","        display(Markdown(f'HINT: Use [TSG118 - Configure Kubernetes config](../repair/tsg118-configure-kube-config.ipynb) to resolve this issue.'))\n","        raise\n","\n","api = client.CoreV1Api()\n","\n","print('Kubernetes client instantiated')"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Get the namespace for the big data cluster\n","\n","Get the namespace of the Big Data Cluster from the Kuberenetes API.\n","\n","**NOTE:**\n","\n","If there is more than one Big Data Cluster in the target Kubernetes\n","cluster, then either:\n","\n","-   set \\[0\\] to the correct value for the big data cluster.\n","-   set the environment variable AZDATA\\_NAMESPACE, before starting\n","    Azure Data Studio."]},{"cell_type":"code","execution_count":null,"metadata":{"tags":["hide_input"]},"outputs":[],"source":["# Place Kubernetes namespace name for BDC into 'namespace' variable\n","\n","if \"AZDATA_NAMESPACE\" in os.environ:\n","    namespace = os.environ[\"AZDATA_NAMESPACE\"]\n","else:\n","    try:\n","        namespace = api.list_namespace(label_selector='MSSQL_CLUSTER').items[0].metadata.name\n","    except IndexError:\n","        from IPython.display import Markdown\n","        display(Markdown(f'HINT: Use [TSG081 - Get namespaces (Kubernetes)](../monitor-k8s/tsg081-get-kubernetes-namespaces.ipynb) to resolve this issue.'))\n","        display(Markdown(f'HINT: Use [TSG010 - Get configuration contexts](../monitor-k8s/tsg010-get-kubernetes-contexts.ipynb) to resolve this issue.'))\n","        display(Markdown(f'HINT: Use [SOP011 - Set kubernetes configuration context](../common/sop011-set-kubernetes-context.ipynb) to resolve this issue.'))\n","        raise\n","\n","print('The kubernetes namespace for your big data cluster is: ' + namespace)"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Create helper function to run `sqlcmd` against the controller database"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["try:\n","  import pandas\n","except ModuleNotFoundError:\n","  !{sys.executable} -m pip install --user pandas\n","  import pandas\n","from io import StringIO\n","pandas.set_option('display.max_colwidth', -1)\n","name = 'controldb-0'\n","container = 'mssql-server'\n","\n","def run_sqlcmd(query):\n","    command=f\"\"\"export SQLCMDPASSWORD=$(cat /var/run/secrets/credentials/mssql-sa-password/password); /opt/mssql-tools/bin/sqlcmd -b -S . -U sa -Q \"SET NOCOUNT ON; {query}\" -d controller  -s\"^\" -W \u003e /tmp/out.csv; sed -i 2d /tmp/out.csv; cat /tmp/out.csv\"\"\"\n","    output=stream(api.connect_get_namespaced_pod_exec, name, namespace, command=['/bin/sh', '-c', command], container=container, stderr=True, stdout=True)\n","    print(output)\n","\n","print(\"Function defined\")"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Insert user or group into the controller database roles table"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["run_sqlcmd(f\"INSERT INTO [controller].[auth].[roles] VALUES (N'{user_or_group_name}@{realm}', '{role}')\")"]},{"cell_type":"markdown","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["### Insert user or group into the controller database active\\_directory\\_principals tables"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["run_sqlcmd(f\"INSERT INTO [controller].[auth].[active_directory_principals] VALUES (N'{user_or_group_name}@{realm}', N'{sid}')\")"]},{"cell_type":"code","execution_count":null,"metadata":{"tags":[]},"outputs":[],"source":["print(\"Notebook execution is complete.\")"]}],"nbformat":4,"nbformat_minor":5,"metadata":{"kernelspec":{"name":"python3","display_name":"Python 3"},"pansop":{"related":"","test":{"strategy":"","types":null,"disable":{"reason":"","workitems":null,"types":null}},"target":{"current":"","final":""},"internal":{"parameters":null,"symlink":false},"timeout":"0"},"language_info":{"codemirror_mode":"{ Name: \"\", Version: \"\"}","file_extension":"","mimetype":"","name":"","nbconvert_exporter":"","pygments_lexer":"","version":""},"widgets":[]}}