Category Archives: Linux

SSL Provider error 31 when connecting to SQL in a docker container

~

I recently bought a Dell XPS 13 running Ubuntu 16.04 and ran into an issue when connecting SQL Operations Studio (version 0.31.4) to SQL 2017 CU9 running in a docker container. Other people seem to encountering this issue as well so am posting it so that it may be of some help to someone in the future.

The error generated was: –

System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 31)

The full error can be viewed here

I was a bit stumped with this one to be honest, I googled around but could not find a resolution.

So I reached out on twitter to get some help: –

I also logged an issue on the SQL Operations Studio Github

Thankfully I got a response from Kevin Cunnane (t): –

Kevin advised me to run: –

sudo update-ca-certificates --fresh

N.B. – See here for a detailed description of what this command does

Once I ran that I was able to connect to SQL running in a docker container from SQL Operations Studio. Many thanks Kevin!

Hope that helps!

Changing the location of docker named volumes

~

A few weeks ago I was presenting at SQL Saturday Raleigh and was asked a question that I didn’t know the answer to.

The question was, “can you change the location of named volumes in docker?”

This is one of the things that I love about presenting, being asked questions that I don’t know the answer to. They give me something to go away and investigate (many thanks to Dave Walden (b|t) for his help!)

N.B. – I’ve previously written about persisting data using named volumes here

First let’s have a look at a named volume. To create one, run: –

docker volume create sqlserver

And now let’s have a look at it: –

docker volume inspect sqlserver

You can see above where the named volume lives on the host. But what if we want to change that location?

UPDATE – February 2022

This article originally only talked about using a docker volume plugin called Local Persist to change the location of a named volume.

However, you can do this without using a plugin by using the docker local driver and the bind option, which I’ll go through here.

I’ve left the details of how to use the plugin below as it does work to move a named volume but the plugin has not been updated for a while so using the local driver is the preferred way.

So let’s create a directory to point our named volume to: –

mkdir /sqlserver

And now create the named volume using the local driver and the bind option, setting the device to our custom location: –

docker volume create --driver local -o o=bind -o type=none -o device=/sqlserver sqlserver

Let’s have a look at it: –

docker volume inspect sqlserver

There we can see the device listed, /sqlserver, and the mount point, /var/lib/docker/volumes/sqlserver/_data.

What will happen when this named volume is used in a container is that /sqlserver will be mounted to /var/lib/docker/volumes/sqlserver/_data

And there you have it, a named volume in a custom location

Original post using the docker volume plugin – 2018

Well, in order to do so we need to use a docker volume plugin. Which unfortunately means that this functionality is not available on Windows or on Macs (as plugins aren’t supported on those platforms). The workaround is to run the plugin from a container but I would just mount a volume from the host (see here).

The plugin that I’m going to use is the Local Persist Plugin

Really simple to install: –

curl -fsSL https://raw.githubusercontent.com/CWSpear/local-persist/master/scripts/install.sh | sudo bash

And we are good to go!

Ok, let’s create a directory to point our named volume to: –

mkdir /sqlserver

And now we can create our named volume: –

docker volume create -d local-persist -o mountpoint=/sqlserver --name=sqlserver2

Let’s have a look at it: –

docker volume inspect sqlserver2

And there you have it, the named volume pointing to a custom location.

Thanks for reading!

Shiny Tech Syndrome

~

This is the name I give to anyone who, whilst learning a new technology, suddenly thinks it’s the best thing since sliced bread and consequently wants to shoehorn it in to everything.

(think that person who just started learning NoSql)

I’m not singling anyone out here as we are all guilty of this at some point in our careers.

For me, it’s Linux. I’ve been interested in learning the platform for a while and have had tastes in the past when say, installing a redis cluster but I haven’t really delved that deep into it.

All that’s changed over the last year, with me digging into tech such as Kubernetes and of course, SQL on Linux being released. This has give me the chance (excuse?) and the opportunity to really start learning as there are now great resources for me in the shape of We Speak Linux and Anthony Nocentino’s (b|t) Plural Sight courses.

I have shiny tech syndrome so bad that I’m honestly considering buying a Dell XPS 13 with Ubuntu pre-installed as my next laptop.

At first that sounds a bit nuts for me. I’m a SQL Server DBA and want a lightweight laptop that I can use for presenting and writing blogs/demos.

But the more I think about it the more the XPS is appealing to me. I’ve been having some frustrations with Windows 10 recently (if I have to uninstall Candy Crush Saga after an update one more time I’m going to scream) and with the release of SQL Operations Studio & VS Code I can happily do everything that I need on a Linux laptop. Plus I have a Windows 10 Jump Box in Azure that I can always fall back to.

Are there any SQL Server DBAs out there who’ve used a Linux distro as their main working environment? Have you had any issues? I’d love to hear from you.

Have a good week!

Changing the port for SQL Server in Azure Kubernetes Services

~

I got asked this question last week and it’s a very good one. After all, running Sql Server in Azure Container Services (AKS) does mean exposing a port to the internet to allow connections.

EDIT – Azure Container Services (AKS) has been renamed to Azure Kubernetes Services. Blog title has been updated

So leaving SQL Server listening on the default port can be risky.

Now I know there’s a debate as to whether or not it is worth changing the port that SQL is listening on in order to secure it. My opinion is that it’ll prevent opportunistic attacks by port scanners but would not prevent a directed attack.

So, how do you do it when running SQL Server in Azure Container Services?

Well there’s a couple of options available.

The first one is to change the port that SQL is listening on in the container, open that port on the container, and direct to that port from the service.

The second one is to leave SQL Server listening on the default port and direct a non-default port to port 1433 from the service.

Let’s run through both.

N.B. – Even though I’ll set this up from scratch I’d recommend you read through my previous post on AKS here

In order to set this up, I’ll use the Azure-CLI via Bash for Windows.

First thing to do is install the Azure-CLI: –

echo "deb [arch=amd64] https://packages.microsoft.com/repos/azure-cli/ wheezy main" | \
     sudo tee /etc/apt/sources.list.d/azure-cli.list
      
      
sudo apt-key adv --keyserver packages.microsoft.com --recv-keys 52E16F86FEE04B979B07E28DB02C46DF417A0893
sudo apt-get install apt-transport-https
sudo apt-get update && sudo apt-get install azure-cli

And install Kubectl: –

az aks install-cli

Then login to Azure: –

az login

Enable AKS on your Azure subscription: –

az provider register -n Microsoft.ContainerService

Create a resource group: –

az group create --name ApContainerResGrp1 --location centralus

And now we can create the cluster: –

az aks create --resource-group ApContainerResGrp1 --name mySQLK8sCluster1 --node-count 2 --generate-ssh-keys

N.B. – This can take some time

Once that’s complete we need to get credentials to connect to the cluster: –

az aks get-credentials --resource-group ApContainerResGrp1 --name mySQLK8sCluster1

Now test the connection by viewing the nodes in the cluster: –

kubectl get nodes

If both nodes come back with a status of Ready, you’re good to go!

Ok, so now let’s create the yaml file to spin up the container and service: –

nano sqlserver.yml

And drop this code into the file: –

apiVersion: apps/v1beta1
kind: Deployment
metadata:
  name: sqlserver
  labels:
    app: sqlserver
spec:
  replicas: 1
  template:
    metadata:
      labels:
        name: sqlserver
    spec:
      containers:
      - name: sqlserver1
        image: microsoft/mssql-server-linux:latest
        ports:
        - containerPort: 4433
        env:
        - name: SA_PASSWORD
          value: "Testing1122"
        - name: ACCEPT_EULA
          value: "Y"
        - name: MSSQL_TCP_PORT
          value: "4433"
---
apiVersion: v1
kind: Service
metadata:
  name: sqlserver-service
spec:
  ports:
  - name: sqlserver
    port: 4433
    targetPort: 4433
  selector:
    name: sqlserver
  type: LoadBalancer

N.B. – Code is also available here

Note the following code in the deployment section: –

        ports:
        - containerPort: 4433
.
.
.
        - name: MSSQL_TCP_PORT
          value: "4433"

This will use an environment variable to change the port that SQL is listening on to 4433 and open that port on the container.

Also note the following code in the service section: –

  ports:
  - name: sqlserver
    port: 4433
    targetPort: 4433

This will open the port 4433 externally and direct any connections to 4433 on the container.

So let’s deploy!

kubectl create -f sqlserver.yml

You can check the deployment process by running: –

kubectl get pods
kubectl get service

Once the pod has a status of Running and the service has an external IP, we can use the external IP and the port to connect to SQL in SSMS: –

And confirm that Sql is listening on the specified port by checking the log: –

EXEC sp_readerrorlog

Cool! Sql is listening on a non-default port and we’ve connected to it!

Alright, let’s try the next option.

First thing is to remove the old deployment: –

kubectl delete service sqlserver-service
kubectl delete deployment sqlserver
rm sqlserver.yml

Now let’s create the new yaml file: –

nano sqlsever.yml

And drop the following into it: –

apiVersion: apps/v1beta1
kind: Deployment
metadata:
  name: sqlserver
  labels:
    app: sqlserver
spec:
  replicas: 1
  template:
    metadata:
      labels:
        name: sqlserver
    spec:
      containers:
      - name: sqlserver1
        image: microsoft/mssql-server-linux:latest
        ports:
        - containerPort: 1433
        env:
        - name: SA_PASSWORD
          value: "Testing1122"
        - name: ACCEPT_EULA
          value: "Y"
---
apiVersion: v1
kind: Service
metadata:
  name: sqlserver-service
spec:
  ports:
  - name: sqlserver
    port: 4433
    targetPort: 1433
  selector:
    name: sqlserver
  type: LoadBalancer

N/B. – The code is also available here

Note the following in the service section:-

  ports:
  - name: sqlserver
    port: 4433
    targetPort: 1433

