9

I start a Windows SQL Server in a Docker Container on a Windows Server 2016 virtual machine with this command:

docker container run --name mssql-server --detach --publish 1433:1433 --memory 2048mb --volume C:\_Data:C:\_Data --env sa_password=myPassword --env ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

Afterwards I want to start the SQL Agent with this command:

docker container exec mssql-server cmd.exe /C "net start sqlserveragent"

Unfortunately I get this Output:

The SQL Server Agent (MSSQLSERVER) service is starting.
The SQL Server Agent (MSSQLSERVER) service could not be started.

The service did not report an error.

More help is available by typing NET HELPMSG 3534

When I type NET HELPMSG 3534 I get:

The service did not report an error.

I am completely out of ideas. On my local machine this works fine, but not on the build server.

Any help appreciated.

Edit: I opened the SQL Server Management Studio and noticed the SQL Server Agent (Agent XPs disabled) node. So I went through everything in https://www.mssqltips.com/sqlservertip/2729/how-to-start-sql-server-agent-when-agent-xps-show-disabled/. Afterwards I executed

exec sp_configure 'Agent XPs'

and the result was: Agent XPs are clearly enabled

But when I tried to start the agent, I got the exact same error message from above (service ist starting... service could not be started... no error reported).

The interesting thing is, when I run

exec sp_configure 'Agent XPs'

again, I get this: Agent XPs are disabled

How is it possible, that the start-agent-command disables the Agent XPs?

Edit2: I just learned from a comment at https://community.spiceworks.com/topic/293771-sql-server-agent-agent-xps-disabled-sql-server-2008 that the agent configures this values automatically when it is started and stopped. So I shouldn't enabled it manually.

Edit3: After a comment from Jeroen Mostert I looked into the EventLog. I used the following command:

docker container exec mssql-server powershell.exe "Get-EventLog Application -Newest 100"

This is the result from the Application-Log

Time         EntryType   Source                                  InstanceID Message                                                                                        
----         ---------   ------                                  ---------- -------                                                                                        
Jun 25 10:37 Information SQLSERVERAGENT                          1073741926 SQLServerAgent service successfully stopped.                                                   
Jun 25 10:37 Information MSSQLSERVER                             1073757281 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
Jun 25 10:36 Information MSSQLSERVER                             1073757281 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.

You can see, that I have manually enabled the Agent XPs. Then I ran the command from above to start the agent, which gave me the next two lines

The System-EventLog gave me:

Time         EntryType   Source                                   InstanceID Message                                                                                                                                                                                                                                                             
----         ---------   ------                                   ---------- -------                                                                                                                                                                                                                                                             
Jun 25 10:37 Information Service Control Manager                  1073748860 The SQL Server Agent (MSSQLSERVER) service entered the stopped state.

Edit 4: As per the comment from Dan Guzman: this is the content of SQLAGENT.OUT:

2019-07-09 11:03:44 - ? [000]
2019-07-09 11:03:44 - ? [098] SQLServerAgent terminated (normally)

Edit 5: The version is (select @@version) is:

Microsoft SQL Server 2017 (RTM-CU3-GDR) (KB4052987) - 14.0.3015.40 (X64)   Dec 22 2017 16:13:22   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)
Rico-E
  • 1,806
  • 2
  • 28
  • 47
  • 1
    Check the SQL Agent error log by running this query in the container SQL instance: `EXEC xp_readerrorlog 0, 2` – Dan Guzman Jun 24 '19 at 15:18
  • @DanGuzman: Tanks for your input. The query result is: `Date | ErrorLevel: 3 | Text: [093] SQLServerAgent terminated (normally)`. This is not much of a hint to me. – Rico-E Jun 25 '19 at 06:57
  • See if the agent has logged anything in the event log. From the console you can get read it with a PowerShell prompt and `Get-EventLog`. – Jeroen Mostert Jun 25 '19 at 07:45
  • @JeroenMostert: Please see my last edit. – Rico-E Jun 25 '19 at 08:42
  • @Rico-E, add the complete SQL Agent log contents to your question. This can be obtained by running command `type "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT"` in the container. – Dan Guzman Jul 04 '19 at 13:58
  • Are you using Express Edition? – Daniel Brughera Jul 04 '19 at 14:16
  • @Rico-E `SELECT @@version` – Lukasz Szozda Jul 04 '19 at 18:11
  • 1
    @LukaszSzozda, the results of the query I get when running command in the question are "Microsoft SQL Server 2017 (RTM-CU3-GDR) (KB4052987) - 14.0.3015.40 (X64) Dec 22 2017 16:13:22 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)". I'd expect the same for Rico-E's container unless an older cached image is used. – Dan Guzman Jul 05 '19 at 11:56
  • Your local machine where this works is also installed in a docker container? – GreyOrGray Jul 08 '19 at 15:28
  • @DanGuzman: please see my edit #4 – Rico-E Jul 09 '19 at 09:07
  • @LukaszSzozda: please see my edit #5. It is exactly as DanGuzman stated – Rico-E Jul 09 '19 at 09:13

6 Answers6

1

My solution was to upgrade the build-server to Windows Server 2019. That's it. Then everything worked without any further change.

Rico-E
  • 1,806
  • 2
  • 28
  • 47
0

Hi you need to start SQL Server Agent Service, How to start service you can follow this link: Start Service

Step 1: First, you have to click on the start menu and search for the SQL Server Configuration Manager and click on the SQL Server Configuration Manager option from the search result.

Step 2: Then you have to select the SQL Server Services from the left menu and you can see the service SQL Server (SQLEXPRESS) is not running and it is stopped.

Step 3: Now, you have to start the service SQL Server (SQLEXPRESS) and for that, you have to press mouse right click on SQL Server (Agent) service and select Start option to start the service.

Step 4: You can see the state of service is running, Now you should try again to connect SQL server database engine.

Nikunj Satasiya
  • 831
  • 9
  • 25
0

Because you are running in a docker container, i am assuming you are also using some form of environmental management (chef, puppet, jenkins) etc. Carefully review all the actors in your environment. maybe one of these is detecting the agents status and whether agent xp's should be allowed. Further you may need to check SQL Policy and AD Policies if you are in a domain. But the speed at which the reversal occurs suggests that an agent in the docker instance is too blame.

Randy Pitkin
  • 31
  • 3
0

I managed to solve this getting into the docker container using "cmd" and then run a specific command to start windows services.

1) docker exec -it YourSQLServerContainerName cmd

2) net start sqlserveragent

You can stop and start your container and it preserve the running status.

RamPrakash
  • 1,687
  • 3
  • 20
  • 25
user12633423
  • 1
0

I had the same issue, here how I fix my problem.

First, I install the SQL Server as a Docker container using the mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-16.04 image, and I use host network_mode for the Database container.

Next, after I view the SQL Agent log, I notice it wasn't able to connect to the server.

 $ tail -100f /var/opt/mssql/log/sqlagent.1 # read agent's logs
 ...
 2020-10-03 21:25:01 - ! [000] Unable to connect to server '(local),1433'; SQLServerAgent cannot start
 2020-10-03 21:25:01 - ! [103] SQLServerAgent could not be started (reason: Unable to connect to server '(local),1433'; SQLServerAgent cannot start)
 2020-10-03 21:25:06 - ! [298] SQLServer Error: 11001, TCP Provider: No such host is known. [SQLSTATE 08001]
 ...

Finally, I found from logs that the SQL Agent was trying to connect with instance '(local),1433', so my solution was to resolve the local and sqlserver host with the local IP. I accomplish it by adding the following records to the /etc/hosts file of the server.

127.0.0.1 local
127.0.0.1 sqlserver
theofilis
  • 411
  • 5
  • 10
0

I found from logs that the SQL Agent was trying to connect with instance '(local),1433' in /var/opt/mssql/log/sqlagent.out

I'm using Amazon Linux 2 with Sql Server 2017 updated.

Edit the local and sqlserver host with the local I by adding the following record (first line) to the /etc/hosts file of the server.

172.31.1.200 ip-172-31-1-200

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost6 localhost6.localdomain6

Joir Ramalho
  • 1