I want to block all user connections during an upgrade to a server. Normally I would set the databases to DBO-Only but I need the SQL Agent jobs to run, and some are owned by non-DBO "accounts".
Is there something else I can do to prevent all externals connections? (Well ... perhaps not all ... I need to connect! although I can RDS into the box if that is the only way)
Perhaps I could change the Listening port to something other than 1433 - or would that cause me some other problem?
I have never tried this, but using a log on trigger might be an option. They specifically mention the following:
You can use logon triggers to audit and control server sessions, such as by tracking login activity, restricting logins to SQL Server, or limiting the number of sessions for a specific login.
I am not an expert and I have bookmarked this thread as I have wondered the same thing.
However I was just about to suggest what James did. But not as well informed.
What about setting deny access then grant? Depending on the number of logins you have.
Typically you change the DNS alias that points to the server to prevent access, let alone logging in. Of course that won't work if you don't have a separate DNS alias users use to reach the server.
I've been having a look at Login Trigger. Bit scary that I might lock everyone out, by accident ... myself included! (Although there is a rescue method for that eventuality I need to do this on a remote server and I'm not 100% sure I can connect myself as the right sort of Admin group as the domain that my login is provided by is a couple of steps removed ... )
I like the DNS approach, but I'm not sure I could guarantee that everyone would be caught by that. Just the other day I put an entry in someone's HOST file so they could connect and do some previewing of a new web product we are rolling out. That HOSTS entry might still be there in years to come if I forget to delete it 
(I also don't have access to the DNS box, so I'd have to ask a colleague to do that for me - a self-sufficient solution would be easier if I needed it in an emergency). Might even be some people connecting by IP address I suppose.
That said, it would be much better everyone was connecting via DNS only so we can mount a new server and then just toggle over the DNS to point to it. Perhaps I should try to enforce "No workarounds".
As it happens this is solved for me, on this occasion, in that I haven't been able to fix some other things in time, so am not ready to lock the database, so the overnight tasks will run tonight and in the morning I plan to disable all jobs, set the databases to DBO only, and then set about moving them to the new server. I'll recopy any user-generated files that anyone may have created this evening, after the scheduled maintenance start time - I doubt there will be any, certainly not many; the ROBOCOPY job for that has completed already so it will only take a few seconds to run it again to catch anything newly created.
I think this would be useful for "the next time", so I'll work on it some more once this rollout is completed.
how about disable the Inbound Rule on windows firewall for the listening port ?
I turned up a couple of things (sorry, this didn't post yesterday for some reason so overlaps Khtan's point):
LOGON TRIGGER would need existing user sessions to be terminated.
An alternative would be to script the STATE of Logons (to re-enable them, afterwards, back to how they were) and then disable them (all bar myself etc.) (Again: have to kill any existing sessions)
If SQL Server is on a dedicated machine - i.e. APPs NOT running on that machine - then: "you can just disable firewall port for SQL Server for all incoming traffic and you’re all good. Another option is to disable IP address in SQL Server configuration manager that is used to access the server (and leave only local IP 127.0.0.1)"
http://dba.stackexchange.com/questions/43802/how-to-block-user-access-to-perform-database-application-update/44155#44155
I have not read the whole thread, but what I do is disable the logins. I believe the agent jobs can still run even if it's owned by one of those logins, though we just use sa. In the case of an Availability Group where the listener is being used for connections, just take down the listener and have the instance to yourself. Now they can skirt around it by connecting directly to the standalone instance, but that would be cheating. So you'd have disabled logins in place as well, just in case.
Thanks Tara. How do you disable logins?
I'm thinking to create a Disable Script and at the same time a Re-Enable script. In particular I am thinking that if a Login is already disabled I don't want to just re-enable all logins after the exercise, as that would enable logins that were previously disabled.
We currently do a similar thing for SQL Agent Jobs that we don't want to fire "unexpectedly" during a maintenance window, so we generate disable /re-enable scripts in order that after maintenance window we put then back to "how they were before".
Similar thing with databases when we move them to a new server - script the current state (Multi User / Read Write or more restricted states), set them all to Single User / Read Only (to stop absolutely anyone / anything from connecting / changing any data), backup and restore to new server, and then run the script to put their state back to how it was before.
I expect there is a Disable / Re-enable Logins script lying around, I'll have a Google 
I had a Google ... why is it that other people's scripts only include One Kitchen Sink whereas I always want at least Four Kitchen Sinks in mine?
What type of logins do I need to include? Current DOCs seem to indicate the following are possible:
SQL_LOGIN
WINDOWS_LOGIN
WINDOWS_GROUP
SERVER_ROLE
CERTIFICATE_MAPPED_LOGIN
ASYMMETRIC_KEY_MAPPED_LOGIN
@Graz has an excellent script http://weblogs.sqlteam.com/billg/archive/2010/07/08/Scripting-out-SQL-Server-Logins.aspx and he has two sections as follows:
where type_desc In ('WINDOWS_GROUP', 'WINDOWS_LOGIN')
AND [name] not like 'BUILTIN%'
AND [name] not like 'NT AUTHORITY%'
AND [name] not like '%\SQLServer%'
where type_desc = 'SQL_LOGIN'
AND [name] not in ('sa', 'guest')
I reckon the syntax I want is:
-- Disable Logins Script
PRINT 'Name: JohnDoe'
GO
ALTER LOGIN JohnDoe DISABLE
GO
PRINT 'Name: JaneDoe (ALREADY DISABLED)'
GO
-- ALTER LOGIN JaneDoe DISABLE
GO
PRINT 'Name: Non$AlphaChar'
GO
ALTER LOGIN [Non$AlphaChar] DISABLE
GO
-- Enable Logins Script
PRINT 'Name: JohnDoe'
GO
ALTER LOGIN JohnDoe ENABLE
GO
PRINT 'Name: JaneDoe (ORIGINALLY DISABLED)'
GO
-- ALTER LOGIN JaneDoe ENABLE
GO
PRINT 'Name: Non$AlphaChar'
GO
ALTER LOGIN [Non$AlphaChar] ENABLE
GO
I want the PRINT statements, and GO termintors, because if I just do
ALTER LOGIN User1 DISABLE
ALTER LOGIN User2 DISABLE
...
and get an error for some reason I won't have much of a clue which login command caused the problem (line number maybe, but I think seeing the error immediately under the details from the PRINT makes sorting it out easier)
I just double-click on the error to bring me to the line number that caused the issue. It's usually correct these days. Plus a script like this will be short. I mean, just how many SQL or Windows logins are you having to disable to do your upgrades? My disable logins script is like 15 lines of code. And it's hard-coded with what I want to disable, no fancy storing of what I did disable so that I can enable those later. I just hard-code it and then hard-code the enables. How often are drastic login changes being made? For us, basically never. That type of stuff should be handled at the application layer. The SQL security stuff should rarely change.
I expect I have fewer than you! We have hundreds, definitely not thousands.
Hadn't thought of it like that. if I only output script lines for CURRENTLY ENABLED logins then:
I don't need to concern myself with any logins that were already disabled - just leave them out of the script altogether.
Thanks Tara.
Nope, I definitely have fewer than you then. On any particular instance, we might have to disable less than 20 logins to do an upgrade. Some of those are Windows groups with domain users, but most are domain users (service accounts) that the applications use.
Now our applications do have thousands upon thousands of users, but those are handled inside the application and not with logins.
Our APPs also do it via a single SQL login, and then handle logins within the APP, but we have a 3rd party Client Server APP that needs a SQL Login for each user, and a second one for any user that needs elevated permissions for certain parts of the APP (company policy requires them not to use that for regular working ...).
But that APP is scheduled to be replaced with a web version, so presumably my Login Problem will go away by itself in a few months ... trouble is we were upgrading the server in anticipation of the new APP not "following successful rollout" of it!
I seemed to remember reading that you had thousands of logins (I think it was on one of your very helpful scripts on your SQLTeam Blog 
), but maybe that was at your previous job ...
Oh yes that one! Yeah that was at a previous job. That system had over 10,000 SQL logins! It was a legacy application. Started out on SQL Server 6.5, actually on FoxPro I believe. Every time we upgraded it, cut it over to DR and back, etc, we had to bring over those 10,000 logins. Oh my! So yeah I had a script for that.
The app was eventually rewritten, and the database was decommissioned (they went with another technology, believe it was DB2). What a relief it was the day it was decommissioned, though I wish they had spent the time to make it work on a modern day platform.
I want to this block all user connetion during on upgarde to a sever .It's normally I would to be database to DBO Only.