T-SQL Tuesday #86: SQL Server Bugs & Enhancement Requests

This month’s T-SQL Tuesday is hosted by Brent Ozar and he’s asked everyone to find interesting bug or enhancement requests in Microsoft Connect related to SQL Server.

The Connect item doesn’t have to have anything to do with T-SQL – it could be about the engine, SSRS, R, the installer, whatever. Now, more than ever, Microsoft has started to respond to Connect requests and get ’em fixed not just in upcoming versions of SQL Server, but even in cumulative updates for existing versions.

Confession time: At the moment, I’m not a DBA (I’m a dev who occasionally does some DBAish stuff), and I don’t spend a lot of time on Microsoft Connect.

However, I do spend a bunch of time in PowerShell, fetching data to process/analyze/mangle for people on an ad-hoc basis, running scheduled tasks, or doing  research trying to find things in an application’s code (stored in the database). And I frequently find myself using Invoke-SqlCmd.

I found two open items which, although addressed by Invoke-SqlCmd2, would be of benefit to the community at large if resolved in the SqlServer module.

1. PowerShell invoke-sqlcmd should support parameterized queries - It’s 2017 and we’re still reading about SQL injection attacks caused by developers improperly escaping text input. Parameterized queries have been around for a very long time and go a long way toward mitigating this vulnerability. Unfortunately, with Invoke-SqlCmd’s current state it can only accept a string as the query to be executed (or an input file), and that query is frequently created by PowerShell users by either concatenating strings or doing text replacement. Even in cases where I’ve created all of the content being concatenated or replaced, I still don’t fully trust my input.
2. Invoke-Sqlcmd does not seem to meaningfully support the -Verbose or -Debug switches - Most PowerShell cmdlets allow you get additional information about the cmdlet’s execution sent to the Verbose and Debug output streams, but Invoke-SqlCmd does nothing. Seeing the connection string and other details about the query’s execution would be helpful in some cases. I’d like to see this request expanded to add the -WhatIf switch as well so that when called from scripts that properly support SupportsShouldProcess, the query isn’t actually executed if that switch is specified.

I had planned on logging a Connect item very similar to Mike Fal’s (blog | twitter) request for passing a connection string to Invoke-SqlCmd, but logging a duplicate would be pointless  that’s already been implemented (I warned you that I don’t spend a lot of time on Connect, I didn’t even remember it was out there)! I still wouldn’t mind being able to pass in just an Application Name to append to the connection string, but that may lead down a path of making too many options to fiddle with in the cmdlet. It’s a tough balancing act when you have an audience as large as the SQL Server team’s.