suspend or stop SQL service at the weekend

Temody Mas
Temody Mas used Ask the Experts™
on
Hello Experts
I need script or what ever tools that allowing me to suspend or stop SQL service at the weekend, even if some one restart the server, until a revers or resume script runing.
The management won't allow users to access applications out of work time
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA

Commented:
Not sure exactly how to do it with SQL Server, but in Oracle, the easiest way to do this would be with a logon trigger.  The logon trigger checks the date and time and if it is outside of the window you want to allow users on, then it throws an error and the login is denied.  SQL Server seems to support logon triggers, so you should be able to do it.  You just need a script that runs in addition to the trigger that will kill all user sessions, this prevents people from staying logged in.  It doesn't sound like you need to stop the service, you just need to keep people from logging in, and if they cannot log in, they cannot do anything.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
A SQL job can stop SQL Server (you can issue a net stop command).

You'd need a NT task/job to restart it.  You can use net start for that.

Author

Commented:
@Scott
Thanks, But if the server restarted then it will be back to run again
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
You'd want it to automatically start sometime Monday morning, right?  So that it's ready for the work day?
Shaun VermaakTechnical Specialist
Awarded 2017
Distinguished Expert 2018

Commented:
The management won't allow users to access applications out of work time
I would just schedule enable and disable a GPO that blocks the application/SQL ports with the Windows Firewall. Do not stop and start SQL

This might be useful

Windows Firewall as Code
https://www.experts-exchange.com/articles/31687/Windows-Firewall-as-Code.html

Author

Commented:
Yes, i need to stop it on Saturday night and start it back in Monday morning.
but even if server rebooted for some reason the services will not back until Monday morning
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
That's really a business decision not a technical one.  I wouldn't mess with blocking and unblocking ports unless I had absolutely no choice.  I'm assuming non-app business activity could still take place on the weekend, and blocking ports could prevent that.  But, again, the business needs to decide which they really need or prefer.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
but even if server rebooted for some reason the services will not back until Monday morning

Good point.  I forgot to mention, be sure to set the service to manual over the weekend, and only back to any level of automatic on Monday.
Shaun VermaakTechnical Specialist
Awarded 2017
Distinguished Expert 2018

Commented:
That's really a business decision not a technical one.  I wouldn't mess with blocking and unblocking ports unless I had absolutely no choice.
No, I would avoid stopping SQL unless I had absolutely no choice. It will prevent any SQL jobs, maintenance and backups from running. Blocking a port will have none of these effects

I'm assuming non-app business activity could still take place on the weekend, and blocking ports could prevent that.
You can configure Windows Firewall to allow/block specific users, groups, computers, app, ports etc. so, no, it won't prevent other business activities if properly configured
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
You can configure Windows Firewall to allow/block specific users, groups, computers, app, ports etc. so, no, it won't prevent other business activities if properly configured

And how much on-going configuration is that?  How many never-ending hours of adjusting users, groups, ports, etc., to keep everything straight?

You've also now prevent remote logins to the instance for any reason I would think.  How do you get in to address, say, a Windows issue?  How does SQL keep functioning if its ports can't be used?  If it can't use the ports it expects to use to communicate to remote resources?  The SQL Agent has jobs that no doubt reference all kinds of resources, except now they can't, so you have massive numbers of failed jobs, some no doubt with partially processed data.  What a nightmare.

I worked at International Paper, with literally 10s of Ks of users.  I can't imagine how we could have used ports instead of just temporarily making SQL unavailable.
Shaun VermaakTechnical Specialist
Awarded 2017
Distinguished Expert 2018

Commented:
I worked at International Paper, with literally 10s of Ks of users.  I can't imagine how we could have used ports instead of just temporarily making SQL unavailable.
Lol, just one of my clients is 10K. Fully CIS, firewall on every endpoint fully automated

Stopping SQL service is an all or nothing action.

You can do the same with a Port block without screwing with SQL and without any admin overhead as you put it.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Right, sure.  So how you keep the SQL Agent jobs from failing with references to things they can no longer get to?

Author

Commented:
@Scott Pletcher
Good point.  I forgot to mention, be sure to set the service to manual over the weekend, and only back to any level of automatic on Monday.
you mean to set SQL Service to manual start not automatically, if so then if server restarted any time during the business day SQL Service will not start again unless i started it, or i need to set a Scheduled job on startup every day except Sunday.
am i right
Shaun VermaakTechnical Specialist
Awarded 2017
Distinguished Expert 2018

Commented:
Right, sure.  So how you keep the SQL Agent jobs from failing with references to things they can no longer get to?
You allow access to those host that you need to get to. A simple IP or host name exception. Most environments already have an AD group with all SQL computers in

netsh advfirewall firewall add rule name="Allow all from 10.0.0.15" dir=in action=allow protocol=ANY remoteip=10.0.0.15

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Interesting.  You just said you "block all ports" now you say you open them up?

It seemed the main idea is that data is NOT supposed to be loaded during this time.  I don't see how you block access to the data -- to prevent it from being loaded -- but still have jobs run.  I guess you have to modify all the job schedules to keep them from running on weekends: wow, I'm glad *I* don't have to try to do that!
johnsoneSenior Oracle DBA

Commented:
All this is why I like the logon trigger idea.  You have a table with hours that you want the system available.  Run a script as a scheduled job that kills all sessions, then the logon trigger prevents anyone from connecting.  Since it is time based and table driven, if the server reboots, nobody cares.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
You have to build in some type of backdoor to the login trigger, in case you do need to get in during the weekend for some special reason.

I think it's bizarre to keep SQL running but allow no one to come in.  Then you'd be constantly fighting to insure that no one gets in.  You'd be far better off to stop SQL and disable the SQL services until you wanted them run again.  No special code, no masses of port changes, just service changes.  Or, to allow reporting only, change all user dbs to read_only (which does not seem to be the case here, OP seems to want to stop all app activity).  [ Certain db tuning would also be easier to do with the instance down, such as defragging drives. ]

Btw, when the dbs move to Azure, how do you manage the port access then?  Wouldn't it just be easier to stop the db(s) on Azure (and save the money)? (Standard Azure doesn't have traditional instances, it's more dbs (and "servers")).
johnsoneSenior Oracle DBA

Commented:
You can build in a back door, or just read the documentation.  There is a documented way to disable the logon trigger when it is preventing logins, by logging in as an administrator in a certain way.  Pretty well spelled out in the documentation link.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
I wouldn't count on them being familiar with DAC, and the restrictions on its use.

You'd also have to create such a trigger on every instance.
johnsoneSenior Oracle DBA

Commented:
Sorry for trying to suggest something different.  Certainly works for us, allows easily changeable blackout windows.  "Backdoor" is simply a user check, we have designated admin accounts and they are always allowed through.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
I don't think anyone objects to alternative solutions. It's just a matter of deciding on the best alternative.
Shaun VermaakTechnical Specialist
Awarded 2017
Distinguished Expert 2018

Commented:
Interesting.  You just said you "block all ports" now you say you open them up?
You block SQL inbound (my article has like to dynamic ports for SQL) and everything outbound is allow. A trivial task except for someone stuck in their ways

It seemed the main idea is that data is NOT supposed to be loaded during this time
Show me. This is OP's comment. The management won't allow users to access applications out of work time

I don't think anyone objects to alternative solutions. It's just a matter of deciding on the best alternative.
Yes, you are.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
As are you, determined that "port blocking" is the only way.

The OP has his/her choice.  I still view port blocking as way too complex here.  If you don't want people to use an app, shut down that app, don't play ring-around-the-rosey with it.
Shaun VermaakTechnical Specialist
Awarded 2017
Distinguished Expert 2018

Commented:
As are you, determined that "port blocking" is the only way.
I am not. I am responding your silly statements such as the ones below.

wouldn't mess with blocking and unblocking ports unless I had absolutely no choice.
And how much on-going configuration is that?
Interesting.  You just said you "block all ports" now you say you open them up?...'m glad *I* don't have to try to do that!
I still view port blocking as way too complex here.  If you don't want people to use an app, shut down that app, don't play ring-around-the-rosey with it.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
wouldn't mess with blocking and unblocking ports unless I had absolutely no choice, [because] it's  too much on-going configuration

What's wrong with that?  That's my view.  That's far more complex to me than just shutting down an app.
johnsoneSenior Oracle DBA

Commented:
I'm staying out of this from now on.

The idea here is to keep users out.

Blocking ports and/or shutting down the database will keep users out, but what scheduled maintenance tasks will be affected?
Shaun VermaakTechnical Specialist
Awarded 2017
Distinguished Expert 2018

Commented:
Online line of configuration too much? Really?
Shaun VermaakTechnical Specialist
Awarded 2017
Distinguished Expert 2018

Commented:
Blocking ports ----- will keep users out, but what scheduled maintenance tasks will be affected?
None, you block all users, not servers. All outbound connections from the server is allowed
Technical Specialist
Awarded 2017
Distinguished Expert 2018
Commented:
Two Powershell scripts. One for block, one for allow. It can be extended by adding excluded IP subnets. Firewall is only inbound, all outbound are allowed

Block
$SqlKey = Get-ChildItem -ErrorAction SilentlyContinue "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server";

If ($SqlKey -ne $null)
{
    netsh advfirewall firewall delete rule name="MSSQL - SQL Server"
    netsh advfirewall firewall delete rule name="MSSQL - SQL Admin Connection"
    netsh advfirewall firewall delete rule name="MSSQL - SQL Database Management"
    netsh advfirewall firewall delete rule name="MSSQL - SQL Service Broker"
    netsh advfirewall firewall delete rule name="MSSQL - SQL Debugger/RPC"
    netsh advfirewall firewall delete rule name="MSSQL - SQL Server Browse Button Service"
    netsh advfirewall firewall delete rule name="MSAS - SQL Analysis Services"
    netsh advfirewall firewall delete rule name="MSAS - SQL Browser"
    netsh advfirewall firewall delete rule name="MSRS - HTTP"
    netsh advfirewall firewall delete rule name="MSRS - SSL"

    netsh advfirewall firewall add rule name="MSSQL - SQL Server" dir=in action=block protocol=TCP localport=1433
    netsh advfirewall firewall add rule name="MSSQL - SQL Admin Connection" dir=in action=block protocol=TCP localport=1434
    netsh advfirewall firewall add rule name="MSSQL - SQL Database Management" dir=in action=block protocol=UDP localport=1434
    netsh advfirewall firewall add rule name="MSSQL - SQL Service Broker" dir=in action=block protocol=TCP localport=4022
    netsh advfirewall firewall add rule name="MSSQL - SQL Debugger/RPC" dir=in action=block protocol=TCP localport=135
    netsh advfirewall firewall add rule name="MSSQL - SQL Server Browse Button Service" dir=in action=block protocol=UDP localport=1433
    netsh advfirewall firewall add rule name="MSAS - SQL Analysis Services" dir=in action=block protocol=TCP localport=2383
    netsh advfirewall firewall add rule name="MSAS - SQL Browser" dir=in action=block protocol=TCP localport=2382
    netsh advfirewall firewall add rule name="MSRS - HTTP" dir=in action=block protocol=TCP localport=80
    netsh advfirewall firewall add rule name="MSRS - SSL" dir=in action=block protocol=TCP localport=443

    $SubKeys = Get-ChildItem "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server";
    ForEach ($SubKey in $SubKeys)
    {
        If ($SubKey.Name.Contains("\MSSQL") -And !$SubKey.Name.Contains("\MSSQLServer"))
        {
           $InstanceName = $SubKey.Name.Split("\")[4]
           $RegistryKey = Get-ItemProperty "HKLM:$($SubKey.Name)\Setup" -name SQLBinRoot;
           $EXEPath = "$($RegistryKey.SQLBinRoot)\sqlservr.exe";

           netsh advfirewall firewall delete rule name="MSSQL - $($InstanceName)"
           netsh advfirewall firewall add rule name="MSSQL - $($InstanceName)" dir=in action=block program="$($EXEPath)"
        }
        If ($SubKey.Name.Contains("\MSAS"))
        {
            $InstanceName = $SubKey.Name.Split("\")[4]
            $RegistryKey = Get-ItemProperty "HKLM:$($SubKey.Name)\Setup" -name SQLBinRoot;
            $EXEPath = "$($RegistryKey.SQLBinRoot)\msmdsrv.exe";

            netsh advfirewall firewall delete rule name="MSAS - $($InstanceName)"
            netsh advfirewall firewall add rule name="MSAS - $($InstanceName)" dir=in action=block program="$($EXEPath)"
        }
    }
}

Open in new window


Allow
$SqlKey = Get-ChildItem -ErrorAction SilentlyContinue "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server";

If ($SqlKey -ne $null)
{
    netsh advfirewall firewall delete rule name="MSSQL - SQL Server"
    netsh advfirewall firewall delete rule name="MSSQL - SQL Admin Connection"
    netsh advfirewall firewall delete rule name="MSSQL - SQL Database Management"
    netsh advfirewall firewall delete rule name="MSSQL - SQL Service Broker"
    netsh advfirewall firewall delete rule name="MSSQL - SQL Debugger/RPC"
    netsh advfirewall firewall delete rule name="MSSQL - SQL Server Browse Button Service"
    netsh advfirewall firewall delete rule name="MSAS - SQL Analysis Services"
    netsh advfirewall firewall delete rule name="MSAS - SQL Browser"
    netsh advfirewall firewall delete rule name="MSRS - HTTP"
    netsh advfirewall firewall delete rule name="MSRS - SSL"

    netsh advfirewall firewall add rule name="MSSQL - SQL Server" dir=in action=allow protocol=TCP localport=1433
    netsh advfirewall firewall add rule name="MSSQL - SQL Admin Connection" dir=in action=allow protocol=TCP localport=1434
    netsh advfirewall firewall add rule name="MSSQL - SQL Database Management" dir=in action=allow protocol=UDP localport=1434
    netsh advfirewall firewall add rule name="MSSQL - SQL Service Broker" dir=in action=allow protocol=TCP localport=4022
    netsh advfirewall firewall add rule name="MSSQL - SQL Debugger/RPC" dir=in action=allow protocol=TCP localport=135
    netsh advfirewall firewall add rule name="MSSQL - SQL Server Browse Button Service" dir=in action=allow protocol=UDP localport=1433
    netsh advfirewall firewall add rule name="MSAS - SQL Analysis Services" dir=in action=allow protocol=TCP localport=2383
    netsh advfirewall firewall add rule name="MSAS - SQL Browser" dir=in action=allow protocol=TCP localport=2382
    netsh advfirewall firewall add rule name="MSRS - HTTP" dir=in action=allow protocol=TCP localport=80
    netsh advfirewall firewall add rule name="MSRS - SSL" dir=in action=allow protocol=TCP localport=443

    $SubKeys = Get-ChildItem "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server";
    ForEach ($SubKey in $SubKeys)
    {
        If ($SubKey.Name.Contains("\MSSQL") -And !$SubKey.Name.Contains("\MSSQLServer"))
        {
           $InstanceName = $SubKey.Name.Split("\")[4]
           $RegistryKey = Get-ItemProperty "HKLM:$($SubKey.Name)\Setup" -name SQLBinRoot;
           $EXEPath = "$($RegistryKey.SQLBinRoot)\sqlservr.exe";

           netsh advfirewall firewall delete rule name="MSSQL - $($InstanceName)"
           netsh advfirewall firewall add rule name="MSSQL - $($InstanceName)" dir=in action=allow program="$($EXEPath)"
        }
        If ($SubKey.Name.Contains("\MSAS"))
        {
            $InstanceName = $SubKey.Name.Split("\")[4]
            $RegistryKey = Get-ItemProperty "HKLM:$($SubKey.Name)\Setup" -name SQLBinRoot;
            $EXEPath = "$($RegistryKey.SQLBinRoot)\msmdsrv.exe";

            netsh advfirewall firewall delete rule name="MSAS - $($InstanceName)"
            netsh advfirewall firewall add rule name="MSAS - $($InstanceName)" dir=in action=allow program="$($EXEPath)"
        }
    }
}

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
And you consider that way simpler than just shutting down one service or app??
Shaun VermaakTechnical Specialist
Awarded 2017
Distinguished Expert 2018

Commented:
No, but I can allow access selectively whilst allowing SQL to run and happily continue . Stopping the service cannot. Not going to repeat that to you again, clearly you are not open to other opinions.

When I implement SQL etc. database is firewalled off so that only application/web servers can access them. Clearly this is not a common practice in other places, good luck

Author

Commented:
Thanks All

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial