Restore `-P` for backward compatibility.

[umaritimus]

go-sqlcmd works really well as a drop in replacement for the legacy sql server command line tools in most cases. However, some 3rd party utilities, e.g. PeopleSoft ChangeAssistant, that rely on -P flag no longer work. Would it be possible to restore the clear text password passing using the -P flag so that go-sqlcmd utility maintains the compatibility with its heavyweight sqlcmd cousin?

[shueybubbles]

@umaritimus Any objection to having it work only if an environment variable like SQLCMDALLOWPWD is set? If we relax the security stance of the app we want to add a bit of multi-factor authorization to use the less-secure option.

[shueybubbles]

Reminder that the reason -P is deprecated is it enables leakage of credentials from the user's machine. Command lines are saved to Windows event logs and many organizations harvest those event logs. Thus members of your company's IT organization could get access to secrets they shouldn't.

[PhilBrammer]

Where in any system are commands logged to an event log?

[PhilBrammer]

Out of curiosity -- why is it the position of the application writers to enforce security onto its users in this way? We have use cases where we want to dynamically build a SQLCMD statement with differing credentials. Perhaps to run one instance of SQLCMD as userA and another as userB. A deployment tool would be the execution engine here, such as Octopus Deploy.

[shueybubbles]

Here's a Windows 11 example

<Data Name="NewProcessName">C:\Program Files\WindowsApps\Microsoft.WindowsNotepad_11.2302.16.0_x64__8wekyb3d8bbwe\Notepad\Notepad.exe</Data> 
  <Data Name="TokenElevationType">%%1938</Data> 
  <Data Name="ProcessId">0x2ef4</Data> 
  <Data Name="CommandLine">notepad err.txt</Data> 

[PhilBrammer]

Got it. Not default server OS configs, but still of note. Above scenario still applies, and setting an env var to unlock this is no big deal for our use cases. But I don't know why that's better. If you want "two-factor," let me specify yet another argument directly.

[shueybubbles]

the environment variable allows an organization to manage it at scale without requiring their database folks to change their scripts. IE if you have been using -P with the ODBC sqlcmd and have lots of scripts, you would be able to bring them over to go-sqlcmd usage "as is" assuming you can deploy the environment variable setting out of band.

I will be chatting with our security folks to decide whether to light this up even without such a variable.

[PhilBrammer]

Sounds good. We won't be able to switch to this using SQL logins if we can't set the credentials on the command line.

Further context: We are removing Active Directory from our web farm so that servers are more scalable (cattle not pets) and will require SQL Logins. We will have a set of credentials by worker (web, scheduled jobs, etc...) and will be rotating passwords daily. Depending on the SQL script task, we may be executing it from a central utility server via Octopus Deploy, switching the SQL Login on the command line accordingly. Further, when we rotate passwords, we won't be able to push out an environment variable change to the fleet of servers, certainly not in a timely manner.