Category: SQL

SQL

Logon Triggers Do Not Prevent SSMS Connections

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'
FOR LOGON
AS
BEGIN
SET NOCOUNT ON;
DECLARE @app SYSNAME = APP_NAME();
IF @app LIKE N'%Management Studio%'
BEGIN
THROW 51000, 'Connection not allowed.', 1;
END;
END;
GO

ENABLE TRIGGER [DenySSMSLogin] ON ALL SERVER;
GO

 

Now we can test this by opening a new window. We should see an error:

failed-to-logon

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.

additional-connection-parameters

And now the connection will work just fine, completely bypassing the trigger (if trying this, don’t forget to disable your trigger when done).

SQL

Enabling IFI on Setup in SQL Server 2016

SQL Server 2016 has added a couple of nice new options to the setup experience. First they added the ability to have multiple tempdb files on install, a nice time saver for later. And now, with CTP 3.0 they have added the ability to enable IFI on install.

What is IFI, and how do I get it?

IFI stands for instant file initialization, and if you are not aware, enabling this allows SQL Server to grow data files almost instantaneously. Without this enabled the data file space has to be claimed and then filled with zeroes, something that is a long and arduous tasks, especially on slower spinning media type storage.

This is only relevant to data files, for security and integrity reasons the log files still need to be zeroed out.

Enabling IFI is actually quite a simple task, you just need to add the SQL service account to the Perform Volume Maintenance Tasks section of the Local Security Policy and then restart the SQL Server service.

What does SQL 2016 do differently?

Prior to SQL Server 2016 (CTP 3.0) you would need to manually add the SQL account to the Perform Volume Maintenance Tasks (PVMT) section of the Local Security Policy (secpol). Now you can have the installer take care of this for you. That really helps with not forgetting to do it later, which can cause some serious performance problems down the road.

Continue reading “Enabling IFI on Setup in SQL Server 2016” →

AlwaysOn, Availability Groups, Bug, SQL

BUG with Availability Groups and sys.master_files

I recently came across a bug with SQL Server and Availability Groups whereby catalog view data is incorrectly reported on all secondary replicas.

This bug has the potential for putting the availability of your environment at risk as reporting around capacity could be calculated incorrectly.

Continue reading “BUG with Availability Groups and sys.master_files” →

SQL, Windows

BCP and Code Page Fun

Quick, what’s the fastest and easiest way to get data out of a SQL Server table?

Time’s up. It’s BCP.

If you aren’t familiar with BCP it is a command line utility to bulk copy data out of, and in to tables. It has been around for donkeys years (technical term), and while it doesn’t have the frills of SSIS it is fast, and lightweight. DBAs have been using it for years, but it may not be something that you’ve heard of.

Recently, while BCPing some data between servers I ran into an interesting problem which caused the data imported to be different than that exported. How does that happen? Code pages…

Continue reading “BCP and Code Page Fun” →

SQL

Getting the SQL Server Install Path

One of the challenges that comes along with a large number of SQL Servers, that aren’t always built out in a standard fashion, is that you run into problems whereby correct AV exclusions are not set, leading to performance problems (and other issues).

Going through and quickly grabbing the location of the SQL Server executable, or the directory in which it resides can be cumbersome. Doing it once is a pain, doing it 30 times multiplies that pain by a factor of about 400.

A quick query run against your servers (and done against server groups) can quickly return results for your machines and help you get on your way again.

Continue reading “Getting the SQL Server Install Path” →

SQL

SSMS Results: A Cautionary Tale

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” →

SQL, Troubleshooting

SSIS Job Steps Failing Pending Execution

The other day a few SQL jobs were failing when attempting to execute SSIS packages. As usual a cryptic error was surfaced up to the job log “Execution Status: 5”

Great, really helpful. So I went to look at the package logs. Those actually didn’t show any failures, rather they just showed “Pending Execution”. At this point I knew it was going to be a long day. Continue reading “SSIS Job Steps Failing Pending Execution” →

SQL

Improving Performance When Querying Multiple DMVs

A couple of days ago I posted a stored procedure (sp_GetAGInformation) which queried multiple DMVs to pull together a bunch of AvailabilityGroup information. If you took a look at the code you would see that it used a couple of CTEs (Common Table Expressions).

CTEs are a great way to do recursive work, and they can also greatly simplify reading code. A CTE without recursion is really nothing more than a subquery that is nicely wrapped.

For example:

Basic+CTE

Basic CTE

Is the same thing as:

Basic+Subquery

Basic Subquery

This can easily lead you down the path towards poor performance. It is quite easy to define a CTE once and use it multiple times, not realizing that every time you use the CTE then you are performing that subquery, meaning is has to be evaluated and executed.

For smaller queries this is not usually a problem, but for larger queries and more importantly when working with DMVs this can be a serious performance problem.

Continue reading “Improving Performance When Querying Multiple DMVs” →