How to connect to MSSQL hosted in Sitecore AKS Infra

Question

I am trying to find a way to run SQL queries in MSSQL that is hosted in a container as part of AKS. I did not perform the deployment, but I have access to the resources within Azure. It uses a mostly standard Sitecore k8s manifest with XP0 and Lighthouse demo.

While I understand it is frowned upon to connect directly to SQL when working with Sitecore, sometimes it is necessary. In my case, I need to review the current contacts in xDB and verify they have (or don't have) the facets that I am expected. These are custom facets and I cannot validate via their profile. If I can connect to MSSQL and run a query I can validate the contacts.

How can I connect/work with MSSQL when it is hosted in this manner?

Answer 1

I imagine there are multiple ways, but the following worked for me.

1. Install Azure CLI
2. Navigate to your Kubernetes resource in Azure, specifically the "Overview" tab
3. Click "Connect" in the menu bar
4. Open Powershell as an administrator and run the 2 commands that are listed
   • az account set --subscription YOURSUBID
   • az aks get-credentials --resource-group YOURRESOURCEGROUP --name AKSRESOURCENAME
5. These commands will populate a file in your user folder locally: C:\Users\YOU\.kube We will need this later
6. Download Lens (it's free) and install it
7. Open Lens
   • Because we previously ran the two Azure CLI commands and populated our Users directory, Lens SHOULD prepopulate our cluster. You can verify by selecting the Catalog icon then "Clusters" and view your available clusters
   • The one with the file=~\.kube\config was automatically picked up. The second one was my attempt to copy and paste the contents of the config file in "File > Add Cluster".
   • Either option should yield the same result
8. Select Workloads > Pods
   • Be sure to change your namespace off the default. (right side of application)
9. Select your mssql container/pod and scroll down to the "Ports" section (about midway)
10. Click "Forward..."

• You can leave the default settings such as "Open in Browser". It won't work in a browser but we can quickly see the port number this way.

11. Click "Start"
12. Identify the port it chose. The browser will open at http://localhost:YOURPORT
13. Open Sql Management Studio and connect

• Server name: it MUST include 127.0.0.1 (NOT localhost) and it MUST use a comma , instead of a colon :. An example, valid server name is 127.0.0.1,59324
• Login: Your SQL username (probably 'sa')
• Password: Your SQL password

Note: you can view your environment variables right below the Port forwarding setting, just click the icon of an eye

You should now be connected to your MSSQL instance running in AKS. It's definitely slow, but is good enough to execute a few queries.