C# - SQLCMD didn't recognised standard input

if you didn't pass any password for SQLCMD it will try to read it from stdin (I can do it manually and it works).

I started cmd process to which i pass the streamWriter with the password as standard input.

Process starts correctly and I can print passed stream, but i got

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'sa'

Is there any way to make sqlcmd read stdin?

C# code

ProcessStartInfo processStartInfo = new ProcessStartInfo
            {
                FileName = @"path_to_my_script",
                UseShellExecute = false,
                CreateNoWindow = false,
                RedirectStandardInput = true
            };

            var process = Process.Start( processStartInfo );

            StreamWriter myStreamWriter = process.StandardInput;

            string pass = "123";

            myStreamWriter.WriteLine( pass );

            myStreamWriter.Close();

myScript (modified query)

SqlCmd -S DESKTOP-UR7LHEE -U sa -Q "SELECT * FROM myDb"

Solution

The following shows how to use System.Diagnostics.Process to run a SQL script using sqlcmd. However, you may want to see if Server Management Objects (SMO) meets your needs.

The following has been tested:

Add the following using statements:

using Microsoft.Win32;
using System.IO;
using System.Diagnostics;

First, we'll create a method to query the registry to find the path to sqlcmd.exe:

Note: If the directory/folder that sqlcmd.exe exists in is in your PATH environment variable, then the method below is unnecessary - one can use "sqlcmd.exe" instead of the fully-qualified filename.

GetSqlCmdPath:

private string GetSqlCmdPath()
{
    //get the fully-qualified path for sqlcmd.exe

    string sqlCmdPath = string.Empty;

    using (RegistryKey key = RegistryKey.OpenBaseKey(Microsoft.Win32.RegistryHive.LocalMachine, RegistryView.Registry64))
    {
        using (RegistryKey subkey = key.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server"))
        {
            if (subkey != null)
            {
                string[] subkeyNames = subkey.GetSubKeyNames();

                if (subkeyNames != null)
                {
                    foreach (string name in subkeyNames)
                    {
                        string clientSetupSubkey = Path.Combine(name, "Tools", "ClientSetup");

                        using (RegistryKey subkeyClientSetup = subkey.OpenSubKey(clientSetupSubkey))
                        {
                            if (subkeyClientSetup != null)
                            {
                                string[] valueNames = subkeyClientSetup.GetValueNames();

                                if (valueNames != null)
                                {
                                    foreach (string vName in valueNames)
                                    {
                                        if (vName == "Path" || vName == "ODBCToolsPath")
                                        {
                                            //get value
                                            string valPath = subkeyClientSetup.GetValue(vName)?.ToString();

                                            //check if sqlcmd.exe exists
                                            if (File.Exists(Path.Combine(valPath, "sqlcmd.exe")))
                                            {
                                                sqlCmdPath = Path.Combine(valPath, "sqlcmd.exe");
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }  
    }

    return sqlCmdPath;    
}

Next, create a method that uses an instance of Process to execute the script:

RunProcessSqlCmd:

private void RunProcessSqlCmd(string scriptFilename, string logFilename, string password)
{
    string sqlCmdPath = GetSqlCmdPath();

    if (String.IsNullOrEmpty(sqlCmdPath))
        throw new Exception("Error: Fully-qualified path to 'sqlcmd.exe' could not be determined.");

    ProcessStartInfo startInfo = new ProcessStartInfo ()
    {
        Arguments = String.Format(@"-S .\SQLExpress -U appAdmin -i {0} -o {1}", scriptFilename, logFilename),
        //Arguments = String.Format(@"-S .\SQLExpress -U appAdmin -i {0}", scriptFilename),
        CreateNoWindow = true,
        FileName = sqlCmdPath,
        RedirectStandardError = true,
        RedirectStandardInput = true,
        RedirectStandardOutput = true,
        UseShellExecute = false,
        WindowStyle = ProcessWindowStyle.Hidden
    };

    using (Process p = new Process () { StartInfo = startInfo, EnableRaisingEvents = true})
    {
        //subscribe to event and add event handler code
        p.ErrorDataReceived += (sender, e) =>
        {
            if (!String.IsNullOrEmpty(e.Data))
            {
                //ToDo: add desired code 
                Debug.WriteLine("Error: " + e.Data);
            }
        };

        //subscribe to event and add event handler code
        p.OutputDataReceived += (sender, e) =>
        {
            if (!String.IsNullOrEmpty(e.Data))
            {
                //ToDo: add desired code
                Debug.WriteLine("Output: " + e.Data);
            }
        };

        //start
        p.Start();

        p.BeginErrorReadLine(); //begin async reading for standard error
        p.BeginOutputReadLine(); //begin async reading for standard output

        using (StreamWriter sw = p.StandardInput)
        {
            //provide values for each input prompt
            //ToDo: add values for each input prompt - changing the for loop as necessary
            //Note: Since we only have 1 prompt, using a loop is unnecessary - a single 'WriteLine' statement would suffice
            for (int i = 0; i < 1; i++)
            {
                if (i == 0)
                    sw.WriteLine(password); //1st prompt
                else
                    break; //exit
            }
        }

        //waits until the process is finished before continuing
        p.WaitForExit();
    }
}

Note: The code above writes the output of the script to a log file. If you'd rather write the output to StandardOutput, change the following:

From:

Arguments = String.Format(@"-S .\SQLExpress -U appAdmin -i {0} -o {1}", scriptFilename, logFilename),

To:

Arguments = String.Format(@"-S .\SQLExpress -U appAdmin -i {0}", scriptFilename),

Also, since the logFilename parameter will no longer be used, you can remove it.

Usage:

//create test script
string logFilename = Path.Combine(Path.GetTempPath(), System.Reflection.Assembly.GetExecutingAssembly().GetName().Name + "_ScriptLog.txt");
string scriptFilename = Path.Combine(Path.GetTempPath(), System.Reflection.Assembly.GetExecutingAssembly().GetName().Name + "_Script.sql");
Debug.WriteLine($"scriptFilename: {scriptFilename} logFilename: {logFilename}");

//string scriptText = "use master;" + System.Environment.NewLine;
//scriptText += "SELECT name, database_id from sys.databases;";
string scriptText = "SELECT name, database_id from sys.databases;";
File.WriteAllText(scriptFilename, scriptText);

RunProcessSqlCmd(scriptFilename, logFilename, "myPassword123");

Resources:

Server Management Objects (SMO) Resources: