Stop and Start MSSQLSERVER and SQLSERVERAGENT

Chef Infra (archive)
1

up until about a month or so ago, my recipe worked fine where after I installed SQL I was able to stop the services and set them to run as a sQL service account we have set up. But now, I till stop them and assign the users, but wont’ start back up. I manually log into the servers and click start. The credentials are good, but it just won’t start the services. See below for the blocks in question.

windows_service 'SQLSERVERAGENT' do
  action :stop
end

windows_service 'MSSQLSERVER' do
  action :stop
end

windows_service 'MSSQLSERVER' do
  run_as_user node['Relativity']['SQLServer']['Service_Account']
  run_as_password node['Relativity']['SQLServer']['Service_Account_Password']
  action :start
end

windows_service 'SQLSERVERAGENT' do
  run_as_user node['Relativity']['SQLServer']['Service_Account']
  run_as_password node['Relativity']['SQLServer']['Service_Account_Password']
  action :start
end
2

Does the chef run fail when trying to stop? I’d also check the event logs to see if it started and then stopped and if any error was collected.

3

yes, the chef run fails and the even logs shows a stop but no start…In the system logs I see it receiving a stopped state, but nothing for start…

4

looks to be a winrm issue because I ran chef-client on the server and it worked without an issue…

I see this in teh System logs…

The WinRM service has received an unsecure HTTP connection from 10.12.236.104. 

 This is not a secure configuration. 

 User Action

The winrm configurations are set as:

Config
    MaxEnvelopeSizekb = 500
    MaxTimeoutms = 60000
    MaxBatchItems = 32000
    MaxProviderRequests = 4294967295
    Client
        NetworkDelayms = 5000
        URLPrefix = wsman
        AllowUnencrypted = false
        Auth
            Basic = true
            Digest = true
            Kerberos = true
            Negotiate = true
            Certificate = true
            CredSSP = false
        DefaultPorts
            HTTP = 5985
            HTTPS = 5986
        TrustedHosts
    Service
        RootSDDL = O:NSG:BAD:P(A;;GA;;;BA)(A;;GR;;;IU)S:P(AU;FA;GA;;;WD)(AU;SA;GXGW;;;WD)
        MaxConcurrentOperations = 4294967295
        MaxConcurrentOperationsPerUser = 1500
        EnumerationTimeoutms = 240000
        MaxConnections = 300
        MaxPacketRetrievalTimeSeconds = 120
        AllowUnencrypted = true
        Auth
            Basic = true
            Kerberos = true
            Negotiate = true
            Certificate = false
            CredSSP = true
            CbtHardeningLevel = Relaxed
        DefaultPorts
            HTTP = 5985
            HTTPS = 5986
        IPv4Filter = *
        IPv6Filter = *
        EnableCompatibilityHttpListener = false
        EnableCompatibilityHttpsListener = false
        CertificateThumbprint
        AllowRemoteAccess = true
    Winrs
        AllowRemoteShellAccess = true
        IdleTimeout = 7200000
        MaxConcurrentUsers = 10
        MaxShellRunTime = 2147483647
        MaxProcessesPerShell = 25
        MaxMemoryPerShellMB = 1024
        MaxShellsPerUser = 30

Do you see a reason why this would be erring?

5

I went ahead and just decided to go with a powershell script to start the service…not sure why the windows_service resource isn’t working

6

I think this is unrelated to the service failures but that event log message regarding the insecure configuration is likely due to the AllowUnencrypted = true setting. Its best not to set that to true. Some chef tooling (knife-windows and test-kitchen) required that in the past but if you are using recent versions its unnecessary.

7

I was recently working on this use case and ran into the same challenge. Just as another example of a way forward, setting up a retry worked for me (the below also includes idempotency via the only_if guard):

windows_service 'SQLSERVERAGENT' do
  action :stop
  guard_interpreter :powershell_script
  only_if '(Get-WMIObject Win32_Service | where-object { $_.name -eq "SQLSERVERAGENT" }| where-object {$_.startname -eq ".\foo_bar"}) -eq $null'
end

windows_service 'MSSQLSERVER' do
  action :stop
  guard_interpreter :powershell_script
  only_if '(Get-WMIObject Win32_Service | where-object { $_.name -eq "MSSQLSERVER" }| where-object {$_.startname -eq ".\foo_bar"}) -eq $null'
end

windows_service 'MSSQLSERVER' do
  run_as_user '.\foo_bar'
  run_as_password 'FooBar!'
  action :start
  retries 5
  retry_delay 10
end

windows_service 'SQLSERVERAGENT' do
  run_as_user '.\foo_bar'
  run_as_password 'FooBar!'
  action :start
  retries 5
  retry_delay 10
end

Hope that’s of help for anyone else running into this. Or if I google around in the future and run into my own post :smiley: