By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,652 Members | 1,484 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,652 IT Pros & Developers. It's quick & easy.

Need security advice on xp_cmdshell, bcp, xml procedure

P: n/a
I have a stored procedure that creates an xml file. It executes a SELECT
statement with the FOR XML clause and then writes the xml file using bcp and
xp_cmdshell. I am calling this procedure by passing it a parameter via ADO.
I have configured the SQL Server Agent with a proxy account so non-SysAdmin
can execute xp_cmdshell.

I'm concerned about giving non-SysAdmins execute on xp_cmdshell. I'm also
concerned about having to maintain the password on my proxy account when
that users' password changes.

Is there a better, more secure way to generate this xml file.

Thanks

Nov 10 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Terri (te***@cybernets.com) writes:
I have a stored procedure that creates an xml file. It executes a SELECT
statement with the FOR XML clause and then writes the xml file using bcp
and xp_cmdshell. I am calling this procedure by passing it a parameter
via ADO. I have configured the SQL Server Agent with a proxy account so
non-SysAdmin can execute xp_cmdshell.

I'm concerned about giving non-SysAdmins execute on xp_cmdshell. I'm also
concerned about having to maintain the password on my proxy account when
that users' password changes.

Is there a better, more secure way to generate this xml file.


I'm not really sure what you but it sounds like you do something like:

bcp "SELECT ... FOR XML" queryout outfile.bcp

This is not likely to work very well. ODBC will chop the XML document
after each 2033 character. See KB 275583.

So you would need to get the XML document to the client, and have the
client to create the file and put it where it belongs. Which probably
is better from a security perspective as well.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 10 '05 #2

P: n/a
This is not likely to work very well. ODBC will chop the XML document
after each 2033 character. See KB 275583.

So you would need to get the XML document to the client, and have the
client to create the file and put it where it belongs. Which probably
is better from a security perspective as well.


Thanks Erland,

I'm not using Query Analyzer so I don't think KB 275583 applies to me.

I'm calling the following procedure via ADO

CREATE PROCEDURE procGenerateXML
@CheckRequestID int
AS
declare @sql nvarchar(4000)
set @sql= 'bcp "EXEC TestDB..proctest ' + CONVERT(varchar(8),@ID) + '"' + '
queryout test.xml -SServer1 -T -c -r -t'
exec master..xp_cmdshell @sql
GO

The procedure proctest looks like:

CREATE PROCEDURE proctest
@ID int
AS
SELECT...
FROM...
WHERE...
FOR XML AUTO, ELEMENTS
GO

I then call the procedure like this
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim Param1
Dim ID As Integer
Dim provstr As String
Dim myfrm As Form
Dim dbs As Database
Set dbs = CurrentDb()
ID = Me.ID

cn.Provider = "sqloledb"
provstr = "Server=Server1;Database=TestDB;Trusted_Connection =Yes"
cn.Open provstr

Set cmd.ActiveConnection = cn
cmd.CommandText = "dbo.procGenerateXML"
cmd.CommandType = adCmdStoredProc
Set Param1 = cmd.CreateParameter("Input", adInteger, adParamInput)
cmd.Parameters.Append Param1
Param1.Value = ID
Set rs = cmd.Execute

I'm looking for guidance on the following:

-Can I use this xp_cmdshell method without giving my end users execute
permissions on xp_cmdshell and if not;
-Are there alternatives that don't use xp_cmdshell

Thanks


Nov 11 '05 #3

P: n/a
Terri (te***@cybernets.com) writes:
This is not likely to work very well. ODBC will chop the XML document
after each 2033 character. See KB 275583.

So you would need to get the XML document to the client, and have the
client to create the file and put it where it belongs. Which probably
is better from a security perspective as well.


Thanks Erland,

I'm not using Query Analyzer so I don't think KB 275583 applies to me.


But you are using BCP which is implemented in ODBC. So I would definitely
encourage you to test to generate a large XML document, before you
ponder the issues with access to xp_cmdshell.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 23 '05 #4

P: n/a
If you are already calling this code from ADO, then instead of BCP and
all that you can just directly execute the FOR XML statement, loop
through each 2033 char return results and create the XML file from the
web server.

Erland Sommarskog wrote:
Terri (te***@cybernets.com) writes:
This is not likely to work very well. ODBC will chop the XML document
after each 2033 character. See KB 275583.

So you would need to get the XML document to the client, and have the
client to create the file and put it where it belongs. Which probably
is better from a security perspective as well.


Thanks Erland,

I'm not using Query Analyzer so I don't think KB 275583 applies to me.


But you are using BCP which is implemented in ODBC. So I would definitely
encourage you to test to generate a large XML document, before you
ponder the issues with access to xp_cmdshell.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx


Nov 23 '05 #5

P: n/a
pb648174 (go****@webpaul.net) writes:
If you are already calling this code from ADO, then instead of BCP and
all that you can just directly execute the FOR XML statement, loop
through each 2033 char return results and create the XML file from the
web server.


Actually, if he would do it the simple-minded way, he would not get
2033-characters slices, as he is using SQLOLEDB(*) - he would get a binary
thingie instead.

I have not investigated it, but I believe the proper way to receive FOR
XML in ADO with SQLOLEDB is to use the Stream object.

But apart from that fine detail, I agree with you. Doing this from SQL
Server will be diffictul.

(*) If you use the MSDASQL provider, that is ODBC, then you would have
to as you say. But I would not recommend that.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 23 '05 #6

P: n/a
Thanks to those who replied, I'm going to investigate the ADO stream object.

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
pb648174 (go****@webpaul.net) writes:
If you are already calling this code from ADO, then instead of BCP and
all that you can just directly execute the FOR XML statement, loop
through each 2033 char return results and create the XML file from the
web server.


Actually, if he would do it the simple-minded way, he would not get
2033-characters slices, as he is using SQLOLEDB(*) - he would get a binary
thingie instead.

I have not investigated it, but I believe the proper way to receive FOR
XML in ADO with SQLOLEDB is to use the Stream object.

But apart from that fine detail, I agree with you. Doing this from SQL
Server will be diffictul.

(*) If you use the MSDASQL provider, that is ODBC, then you would have
to as you say. But I would not recommend that.

Nov 23 '05 #7

P: n/a
I did something like this in C# .NET recently via the native SQL OleDB
provider and had to do the looping action. I think that even when I
submitted the Query using Query Analyzer, I could see it coming back as
multiple rows... I could be wrong though.

Nov 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.