I recently saw a post on StackExchange where a user was having a problem with tempdb filling up, which was causing their logon trigger to fail.
The logon trigger was attempting to prevent users from connecting with SQL Server Management Studio (SSMS). It seems that the user was not aware that it is trivial to connect using SSMS to SQL bypassing the app level restriction in place.
Let’s look at an example.
First we’ll create the logon trigger that explicitly looks to see if the application attempting to logon is management studio:
CREATE TRIGGER [DenySSMSLogin] ON ALL SERVER
WITH EXECUTE AS 'sa'
SET NOCOUNT ON;
DECLARE @app SYSNAME = APP_NAME();
IF @app LIKE N'%Management Studio%'
THROW 51000, 'Connection not allowed.', 1;
ENABLE TRIGGER [DenySSMSLogin] ON ALL SERVER;
Now we can test this by opening a new window. We should see an error:
Looks like it’s working just fine.
So let’s circumvent this, try connecting again, this time we just need to edit the Additional Connection Parameters in the connection dialog and pass along a new application name.
And now the connection will work just fine, completely bypassing the trigger (if trying this, don’t forget to disable your trigger when done).
As a person who messes with SQL Server there is a pretty strong probability that you are using SQL Server Management Studio (SSMS). It is a very full featured tool, but does have a couple of problems and quirks (like crashing on large result sets due to it reaching the maximum memory allocation for a 32bit process).
The other day I came across another quirk, maybe even a bug, which can happen when returning and working with XML results, which could lead you to copy incorrect data…
Continue reading “SSMS Results: A Cautionary Tale” →
Hit a strange issue this morning. I was working with one of the network guys testing out connecting to SQL behind an F5 load balancer, something I’ve done multiple times previously.
I was able to connect using SQLCMD, Invoke-SQLCmd and using a UDL source, but for some reason, every time I tried to connect using SSMS I would get an error stating that an “Index was outside the bounds of an array”.
Advanced Information for Error
A little research showed that this error cropped up when trying to connect to SQL 2012 from an earlier version of SSMS. This wasn’t the case here though, everything was SQL 2012, and I was able to connect to the server directly without any problems, it was only an error when going through the F5 VIP.
After a little work and research with the network admin we discovered that OneConnect was causing the issue. It was attempting to pool connections and was causing the problem, turning it off fixed the issue entirely.
Just something to keep an eye out for if you use F5’s to handle load balancing or DR client redirection.