Setting up MinIO for SQL Server 2022 s3 Object Storage Integration
Introduction
In this post, I will walk you through how to set up MinIO, so you can use it to work with SQL Server 2022’s s3 object integrations. Working with s3 and SQL Server requires a valid and trusted TLS certificate. This can be a pain for some users and environments. So I’m writing this post so you can get off the ground running with this new feature set in SQL Server 2022. The certificate we’re working with here is self-signed. You could get a real certificate for your environment, and that’s encouraged. But this walk-through intends to get you up and running fast so that you can test out SQL Server’s s3 object integrations. We’re using MinIO’s free GNU AGPL v3 edition and running it in a docker container for our s3 compatible object storage and SQL Server 2022 CTP 2.0, which is also running in a container.
Create docker networking
First, let’s create a network to put the SQL and the MinIO containers on. The containers will get IPs from this network and communicate on those IP addresses on the s3demo network. The ports published are available on the host OS too.
docker network create s3demo
Create the MinIO container
Next, let’s create the MinIO container and attach it to our docker network. Expose its services to the host on 9000 for data and 9001 for the admin console. Also, let’s create a docker volume for data and attach it at /data inside the container for persistent storage for our container. Next, we’ll define the root username and password. Finally, we’ll run the container in detached mode -d and the image is quay.io/minio/minio with the parameters /data telling MinIO where to store its data and that it will listen on 9001 for console access.
docker run \
--name minio1 \
--network s3demo \
--publish 9000:9000 \
--publish 9001:9001 \
-v s3data:/data \
-e "MINIO_ROOT_USER=MYROOTUSER" \
-e "MINIO_ROOT_PASSWORD=MYROOTPASSWORD" \
-d quay.io/minio/minio server /data --console-address ":9001"
We need to get the IP address of the MinIO container so we can use it when we create the SQL Server container and when we create the TLS certificate. Use this code to get its IP address and assign the value to a variable for reuse later on.
MINIO_IP=$(docker container inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' minio1)
echo $MINIO_IP
Create the SQL Server container
Next, let’s create the SQL Server container. Attach it to the docker network with --network s3demo. Expose it on port 1433 with --publish 1433:1433, then define a hostname for DNS resolution using the IP retrieved above with the parameter --add-host= "s3.example.com:$MINIO_IP". Certificates rely on proper name resolution, so this is a way for us to ensure that we meet that requirement. We’re also attaching a volume for persistent data and using the parameter -v sqldata:/var/opt/mssql. Then to round things off, defining the required environment variables for SQL Server to start, running the container in detached mode with -d and using the container image mcr.microsoft.com/mssql/server:2022-latest, which today is CTP 2.0.
docker run \
--name 'sql1' \
--hostname sql1 \
--network s3demo \
--publish 1433:1433 \
--add-host="s3.example.com:$MINIO_IP" \
-v sqldata:/var/opt/mssql \
-e 'ACCEPT_EULA=Y' \
-e 'MSSQL_SA_PASSWORD=S0methingS@Str0ng!' \
-d mcr.microsoft.com/mssql/server:2022-latest
Next, let’s create a test database in the container. Remember, it takes a few seconds for the SQL Server container to start up. So wait a second before running this code.
sqlcmd -S localhost,1433 -U sa -Q 'CREATE DATABASE TestDB1' -P 'S0methingS@Str0ng!'
Creating a Self-Signed Certificate for MinIO
Now for the fun part, let’s generate a certificate and make it trusted on the container.
First, create a certificate using a config using this example. This is a heredoc that will generate the openssl.cnf file for you. Notice I’m using the variable $MINIO_IP to add the IP address of the MinIO container to the list of subject alternative names.
cat <<EOF | cat > openssl.cnf
[req]
distinguished_name = s3.example.com
x509_extensions = v3_req
prompt = no
[s3.example.com]
C = US
ST = MS
L = Somewhere
O = IT
OU = DBATeam
CN = s3.example.com
[v3_req]
subjectAltName = @alt_names
[alt_names]
IP.1 = 127.0.0.1
DNS.1 = localhost
IP.2 = $MINIO_IP
DNS.2 = s3.example.com
EOF
With that generated, you can now use openssl to generate the certificate passing in the config file with the -config parameter. This will generate two files, public.crt, the certificate, and private.key, the private key.
openssl req -x509 -nodes -days 3650 -newkey rsa:2048 -keyout private.key -out public.crt -config openssl.cnf
Configure MinIO to use the Self-Signed Certificate
Next, we need to configure MinIO to use the certificate and key. You can copy the key and the certificate into known MinIO locations for the client and server and restart the container. MinIO looks in /root for these files, so no additional configuration is needed, just the container restart.
docker cp public.crt minio1:/root/.minio/certs/
docker cp private.key minio1:/root/.minio/certs/
docker stop minio1 && docker start minio1
Configure the SQL Server’s Container OS and SQL Server to Trust Our Self-Signed Certificate
Copy the certificate into the CA location for the container and update the CA list with the new certificate. This tells the container’s base OS to trust the self-signed certificate, so the tools we use inside the container will trust this certificate. Once the certificate is copied into the container, you will use update-ca-certificates to refresh the CA listings for the base OS.
docker cp public.crt sql1:/usr/local/share/ca-certificates
docker exec -it -u 0 sql1 update-ca-certificates
We now need to tell SQL Server to trust the self-signed certificate. We do that by copying the certificate into the known CA location for SQL Server, setting permissions on the files, and then restarting the container. This will allow SQL Server to trust the self-signed certificate.
docker exec -it -u 0 sql1 mkdir /usr/local/share/ca-certificates/mssql-ca-certificates/
docker cp public.crt sql1:/usr/local/share/ca-certificates/mssql-ca-certificates/
docker exec -it -u 0 sql1 chown 10001:10001 -R /usr/local/share/ca-certificates/mssql-ca-certificates/
docker stop sql1 && docker start sql1
Once you restart the container, you can look in the SQL Server’s error lot to confirm its loads the self-signed certificate. Once you see ‘Installing Client TLS certificates to the store,’ your certificate is loaded by SQL Server. You can look at the logs with the following command. You’re looking for the second occurrence of this string. The first occurrence is from the initial container start-up.
docker logs sql1 --follow | grep 'Installing Client TLS certificates to the store'
Testing s3 Connectivity with mc
You can interact with MinIO at the command line with the tool mc. You can also use the console GUI, which is available at https://localhost:9001. If you’re using the GUI from your workstation, you will get a certificate error. You can safely ignore that error. Right now, only the two containers trust the self-signed certificates we generated. Let’s get started with testing.
First, download the mc client to test access to your MinIO server.
wget https://dl.min.io/client/mc/release/linux-amd64/mc
Then copy mc into the SQL Server container. I’m copying the file into the SQL Server container because I want to test the connectivity from the SQL container to the MinIO container to confirm that all works before I move up into SQL Server and configure its S3 integrations. You could also run mc from your host OS if you like. You will need to add a record for s3.example.com to your base operating systems host file pointing to the MinIO container’s published ports. The last step here is setting mc as executable.
docker cp mc sql1:/usr/local/bin
docker exec -it -u 0 sql1 chmod +x /usr/local/bin/mc
Now, create an alias named s3admin in with mc using the root username and password; we’ll use this for administrative operations like creating a user. An alias is a URL location, username, and password for your s3 compatible storage. It’s used, so you don’t have to type each parameter every time you use mc.
docker exec -it -u 0 sql1 mc alias set s3admin https://s3.example.com:9000 MYROOTUSER MYROOTPASSWORD --api S3v4
Next, create a non-root user, anthony, and assign it to the readwrite policy. The username here is anthony, and the password is nocentino, clever, huh?
docker exec -it -u 0 sql1 mc admin user add s3admin anthony nocentino
docker exec -it -u 0 sql1 mc admin policy set s3admin readwrite user=anthony
And then, let’s create an alias for the user anthony. We shouldn’t do stuff as root :)
docker exec -it -u 0 sql1 mc alias set anthony https://s3.example.com:9000 anthony nocentino
Next, to test access to the MinIO server, you can do a simple operation like ls..if you see 200 OK and TLS certificate found… you’re good to go.
docker exec -it -u 0 sql1 mc ls anthony --debug
Now that we know we can access our s3 compatible storage from our SQL Server container, let’s create a bucket to store our backups in; it’s in the format alias/bucketname.
docker exec -it -u 0 sql1 mc mb anthony/sqlbackups
Configure SQL Server for S3 Backups
First, create the S3 credential in SQL Server. I’m using sqlcmd for this since I like to work at the command line when setting this up. You could also do this in SSMS or via PowerShell if you like.
QUERY=$(echo "CREATE CREDENTIAL [s3://s3.example.com:9000/sqlbackups] WITH IDENTITY = 'S3 Access Key', SECRET = 'anthony:nocentino';")
sqlcmd -S localhost,1433 -U sa -Q $QUERY -P 'S0methingS@Str0ng!'
Now with all the pieces in place, let’s run a backup.
QUERY=$(echo "BACKUP DATABASE TestDB1 TO URL = 's3://s3.example.com:9000/sqlbackups/TestDB1.bak' WITH COMPRESSION, STATS = 10, FORMAT, INIT;")
sqlcmd -S localhost,1433 -U sa -Q $QUERY -P 'S0methingS@Str0ng!'
We can then use mc to see the backup in MinIO. You can also use the console at https://localhost:9001 from your container’s host.
docker exec -it -u 0 sql1 mc ls anthony/sqlbackups
Here’s some code to clean this all up when you’re finished or if you need to restart over from an error.
docker rm -f sql1
docker rm -f minio1
docker volume rm s3data
docker volume rm sqldata
docker network rm s3demo
rm public.crt
rm private.key
By the end of this post, you should have a working s3 compatible object storage environment with MinIO. You should be able to run a backup using the MinIO instance as your backup target over s3. You can also use this configuration for data virtualization work. I’ll be covering that in an upcoming post.