This opens port 4433 on the service and directs it to port 1433 in the container.

Rebuild the deployment: –

kubectl create -f sqlserver.yml

And once that’s created, connect on the service’s external IP and port 4433.

Awesome stuff! SQL is listening on the default port but we’ve connected to the port opened on the service and it has routed it to port 1433 opened on the container.

But which method would I recommend?

How about both! 🙂

Let’s change the default port that SQL is listening on and open a different port in the service!

Again, remove the old deployment: –

kubectl delete service sqlserver-service
kubectl delete deployment sqlserver
rm sqlserver.yml

Recreate the yaml file: –

nano sqlsever.yml

And the drop the following into the file: –

apiVersion: apps/v1beta1
kind: Deployment
metadata:
  name: sqlserver
  labels:
    app: sqlserver
spec:
  replicas: 1
  template:
    metadata:
      labels:
        name: sqlserver
    spec:
      containers:
      - name: sqlserver1
        image: microsoft/mssql-server-linux:latest
        ports:
        - containerPort: 4433
        env:
        - name: SA_PASSWORD
          value: "Testing1122"
        - name: ACCEPT_EULA
          value: "Y"
        - name: MSSQL_TCP_PORT
          value: "4433"
---
apiVersion: v1
kind: Service
metadata:
  name: sqlserver-service
spec:
  ports:
  - name: sqlserver
    port: 15789
    targetPort: 4433
  selector:
    name: sqlserver
  type: LoadBalancer

N.B. – This code is also available here

What’s happening here is that SQL will be configured to listen on port 4433 but we’ll connect externally to the service to port 15789 which is mapped to 4433 on the container.

Now redeploy: –

kubectl create -f sqlserver.yml

Same as before, wait for the container to be created and the service to have an external IP assigned: –

kubectl get pods
kubectl get service

Then use the external IP and the port 15789 to connect in SSMS: –

How cool is that?! SQL is listening on a non-default port and we’ve used a completely different port to connect!

Finally, to tear everything down: –

az group delete --name ApContainerResGrp1

Thanks for reading!

Enabling the SQL Server Agent in Linux Containers

~

EDIT 3 – November 2019 – There is an environment variable that can now be used to enable the SQL Server Agent, MSSQL_AGENT_ENABLED

docker run -d -p 15789:1433 `
--env ACCEPT_EULA=Y `
--env SA_PASSWORD=Testing1122 `
--env MSSQL_AGENT_ENABLED=True `
--name testcontainer `
mcr.microsoft.com/mssql/server:2019-GDR1-ubuntu-16.04

A full list of environment variables available for SQL Server can be found here

EDIT 2 – May 2018 – Thanks to Jon & Russell’s comments for pointing out that as of SQL 2017 CU4 the Agent no longer needs to be installed separately, just enabled. I have updated the code here and on Github to reflect that change

There is also the option to enable the agent in the microsoft/mssql-server-linux:latest image, just run the line of code below to enable the agent from within the container.

EDIT – Feb 2017 – Justin Hartman (t) pointed out that the original code here no longer works but a fix has been provided by Microsoft. I’ve updated the image in DockerHub and the code here and in my Github repository

At SQL Saturday Holland a few weeks ago I was (surprise, surprise) chatting about containers and mentioned that I hadn’t been able to get the SQL Agent working.

Now, one of the benefits of attending SQL Saturdays is that you get to pick the brains of a lot of very clever people and luckily for me, Jan Van Humbeek (blog|twitter) was there.

Jan said that he had gotten the SQL Agent running in Linux containers so I asked if he could send on his code and he very kindly obliged.

So, the disclaimer for this blog post is that I didn’t write the code here, Jan did. All I’ve done is drop it into a dockerfile so that an image can be built. Thank you very much Jan!

Here’s the dockerfile to build an image with the Agent installed: –

# building from ubuntu
FROM ubuntu:16.04

# install curl & sudo & apt-transport-https
RUN apt-get update && apt-get install -y curl sudo && apt-get install -y apt-transport-https

# Import the public repository GPG keys
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

# Register the Microsoft SQL Server Ubuntu repository
RUN curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list | tee /etc/apt/sources.list.d/mssql-server.list

# update package list 
RUN apt-get update -y

# install sql server
RUN apt-get install -y mssql-server

# enable the agent
RUN sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true 

# start sql server 
CMD /opt/mssql/bin/sqlservr

N.B. – This dockerfile is also available on my Github

Ok, so I then dropped this docker into C:\docker\builds\linuxwithagent and built the image: –

docker build -t linuxwithagent C:\docker\builds\linuxwithagent

This does throw a few errors but will successfully build an image. A container can then be run: –

docker run -d -p 15789:1433 --env ACCEPT_EULA=Y --env SA_PASSWORD=Testing11@@ --name testcontainer linuxwithagent

Once the container is running, connect via SSMS and the agent will be available.

I’ve made this image available on the docker hub. You can pull it down by running: –

docker pull dbafromthecold/sqlserverlinuxagent:latest

Thanks for reading!