In August last year I posted about a command line parser problem I ran into with AzCopy, which I eventually resolved by writing a batch file and escaping a number of characters in the access token.
A week ago, my friend and colleague Steve Stedman (blog | Twitter) came to me with a similar problem using SQLCMD.
For an automation task on a series of SQL Server Express Edition instances, Steve had come up with the following template to run DBCC CHECKDB against each database by looping through each instance and inserting the instance, password and database into an xp_cmdshell operation.
Using accepted good practice, the password and script were escaped with double quotes. (note that instance, password and database are the replacement values in question):
sqlcmd -S instance -U maintenanceUser -P "password" -Q "dbcc checkdb ('database') with DATA_PURITY, NO_INFOMSGS;"
Unfortunately, one of the passwords started with a double quotation mark which led to the command failing for one specific Express Edition instance.
Depending on what we tried, the error message returned was a login failure:
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'maintenanceUser'..
Or when trying to escape the control characters, the command wasn’t even parsing correctly:
Sqlcmd: 'password': Unexpected argument. Enter '-?' for help.
After trying various combinations of escape characters I gave up and suggested that the customer change the password in question to remove the double quotation marks. I’ve seen this several times before, and the various search engine results seem to confirm that passwords starting with quotation marks don’t play well with sqlcmd.
Steve went on with his day, and I cracked open Wireshark, “the world’s foremost and widely-used network protocol analyzer.” After a couple of hours, also playing with Microsoft Message Analyzer and the older Microsoft Network Monitor, it was clear that the problem wasn’t with the password, which could be escaped by a single leading quotation mark. This worked just fine:
sqlcmd -S instance -U maintenanceUser -P ""weirdPassword
The issue we can see here is that there’s no closing quotation mark. This looked like a bug with the command line parser in SQLCMD itself.
While I can’t exactly prove that, it meant that the query which was wrapped in quotation marks, was causing the problem. My workaround was to move the -U and -P parameters after the -Q parameter so that the password is at the end of the command, like so (note the missing quotation mark at the end):
sqlcmd -S MSSQLSERVER$EXPRESS -Q "dbcc checkdb ('master') with DATA_PURITY, NO_INFOMSGS;" -U maintenanceUser -P ""weirdPassword
The command completed without incident. Hopefully this will help someone in the future.
I need to thank Daniel Farina for helping me isolate this problem and come up with the workaround, using information in the article Issues with SQLCMD when using special characters, but specifically Paul’s comment about doubling up the quotation marks.
Photo by Philipp Katzenberger on Unsplash.