Wayne Sheffield

My blog about SQL Server

Browsing Posts published by Wayne Sheffield

SSMS 17.3

SSMS 17.3 was just released (download link), and it has a couple of new features worthy of mention:

  • Import Flat File Wizard
  • XEvent Profiler

Import Flat File Wizard

If you’ve worked with SQL Server for very long, you have most likely needed to import flat files from time to time. SSMS 17.3 utilizes a new import technology (Microsoft calls it the “Microsoft Program Synthesis using Examples” (PROSE) SDK). This new technology can take raw, semi-structured data in .csv or .txt files, and it identifies patterns to do “predictive file splitting” to allow the wizard to learn about the file, including column names, types, delimiters, etc.

This wizard is available from the Tasks context menu of a database node:

In the wizard that comes up, you simply specify the input file, and the destination table. The new table name will default to the name of the flat file. The wizard then steps you through previewing the data and specifying column schema.

Testing the Import Flat File Wizard

One of the biggest issues that I’ve dealt with is dealing with lines that have quoted text. So, let’s try this new wizard out. We’ll use this import file:

Stepping through the wizard, we first specify the file being imported:

The next step of the wizard let’s you preview the data:

This preview shows that most of the lines handled the quoted text (and other columns) correctly. I expected the last line to have ended with a double-quote, and it doesn’t. However, lines with double-quotes embedded in the line did keep those double-quotes.

In the next step, you specify the schema for the new table:

After this step is a summary screen that tells you simply the server / database name, table name, and file name being imported. Clicking Finish here creates the table and imports the file into the table.

For this simple test, it worked pretty well, and it should work well for most of the requirements that you have. Time will tell how reliably this new feature does work.

The Import Flat File is available when connecting to SQL Server version 2005 or higher. I haven’t tried this on a lower version, but I don’t see any reason why it wouldn’t work there either. You can read more about this feature in Microsoft’s documentation.

XEvent Profiler

The Extended Events (XE) that were introduced in SQL Server 2008 are intended to replace usage of the SQL Profiler. They are not as invasive (resource-wise) on your system as SQL Profiler is. There are a lot of people still don’t use them because they feel that it’s faster to get a profiler trace running than an XE session. The new XEvent Profiler overcomes this. According to the Microsoft blog post introducing this feature, they even want to know if there is a gap that requires you to use SQL Profiler instead of this new XEvent Profiler.

The XEvent Profiler gives you the ability to start and watch (by live target view) either of two predefined XEvent sessions. These provide you with a quick view capability into your system. It provides:

  • Only the two sessions.
  • No filtering.
  • Only the predefined events in those sessions, and only the events available in SQL Profiler.

Since the XEvent Profiler replaces SQL Profiler, it only has the events that are available in SQL Profiler. This means that none of the newer features that need XEvents to monitor are available in these sessions. I predict that you’ll either love or hate this feature – and I know people who are on both sides of this feature already!

The XEvent Profiler is available in SSMS just under the SQL Server Agent node:

From here, you can start and stop the session. When you start (launch) the session, it will open up the XEvent Live Data Viewer.

The XEvent Profiler is available when connecting to SQL Server versions 2012 or higher. You can read more about this feature in Microsoft’s documentation.

 

It’s not stable until Service Pack 1…

Are you at a company that doesn’t install a new version of SQL Server until Service Pack 1 (SP1) has been released? Well, you need not wait for SP1 any more.

Because there’s not going to be any more service packs.

Microsoft just announced their “Modern Servicing Module for SQL Server“. The big news is that starting with SQL Server 2017, service packs will no longer be available. Only Cumulative Updates (CU) and General Distribution Releases (GDR).

Background

Historically, Microsoft has released a CU every 2 months after a major version has been released, and approximately every year a SP is released. The SP contains all of the fixes from the previous CUs, plus anything requiring localization (feature completeness, supportability enhancements).

The release of an SP would establish a new product baseline. CUs are provided for 12 months after the next SP releases or until the end of the product’s support lifecycle, whichever occurs first.

A GDR is released when needed, and contains only security related fixes.

The Modern Servicing Model

  • No SPs, only CUs and GDRs.
  • CUs will now have localized content. This will allow new feature completeness and supportability enhancements to be delivered faster.
  • CUs will be delivered every month for the first 12 months, and then quarterly for the next 4 years (which handles the complete 5 year mainstream lifecycle). Microsoft research shows that the vast majority of hotfixes occur during the first year. Releasing monthly allows bringing fixes to customers as quickly as possible, where the impact is the greatest.
  • CUs will be delivered on the 3rd Tuesday of the month.
  • When CU12 is released, slipstream media will be available to install the product with all fixes through CU12.

What about previous versions of SQL Server?

There is nothing to worry about – nothing is being changed for existing versions. Specifically:

  • Years 0-5 (Mainstream Support) – Security and functional issue resolution via CUs. Security issues through GDRs.
  • Years 6-10 (Extended Support): Security or critical functional issues.
  • Years 11-16 (Premium Assurance): Optional paid extension to Extended Support

If you insist on waiting because the RTM release isn’t stable…

If your company has the policy now of waiting for SP1, this is most likely because in the (distant) past, there were issues that weren’t working adequately until SP1. In my opinion, this is no longer the case, and hasn’t been so in quite a while. In recent years, features are being tried out (and fixed) in Azure SQL Database (aka “The Cloud”) for quite some time before the feature makes it into the on-premises release. The features have been stabilized and just don’t have the issues that may have been present years ago.

If you insist on waiting, then wait until CU12. Since the first year has monthly CUs, CU12 will be after a year. SPs were being released approximately yearly, so CU12 will line up with SP1. Furthermore, CU12 will be available via slipstream media so that you don’t have to install all of the CUs after installing the product.

In Conclusion

  • If you have any other questions, the announcement link above has a FAQ section that answers most questions that you would have about this change.
  • Keep in mind that you can’t move back and forth between GDR and CU – once you apply a CU, you are on the CU path. You can only stay on the GDR path if you only apply GDRs. (But seriously – don’t you want the fixes?)
  • You don’t have to install the CUs as they are released – you can use your own cycle. Like every 2 months? Go with that. Or Quarterly. Or twice a year. Whatever works for you. Just please remember to do a full regression test in a testing environment before you apply the changes to production. You do not want to be surprised by something that is now working differently.

 

Announcing Seattle Freecon 2017

Are you going to the PASS Summit this year? (and if not… why not?)

Are you coming in to Seattle on or before Tuesday?

Are you not attending any of the wonderful precons at the PASS Summit on Tuesday?

Then I have a great deal for you. Come attend the Seattle Freecon 2017!

Free SQL Training!

Several friends have teamed up to run a day of SQL training (while the training is free, the hotel we are using requires us to use their catering for lunch, so we are charging for lunch). Join us on Tuesday, October 31st, 2017 at the SpringHill Suites located at 1800 Yale Avenue, Seattle, WA 98101. This is about 4-5 blocks (just a bit over a half mile) from the Convention Center, so it’s just a short walk for anyone in the area. The only catch is that it starts promptly at 9am, so no late-night rebel-rousing if you want to spend your day learning!

From the speakers, we have 4 Microsoft Certified Masters (MCMs), 4 MVPs and several Friends of RedGate. These SQL Server experts are:
Jason Brimhall (blog / twitter)
Louis Davidson (blog / twitter)
Andy Leonard (blog / twitter)
Jimmy May (blog / twitter)
Gail Shaw (blog / twitter)
Wayne Sheffield (blog / twitter)

Just take a look at these speakers! Wow, what a great list! However, the speakers won’t make it a great day by themselves. While they do come close, this day is really all about the training. Topics for the day include:
Designing an SSIS Framework
What is this “SQL Inj/stuff/ection” and how does it affect me?
Parameter Sniffing and other cases of the confused optimizer
Implementing a Heirarchy in SQL Server
Two additional sessions will be announced soon (and I’ll update this page when they are).

Are you really eager to go to this now? Well, just jump over to our registration page, and sign on up! We look forward to seeing you there!

In a recent post, I introduced you to how to work with the registry directly from within SQL Server. Continuing this theme, this post provides an example situation where you would do so.

In this example, we will want to configure SQL Server to enable a few trace flags (TF) when SQL Server starts. Specifically, let’s set trace flags 1117 and 1118 so that they are enabled when SQL Server starts up, and enable them now. Additionally, we have trace flags 1204 and 1222 now enabled on some servers, and we want to disable those (since we have the deadlocks being captured in the system health XE, we don’t need them in the error log also). We also don’t want to force a restart of the SQL Server services.

Let’s start off with how the environment is configured. The below screen shot (from SQL Server Configuration Manager) shows the startup parameters now in use:

Registry-Current Startup Parameters

Notice that this instance has only TF 1204 enabled.

Retrieving the Startup Parameters

The registry key “HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer\Parameters” stores the startup parameters. Especially relevant is the “MSSQL12.SQL2014” portion, which will vary depending on the SQL Server version and your instance name. However, if you remember from the earlier post, there are instance-aware versions of the registry extended stored procedures that automatically handle this. For the instance-aware versions, use this registry key instead: “HKLM\Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters”.

To get all the startup parameters, we would use the xp_instance_regenumvalues extended stored procedure. As noted in the earlier post, this produces a result set for each value. Therefore, this example loads them into a table variable and then displays this table:

This script could have omitted the two variables, but since I’m planning on using them for updating the registry, I decided to store their locations in variables and just use those instead. Additionally, since I’ll be adding to and removing from this list and I want to keep the parameters in the same order, I have added a computed column to calculate the argument number. Running this script shows all the startup parameters:

Registry-Current Startup Parameters TSQL

This matches the earlier screen shot. Running the following will show the running trace flags:

Trace Flags Current

Enabling / disabling trace flags

The next step that needs performed is to enable trace flags 1117 and 1118, and to disable trace flags 1204 and 1222. The commands DBCC TRACEON and DBCC TRACEOFF are used to accomplish this. These commands take a comma-delimited list of trace flags. To perform their action at a global level, use the optional flag of “-1”. These commands set and verify the trace flags:

Trace Flags Changed

Setting trace flags to handle the instance restarting

This will start and stop the trace flags immediately; however this has not changed their startup state when the instance next restarts. Consequently, we need to modify the registry to remove TF 1204 and to add TFs 1117 and 1118. The following statements will override SQLArg3 with the TF 1117, and add TF 1118 to SQLArg4. Remember that writing to the registry requires access for the service account to the specified keys. Optionally, the service account can be a member of the Local Administrators group. In order for SQL Server to pick up this change, the service must be restarted after these changes have been made.

Cleaning up the registry

If we remove more values than what were added, we might need to call master.sys.xp_instance_regdeletevalue. Additionally, if we are only deleting some values, there might be gaps left in the argument number which need eliminated. The following script enables and/or disables current trace flags, modifies the registry for the startup parameters, and handles the registry consolidation / cleanup of this key.

After running, you can see that the changes have in fact been changed:

Registry-Changed Startup Parameters

Registry-Changed Startup Parameters TSQL

 

There is a lot of information within the Windows registry. Sometimes, it would sure be nice to work with the registry within your T-SQL scripts. As it turns out, there are a bunch of undocumented extended stored procedures that do just this. A listing of these procedures are:

Regular Instance-Aware
sys.xp_regread sys.xp_instance_regread
sys.xp_regenumvalues sys.xp_instance_regenumvalues
sys.xp_regenumkeys sys.xp_instance_regenumkeys
sys.xp_regwrite sys.xp_instance_regwrite
sys.xp_regdeletevalue sys.xp_instance_regdeletevalue
sys.xp_regdeletekey sys.xp_instance_regdeletekey
sys.xp_regaddmultistring sys.xp_instance_regaddmultistring
sys.xp_regremovemultistring sys.xp_instance_regremovemultistring

As you can see, there are two categories of extended stored procedures that we can use, with complimentary procedures between them. The “Instance-Aware” category procedures utilizes key word substitution in order to navigate to the appropriate registry keys for the SQL Server instance that is running. I’ll show you an example shortly.

Registry Basics

Before we go too much further, lets explain a few key phrases when dealing with the registry:

Registry: The registry is a hierarchical database used by Windows. It is organized in a tree view manner.

Registry Hive: A hive is a logical group of keys, subkeys, and values in the registry that has a set of supporting files containing backups of its data.

Examples of Registry Hives are:

Registry Hive
HKEY_CURRENT_CONFIG<
HKEY_CURRENT_USER
HKEY_LOCAL_MACHINE\SAM
HKEY_LOCAL_MACHINE\Security
HKEY_LOCAL_MACHINE\Software
HKEY_LOCAL_MACHINE\System
HKEY_USERS\.DEFAULT

Of all of these hives, you will probably use HKEY_LOCAL_MACHINE and HKEY_CURRENT_USER the most.

Registry Key: A node within a registry hive is called a key. A key can contain both sub-keys and values. Sometimes an application will only be looking for the presence of a key; at other times it will need to look at the value.

Registry Value: A registry value is simply a piece of data, of any data type.

The following image shows the tree view layout of the registry, and identifies the various items within it:

Registry

Querying the registry from SQL Server

Using xp_regread / xp_instance_regread

Let’s start off by querying some data within SQL Server. I have an instance of SQL Server named “SQL2014” (would you care to take a guess as to what version of SQL Server this is?). One of the items stored in the registry is the location of the SQL Agent working directory. These procedures can query the registry and return the specified values. For example:

When I execute this statement, SQL returns the following result:

In order for you to run this, you may need to change the key as appropriate for the version and instance on your server. The result includes both the value, and the data for the specified path.

xp_instance_regread

In this example, I used xp_regread to read the direct registry path. If you remember from earlier, there are SQL Server instance-aware versions of each registry procedure. A comparable statement using the instance-aware procedure would be:

This statement returns the exact same information. Let’s look at the difference between these – in the first query, the registry path is the exact registry path needed, and it includes “\Microsoft SQL Server\MSSQL12.SQL2014\”. In the latter query, this string is replaced with “\MSSQLSERVER\”. Since the latter function is instance aware, it replaces the “MSSQLSERVER” with the exact registry path necessary for this instance of SQL Server. Pretty neat, isn’t it? This allows you to have a script that will run properly regardless of the instance that it is being run on. The rest of the examples in this post will utilize the instance-aware procedures to make it easier for you to follow along and run these yourself.

Syntax

The syntax for these procedures is:

The first parameter is the registry hive that you want to query, the second parameter is the key path, and the third is the value name. The third parameter is optional – if it is provided, then the procedure will return the data from the specified value item; if it is not provided, then the procedure only returns whether the specified key exists. There is also an optional fourth parameter, which is an output parameter, and the data output will go into that. The parameters are positional, and while you can specify a name for the parameter, any name will work. Since the parameters are positional, my recommendation is to not use the parameter names. The following example utilizes the optional fourth parameter, and it will return just the specified path into the variable:

You would think that if you specify either NULL or an empty string for the third parameter, that you could send whether the key exists to an output variable – however, I have not been able to figure out a way to accomplish this. Specifying either of these values results in an error when running this statement. If you know how to do this, please leave a remark so that this post can be updated with that information.

Using xp_regenumvalues / xp_instance_regenumvalues

These procedures will enumerate through all of the values of the specified key, returning a separate result set for each value. For instance, the following statement will return all of the values in the above SQLServerAgent key:

Having all of these result sets makes it difficult to work with this procedure. Thankfully, you can put these all into one result set – just create a temporary table (or table variable) to hold the output, and then use INSERT / EXECUTE to fill it, like the following example does:

Now that all of the results are in one result set, you can work with it a bit easier.

Syntax

The syntax for these procedures is:

Using xp_regenumkeys / xp_instance_regenumkeys

These procedures will enumerate through all of the keys in a specified path, and return all of the keys in that path. Unlike xp_instance_regenumvalues, all of the keys are returned in one result set, though you will probably want to use INSERT / EXECUTE to put this into temporary storage so that you can work with it. An example of using these procedures is:

The syntax for these procedures is:

Modifying the registry from within SQL Server

Up to this point, we have focused on retrieving data from the registry. What if you want to modify the registry? Read on… be forewarned that the following procedures are modifying the registry, which means that they can also damage the registry, possibly rendering the server unusable. Use at your own risk!

xp_regwrite / xp_instance_regwrite

These procedures are used to create keys and write data into the registry. You can create up to 32 sub-keys at a time. In the following example, a new key “MyNewKey” will be added to the SQLServerAgent key, and the value “MyNewValue” will be added to this new key with the data “Now you see me!”. It will then read this value from the registry.

Syntax

The syntax for these procedures is:

xp_regdeletevalue / xp_instance_regdeletevalue

These procedures are used to delete a specified value from the registry. In this example, the “MyNewValue” value will be deleted. The example first enumerates through all of the values in this key (just the one), deletes the value, enumerates through them again (since there are no values, there will be no result set), and then finally shows that the key is still present.

Syntax

