Author Archives: dbafromthecold

Displaying the tags within the SQL Server docker repository

~

A while back Microsoft moved all their SQL images to their new registry mcr.microsoft.com which means that we can no longer see the SQL repository when running docker search

Error response from daemon: Unexpected status code 404

To be honest, this isn’t really that much of an issue for me. I know the name of the repository that has the SQL Server images in it. What I really want to be able to do is view the tags of the images within it (this was a pain point with the images in the mssql-server-linux repository as well).

So I was pretty excited when a few days ago I saw this tweet by Tobias Fenster which said that mcr.microsoft.com supports the hub catalog API.

The hub catalog API allows us to view the tags of images within a repository!

It’s really simple to get the tags (the trick was finding the correct HTTP address): –

$repo = invoke-webrequest https://mcr.microsoft.com/v2/mssql/server/tags/list
$repo.content

And there we have it, all the tags listed for the SQL Server images within the repository.

Thanks for reading!

Creating custom SQL Server Helm charts

~

In my previous post I went through how to deploy SQL Server to Kubernetes using Helm in which I used the SQL Server chart that is available in the Helm Hub.

That’s great but what if we want to create our own charts? Let’s run through creating a simple SQL Server chart and deploying to Kubernetes (AKS).

First, ensure that Tiller (the server-side component of Helm) is installed on your cluster: –

helm init

Then create a directory to deploy the new chart into: –

mkdir C:\Helm

Navigate to the new directory: –

cd C:\Helm

And now create the new chart!

helm create testsqlchart

OK, what that has done is create an empty chart so we need to drop in our yaml configuration files.

Navigate to templates directory: –

cd testsqlchart/templates

Remove the template yaml files: –

rm deployment.yaml
rm service.yaml
rm ingress.yaml

Re-create deployment.yaml: –

apiVersion: apps/v1beta1
kind: Deployment
metadata:
  name: sqlserver
spec:
  replicas: 1
  template:
    metadata:
      labels:
        name: sqlserver
    spec:
      containers:
      - name: sqlserver1
        image: mcr.microsoft.com/mssql/server:2019-CTP2.2-ubuntu
        ports:
        - containerPort: 1433
        env:
        - name: SA_PASSWORD
          value: "Testing1122"
        - name: ACCEPT_EULA
          value: "Y"

Re-create service.yaml file: –

apiVersion: v1
kind: Service
metadata:
  name: sqlserver-service
spec:
  ports:
  - name: sqlserver
    port: 1433
    targetPort: 1433
  selector:
    name: sqlserver
  type: LoadBalancer

N.B. – Be careful when doing this, I’ve found that sometimes that Helm doesn’t like the format of the files. Re-creating in VS Code seems to do the trick.

Go back one directory: –

cd C:\Helm

And now we can test a deployment with –dry-run: –

helm install --dry-run --debug ./testsqlchart

If you get the following error: –

Tiller needs to be re-initialised: –

# delete current tiller deployment
kubectl delete deployment tiller-deploy --namespace kube-system

# create a service account
kubectl create serviceaccount --namespace kube-system tiller

# create clusterrolebinding
kubectl create clusterrolebinding tiller-cluster-rule --clusterrole=cluster-admin --serviceaccount=kube-system:tiller

# re-initialise tiller
helm init --service-account tiller --upgrade

Once the dry run returns no errors, you’re good to go!

helm install ./testsqlchart --name testsqlserver

To check the status: –

helm list

And you can monitor the creation of the deployment/service by running the usual kubectl commands: –

kubectl get deployments

kubectl get pods

kubectl get services

And that’s a custom SQL Server chart deployed into Kubernetes. SQL can be accessed by using the external IP of the service created.

Finally, to delete the deployed chart: –

helm delete testsqlserver

Thanks for reading!

Azure Kubernetes Service Error – LoadBalancer type service external IP stays pending

~

Last week I was working on my Azure Kubernetes Service cluster when I ran into a rather odd issue. I’d created a service with a type of LoadBalancer in order to get an external IP to connect to SQL Server running in a pod from my local machine.

I’ve done this quite a few times at this point so wasn’t expecting anything out of the ordinary.

However, my service never got it’s external IP address. It remained in a state of pending: –

N.B. – The images in this post are taken after the issue was resolved as I didn’t think at the time to screen shot everything 😦

I knew something was wrong after about 20 minutes as the IP should have definitely come up by then.

So I delved into the service by running: –

kubectl describe service sqlserver-service

And was greeted with the following: –

Error creating load balancer (will retry): failed to ensure load balancer for service default/sqlserver-service: azure.BearerAuthorizer#WithAuthorization: Failed to refresh the Token for request to https://management.azure.com/subscriptions/subscriptionID/resourceGroups/MC_containers1_SQLK8sCluster1_eastus/providers/Microsoft.Network/loadBalancers?api-version=2017-09-01: StatusCode=0 — Original Error: adal: Refresh request failed. Status Code = ‘401’. Response body: {“error”:”invalid_client”,”error_description”:”AADSTS70002: Error validating credentials. AADSTS50012: Invalid client secret is provided.\r\nTrace ID: 17d1f0ce-6c11-4f8e-895d-29194d973900\r\nCorrelation ID: 3e11d85c-77bf-4041-a41d-267bfd5f066c\r\nTimestamp: 2019-01-23 18:58:59Z”,”error_codes”:[70002,50012],”timestamp”:”2019-01-23 18:58:59Z”,”trace_id”:”17d1f0ce-6c11-4f8e-895d-29194d973900″,”correlation_id”:”3e11d85c-77bf-4041-a41d-267bfd5f066c”}

Yikes! What’s happened there?

I logged a case with MS Support and when they came back to me, they advised that the service principal that is spun up in the background had expired. This service principal is required to allow the cluster to interact with the Azure APIs in order to create other Azure resources.

When a service is created within AKS with a type of LoadBalancer, a Load Balancer is created in the background which provides the external IP I was waiting on to allow me to connect to the cluster.

Because this principal had expired, the cluster was unable to create the Load Balancer and the external IP of the service remained in the pending state.

So I needed to update the service principal so that it was no longer expired. In order to update the service principal I needed two pieces of information. The clientId of the cluster and a secret used for the service principal password. This wasn’t the easiest process in the world so I’ll run through how to do it here.

First, log into Azure:-

az login

Then get the clientId of your cluster: –

az aks show --resource-group RESOURCEGROUPNAME --name CLUSTERNAME --query "servicePrincipalProfile.clientId" --output tsv

To confirm that the principal is expired: –

az ad sp credential list --id CLIENTID

Check the endDate value highlighted above. If it’s past the current date, that’s your issue!

You may have noticed that mine is set 10 years from now. This is because I’m running these commands to get screenshots after I’ve fixed the issue…I figured 10 years should be long enough 🙂

The way I got the secret was to ssh onto one of the nodes in my cluster. This is a little involved but I’ll go through it step-by-step.

Resources for an AKS cluster are created in a separate resource group (for….reasons). To get that resource group name run: –

az aks show --resource-group RESOURCEGROUPNAME --name CLUSTERNAME --query nodeResourceGroup -o tsv

Then grab the nodes in the cluster (RESOURCEGROUPNAME2 is the output of the above command): –

az vm list --resource-group RESOURCEGROUPNAME2 -o table

And then get the IP address of each node: –

az vm list-ip-addresses --resource-group RESOURCEGROUPNAME2 -o table

OK, now that I had the node details I could copy my SSH public key into one of them.

The ssh keys were generated when I created the cluster using the –generate-ssh-keys flag. If you didn’t specify this you’ll need to generate the keys before continuing on.

So I copied my public key into one of the nodes: –

az vm user update \
  --resource-group RESOURCEGROUPNAME2 \
  --name NODENAME \
  --username azureuser \
  --ssh-key-value id_rsa.pub

N.B. – I found it easiest to navigate to the directory that held my ssh keys before running this script

Then I spun up a pod with openssh-client installed so that I could ssh into one of the nodes from within the cluster (the nodes aren’t accessible externally).

To do this I created a docker image from the Alpine:latest image and installed the client. Pushed it to the Docker Hub and then ran: –

kubectl run -it --rm aks-ssh --image=dbafromthecold/alpine_ssh:latest

N.B. – the dbafromthecold/alpine_ssh:latest image is public so this will work for you as well

In a separate command prompt I got the name of the pod:-

kubectl get pods

And then copied my private ssh key into the pod:-

kubectl cp id_rsa PODNAME:/id_rsa

Once the key was copied in, I closed that window and went back to the original window where I had run the pod and changed the permissions on the private key: –

chmod 0600 id_rsa

And then I was able to ssh into one of the nodes:-

ssh -i id_rsa azureuser@NODEIPADDRESS

The secret is contained in a json file (as aadClientSecret). To grab it I ran: –

sudo cat /etc/kubernetes/azure.json

Once I had that information I could exit the node, then the pod, and update the service principal: –

az ad sp credential reset --name CLIENTID--password SECRET --years 10

I confirmed that the service principal had been updated: –

az ad sp credential list --id CLIENTID

And was then able to deploy a loadbalancer type service, and get an external IP!

kubectl get services

Phew 🙂

Hope that helps anyone who runs into the same issue!

Deploying SQL Server to Kubernetes using Helm

~

In previous posts I’ve run through how to deploy sql server to Kubernetes using yaml files. That’s a great way to deploy but is there possibly an easier way?

Enter Helm. A package manager for Kubernetes.

Helm packages are called charts and wouldn’t you know it? There’s a chart for SQL Server!

Helm comes in two parts. Helm itself is the client side tool, and tiller, which is the server side component. Details of what each part does can be found here.

So the first thing to do is install Helm. Now you can download the installers from the website here but I found the easiest way to install Helm locally was to install the Kubernetes extension for VS Code. Installing that extension will ask if you want to install other tools, one of which being helm. Nice and easy!

Now install tiller on your cluster (if you don’t have a cluster setup, I have a guide to building one in AKS here): –

helm init

Now search for the mssql-linux package: –

helm search stable/mssql-linux

And deploy!

helm install --name sql-server stable/mssql-linux --set acceptEula.value=Y --set sapassword=Testing1122 --set edition.value=Developer

N.B. – If you get the following error: –

Error: release sql-server failed: namespaces “default” is forbidden:
User “system:serviceaccount:kube-system:default” cannot get namespaces in the namespace “default”

Run the following and then retry the deployment: –

kubectl create clusterrolebinding permissive-binding --clusterrole=cluster-admin --user=admin --user=kubelet --group=system:serviceaccounts;

You can check the progress of the deployment by running the following: –

kubectl get deployments

kubectl get pods

kubectl get services

You may have noticed that the service does not have an external IP. So we can’t connect externally to the SQL instance running in the pod. In order to connect, let’s spin up another pod which has sqlcmd installed and enter a bash session: –

kubectl run sqltools --image=microsoft/mssql-tools -ti --restart=Never --rm=true -- /bin/bash

Now we can connect using sqlcmd: –

sqlcmd -S sql-server-mssql-linux -U sa

And now run a test query: –

SELECT @@VERSION;
GO

And that’s how to deploy SQL Server to Kubernetes using Helm! Pretty cool imho.

Finally to delete the deployment: –

helm delete sql-server

Thanks for reading!

A shell for working with Kubernetes

~

I’m always a fan of tools that can make my life easier.

One tool that I’ve recently come across is kube-shell, an integrated shell for working with Kubernetes. What’s great about it is that it’s cross-platform and has intellisense for kubectl.

Installation is a cinch! The prerequisites are python and pip, which can be downloaded from here.

N.B. – python and pip are also prerequisites for mssql-cli which I also highly recommend you check out.

Make sure that python is added to your path environment variable and then you’re good to go and install kube-shell: –

pip install kube-shell

Once installed, typing kube-shell takes you into the shell…and you’re off!

How cool is that?

Thanks for reading!