Timeline for Grant user from my database to execute xp_cmdshell
Current License: CC BY-SA 3.0
24 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 21, 2018 at 10:51 | vote | accept | VansFannel | ||
| Mar 20, 2018 at 20:33 | comment | added | Solomon Rutzky |
@VansFannel This really should be a separate question at this point. What you are not getting a value back or the file created is completely different than how to securely use xp_cmdshell. You should open a new question for help with debugging your app code. I'm sure it's something simple that is being overlooked. For now, why not set the .NET variable that you put the JSON output parameter into to a specific value rather than the output parameter value, just to prove that you can create a file from a string variable containing "a". Try to narrow down what you are debugging.
|
|
| Mar 20, 2018 at 19:21 | comment | added | VansFannel | @SolomonRutzky I have updated the question again with more details. | |
| Mar 20, 2018 at 19:21 | history | edited | VansFannel | CC BY-SA 3.0 |
added 403 characters in body
|
| Mar 20, 2018 at 19:08 | comment | added | Solomon Rutzky | @VansFannel Are you 100% certain that the file doesn't get created? Are you using a relative or absolute path for the filename? If relative, are you sure you checked the correct directory? Try using an absolute path. If you used an absolute path and the file isn't there, then that line of code never ran or the exception is getting swallowed somehow. | |
| Mar 20, 2018 at 18:54 | comment | added | Solomon Rutzky | @VansFannel Thanks. You might want to be clearer about what is not working. How are you getting the value from the stored procedure? result set column or output parameter? can you confirm that you get anything back at all, prior to attempting to write out to a file? You might need to post code (again, in a different question) because it sounds like you have it set up to swallow the exception(s). But if you try to create a file, then it either exists, or you have an exception somewhere, or that line never ran. | |
| Mar 20, 2018 at 18:52 | comment | added | VansFannel |
@AaronBertrand I just asking to evaluate another possibilities. Maybe I will try to write it down with C# but the problem is that I don't know what is the problem: I don't get the file when I use File.WriteAllText neither an exception.
|
|
| Mar 20, 2018 at 18:50 | comment | added | VansFannel | @SolomonRutzky I have updated the question. | |
| Mar 20, 2018 at 18:50 | history | edited | VansFannel | CC BY-SA 3.0 |
added 262 characters in body
|
| Mar 20, 2018 at 16:25 | comment | added | Aaron Bertrand | Why not just continue writing it to a file in C#? | |
| Mar 20, 2018 at 15:52 | answer | added | Solomon Rutzky | timeline score: 2 | |
| Mar 20, 2018 at 15:41 | comment | added | Solomon Rutzky |
@VansFannel Well, as far as I can tell via testing, the max length of a command to pass to xp_cmdshell is 8000 characters (i.e. VARCHAR(8000)). It does not seem to take VARCHAR(MAX). However, you can do this rather easily via SQLCLR. Please update the question with the requirement for writing out large JSON documents.
|
|
| Mar 20, 2018 at 15:32 | comment | added | VansFannel |
@SolomonRutzky echo > filename.txt is only an example. I have a stored procedure that outputs a json and, sometimes, that json is very large. In C# code I will write it down to a file, so I've thought to write it to a file directly into the stored procedure.
|
|
| Mar 20, 2018 at 14:12 | comment | added | Solomon Rutzky |
Vans: what exactly do you need to write out to this text file? Using echo > filename.txt is somewhat limited in terms of length and probably characters, etc. Is this simple US English alphanumeric characters or do you have various code pages or Unicode text to save? The default DOS code page for US English is 437, which is not an exact match to 1252, which is what the Latin1_General Collations use.
|
|
| Mar 20, 2018 at 13:41 | comment | added | Aaron Bertrand | Why does this have to be a stored procedure? Why can't you generate your output from Powershell, which can easily write to the file system without any of this hokey xp_cmdshell risk. In fact, you could create the procedure that produces the output, and have Powershell simply call the procedure and write to a file. Use the right tool for the job. | |
| Mar 20, 2018 at 12:36 | answer | added | Dan Guzman | timeline score: 5 | |
| Mar 20, 2018 at 12:10 | comment | added | Danielle Paquette-Harvey | +1 for Dan Guzman. It's exactly what I would do. Doing it in a proc and giving the right to the user to execute the stored proc is far more secure! | |
| Mar 20, 2018 at 11:48 | comment | added | Dan Guzman | I suggest you wrap the code that executes xp_cmdshell in a stored proc in your user database and sign it with a certificate that has the needed permissions. That way, users are limited to the proc and can't execute ad-hoc xp_cmdshell commands. See this for an example, except in this case do not add the certificate login to the sysadmin role. | |
| Mar 20, 2018 at 11:24 | comment | added | VansFannel | @DanGuzman I have added more details about what I'm trying to do. | |
| Mar 20, 2018 at 11:24 | history | edited | VansFannel | CC BY-SA 3.0 |
added 561 characters in body
|
| Mar 20, 2018 at 11:16 | comment | added | Dan Guzman | Simply adding a user to master isn't unsafe since the user will have only the public permissions they already had via the guest user. However, granting execute permissions on xp_cmdshell is a security concern because the non-sysadmin user can then execute any ad-hoc OS command on the database server, limited only by the xp_cmdshell proxy account permissions. I suggest you specify why you need to do this in your question as there may be a more secure solution. | |
| S Mar 20, 2018 at 11:09 | history | edited | VansFannel | CC BY-SA 3.0 |
deleted 9 characters in body
|
| Mar 20, 2018 at 11:07 | review | Suggested edits | |||
| S Mar 20, 2018 at 11:09 | |||||
| Mar 20, 2018 at 10:53 | history | asked | VansFannel | CC BY-SA 3.0 |