Sql server docker linux

Secure SQL Server Docker containers

Applies to: SQL Server (all supported versions) — Linux

SQL Server 2017 containers start up as the root user by default. This can cause some security concerns. This article talks about security options that you have when running SQL Server Docker containers, and how to build a SQL Server container as a non-root user.

Build and run non-root SQL Server 2017 containers

Follow the steps below to build a SQL Server 2017 container that starts up as the mssql (non-root) user.

SQL Server 2019 containers automatically start up as non-root, so the following steps only apply to SQL Server 2017 containers, which start as root by default.

Run the following command in the context of the dockerfile directory to build the non-root SQL Server container:

Start the container.

The —cap-add SYS_PTRACE flag is required for non-root SQL Server containers to generate dumps for troubleshooting purposes.

Check that the container is running as non-root user:

Run whoami , which will return the user running within the container.

Run container as a different non-root user on the host

To run the SQL Server container as a different non-root user, add the -u flag to the docker run command. The non-root container has the restriction that it must run as part of the root group unless a volume is mounted to /var/opt/mssql that the non-root user can access. The root group doesn’t grant any extra root permissions to the non-root user.

Run as a user with a UID 4000

You can start SQL Server with a custom UID. For example, the command below starts SQL Server with UID 4000:

Ensure that the SQL Server container has a named user such as ‘mssql’ or ‘root’ or SQLCMD will not be able to run within the container. You can check if the SQL Server container is running as a named user by running whoami within the container.

Run the non-root container as the root user

You can run the non-root container as the root user if necessary. This would also grant all file permissions automatically to the container because it has higher privilege.

Run as a user on your host machine

You can start SQL Server with an existing user on the host machine with the following command:

Run as a different user and group

You can start SQL Server with a custom user and group. In this example, the mounted volume has permissions configured for the user or group on the host machine.

Configure persistent storage permissions for non-root containers

To allow the non-root user to access database files that are on mounted volumes, ensure that the user or group you run the container under can read/write the persistent file storage.

You can get the current ownership of the database files with this command.

Run one of the following commands if SQL Server does not have access to persisted database files.

Grant the root group r/w access to the DB files

Grant the root group permissions to the following directories so that the non-root SQL Server container has access to database files.

Set the non-root user as the owner of the files

This can be the default non-root user, or any other non-root user you’d like to specify. In this example, we set UID 10001 as the non-root user.

Encrypting connections to SQL Server Linux Containers

To encrypt connections to SQL Server Linux containers, you will need a certificate with the following requirements.

Below is an example of how the connection can be encrypted to SQL Server Linux Containers. Here we use a Self-Signed Certificate, this should not be used for production scenarios for such environments, you should use CA certificates.

Create a self-signed certificate, which is suited for test and non-production environments only.

Where sql1 is the hostname of the SQL container, so when connecting to this container the name used in the connection string is going to be ‘sql1.contoso.com,port’.

Please ensure that the folder path /container/sql1/ already exists before running the above command.

Ensure you set the right permissions on the mssql.key and mssql.pem files, so you avoid errors when you mount the files to SQL container:

Now create a mssql.conf file with the below content to enable the Server Initiated encryption, for Client initiated encryption please change the last line to ‘forceencryption = 0’.

For some Linux distributions the path for storing the certificate and key could also be : /etc/pki/tls/certs/ and /etc/pki/tls/private/ respectively. Please verify the path before updating the mssql.conf for SQL containers. The location you set in the mssql.conf will be the location where SQL Server in the container is going to search for the certificate and its key. In this case, that location is /etc/ssl/certs/ and /etc/ssl/private/.

The mssql.conf file is also created under the same folder location /container/sql1/. After running the above steps, you should have three files: mssql.conf, mssql.key and mssql.pem in the sql1 folder.

Deploy the SQL container with the command shown below:

In the command above, we have mounted the mssql.conf, mssql.pem and mssql.key files to the container and mapped the 1433 (SQL Server default port) port in the container to 5434 port of the host.

If you are using RHEL 8 and above you can also use the ‘podman run’ command instead of ‘docker run’.

Follow the «Register the certificate on your client machine» and «Example connection strings» sections documented in Client Initiated Encryption to start encrypting connections to SQL Server on Linux containers.

Источник

Установка Служб машинного обучения SQL Server (Python, R) в Docker

Применимо к: SQL Server 2019 (15.x) — Linux

В этой статье объясняется, как установить Службы машинного обучения SQL Server в Docker. Службы машинного обучения можно использовать для запуска сценариев R или Python в базе данных. Мы не предоставляем готовые контейнеры со Службами машинного обучения. Вы можете создать их из контейнеров SQL Server, используя пример шаблона, доступный на сайте GitHub.

Предварительные требования

