What is the best to remove xp_cmdshell calls from t-sql code

Question

I'm maintaining large t-sql based application. It has a lot of usages of bcp called through xp_cmdshell.

It is problematic, because xp_cmdshell has the same security context as SQL Server service account and it's more than necessary to the work.

My first idea to get rid of this disadvantage is to use CLR code. CLR is running with permissions of user that called the code. I created following procedure and it works fine. I can see that it's using permissions of account that is running this code:

public static void RunBCP(SqlString arguments, out SqlString output_msg, out SqlString error_msg, out SqlInt32 return_val) {
        output_msg = string.Empty;
        error_msg = string.Empty;
        try {
            var proc = new Process {
                StartInfo = new ProcessStartInfo {
                    FileName = "bcp",
                    Arguments = arguments.ToString(),
                    UseShellExecute = false,
                    RedirectStandardOutput = true,
                    CreateNoWindow = true
                }
            };
            proc.Start();
            while (!proc.StandardOutput.EndOfStream) {
                output_msg += proc.StandardOutput.ReadLine();
            }
            return_val = proc.ExitCode;
        }
        catch (Exception e) {
            error_msg = e.Message;
            return_val = 1;
        }
    }

This is good solution because I'm not messing up in BCP calls(arguments are the same). There are no major changes in logic so there is no risk of an error.

Therefore previous call of BCP in T-SQL was looking this way:

declare @ReturnCode int;
declare @cmd varchar(1000);
SELECT @CMD = 'bcp "select FirstName, LastName, DateOfBirth"  queryout "c:\temp\OutputFile.csv" -c -t -T -S"(local)"'
EXEC @ReturnCode=xp_cmdshell @CMD,no_output

Now I call it this way:

declare @ReturnCode int;
declare @cmd varchar(1000);
SELECT @CMD = '"select FirstName, LastName, DateOfBirth"  queryout "c:\temp\OutputFile.csv" -c -t -T -S"(local)"'
exec DataBase.dbo.up_RunBCP @arguments = @cmd;

So, the question is: is there any other way to get rid of xp_cmdshell bcp code? I heard that I can use PowerShell(sqlps). But examples I found suggest to create a powershell script. Can I call such script from t-sql code? How this code(powershell script) should be stored? As a database object? Or maybe there is some other way? Not necessary SSIS. Most what I'd like to know is about powershell.

Thanks for any advices.

Answer 1

Your options for data EXPORT are the following:

• using xp_cmdshell to call bcp.exe - your old way of bulk copying
• using CLR - your new way of bulk copying
• SSIS - my preferred way of doing this; here is the example
• INSERT INTO OPENROWSET - the interesting alternative you can use if you are either working on 32-bit environment with text/Jet/whatever drivers installed, or you can install 64-bit drivers (e.g. 64-bit ODBC text driver, see Microsoft Access Database Engine 2010 Redistributable)
• SQL Server Import/Export wizard - ugly manual way that seldom works in the way you want it to work
• using external CSV table - not supported yet (SQL Server 2016 promises it will be...)

HTH

Answer 2

I would use simple Powershell script that does this, something like:

Invoke-SqlCommand -query '...' | ExportTo-Csv ...

Generally, for administrative functions you could add this to Task Scheduler and be done with it. If you need to execute this task as needed, you can do it via xp_cmdshell using schtasks.exe run Task_NAME which might be better for you since it might be easier to express yourself in Powershell then in T-SQL in given context.

Other mentioned thing all require extra tools (SSIS requires VS for example), this is portable with no dependencies.

To call script without xp_cmdshell you should create a job with powershell step and run it from within t-sql.