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_triggerON ALL SERVERFORLOGONASBEGINIF( (SELECTCOUNT(*)FROMsys.databases WHEREstate!= 0)> 0
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_triggerON ALL SERVER FORLOGONASBEGINIF(ORIGINAL_LOGIN()!='DOMAIN_NAME\SQLServiceAcc'AND ((SELECTCOUNT(*)FROM sys.databasesWHEREstate!= 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 ASBEGINIF 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',1Reconfigure with OverrideGo
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_triggerON ALL SERVER FOR LOGONASBEGINIF 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: http://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
It is very interesting article, but how I should create such solution using Windows 2012 Cluster and SQL2012 in Cluster?
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.
Good post. Any reason you didn't validate just something like (...) WHERE state != 2 OR state != 3 (...)?
Correction: I meant (...) WHERE state = 2 OR state = 3 (...)
Thanks for your comments.
@Stisse: I haven't tested it on Win 2012\SQL 2012. However, I will check this out and let you know.
@Szymon: I didnt't thought of this scenario. Obviously, you need to make relevant modifications to the trigger code to exclude Mirror database.
@Pedro: There is no reason for not using the code that you have provided :) I just used a simplest scenario to write this blog. You may want to change the code as per your requirement.
How about just using a trace flag 3614?
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.aspx