How enable SQL Server Agent in docker container existing

Question

I have a running SQL Server image on Docker, however I need to enable SQL Server Agent to run some jobs, but I have not succeeded. The main problem is that the container has quite a few databases and settings that need to be maintained. The command "docker run -e" MSSQL_AGENT_ENABLED = true ... "" is not useful to me because it creates a new container and I would lose the current configuration.

I used the following commands that allowed to enable the interface, but when running the job I get an error that SQL Server Agent is not enabled

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Agent XPs',1
reconfigure 

The error generated when executing the job is the following

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)

I tried to start the SQL Server Agent with the command EXEC xp_servicecontrol N'START ', N'SQLServerAGENT', but it generates another error

StartService() returned error 1053, 'The service did not respond to the start or control request in a timely fashion.'

The question would be, how can I enable the SQL Server Agent in the container that is already running to be able to schedule jobs

Scalene

Creating a new container is the only way to patch SQL Server. So you should do that here too. – Enlarge

StartService error 1053 is a Windows service control manager error. Are you sure you're connected to the SQL Server inside the Docker container? – Edin

Answer 1

If you've already created a Docker container with something like SQL Server 2019 Developer edition:

$ docker run -e ACCEPT_EULA=Y -e MSSQL_PID=Developer -e MSSQL_SA_PASSWORD=YourStrongPassw0rd -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest
57301203bac57455a118e0dbe6ff392cb19313375c134050e6ecd77414555e7e

With reference to Configure SQL Server on Linux with the mssql-conf tool, get a root shell in the container:

$ docker exec -it --user root 57301203bac5 bash

Then enable SQL Agent in the configuration file and restart the SQL Server service:

root@57301203bac5:/# /opt/mssql/bin/mssql-conf set sqlagent.enabled true
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.

root@57301203bac5:/# systemctl restart mssql-server.service

If you get an error message such as systemctl: command not found then just stop and start the container for the changes to take effect.

Edin

could you pls, edit your comment and add the restart command at the end as well ? $ docker restart 57301203bac5 – Ventricose

Answer 2

From outside the container go into terminal by doing the following:

sudo docker exec -it --user root sql1 "bash"

The password will not be of the container.

Next elevate yourself to superuser:

su -

Next enable sqlagent by changing the configuration file:

/opt/mssql/bin/mssql-conf set sqlagent.enabled true

Now exit the container by typing exit once to logout from root and next to exit from the container

exit
exit

Finally restart the the docker container that has your microsoft sql server instance running

(in this case tagged sql1)

docker restart sql1

N.B. Within SQL Management Studio you need to right click the SQL Server Agent Icon and refresh to verify it is up.

Note you no longer have to do this if you enabled SQL server agent with the flag when initialising the container:

docker run -d -p 1433:1433 --env ACCEPT_EULA=Y --env SA_PASSWORD='MYSUP3RS@!3' --env MSSQL_AGENT_ENABLED=True --name sql1 mcr.microsoft.com/mssql/server:2019-GDR1-ubuntu-16.04 

Trochelminth

"su" DO asks for an unknown password. – Caroylncarp

Yeah this will be the "root" password for the host. – Trochelminth

I followed this to the point. All steps and also the alternative with the MSSQL_AGENT_ENABLED in the compose script environment values list. In SMSS, the agent icon shows a microscopic half-pixel-sized green spot for which to see I needed stronger glasses, but no dice. agent service does not start. attempting to start a job gives 22022 SQLServerAgent is not currently running so it cannot... Nothing of relevance in the container log. – Circumjacent

resolution: the instance name (host name) may not be longer than 15 characters. who'd've thunk. https://mcmap.net/q/827223/-sql-agent-not-starting-on-an-ubuntu-sql-server-docker-container-in-an-azure-container – Circumjacent

This has helped me to log into the running SQL Qerver container as the root user and thus circumvent the need to enter a password for it. – Napkin

Answer 3

For anyone using Docker Compose, it's much easier to just add a flag to the yaml environment:

environment:
  MSSQL_AGENT_ENABLED: true

Then just down and up the container again.

More info at Microsoft.com

Credit goes to Mickeybyte: https://stackoverflow.com/a/76182745

Unhandy