SQL Server 2019 - Can't use apt-get to update or install packages in container

[MaximoTrinidad]

While trying to update the demo script file for an upcoming developers event, I found out an issue with the latest version of SQL Server 2019 (including the CU2). I can't update the container using "apt-get" command.

Here's my sequence of commands to replicate the issue:

docker pull mcr.microsoft.com/mssql/server:2019-latest

docker run --name mssql2k1901 -p 1433:1433 `
       -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=$SqlPwd01!' -e 'MSSQL_PID=Developer' `
       -d mcr.microsoft.com/mssql/server:2019-latest;

PS /home/maxt> docker exec -it mssql2k1901 bash                                            
mssql@cf4e3f6b3a6a:/$ apt update                                                           
Reading package lists... Done                                                              
E: List directory /var/lib/apt/lists/partial is missing. - Acquire (13: Permission denied) 
mssql@cf4e3f6b3a6a:/$                                                                      
mssql@cf4e3f6b3a6a:/$ sudo su                                                              
bash: sudo: command not found                                                              
mssql@cf4e3f6b3a6a:/$                                                                      
mssql@cf4e3f6b3a6a:/$      

I just notice, bash prompt is opening as non-root as is showing "$" intead of "#". And, follows a "Permission denied". (see image)

This is not happening with SQL Server 2017 latest. (see image)

[croblesm]

Hello there,

SQL Server 2019 containers run as non-root by default here is more info.

There is a workaround, container creation:

docker run --name mssql2k1901 \
-p 1433:1433 \
-e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=$SqlPwd01!' \
-d mcr.microsoft.com/mssql/server:2019-latest

As you can see, I omitted the "MSSQL_PID" variable in purpose. All the SQL Server containers use the developer edition when none is specified :)

Now that we have our container up and running, let's connect using docker exec but this time specifying the user root:

[dba mastery] $  docker exec -it -u root mssql2k1901 bash 

I was able to connect, now let's check the user and run the apt update:

root@0583dfd43d19:/# whoami
root
root@0583dfd43d19:/# apt update  
Get:1 http://security.ubuntu.com/ubuntu xenial-security InRelease [109 kB]
Get:2 https://packages.microsoft.com/ubuntu/16.04/prod xenial InRelease [4003 B]
Get:3 https://packages.microsoft.com/ubuntu/16.04/prod xenial/main amd64 Packages [136 kB]
Get:4 http://archive.ubuntu.com/ubuntu xenial InRelease [247 kB]                   
Get:5 http://security.ubuntu.com/ubuntu xenial-security/universe Sources [143 kB]
Get:6 http://security.ubuntu.com/ubuntu xenial-security/main amd64 Packages [1062 kB]
Get:7 http://archive.ubuntu.com/ubuntu xenial-updates InRelease [109 kB]          
Get:8 http://archive.ubuntu.com/ubuntu xenial-backports InRelease [107 kB]                 
Get:9 http://security.ubuntu.com/ubuntu xenial-security/restricted amd64 Packages [12.7 kB]
Get:10 http://security.ubuntu.com/ubuntu xenial-security/universe amd64 Packages [612 kB]
Get:11 http://security.ubuntu.com/ubuntu xenial-security/multiverse amd64 Packages [6280 B]              
Get:12 http://archive.ubuntu.com/ubuntu xenial/universe Sources [9802 kB]                            
Get:13 http://archive.ubuntu.com/ubuntu xenial/main amd64 Packages [1558 kB]        
Get:14 http://archive.ubuntu.com/ubuntu xenial/restricted amd64 Packages [14.1 kB]
Get:15 http://archive.ubuntu.com/ubuntu xenial/universe amd64 Packages [9827 kB]
Get:16 http://archive.ubuntu.com/ubuntu xenial/multiverse amd64 Packages [176 kB]
Get:17 http://archive.ubuntu.com/ubuntu xenial-updates/universe Sources [335 kB]
Get:18 http://archive.ubuntu.com/ubuntu xenial-updates/main amd64 Packages [1432 kB]
Get:19 http://archive.ubuntu.com/ubuntu xenial-updates/restricted amd64 Packages [13.1 kB]
Get:20 http://archive.ubuntu.com/ubuntu xenial-updates/universe amd64 Packages [1014 kB]
Get:21 http://archive.ubuntu.com/ubuntu xenial-updates/multiverse amd64 Packages [19.3 kB]
Get:22 http://archive.ubuntu.com/ubuntu xenial-backports/main amd64 Packages [7942 B]
Get:23 http://archive.ubuntu.com/ubuntu xenial-backports/universe amd64 Packages [8807 B]
Fetched 26.8 MB in 6s (4025 kB/s)                                                                                         
Reading package lists... Done
Building dependency tree       
Reading state information... Done
1 package can be upgraded. Run 'apt list --upgradable' to see it.
root@0583dfd43d19:/# 

Hopefully, this is what you were looking for. Cheers.

[MaximoTrinidad]

@dbamaster

That's Awesome! Yes! It works. Now I can continue fine-tuning my demo script file. I didn't have this issue and never use the "-u' parameter.

Now, is this now official? I look around the Microsoft documentation and there's no information about using the non-root as default in SQL Server 2019 containers.

Got it now!

Greatly Appreciated, Max

[tracker1]

@MaximoTrinidad I know it's documented, I came across this when I created #555. I'm just curious why are you modifying the database container's contents directly?

[croblesm]

@dbamaster

That's Awesome! Yes! It works. Now I can continue fine-tuning my demo script file. I didn't have this issue and never use the "-u' parameter.

Now, is this now official? I look around the Microsoft documentation and there's no information about using the non-root as default in SQL Server 2019 containers.

Got it now!

Greatly Appreciated, Max

@MaximoTrinidad You are very welcome :) Yes, the SQL Server 2019 non-root containers is documented here

Cheers.

[MaximoTrinidad]

@dbamaster

Oops!! Thanks again ... I missed the documented part.

But... I meant under the Microsoft Documentation which is where (and I think) everyone looks at first: https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver15&pivots=cs1-bash https://docs.microsoft.com/en-us/sql/linux/tutorial-restore-backup-in-sql-server-container?view=sql-server-ver15

I will pass this information in my next upcoming event.

[MaximoTrinidad]

@tracker1

Yes! For the last few years, I've been presenting in how to customized existing SQLServer docker images.

Basically! The first thing I do:

1. Get to the Bash prompt, which previous default was root, then do both:

   apt update 
   apt upgrade

   This will put my container Linux OS packages current.

2. Then, I can proceed to install my additional tools:

   
   apt -y install curl vim netbase iputils-ping net-tools \
   samba smbclient cifs-utils \
   powershell-preview

apt update

After this, I commit my container changes, save the image with a different name. 
Now, I have my favorite tools installed so I can work as a SQL Developer. Especially with PowerShell included I can install the Sqlserver module. This way I can work inside and outside the container.

Then, again this just for non-production unless you peek and choose what tools will remain in the new container.

[croblesm]

@dbamaster

Oops!! Thanks again ... I missed the documented part.

But... I meant under the Microsoft Documentation which is where (and I think) everyone looks at first: https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver15&pivots=cs1-bash https://docs.microsoft.com/en-us/sql/linux/tutorial-restore-backup-in-sql-server-container?view=sql-server-ver15

I will pass this information in my next upcoming event.

Excellent, I think this issue can be closed then :)

[croblesm]

@tracker1

Yes! For the last few years, I've been presenting in how to customized existing SQLServer docker images.

Basically! The first thing I do:

1. Get to the Bash prompt, which previous default was root, then do both:

apt update 
apt upgrade

This will put my container Linux OS packages current.

1. Then, I can proceed to install my additional tools:

apt -y install curl vim netbase iputils-ping net-tools \
samba smbclient cifs-utils \
powershell-preview

apt update

After this, I commit my container changes, save the image with a different name. Now, I have my favorite tools installed so I can work as a SQL Developer. Especially with PowerShell included I can install the Sqlserver module. This way I can work inside and outside the container.

Then, again this just for non-production unless you peek and choose what tools will remain in the new container.

You can create a custom image using a Dockerfile, to save you some time putting all the things together. I included an example of this during my last PASS Summit session, in case you want to take a look. Here is the script with some comments as guidance, and also the Dockerfile.

Cheers.

[MaximoTrinidad]

@tracker1

Thanks for sharing! And I will check it out.

My session is to help people get started and not to be afraid of using the PowerShell and Docker CLI. More of a Part 1 session, and then Part 2 will be to use Docker Compose.

This is an amazing technology everyone needs to learn.

Thanks again!

[MaximoTrinidad]

I can close this issue.

Thanks, everyone!

[tracker1]

@dbmaster Thanks for sharing that... actually, really hadn't occurred to me to go further than the stock container, other than putting sql files in a temp location to use sqlcmd that way I don't have to install it on the host system. Thinking the smb client might be useful for other automation scenarios. Not sure about the WAIT_SQL, or what effect that has, didn't dig in... mostly using the docker version for test/uat environments, and for testing wait 2.5 seconds, then every second run the following to see if it's successful, then wait another second before continuing...

docker exec 
  -i "${name}"
  /opt/mssql-tools/bin/sqlcmd
  -S localhost
  -U sa
  -P "${sapwd}"
  -Q "SELECT Name from sys.Databases"

The 2.5 seconds is because if I try to run it before, it can bork the setup of a fresh instance... once it exits with a 0 exit code, it's generally ready, then I wait a little bit more just in case. If you did something like this in bash, would need to redirect to /dev/null and just check the exit code...

[croblesm]

@dbmaster Thanks for sharing that... actually, really hadn't occurred to me to go further than the stock container, other than putting sql files in a temp location to use sqlcmd that way I don't have to install it on the host system. Thinking the smb client might be useful for other automation scenarios. Not sure about the WAIT_SQL, or what effect that has, didn't dig in... mostly using the docker version for test/uat environments, and for testing wait 2.5 seconds, then every second run the following to see if it's successful, then wait another second before continuing...

docker exec 
  -i "${name}"
  /opt/mssql-tools/bin/sqlcmd
  -S localhost
  -U sa
  -P "${sapwd}"
  -Q "SELECT Name from sys.Databases"

The 2.5 seconds is because if I try to run it before, it can bork the setup of a fresh instance... once it exits with a 0 exit code, it's generally ready, then I wait a little bit more just in case. If you did something like this in bash, would need to redirect to /dev/null and just check the exit code...

Hello there!

The WAIT_SQL is just an env variable I use to wait for SQL Server to start inside the container. Depending on the environment it usually takes from 5-30 seconds to be ready to accept user connections.

Regards.

[ezzzzz]

Just wanted to comment in case anybody runs into this issue using Docker-Compose. For me the issue was that I did not specify a User in my referenced Dockerfile, adding 'USER root' to my Dockerfiles resolved the issue in migrating from 2017-Latest to 2019-latest.