Интерфейс командной строки Git.

Docker Engine 1.8 или более поздней версии на любом поддерживаемом дистрибутиве Linux или Docker для Mac или Windows. Дополнительные сведения см. в статье Получение Docker.

Клонирование репозитория mssql-docker

Следующая команда позволяет клонировать репозиторий mssql-docker Git в локальный каталог.

Откройте терминал Bash в Linux или Mac.

Создайте каталог для хранения локальной копии репозитория mssql-docker.

Выполните команду git clone, чтобы клонировать репозиторий mssql-docker:

Создание образа контейнера SQL Server Linux

Чтобы создать образ Docker, выполните следующие шаги:

Измените каталог на mssql-mlservices:

В том же каталоге выполните следующую команду:

Выполните приведенную ниже команду.

Для MSSQL_PID можно использовать следующие значения: Developer (бесплатный), Express (бесплатный), Enteprise (платный), Standard (платный). Если вы используете платный выпуск, убедитесь, что вы приобрели лицензию. Замените значение (пароль) фактическим паролем. Монтирование томов с помощью -v необязательно. Замените (каталог в ОС узла) фактическим каталогом, в который необходимо подключить файлы данных и журналов базы данных.

Подтвердите, выполнив следующую команду:

Для создания образа Docker вам нужно установить пакеты размером в несколько ГБ. Выполнение скрипта может занять некоторое время в зависимости от пропускной способности сети.

Запуск образа контейнера SQL Server Linux

Перед запуском контейнера задайте переменные среды. В качестве значения переменной среды PATH_TO_MSSQL укажите каталог узла:

Процесс запуска контейнера с рабочими выпусками SQL Server немного отличается. Дополнительные сведения см. в статье Настройка образов контейнеров SQL Server в Docker. Если вы используете те же имена и порты контейнеров, действия в оставшейся части этого руководства будут актуальны и для рабочих контейнеров.

Чтобы просмотреть контейнеры Docker, выполните команду docker ps :

Если в столбце STATUS (Состояние) отображается значение Up (Работает), SQL Server выполняется в контейнере и прослушивает порт, указанный в столбце PORTS (Порты). Если в столбце STATUS контейнера с SQL Server отображается Exited (завершен), см.руководство Устранение неполадок конфигурации.

Включение служб машинного обучения

Чтобы включить Службы машинного обучения, подключитесь к экземпляру SQL Server и выполните следующую инструкцию T-SQL:

Дальнейшие действия

Разработчики на языке Python могут узнать, как использовать Python с SQL Server, изучив следующие руководства.

Разработчики на языке R могут ознакомиться с простыми примерами, а также узнать, как код R работает с SQL Server. Дополнительные сведения см. в следующих статьях.

Источник

Deploy and connect to SQL Server Docker containers

Applies to: SQL Server (all supported versions) — Linux

This article explains how to deploy and connect to SQL server docker containers.

For other deployment scenarios, see:

This article specifically focuses on using the mssql-server-linux image. The Windows image is not covered, but you can learn more about it on the mssql-server-windows Docker Hub page.

Before choosing to run a SQL Server container for production use cases, please review our support policy for SQL Server Containers to ensure that you are running on a supported configuration.

This 6-minute video provides an introduction into running SQL Server on containers:

Pull and run the container image

To pull and run the Docker container images for SQL Server 2017 and SQL Server 2019, follow the prerequisites and steps in the following quickstart:

This configuration article provides additional usage scenarios in the following sections.

Connect and query

You can connect and query SQL Server in a container from either outside the container or from within the container. The following sections explain both scenarios.

Tools outside the container

You can connect to the SQL Server instance on your Docker machine from any external Linux, Windows, or macOS tool that supports SQL connections. Some common tools include:

The following example uses sqlcmd to connect to SQL Server running in a Docker container. The IP address in the connection string is the IP address of the host machine that is running the container.

If you mapped a host port that was not the default 1433, add that port to the connection string. For example, if you specified -p 1400:1433 in your docker run command, then connect by explicitly specify port 1400.

Tools inside the container

Starting with SQL Server 2017, the SQL Server command-line tools are included in the container image. If you attach to the image with an interactive command-prompt, you can run the tools locally.

Use the docker exec -it command to start an interactive bash shell inside your running container. In the following example e69e056c702d is the container ID.

You don’t always have to specify the entire container ID. You only have to specify enough characters to uniquely identify it. So in this example, it might be enough to use e6 or e69 rather than the full ID. To find out the container ID, run the command docker ps -a .

Once inside the container, connect locally with sqlcmd. Sqlcmd is not in the path by default, so you have to specify the full path.

When finished with sqlcmd, type exit .

When finished with the interactive command-prompt, type exit . Your container continues to run after you exit the interactive bash shell.

Check the container version

If you want to know the version of SQL Server in a running docker container, run the following command to display it. Replace with the target container ID or name. Replace with the SQL Server password for the SA login.

You can also identify the SQL Server version and build number for a target docker container image. The following command displays the SQL Server version and build information for the mcr.microsoft.com/mssql/server:2017-latest image. It does this by running a new container with an environment variable PAL_PROGRAM_INFO=1. The resulting container instantly exits, and the docker rm command removes it.

The previous commands display version information similar to the following output:

Run a specific SQL Server container image

• Starting with SQL Server 2019 CU3, Ubuntu 18.04 is supported.
• Starting with SQL Server 2019 CU10, Ubuntu 20.04 is supported.
• You can retrieve a list of all available tags for mssql/server at https://mcr.microsoft.com/v2/mssql/server/tags/list.

There are scenarios where you might not want to use the latest SQL Server container image. To run a specific SQL Server container image, use the following steps:

Identify the Docker tag for the release you want to use. To view the available tags, see the mssql-server-linux Docker hub page.

Pull the SQL Server container image with the tag. For example, to pull the 2019-CU7-ubuntu-18.04 image, replace in the following command with 2019-CU7-ubuntu-18.04 .

To run a new container with that image, specify the tag name in the docker run command. In the following command, replace with the version you want to run.

These steps can also be used to downgrade an existing container. For example, you might want to rollback or downgrade a running container for troubleshooting or testing. To downgrade a running container, you must be using a persistence technique for the data folder. Follow the same steps outlined in the upgrade section, but specify the tag name of the older version when you run the new container.

Run RHEL-based container images

The documentation for SQL Server Linux container images points to Ubuntu-based containers. Beginning with SQL Server 2019, you can use containers based on Red Hat Enterprise Linux (RHEL). An example of the image for RHEL will look like mcr.microsoft.com/mssql/rhel/server:2019-CU1-rhel-8.

For example, the following command pulls the Cumulative Update 1 for SQL Server 2019 container that uses RHEL 8:

Run production container images

The quickstart in the previous section runs the free Developer edition of SQL Server from Docker Hub. Most of the information still applies if you want to run production container images, such as Enterprise, Standard, or Web editions. However, there are a few differences that are outlined here.

You can only use SQL Server in a production environment if you have a valid license. You can obtain a free SQL Server Express production license here. SQL Server Standard and Enterprise Edition licenses are available through Microsoft Volume Licensing.

The Developer container image can be configured to run the production editions as well. Use the following steps to run production editions:

Review the requirements and run procedures in the quickstart. You must specify your production edition with the MSSQL_PID environment variable. The following example shows how to run the latest SQL Server 2017 container image for the Enterprise Edition:

By passing the value Y to the environment variable ACCEPT_EULA and an edition value to MSSQL_PID, you are expressing that you have a valid and existing license for the edition and version of SQL Server that you intend to use. You also agree that your use of SQL Server software running in a Docker container image will be governed by the terms of your SQL Server license.

For a full list of possible values for MSSQL_PID, see Configure SQL Server settings with environment variables on Linux.

Run multiple SQL Server containers

Docker provides a way to run multiple SQL Server containers on the same host machine. Use this approach for scenarios that require multiple instances of SQL Server on the same host. Each container must expose itself on a different port.

The following example creates two SQL Server 2017 containers and maps them to ports 1401 and 1402 on the host machine.

The following example creates two SQL Server 2019 containers and maps them to ports 1401 and 1402 on the host machine.

Now there are two instances of SQL Server running in separate containers. Clients can connect to each SQL Server instance by using the IP address of the Docker host and the port number for the container.

Upgrade SQL Server in containers

To upgrade the container image with Docker, first identify the tag for the release for your upgrade. Pull this version from the registry with the docker pull command:

This updates the SQL Server image for any new containers you create, but it does not update SQL Server in any running containers. To do this, you must create a new container with the latest SQL Server container image and migrate your data to that new container.

Make sure you are using one of the data persistence techniques for your existing SQL Server container. This enables you to start a new container with the same data.

Stop the SQL Server container with the docker stop command.

Create a new SQL Server container with docker run and specify either a mapped host directory or a data volume container. Make sure to use the specific tag for your SQL Server upgrade. The new container now uses a new version of SQL Server with your existing SQL Server data.

Upgrade is only supported between RC1, RC2, and GA at this time.

Verify your databases and data in the new container.

Optionally, remove the old container with docker rm .

Next steps

• Get started with SQL Server 2017 container images on Docker by going through the quickstart

• Get started with SQL Server 2019 container images on Docker by going through the quickstart

See the mssql-docker GitHub repository for resources, feedback, and known issues

Источник