How to limit connections to SQL Server until all databases are ONLINE!
Strange requirement, right?
Recently, I got a peculiar case wherein the demand made by the customer was to make sure no application should be able to connect to SQL Server unless and until all the databases are ONLINE. The business requirement of this company was to have all databases online before any of their ten applications can connect to the SQL Server. Well, there is a solution to this requirement but it was quite a different demand I would say!
The solution for this requirement is to use a Logon Triggers. Using Logon trigger we will be able to limit the connections to SQL Server unless all databases are ONLINE, but what if this is a SQL Server cluster? Well, there is a solution for this also! However, there are few precautions that you should take before implementing it.
Environment: SQL Server 2005\SQL Server 2008; Windows Server 2003\Windows Server 2008\R2; Standalone\Clustered Environment.
The code of the Logon trigger for standalone and clustered environments is different. The reason being, on clustered environment there is one more connection which comes to the SQL Server which Cluster Service makes to do ISALIVE check. I will first explain the code for standalone SQL Server and then for clustered environment.
For standalone SQL Server:
CREATE TRIGGER tr_limit_trigger
ON ALL SERVER
FORLOGON
AS
BEGIN
IF
( (SELECTCOUNT(*)FROMsys.databases WHEREstate!= 0)> 0
)
ROLLBACK;
END;
Accordingly, if the any row value of the State column within sys.databases is not 0 (State 0 means database is ONLINE) then SQL won’t allow any database connection to any database.
I tested this scenario by making a database set to OFFLINE (state=6) and I wasn’t able to login to the server at all.
But, in a clustered environment this logon trigger will work with slight modification. Modifications are required since ISALIVE checks may fail which eventually checks if the SQL Server is up by making a connection to SQL instance and runs SELECT @@VERSION. Since we are not allowing any connection on the SQL instance the Resource DLL won’t be able to do ISALIVE checks and hence the entire SQL cluster may come down.
In Windows Server 2003, ISALIVE checks are done under the security context of Cluster Service (domain account) which has to be sysadmin on SQL Server instance.
However, in Windows 2008 and above, you can run Cluster Service in the context of NT AUTHORITY\SYSTEM account. Which means, on SQL Server NT AUTHORITY\SYSTEM or the domain account under which Cluster Service is running has to be sysadmin.
So, the code on Windows Server 2003 cluster will change like this:
CREATE TRIGGER tr_limit_trigger
ON ALL SERVER FORLOGON
AS
BEGIN
IF
(ORIGINAL_LOGIN()!='DOMAIN_NAME\SQLServiceAcc'AND ((SELECTCOUNT(*)FROM sys.databases
WHEREstate!= 0)> 0)
ROLLBACK;
END;
Assuming “DOMAIN_NAME\SQLServiceAcc” is my cluster service account on Windows Server 2003 cluster.
Now, I did this test on Windows Server 2008\R2 cluster. My Cluster Service was running under NT AUTHORITY\SYSTEM and SQL Services were running under domain account.
I DO NOT have BUILTIN\ADMINISTRATORS groups in the SQL Logins. As a result I have made NT AUTHORITY\SYSTEM Sysadmin on the SQL Server, which was necessary.
Then I created following Logon Trigger:
CREATE TRIGGER tr_limit_trigger
ON ALL SERVER FOR LOGON AS
BEGIN
IF ORIGINAL_LOGIN()!='NT AUTHORITY\SYSTEM'AND ((SELECTCOUNT(*)FROM sys.databases WHERE state!= 0)> 0)
ROLLBACK;
END;
This trigger won’t allow any login except 'NT AUTHORITY\SYSTEM' to logon to SQL Server in case any of the databases are not ONLINE. This will make sure that my SQL Server instance will be up and running!
Precautions: In case you want to login to the SQL Server for doing any kind of operation, then you have to make sure to enable ‘Remote Admin Connections’ from Sp_Configure
sp_configure 'remote admin connections',1
Reconfigure with Override
Go
This is an important step which will allow you to login to SQL Server using DAC connection! Do not forget to enable this setting, else you may not be able to login to SQL Server unless and until all DBs are up and running!
Another issue that I experienced while doing this experiment was that my SQL Agent resource went down since it connects to SQL Server using some login. To pass through this hurdle, I made a following change in the code of the trigger – Please be noted that this change should be done on both Windows 2003\2008 R2 clustered.
CREATE TRIGGER tr_limit_trigger
ON ALL SERVER FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()!='NT AUTHORITY\SYSTEM'AND ORIGINAL_LOGIN()!='DOMAIN_NAME\SQLAgentAcc'AND ((SELECTCOUNT(*) FROM sys.databases WHERE state!= 0)> 0)
ROLLBACK;
END;
Here DOMAIN_NAME\SQLAgentAccis the service account for SQL Server Agent. Please follow these guidelines to make an account service account for SQL Server Agent: https://msdn.microsoft.com/en-us/library/ms191543.aspx
Bottom line of this experiment is that, you have the flexibility of NOT allowing any login to the SQL Server if until all databases are ONLINE or other conditions. However, you should take care of the issues with Cluster Service connectivity, SQL Agent Connectivity and other services which connect to SQL Server. The most important point to be noted here is that if you put this setting in your environment, please make sure to enable 'remote admin connections', else you will not be able to login to SQL Server in case you wish to do some troubleshooting on SQL Server.
What if I forget to enable DAC on a cluster???
Do look out for my next blog post on how to login to SQL Server using NT Authority\System account using PSExec tool, this may save you in the above test in case DAC is not enabled!
Written By: Sumit Sarabhai, Escalation Services team, Microsoft India GTSC
Comments
Anonymous
December 17, 2012
Hi It is very interesting article, but how I should create such solution using Windows 2012 Cluster and SQL2012 in Cluster?Anonymous
December 18, 2012
What about databases which are mirrored? They have state = 1 in sys.databases, so in this example you won't be able to log in if your server contains a mirror database.Anonymous
December 18, 2012
Hi Sumit, Good post. Any reason you didn't validate just something like (...) WHERE state != 2 OR state != 3 (...)? Thanks PLAnonymous
December 18, 2012
Correction: I meant (...) WHERE state = 2 OR state = 3 (...)Anonymous
December 19, 2012
The comment has been removedAnonymous
September 24, 2013
How about just using a trace flag 3614?Anonymous
December 02, 2014
Very interesting article, thanks for posting One point, the psexec link is broken ... it's going to http://psexec/ ... rather than ... blogs.msdn.com/.../how-to-login-to-sql-server-with-nt-authority-system-using-psexec-tool.aspxAnonymous
May 09, 2015
Nice article