The syntax for these procedures is:

xp_regdeletekey / xp_instance_regdeletekey

These procedures are used to delete an entire key from the registry. In this example, the script will first add another new key under “MyNewKey”, and a value in that new key. The script then deletes both the “AnotherNewKey” (which deletes the value just added also) and “MyNewKey” keys and finally shows that both keys have been deleted. Note that in order to delete a key, it cannot have any sub-keys, which is why the script deletes “AnotherNewKey” first (try running the script first by commenting out the first xp_instance_regdeletekey to see that it must be empty).

Syntax

The syntax for these procedures is:

xp_regaddmultistring / xp_instance_regaddmultistring

These procedures are used to add a string to a multi-string entry in the registry, or to create a multi-string registry entry. In this example, I’ll call the procedure twice. The first time will create the entry with one string in it, and the second time will add a second string to it. Then the example will show the results of this value.

Did you notice that the regread procedure got a little confused here? It has two value columns (which is the name of the value), but the second one has the data for the first one. Then the data column is null. If a third string is added, it still returns just these three columns. To see that we actually added these strings, we’ll have to use regedit.exe:

Registry2

Syntax

The syntax for these procedures is:

xp_regremovemultistring / xp_instance_regremovemultistring

These procedures are used to remove a string from a multi-string entry. This example will remove the middle string.

Examining the string now in regedit.exe, it can be seen that it removed all of the strings starting with the specified string… in other words, it removed the second and third strings. If we add the strings back in, and then remove the third string, we can see that it also removes the second and third string. In testing with a fourth string, it appears that if you are deleting a string that is not the first string, then all of the remainder of the strings after the first string are removed. Deleting the first string deletes all of the strings. Well, these are undocumented procedures, so it’s not likely that this bug will ever be fixed.

Syntax

The syntax for these procedures is:

And finally, let’s ensure that things are cleaned up:

Conclusion

With the exception of removing and reading the multi-string values, these extended stored procedures all work pretty well. We’ve been able to read registry values, and to enumerate through a list of keys and values. Keys and values can be created and deleted.

If you try to use the procedures in sections outside of SQL Server keys, you may run into registry security problems where the SQL Server service account doesn’t have permissions to accomplish the task. On the internet, you will find articles telling you to add the service account to the local administrators group (and to restart the service). This is a VERY BAD IDEA – by doing this, the server can be completely compromised – you will be allowed to do anything that you desire on the server. Instead, use regedt32.exe, which will allow you to modify the permissions on the key that where you are having access issues.

I close by repeating my warning from above – be very careful when modifying the registry. It is possible to corrupt the registry to the point where the server will no longer function. To recover, an operating system reinstall will be necessary.

As a footnote on working with the instance-aware installation. I recall seeing on the internet other keywords that could be substituted instead of MSSQLSERVER. One of them is SQLServerAgent. However, I can’t find any others right now. If you happen to know of other keywords that work, please reply to this post so that it can be added to this post.

This post is re-published from my original post on SQL Solutions Group.

In order for your SQL Server instance to run optimally, there are many SQL Server best practices that you need to follow. The SQL Health Check that you can have us perform on your instances looks at many of these. SQL Server 2016, which at the time of this writing is in a Release Candidate 0 state of readiness, has been changed to handle several of these best practices. This post will look at several of these changes.

Some of the SQL Server Best Practices

Instant File Initialization

The first change appears during the installation of SQL Server 2016 itself. One of the best practice items that I look for in my Health Check is that the SQL Server Database Engine service has the “Perform Volume Maintenance Task” security privilege. SQL Server 2016 now allows the setting of this privilege right in the install screen.

SSMS2016-PVMT

The benefit of having this privilege set is that it allows SQL Server to utilize “Instant File Initialization” (IFI). When IFI is not set, then any growth of a database’s data file will be zero-initialized… that is, the entire growth portion of the file is overwritten with zeros (note that database log files are always zero-initialized initialized with 0xC0… this is needed for the crash recovery process). During the time that the database data file is being zero-initialized, no other activity can occur in the database. This setting not only affects growths of data files, but also affects database restores and adding new files to a filegroup. Kimberly Tripp has an excellent post that explains this in more detail, and the kind of performance impact that can be obtained by having IFI enabled.

However, configuring the instance to utilize IFI has a potential negative security side-effect. If you have just dropped a database, and a growth occurs on another database, it could use the part of the drive that the other database was using. And although it is extremely unlikely, the pages could possibly line up. With advanced techniques, one could then read the data that was on those pages. Obviously, zero-initializing the pages would eliminate this problem. It is also obvious that there are a lot of circumstances that have to line up to allow this potential security issue…but the chance of it occurring will never be zero with IFI enabled.

Because of the performance impact, and extremely low security risk, most SQL Server professionals recommend having this option set.

tempdb

Ahh, tempdb. In SQL Server, that database does so much… it’s where temporary tables and table variables are stored. It’s where row version activity is stored. It’s where running queries will spill out temporary workspace needed for the query. And on, and on, and on. On busy systems, having this database running optimally is of vital importance. And there are several best practices that need to be observed here.

First up is the number of data files needed for tempdb. On a busy system that is creating a lot of temporary objects (specifically temporary tables and table variables), the creation of these objects creates a hot-spot in one of the database file’s internal page types that stores information about the allocation of these objects. However, each database file has these internal pages. Frequently, this contention can be greatly reduced by simply adding additional database files to this database. The specific advice for how many files to have has changed over time, and there is disagreement over the exact number of files to have. What isn’t disagreed over is that additional files are needed. And once again, during the SQL Server 2016 installation, this can be set, as seen in the following screen shot:

SSMS2016-TempDB

This screen shot shows several of the changes that are now available in the installation process. First off is the number of data files. Note that the installation limits the number of data files to the lower of 8 or the number of CPUs presented to SQL Server (one of the methods of determining how many files to have).

The next settings that this screen shot shows is the new default size and autogrowth values. Technically, these changes are made to the model database. However, they apply to all new databases, including tempdb when it is created. The initial size of the data and log files is now 8 MB. The default auto-growth of data and log files is now 64MB.

The next setting available in this screen is the directory to place the data and log files in. Specifically, you can have tempdb data files in multiple directories. During the installation, these files are created in the directories in a round-robin fashion.

Trace Flags

There are three specific trace flags that are commonly set in SQL Server. If trace flag (TF) 1117 is enabled, then whenever a data file grows, all of the files in that database will also grow. In SQL Server 2016, this trace flag has been replaced with the AUTOGROW_ALL_FLAGS or AUTOGROW_SINGLE_FILE option of the ALTER DATABASE command, and setting TF 1117 no longer has any effect. Since it is set as part of the ALTER DATABASE command, it can be controlled at the database level, where TF 1117 was a setting across the entire instance. Furthermore, because this setting is so valuable to have set in tempdb, by default tempdb is set with this feature enabled.

TF 1118 controls whether an extent can have pages belonging to multiple objects (known as a mixed extent), or to a single object (known as a dedicated extent). Using mixed extents allows having small tables use the absolute minimum amount of storage space…which isn’t really that much of an issue as it was in SQL 7.0 when it was added. Using dedicated extents can also help in the contention when creating lots of objects really fast. In SQL Server 2016, this trace flag has been replaced with the SET MIXED_PAGE_ALLOCATION option of the ALTER DATABASE command (and like TF 1117, TF1118 no longer has any effect). Furthermore, in the tempdb database, this setting is also enabled by default.

The next trace flag to talk about is TF 4199. This trace flag controls whether changes to how the query optimizer functions are used or not. In prior versions of SQL Server, the changes were not used unless this TF had been enabled. Starting in SQL Server 2016, most of these behaviors are enabled if the database is in the SQL Server 2016 compatibility level.

Reference: SQL Server 2016: What’s New in (the) Database Engine

This post is re-published from my original post on SQL Solutions Group.

secret_iTarget. Home Depot. NASA. U.S. Army. Anthem. Wall Street Journal. MarketWired. Ashley Madison. What do they all have in common? They are all recent victims of cyber-attacks – several of these are confirmed to involve SQL Injection. Even though the exact method used to penetrate the other systems has not been released, experts believe that SQL Injection was involved in all of these cases. Take a look at http://codecurmudgeon.com/wp/sql-injection-hall-of-shame/ for places that are known victims of SQL Injection.

The part that is really astonishing is that we have known how to stop SQL Injection for more than a decade. Completely. Dead. In. Its. Tracks. Which means that the problems happening today are because of careless coding techniques, and as such, they can be prevented.

The “Funky query”

A few years ago, I was working for a company that developed and sold software. One day, my supervisor sends me an email titled “Funky query”. Hmm, that sounds interesting… I’ll open this up right away, instead of ignoring it until I finish what I’m currently working on (like I would usually do).

Wayne… one of our customer support engineers sent us this from the application error log of a customer. They were running a report. Any ideas on how this funky query came to be?

Query from error log (genericized… because I can’t use the real query):

SELECT * FROM sys.objects WHERE name = ‘O’Malley’

Details from the error log:

Me: Umm, boss… Does this report allow users to enter in search criteria?

Boss: But of course!

Me: Well, I really hate to tell you this, but we have a SQL Injection problem.

And after a bit of back and forth where the developers were insisting that no way was there a SQL Injection problem, I sat down with the dev team lead and the boss and proved it to them. We created a dummy table in the database, went to the report criteria form, and I dropped the table.

Wayne: +1000

Development Team: -1000

Defining SQL Injection

Before we go into how SQL Injection occurs, let’s first examine what it is. SQL Injection is simply a code injection technique that allows SQL code that the user enters in the front-end to be run against the back-end database.

Please note a few things about this definition:

  1. SQL Server was not mentioned. Any and all database platforms are susceptible to SQL Injection. SQL Server. Oracle. PostgreSQL. DB2. It doesn’t matter – all database platforms are susceptible to SQL Injection.
  2. A generic front-end is mentioned. This is anything that connects to a back-end database, be it a web form or your favorite application. Anything that connects to a database can allow SQL Injection.
  3. Improperly written code within the database (such as a stored procedure) can allow SQL Injection. It’s all about how the query is created and run against the database.
  4. There are other forms of Injection attacks that can occur; SQL Injection is just one. Other forms include Cross-site scripting, Web Parameter Tampering, LDAP Injection and XPath Injection (as I write this, WordPress 4.3.1 is being released to fix two Cross-site scripting issues).

SQL Injection can happen in three different ways:

  1. Classic – this is when a user enters SQL code, and can immediately view the results on screen.
  2. Blind – this is essentially a classic attack, but the user does not see the results. Things may still happen, and the user may be able to send results to files that can be downloaded, but the results are not shown on screen.
  3. Second Order – the user supplied SQL code is actually stored in the database, and at a later point in time it is run.

Creating SQL Injection

SQL Injection happens when a SQL statement is created from the data supplied by the user, and it is run by the application or other process. Obviously the intention of the application is to only run the queries that it supplies. Consider a login form with user name and password data entry fields. Assume that the user enters “Mickey” as the user, and “1234” as the password. In this case, the application essentially performs:

This will create the SQL statement:

And when we run this, we get the expected results. But now, a hacker becomes the user. Being wise to SQL Injection, the hacker enters for the password the string

This results in the following SQL Statement being generated:

Since every row matches the “OR 1=1” predicate, every username and password will be returned.

Hacker: +1

Developer: -1

Notice that I showed this attack with T-SQL code – this means that you can have SQL Injection vulnerabilities within your T-SQL code (such as stored procedures), as well as any SQL commands that the application might construct and send to the database. The application might be properly handling for SQL Injection, but if it is calling SQL code that is improperly coded, you can still be vulnerable to SQL Injection. Everything has to be done properly – all it takes is one point where SQL Injection is possible to become the latest victim.

What can a hacker do with a SQL Injection vulnerability?

At this point, the hacker knows that the application is susceptible to SQL Injection, and can now issue any SQL statement desired. Depending on the access level that the application has to connect to the database, the hacker can:

  1. Get a list of all of the databases in the instance.
  2. Get a list of all of the tables in the current database, or any other database.
  3. Query the data in any of the other tables that can be seen.
  4. Modify (insert / update / delete) data in any of the tables that can be seen.
  5. Create / modify / drop any object in any database on the instance.
  6. Create elevated permissions at the SQL Server instance level. This will allow the hacker to do anything they desire on this instance. Drop tables? Yes. Query any and all data? Yes.
  7. Create elevated permissions at the Operating System level.
  8. Install programs on the server.
  9. Access any server on the network, and perform all of the above on those servers.

Scary? Absolutely. However, as I mentioned before, we know how to prevent SQL Injection. And it turns out that it’s just not that hard. Instead of making SQL Statements that include data (either as directly supplied from the user, or from the database), pass that data to the SQL statement through parameters.

If we take the previous example, and modify it to use parameters, we would have:

What’s the chances that you have a password of “1234′ OR 1=1; –” in your system? Assuming that you don’t, this query will return zero rows. SQL Injection has been prevented.

So now you want an example of the difference between classic and blind SQL Injection? Well, I’m not going to show you an example… because this will normally come down to how the application handles the data being returned. In the “OR 1=1” example, if the application only shows the first record, then the hacker won’t see the results of the SQL Injection (unless they notice that it’s showing the wrong data). Or the application may have been coded to expect only 1 row, and if more are returned then it won’t display any.

Second Order SQL Injection

But how about a second order SQL Injection? Let me explain that with an example. Imagine a form for entering new users. The development team has learned about the SQL Injection on the forms, and they are now using parameters. When the hacker enters for the password:

1234′ OR 1=1;–

The generated SQL statement is:

When the data is queried:

We get the following results:

The data has been stored in the database, and we are now primed for a second order SQL Injection attack. Some other process comes along (for example, a month-end report), and it creates dynamic SQL by building the SQL statement from the data. And the second order SQL Injection has just been released…

What have we learned so far?

  1. SQL Injection can happen to any database system.
  2. SQL Injection can be within SQL code (stored procedures), or it can be in the statements passed to the database from the application.
  3. Parameters will prevent the SQL code from being executed immediately, thereby preventing Classic and Blind SQL Injection attacks.
  4. The SQL Injection attempt can still be stored in the database, priming yourself for a Second Order SQL Injection attack.

Preventing SQL Injection

Whenever you are creating a SQL statement, it must never create the statement with data (either from the database, or from what the user may pass). Instead, all SQL statements must use parameters. Even the month-end report.

Within SQL Server, have your queries reference the parameters (ColumnName = @VariableName). If you must use dynamic SQL to build your statement, use sp_executesql – it allows for the building of SQL statements with parameters, and then defining and passing those parameters in as the procedure is called.

In your application, there are several component choices. Some directly execute SQL, and others can use parameters. You want to always use the components that use parameters, and you must use those parameters.

Learning more

The Open Web Application Security Project is a community-driven, non-profit organization where experts analyze web application security issues and then compile a list of what the biggest issues are. For the last several years, the category “Injection” has been the biggest issue, and this includes SQL Injection.

This isn’t all that they do. They also have educational resources to not only instruct you on how these issues manifest themselves, but what to do about them. I strongly encourage everyone to take this resource back to your company, and to start identifying and addressing security issues in your code.

Another issue is that developers don’t really know about SQL Injection. In the workshops that I have given where this topic is presented, very few attendees have seen SQL Injection in a test environment. Most can barely give an adequate description of what it is. Those that have seen it are usually those who have been a victim of it. So I also recommend getting your company to arrange for SQL Injection training for the developers and DBAs. It would be good to send your QA team to this also.

This post is re-published from my original post on SQL Solutions Group.

NoProfilerIn a prior post, I shared a script that will take a running trace and show you the XE events that it relates to, and what columns are available within those XE events. Specifically, this was for converting a deadlock trace into an XE session; however the process is the same for converting any trace into an XE session. In today’s post, we’ll compare the deadlock trace and the new XE by running both, creating a deadlock, and comparing the captured data. We’ll look at the data captured in the XE from both script and GUI, and look at a few other differences between running a trace and an XE session.

The first step is to grab the trace and XE scripts from the prior post. Modify both scripts to put the output files in an appropriate place on your system. Run both of the scripts to start the trace and to create the XE session. Next, start the XE session with the following script:

The next step is to create a deadlock. Open up a new query window, and run the following. Leave this query window open.

Next, open up a second query window, and run the following code in that window:

Finally, return to the first query window and run the following code, at which point one of the statements in one of the query windows will be deadlocked:

Now that we’ve created a deadlock, let’s compare the trace output data to the XE output data. First, let’s grab the data from the trace file with this script, which selects all of the non-null columns from the table valued function (remember to change the filename/path as appropriate):

SSMS2014XE09-XE-Collected-Trace-Data

SSMS2014XE10-XE-Collected-Trace-Data

SSMS2014XE11-XE-Collected-Trace-Data

We now want to compare this to the data that was collected by the Extended Event session. In SSMS, expand the Management Tree, then the Extended Events tree, Session tree, and the Deadlocks tree:

SSMS2014XE07-XE-Session-target

The event file target can now be seen. Double-click the file target to view the data, and you can see the events that fired for the deadlock. Note that you can right-click on the grid header, and choose additional columns to put into the grid – for this XE session, I like to add the database_name, resource_description, resource_owner, resource_type and xml_report columns.

SSMS2014XE08-XE-Collected-Data

By selecting one of the rows, all of the data will be available in the lower grid in a Name/Value format.

Let’s compare the XE output to the trace output, and ensure that the data captured by the trace is present in the XE output. The first trace column is TextData. In that, we see multiple bits of data: SPID, lock resource, and the deadlock graph in XML format. In the XE, the resource_description column has the lock resource, the xml_report column has the deadlock graph in XML format, and the session_id column has the SPID. Skipping the BinaryData column, the next column is the NTUserName. Here we can see that this is collected in the XE nt_username column. A minor difference is that the XE includes the domain name, and the trace doesn’t.

The ClientProcessID column maps to the client_pid column. Likewise, the ApplicationName maps to client_app_name, LoginName maps to server_principal_name, SPID maps to session_id, Duration maps to duration, StartTime maps to timestamp, ObjectID maps to object_id. The EventClass, Mode and Type columns maps via friendly names to the name, mode and resource_type columns. The only columns that aren’t explicitly in the XE results are the BinaryData, EndDate and ServerName columns. The EndDate can be calculated from the timestamp and duration columns, and we could have selected the server_instance_name in “Global Fields (Actions)” tab when configuring the XE session. So here, we can see that everything needed from the trace is available in the XE session.

For the next step, one might want to load the data into a table, and viewing it in the viewer doesn’t allow for this. Or maybe you are working on a server prior to SQL Server 2012, and the GUI isn’t available for you to use. The following script can be used to query the data from the file target (again, remember to change the file name/path as necessary):

SSMS2014XE12-XE-target-file-query-results

As can be seen from the output, the data that we most want to see is stored as XML (when working with Extended Events, all of the data is stored as XML) in the event_data column, so I have converted this column to XML. At this point, you can click the XML output, and it will be opened up in text, allowing you to see all of the data as it is stored in the XML output.

SSMS2014XE13-XE-target-file-event_data-results

From this point, it’s just a matter of modifying the query to return the columns that you are interested in.

This query extracts the data from XE file target. It also calculates the end date, and displays both the internal and user-friendly names for the resource_type and mode columns – the internal values are what the trace was returning.

For a quick recap: in Part 1, you learned how to convert an existing trace into an XE session, identifying the columns and stepping through the GUI for creating the XE session. In part 2 you learned how to query both the trace and XE file target outputs, and then compared the two outputs and learned that all of the data in the trace output is available in the XE output.

This post is re-published from my original post on SQL Solutions Group.

NoProfilerIn this brave, new world of Extended Events (XE, XEvents), I find myself with a mixture of scripts for troubleshooting issues – some use XE, and some use traces. We’ve all been told that XE is a much better system (it is much more lightweight, causing less of an issue with the server). In fact, it is so much better that Microsoft has deprecated SQL Trace and SQL Profiler, and in the future, one will not be able to run any traces at all. Believe it or not, this is a good thing!

TSQL TuesdayIt just so happens that today is the 67th installment of the monthly T-SQL Tuesday blogging event. T-SQL Tuesday, that wonderful monthly blogging party started by Adam Machanic where a selected host challenges the SQL Server universe to have a blog post about a specific topic. This wild frenzy of SQL Server blog posting occurs on the second Tuesday of each month. This month, it is being hosted by my friend (and the person with the highest amount of energy known to mankind) Jes Borland (b / t), and the topic that she has chosen is Extended Events. Her specific challenge to the SQL Server universe is:

I want to know (and others do, too) how you’ve solved problems with Extended Events. What sessions have you created? What unique way have you used predicates or targets? What challenges have you overcome?

The biggest challenge that I have is not a technical challenge… it’s a personal challenge: actually getting started with XE. I have so many scripts for doing traces, that I just immediately use them instead of the better XE system. I really need to wean myself off of using Profiler / traces. Therefore, I’ve decided to start converting my trace scripts into XE scripts, and I’ll share with you how I go about doing it. Today, I’m going to look at my favorite trace script – a trace to capture deadlock information.

First off, let’s start with the trace script:

From this script, you can see that the trace is collecting data from three events (Deadlock Graph (148), Lock:Deadlock (25) and Lock:Deadlock Chain (59)) and several columns for each event. The next step is to convert all of this information into XE events / actions. For this, I’ll modify the script in BOL (at https://msdn.microsoft.com/en-us/library/ff878264.aspx) to the following to return the XE events / actions for a specified running trace. Since the trace needs to be running, execute the above trace script to start the trace, then run the following code:

These results may contain NULL values in the “Extended Events action” column. A NULL value here means that there is not a corresponding event action for that column. For my deadlock trace, I get the following results:

SSMS2014XE00-TraceXExref

With this information, I can now jump into SSMS (2014) and use the New Session wizard to create a new XE session (Expand the Management and Extended Events nodes. Right click on Sessions and select New Session). I name the session “Deadlocks”, and click the “Events” page to select the events that I want to use.

SSMS2014XE01-NewXESession

Since all of the events that I’m looking for contain “Deadlock”, I search for this and 5 events are displayed. I double-click each of the three listed above (or select them and click the “>” button) to move them to the “Selected Events” grid.

SSMS2014XE02-SelectEvents

The next step is to configure the columns, so click the Configure button.

SSMS2014XE03-ConfigureEvents

Select the lock_deadlock event, and on the “Global Fields (Actions)” tab select the client_app_name, client_pid, database_name, nt_username, server_principal_name and session_id columns.

SSMS2014XE04-ConfigureEventColumns

Click on the “Event Fields” tab, and check the checkboxes for database_name and resource_description. Fields that show up in this tab with checkboxes have customizable actions, and don’t actually collect the information unless checked. Frequently this will be because these fields require an additional level of resource usage to gather this information.

SSMS2014XE05-ConfigureEventColumns

Select the lock_deadlock_chain event, and select the session_id and database_name columns on the “Global Fields (Actions)” tab. Click on the “Event Fields” tab and check the checkbox for database_name and resource_description. Finally, select the xml_deadlock_report event, and select the server_principal_name and session_id columns on the “Global Fields (Actions)” tab.

Finally, click on the Data Storage page, and add an event_file target type. The file name defaults to the name that you gave the session earlier. I set the maximum size to 250mb and 5 rollover files. If you select to script to a new query window (HIGHLY recommended), you will have a script with all of the DDL necessary to create this XE.

SSMS2014XE06-ConfigureDataStorage

At this point, there are two changes that I like to make. First, I like to make my scripts so that they won’t have an error, and if the XE session already exists then one will be generated. To make it bullet-proof, I add an IF EXISTS check at the top and drop the XE session if it already exists. Secondly, I like to call xp_create_subdir to create the directory that the script points to, just in case the directory doesn’t exist. Note that xp_create_subdir will be successful if the directory already exists, so I don’t check to ensure that the directory doesn’t exist before executing this procedure.

My final XE Deadlock script looks like:

Now that you have this XE session scripted out, it can be easily installed on multiple servers. If you encounter a deadlock problem, you can easily start the XE session and let it run to trap your deadlocks. They will be persisted to a file dedicated for the deadlocks. You can use my Deadlock Shredder script at http://bit.ly/ShredDL to read the deadlocks from the file and shred the deadlock XML into a tabular output.

Note that the default system_health XE session also captures deadlocks. I like to have a dedicated session for just deadlocks. As lightweight as XE is, sometimes it may benefit a server to turn off the system_health session. Additionally, Jonathan Kehayias has a script that will take a running trace and completely script out an XE session for it. This script can be found at https://www.sqlskills.com/blogs/jonathan/converting-sql-trace-to-extended-events-in-sql-server-2012/. Even though this script is available, I like to figure things out for myself so that I can learn what is actually going on.

Did you catch the note above where I mentioned that the default system_health XE captures deadlocks? This means that if you are still enabling trace flags 1204 and/or 1222 to capture deadlock information in your error logs, you don’t need to do that anymore. Furthermore, by using this Deadlock XE, you can have the deadlocks persisted to a file where, in combination with my Deadlock Shredder script, it will be even easier to analyze the deadlocks than trying to figure it out from the captured information in the error logs.

I hope that this post will help someone with the conversion from using SQL Profiler to using XE instead. The process for converting other traces would be the same, so it can be easily adapted. I also hope that these deadlock scripts will be helpful to you!

In my next post, I will be comparing the results of the deadlock trace and the deadlock XE session.

And Jes – thanks for hosting this month! I’m really looking forward to seeing the roundup for this month.

This post is re-published from my original post on SQL Solutions Group.

I was recently reading this msdn article on Ghost Records, and it mentioned that you could get the number of ghost records on a page with DBCC DBTABLE… and it also mentioned that you need to be sure that you enable Trace Flag 3604 in order to see the results. So, two things immediately jumped out at me. First, I wanted to look at this to see where the ghost records were located. Secondly, I’ve just written a few articles (here, here, here and here) where I’ve been able to use the “WITH TABLERESULTS” option on the DBCC command to avoid using this trace flag and to provide automation for the process, and I wanted to see if that would work here also.

The good news is that “WITH TABLERESULTS” does indeed work with DBCC DBTABLE. The bad news is that I could not find the ghost record count in the results.

When I was looking for this information, I noted that the results meta-data are identical to the way DBCC PAGE has its output, so this means that the automation processes already developed will work for them. And as I was looking through the results, looking for a ghost record counter, I noticed two interesting fields:

Field Value
m_FormattedSectorSize 4096
m_ActualSectorSize 512

Hmm, this is showing me the disk Sector Size of each database file. After checking things on a few different systems, it looks like the m_ActualSectorSize is what the sector size is for the disk that the database file is currently on, and the m_FormattedSectorSize appears to be the sector size for when the database was created – and it is copied from the model database, so it appears to be what the disk was like when Microsoft created the model database.

Since it’s a best practice to have the disk sector size (also known as the allocation unit size or block size) set to 64kb (see this white paper), I decided to programmatically get this information. After digging through the Object and ParentObject columns, this script to get the current allocation using size (Sector Size) for each drive was developed:

And here we have yet another way for how a process can be automated by using “WITH TABLERESULTS” on a DBCC command. I think that this one is a particularly good one to show the possibilities – to get this information you have to hit multiple parts of the DBCC results, and repeat it for each file in each database. Doing this by using the 3604 trace flag, finding the appropriate piece and then proceeding on to the piece would be very time consuming to do manually.

Finally, a quick note here: there are better ways of getting the disk sector size – since you can get it with WMI calls, you can get it with PowerShell (or even dos), and there are also command line utilities that will also get you this information. This is just a way to do it from within SQL Server. Note also that this only gets the drives that contain database files on this SQL Server instance – if you are looking for other drives, then this won’t work for you.

Take a look at the other fields that are available in DBTABLE – you just might find another item that you’d like to be able to retrieve.

Previous related posts:

Persisting DBCC Output
Automating DBCC Page
Automating DBCC Page, Part 2
Automating DBCC Page, Part 3

This post is re-published from my original post on SQL Solutions Group.