April 4, 2016 at 5:01 am
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.
April 4, 2016 at 5:24 am
I'd first ask yourself why you want to replace it with something else.
If this is to make it more secure, then I'd suggest looking at who can run it. It normally takes sysadmin privs to execute xp_cmdshell. If you have an application connecting and using xp_cmdshell using the sa login, then using xp_cmdshell isn't your problem...having the sa login enabled is the problem. If xp_cmdshell is disabled and someone breaks in using the sa login, they'll just enable it and do whatever they want anyway. Better yet, the attacker doesn't even have to do it themselves because the automated tools do it for them. I'I've seen demonstrations of this and it's really pretty easy to break in using the sa login. It's faster and easier than you think because the sa login is so well-known. The first step in making your server more secure is to get the applications using a different login (without sysadmin privs) and disable the sa login and leave it disabled.
On a related note, if your applications are connecting with other logins that have the sysadmin server role, that's also a security hole. If they wish to, they can simply enable xp_cmdshell and use it whenever they want. It takes sysadmin privs to use it and the same privs to enable it. Just as importantly, logins with sysadmin privs can access any data in any database any time they want. The only advantage they have over the sa login is that they aren't well-known logins that have the sysadmin server role.
Next, you can configure xp_cmdshell to be more secure. See the post at http://www.sqlservercentral.com/Forums/Topic1160922-1550-7.aspx#bm1707671 where there's a SQL Saturday presentation on how to set up a proxy account.
Please understand that you don't want to give other logins permission to use it directly, but rather to create stored procedures that use it and then give logins permission to run the stored procedures. You'll end up with the logins able to do what they need to do and yet not being able to use xp_cmdshell to do whatever they want to do. You'll also have your sysadmin login disabled permanently, which closes a large hole that lets attackers into your system.
I know this wasn't your question, but I hope seeing the bigger picture helps.
April 4, 2016 at 5:45 am
Application connects to server using domain users. They have no admin privileges.
sa account is already disabled. It is application that works for few years already and now I'm maintaining it.
Using xp_cmdshell is enabled on server for now. When callings of xp_cmdshell will be removed we will be able to disable ability of using xp_cmdshell.
We have to rid of xp_cmdshell due to audit report we received form auditing software.
April 4, 2016 at 5:56 am
i leave xp_cmdshell disabled by default , and have processes enable it, perform bcp via xp_cmdshell, and then disable again.
you could perform bcp from a command line SQL job, and never use xp_cmdshell.
if needed, you could even dynamically create the job and the command, use it, and drop the job again, or dynamically modify the job step and run it.
Lowell
April 4, 2016 at 6:39 am
Dynamic creation of such step is not the answer for me.
The step with BCP is part of sequential flow. So if I use job I would have to monitor is it finished or not.
April 4, 2016 at 7:45 am
If it were me, I'd prefer Lowell's first approach as opposed to a database job. The procedure, having permissions to use xp_cmdshell, must be running with sysadmin privs, so it has permissions to enable and disable it. Have the procedure enable xp_cmdshell, do the bcp and then disable it again.
April 4, 2016 at 7:58 am
Well generally it appears that my first approach with CLR is the best solution.
May 6, 2016 at 12:38 am
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.
May 6, 2016 at 9:08 pm
Mcin (4/4/2016)
Application connects to server using domain users. They have no admin privileges.sa account is already disabled. It is application that works for few years already and now I'm maintaining it.
Using xp_cmdshell is enabled on server for now. When callings of xp_cmdshell will be removed we will be able to disable ability of using xp_cmdshell.
We have to rid of xp_cmdshell due to audit report we received form auditing software.
I'm with Ed. The auditing software is incorrect. Properly used, xp_CmdShell isn't a security problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2016 at 8:40 am
Mcin (4/4/2016)
Application connects to server using domain users. They have no admin privileges.sa account is already disabled. It is application that works for few years already and now I'm maintaining it.
Using xp_cmdshell is enabled on server for now. When callings of xp_cmdshell will be removed we will be able to disable ability of using xp_cmdshell.
We have to rid of xp_cmdshell due to audit report we received form auditing software.
Heh... piling on and in full support of what Ed stated... more auditing software written by people who don't actually know what goes on with xp_CmdShell or people that play on such silly fears so that they can make money.
I like your CLR but unless someone were foolish enough to give non-sysadmin users privs to run xp_CmdShell directly (which I NEVER do), xp_CmdShell can be used very safely because only sysadmins can use it.
All that, notwithstanding, there actually is a risk to doing what you're doing. No one ever makes mistakes when reworking code, right?
There's also another risk. People get the nice warm fuzzies about their server security when they have xp_CmdShell disabled. Always remember that attack software or a pissed off DBA can turn it on in just a millisecond or two. That can certainly give them elevated privs as the server login whether you have xp_CmdShell in your code or not and whether you have it turned on or not. With that thought in mind, you have to do 4 things no matter the case...
1. Grant sysadmin privs only to those trusted individuals that actually need it to do their jobs. Remember that "DBA" stands for "DATABASE Administrator"... not "SYSTEM Administrator". It is possible for many DBAs to be able to work their magic without having sysadmin privs.
2. If you don't actually trust your DBAs, then you've hired the wrong people. Hire people that have a track record of throwing themselves on the proverbial grenade rather than allow any unauthorized release of data.
3. In any case, ensure that all "service" logins for SQL Server, SQL Agent, etc, NEVER have more privs that what you'd want a DBA to see.
4. Rather than auditing useful tools out of existence, ensure that you do full and very aggressive penetration testing because SQL Injection through an application is still the #1 attack vector followed very closely by uninformed people trusting 3rd party vendors or sharing privileged passwords. That means you need to train people for "Red Flag" incidents.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2016 at 11:14 am
Mcin (4/4/2016)
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:
...{code removed}...
While I don't have any issues with using SQLCLR to overcome various aspects of xp_cmdshell, there are some misunderstandings here with regards to how SQLCLR is working, and how to best use SQLCLR for something like this.
Yes and No. When using a SqlConnection to make an internal connection (i.e. attach to the calling session via a connection string of "Context Connection = true;") then yes, whatever T-SQL is executed is done in the current security context of the session.
HOWEVER, all external access -- whether to a database, file system, network, etc -- is, by default, made in the security context of the Winows Login (or "Local System Account", etc) associated with the SQL Server process. The only way to get external operations to be done in the security context of the calling session's security context is to use Impersonation. Your code is not using Impersonation, hence bcp is being executed in the context of the SQL Server service account. You should be able to test this with your code by running the following:
declare @ReturnCode int;
declare @cmd nvarchar(1000);
SELECT @CMD = '"SELECT DB_NAME(), ORIGINAL_LOGIN(), SESSION_USER, SUSER_SNAME();" queryout "c:\temp\_SecurityInfo.txt" -c -t -T -S"(local)"'
exec DataBase.dbo.up_RunBCP @arguments = @cmd;
Using this approach, the Assembly only needs to be EXTERNAL_ACCESS, not UNSAFE, and the query is running in the context of the session's current security context. The file being created, since it is external, is done in the context of the SQL Server service account, but that can be overridden by using Impersonation (2 or 3 extra lines of code).
For more information on working with SQLCLR, including examples that show both using Impersonation and setting up the Asymmetric Key and Asymmetric Key-based Login, please see the following Stairway series that I am writing, here on SQL Server Central: Stairway to SQLCLR[/url].
Also, I have created a SQLCLR stored procedure that does just this: it exports the results of a query to a text file. It incorporates many of the options of bcp as well as a few from SSIS. It is called DB_BulkExport and is available in the SQL#[/url] SQLCLR library, but only in the Full version, not in the Free version.
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
July 15, 2016 at 12:18 pm
Jeff Moden (5/6/2016)
I'm with Ed. The auditing software is incorrect. Properly used, xp_CmdShell isn't a security problem.
I am not familiar with proxy accounts and even less on what are conditions to ensure that xp_CmdShell can be used in a safe manner. Is there an article that manages to explain it in simple terms anywhere ?
July 15, 2016 at 9:28 pm
j-1064772 (7/15/2016)
Jeff Moden (5/6/2016)
I'm with Ed. The auditing software is incorrect. Properly used, xp_CmdShell isn't a security problem.I am not familiar with proxy accounts and even less on what are conditions to ensure that xp_CmdShell can be used in a safe manner. Is there an article that manages to explain it in simple terms anywhere ?
Not yet but there's a really good presentation on it that talks about the security and provides working code for the method along with demonstrations. See the link below. It does need an update to include what you can do with policy (which someone with SysAdmin privs can still break) but I know the guy who wrote it. 😉
http://www.sqlservercentral.com/Forums/Attachment17582.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2016 at 7:31 am
Jeff Moden (7/15/2016)
Thank you !
July 18, 2016 at 8:11 am
You bet. If you get the chance, let us know how it worked out for you one way or another. Thanks!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply