suspend or stop SQL service at the weekend

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
Temody MasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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 DBACommented:
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.
Temody MasAuthor Commented:
@Scott
Thanks, But if the server restarted then it will be back to run again
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Scott PletcherSenior DBACommented:
You'd want it to automatically start sometime Monday morning, right?  So that it's ready for the work day?
Shaun VermaakTechnical SpecialistCommented:
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
Temody MasAuthor 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 DBACommented:
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 DBACommented:
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 SpecialistCommented:
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 DBACommented:
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 SpecialistCommented:
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 DBACommented:
Right, sure.  So how you keep the SQL Agent jobs from failing with references to things they can no longer get to?
Temody MasAuthor 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 SpecialistCommented:
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 DBACommented:
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 DBACommented:
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 DBACommented:
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 DBACommented:
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 DBACommented:
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 DBACommented:
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 DBACommented:
I don't think anyone objects to alternative solutions. It's just a matter of deciding on the best alternative.
Shaun VermaakTechnical SpecialistCommented:
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 DBACommented:
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 SpecialistCommented:
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 DBACommented:
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 DBACommented:
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 SpecialistCommented:
Online line of configuration too much? Really?
Shaun VermaakTechnical SpecialistCommented:
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
Shaun VermaakTechnical SpecialistCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
And you consider that way simpler than just shutting down one service or app??
Shaun VermaakTechnical SpecialistCommented:
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
Temody MasAuthor Commented:
Thanks All
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.