October 14, 2008 at 10:26 am
I have a stored procedure that calls both SQL and DOS commands. The SQL server is mixed authentication (ERP legacy system). The SQL user ID executing the stored procedure is 'DBSBATCH' The Operating System (CmdExec) proxy establishing a relationship with the Windows domain user (ProxyUser) with the SQL user ID (DBSBATCH)
The stored procedure does not specify specific permissions (Grant Execute to DBSBATCH).
When I open Studio Manager and login as DBSBATCH to execute the stored procedure I get this message:
Msg 229, Level 14, State 5, Procedure HOPE_alloc_step3, Line 1
The EXECUTE permission was denied on the object 'HOPE_alloc_step3', database 'DBSallc', schema 'dbo'.
If I add Grant Execute to DBSBATCH and rerun the procedure again under DBSBATCH I get this
Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
Rename and Copy complete
Msg 1088, Level 16, State 7, Procedure HOPE_alloc_step3, Line 40
Cannot find the object "HOPE_ALLOC_JRNL" because it does not exist or you do not have permissions.
If I run the stored procedure under my domain account it runs successfully.
Something in security needs a tweaking...
Here is the stored procedure.
ALTER PROCEDURE
[dbo].[HOPE_alloc_step3]
AS
DECLARE
@copy nvarchar(1000),
@bulkcmd nvarchar(1000),
@PJCT nvarchar(1000),
@PJLN nvarchar(1000),
@combine nvarchar(2000)
BEGIN
SET @copy = 'copy e:\Allocations\alloc_gl_load\journal.ojb e:\Allocations\alloc_gl_load\journal.txt'
SET @bulkcmd = 'BULK INSERT DBSallc.dbo.HOPE_ALLOC_JRNL
FROM ''e:\Allocations\alloc_gl_load\journal.txt''
WITH (FIRSTROW = 2, MAXERRORS = 1,
ERRORFILE = ''e:\Allocations\alloc_gl_load\bulkinsert.err'')'
SET @PJCT = 'bcp DBSallc.dbo.HOPE_PJCT_LOAD out e:\Allocations\alloc_gl_load\PJCTLOAD.txt -e e:\Allocations\alloc_gl_load\PJCTLOAD.err -c -T -V 80'
SET @PJLN = 'bcp DBSallc.dbo.HOPE_PJLN_LOAD out e:\Allocations\alloc_gl_load\PJLNLOAD.txt -e e:\Allocations\alloc_gl_load\PJCLNLOAD.err -c -T -V 80'
SET @combine = 'copy e:\Allocations\alloc_gl_load\PJCTLOAD.txt + e:\Allocations\alloc_gl_load\PJLNLOAD.txt e:\Allocations\before_proj_jrnl_load\projload.txt /A'
/* Rename the Ledger Allocation output file from .ojb to .txt extension */
EXECUTE master.dbo.xp_cmdshell @copy
PRINT 'Rename and Copy complete'
/* Truncate working tables */
TRUNCATE TABLE DBSallc.dbo.HOPE_ALLOC_JRNL
TRUNCATE TABLE DBSallc.dbo.HOPE_PJCT_LOAD
TRUNCATE TABLE DBSallc.dbo.HOPE_PJLN_LOAD
PRINT 'Truncate tables complete'
/* Insert Ledger Allocation Output file into Alloc_jrnl table */
EXECUTE (@bulkcmd)
PRINT 'Bulk insert into table complete'
/* INSERT PJCT rows from Alloc_jrnl into PJCT_LOAD table */
INSERT INTO DBSallc.dbo.HOPE_PJCT_LOAD (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6,
FIELD7, FIELD8, FIELD9, FIELD10, FIELD11, FIELD12, FIELD13, FIELD14, FIELD15, FIELD16,
FIELD17, FIELD18, FIELD19, FIELD20)
SELECT 'PJCT' AS FIELD1,
'A' AS FIELD2,
'PHUSA' AS FIELD3,
a.jrnl_id AS FIELD4,
a.jrnl_seq_nbr AS FIELD5,
'B' AS FIELD6,
'N' AS FIELD7,
' ' AS FIELD8,
a.trans_curr_code AS FIELD9,
'Y' AS FIELD10,
'Y' AS FIELD11,
CONVERT (varchar(30), a.eff_date, 101) as FIELD12,
' ' AS FIELD13,
' ' AS FIELD14,
' ' AS FIELD15,
' ' AS FIELD16,
a.descp AS FIELD17,
' ' AS FIELD18,
'Y' AS FIELD19,
' ' AS FIELD20
FROM DBSallc.dbo.HOPE_ALLOC_JRNL AS a
WHERE a.jrnl_line_nbr = 1-- Insert uses jrnl_line_nbr to obtain unique jrnl IDs
PRINT 'PJCT rows insertion complete'
/* Insert PJLN rows from Alloc_jrnl into PJLN_LOAD table */
INSERT INTO DBSallc.dbo.HOPE_PJLN_LOAD (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6,
FIELD7, FIELD8, FIELD9, FIELD10, FIELD11, FIELD12, FIELD13, FIELD14, FIELD15, FIELD16,
FIELD17, FIELD18, FIELD19, FIELD20, FIELD21, FIELD22, FIELD23, FIELD24, FIELD25,
FIELD26, FIELD27, FIELD28, FIELD29, FIELD30, FIELD31)
SELECT
'PJLN' AS FIELD1,
'A' AS FIELD2,
'PHUSA' AS FIELD3,
a.jrnl_id AS FIELD4,
a.jrnl_seq_nbr AS FIELD5,
a.jrnl_line_nbr AS FIELD6,
a.dbs_proj_code AS FIELD7,
a.dbs_funding_source AS FIELD8,
a.prim_dr_cr_code AS FIELD9,
a.descp AS FIELD10,
' ' AS FIELD11,
' ' AS FIELD12,
' ' AS FIELD13,
' ' AS FIELD14,
' ' AS FIELD15,
' ' AS FIELD16,
'N' AS FIELD17,
' ' AS FIELD18,
' ' AS FIELD19,
' ' AS FIELD20,
' ' AS FIELD21,
' ' AS FIELD22,
a.dbs_gl_account + '1' AS FIELD23,
a.amt_class_1 AS FIELD24,
ABS (a.trans_amt) AS FIELD25,
ABS (a.trans_amt) AS FIELD26,
'Y' AS FIELD27,
'Y' AS FIELD28,
ABS (a.trans_amt) AS FIELD29,
'Y' AS FIELD30,
'Y' AS FIELD31
FROM DBSallc.dbo.HOPE_ALLOC_JRNL AS a
PRINT 'PJLN rows insertion complete'
/* Export PJCT_LOAD table to file output PJCTLOAD.txt */
EXECUTE master.dbo.xp_cmdshell @PJCT
PRINT 'PJCT export complete'
/* Export PJLN_LOAD table to file output PJLNLOAD.txt */
EXECUTE master.dbo.xp_cmdshell @PJLN
PRINT 'PJLN export complete'
/* Combine PJCT_LOAD.txt and PJLN_LOAD.txt files */
EXECUTE master.dbo.xp_cmdshell @combine
PRINT 'File combine complete'
PRINT 'Step 3 file conversion successful'
END
SET QUOTED_IDENTIFIER ON
October 14, 2008 at 10:47 am
You forgot to mention which version of SQL server you are using? (Based on credential error message it should be 2005).
xp_cmdshell execution requires windows security rights and can't be executed using SQL server credentials directly.
When you are logging in as yourself (Windows account; and I would assume that you are local admin as well) - you can execute stored procedure.
If you are indeed on SQL 2005, you would have to create a Windows account which have rights on the folder where you are running your copy command.
Then create a SQL login for this account.
Then create credentials for this Windows user.
Then map your SQL user to the abovementioned credentials.
October 14, 2008 at 10:49 am
I doubt if this is something one should doing "in transaction scope" like within a sproc.
I would convert the sproc into a single sqlagent job, containing of multiple steps.
If you need this sproc to be launched form within an application, I would raise a user defined arror and have sqlagent respond on that.
Have a look athttp://qa.sqlservercentral.com/scripts/Miscellaneous/31032/
Or I would use Service Broker solution.
In both ways you have a better overview on number of executes, which step takes most of the time, how it grows, ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 14, 2008 at 11:11 am
ALZDBA (10/14/2008)
I doubt if this is something one should doing "in transaction scope" like within a sproc.I would convert the sproc into a single sqlagent job, containing of multiple steps.
If you need this sproc to be launched form within an application, I would raise a user defined arror and have sqlagent respond on that.
Have a look athttp://qa.sqlservercentral.com/scripts/Miscellaneous/31032/
Or I would use Service Broker solution.
In both ways you have a better overview on number of executes, which step takes most of the time, how it grows, ...
We try to keep it simple where as the allocation process is smooth and in one format (the ERP job scheduler window) I do not want to give the user or have the IT department to run this job outside of the ERP system (there is the control) Thanks for the link on raising a user error...
October 14, 2008 at 11:29 am
jsheldon (10/14/2008)If I add Grant Execute to DBSBATCH and rerun the procedure again under DBSBATCH I get this
Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
Rename and Copy complete
Msg 1088, Level 16, State 7, Procedure HOPE_alloc_step3, Line 40
Cannot find the object "HOPE_ALLOC_JRNL" because it does not exist or you do not have permissions.
If I run the stored procedure under my domain account it runs successfully.
Two questions:
1) Does the '##xp_cmdshell_proxy_account##' credential exist? If so, what is it?
2) What rights does your domain account have in the SQL Server at the Server level?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 11:35 am
This paragraph from BOL pretty much sums up the situation:
xp_cmdshell Proxy Account
When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.
Your domain account probably is in sysadmin, which means that its xp_cmdshell commands will use the SQL Server Windows service account instead of the proxy account.
Note that unlike most other security checks in SQL Server, xp_cmdshell only cares about what your Login to SQL Server was. "EXECUTE AS..", owner-chaining and access permissions, etc. have no effect on it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 11:42 am
rbarryyoung (10/14/2008)
jsheldon (10/14/2008)If I add Grant Execute to DBSBATCH and rerun the procedure again under DBSBATCH I get this
Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
Rename and Copy complete
Msg 1088, Level 16, State 7, Procedure HOPE_alloc_step3, Line 40
Cannot find the object "HOPE_ALLOC_JRNL" because it does not exist or you do not have permissions.
If I run the stored procedure under my domain account it runs successfully.
Two questions:
1) Does the '##xp_cmdshell_proxy_account##' credential exist? If so, what is it?
2) What rights does your domain account have in the SQL Server at the Server level?
1) yes it exists under SQL Server Agent/Proxies/Operating System (CmdExec)
2) the proxy user is part of the Administrators group on the local server and the server has all but full control
October 14, 2008 at 12:00 pm
jsheldon (10/14/2008)
rbarryyoung (10/14/2008)
jsheldon (10/14/2008)If I run the stored procedure under my domain account it runs successfully.
Two questions:
1) Does the '##xp_cmdshell_proxy_account##' credential exist? If so, what is it?
2) What rights does your domain account have in the SQL Server at the Server level?
1) yes it exists under SQL Server Agent/Proxies/Operating System (CmdExec)
2) the proxy user is part of the Administrators group on the local server and the server has all but full control
OK, try again:
1) Does the '##xp_cmdshell_proxy_account##' credential's Windows logon exist? Please check it and its password by trying to logon to it yourself.
2) I am asking about "your domain account" that you used when you sucessfully ran the stored proc: What are it's SQL Server rights at the server level? In other words is it a sysadmin in SQL server?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 12:05 pm
rbarryyoung (10/14/2008)
This paragraph from BOL pretty much sums up the situation:xp_cmdshell Proxy Account
When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.
Your domain account probably is in sysadmin, which means that its xp_cmdshell commands will use the SQL Server Windows service account instead of the proxy account.
Note that unlike most other security checks in SQL Server, xp_cmdshell only cares about what your Login to SQL Server was. "EXECUTE AS..", owner-chaining and access permissions, etc. have no effect on it.
Ok I am racking my brains on this, again to my knowledge nothing has changed we are pretty careful on this server (yet we have this problem :w00t:)
1. DBSBATCH is a login and user to the SQL Server. DBSBATCH has Execute permissions on master.dbo.sys.xp_cmdshell extended stored procedure
2. DBSBATCH user ID in database DBSallc has Execute permission to the stored procedure HOPE_alloc_step3
3. I reran EXEC sp_xp_cmdshell_proxy_account 'MyDomain\MyUserName', 'myDomainPassword' for our SQLProxy account
4. The SQLProxy account is shown in the Agent/Proxies/Operating Cmd and its properties state its resource as DBSBATCH
5. SQLProxy is part of the local Admin group and the local Admin group has all but full rights to the E:\Allocations folder where the DOS commands are running
6. If I run the job my user ID also in the local admin group (yet my user ID is a domain admin account) it has no problems...
7. Our SQL Server is running under our SQL Service account
banging my head against the wall on this...
October 14, 2008 at 12:36 pm
rbarryyoung (10/14/2008)
jsheldon (10/14/2008)
rbarryyoung (10/14/2008)
jsheldon (10/14/2008)If I run the stored procedure under my domain account it runs successfully.
Two questions:
1) Does the '##xp_cmdshell_proxy_account##' credential exist? If so, what is it?
2) What rights does your domain account have in the SQL Server at the Server level?
1) yes it exists under SQL Server Agent/Proxies/Operating System (CmdExec)
2) the proxy user is part of the Administrators group on the local server and the server has all but full control
OK, try again:
1) Does the '##xp_cmdshell_proxy_account##' credential's Windows logon exist? Please check it and its password by trying to logon to it yourself.
2) I am asking about "your domain account" that you used when you sucessfully ran the stored proc: What are it's SQL Server rights at the server level? In other words is it a sysadmin in SQL server?
yes to both of your questions...the SQLProxy account is valid and able to log on to the server, my domain account is a sysadmin rights on the SQL Server
October 14, 2008 at 1:26 pm
jsheldon (10/14/2008)
yes to both of your questions...the SQLProxy account is valid and able to log on to the server, my domain account is a sysadmin rights on the SQL Server
Well, that explains half of the mystery anyway. It works from your domain account because, having sysadmin, your invocation of xp_cmdshell does not use the xp_cmdshell_proxy_account, but rather uses the windows service account, which clearly is OK.
So that leaves the question of what is wrong with the xp_cmdshell_proxy_account. If you can login to it yourself, on the server, then I am a little stumped as to what it could be.
I am not sure, but your SQL Server's service account may need impersonation rights.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 1:40 pm
rbarryyoung (10/14/2008)
jsheldon (10/14/2008)
yes to both of your questions...the SQLProxy account is valid and able to log on to the server, my domain account is a sysadmin rights on the SQL ServerWell, that explains half of the mystery anyway. It works from your domain account because, having sysadmin, your invocation of xp_cmdshell does not use the xp_cmdshell_proxy_account, but rather uses the windows service account, which clearly is OK.
So that leaves the question of what is wrong with the xp_cmdshell_proxy_account. If you can login to it yourself, on the server, then I am a little stumped as to what it could be.
I am not sure, but your SQL Server's service account may need impersonation rights.
I do appreciate the help, bounce this off you if I can
1. Log in as the SQLProxy user to the server, start SQL Server Management studio, try to log in (error Proxy windows account is not a login to the SQL server) switch to sa. Logging in as sa able to run the stored procedure successfully
2. Log in as the SQLService windows account associated with the SQL Services, started Management Studio, executed the stored procedure successfully.
So as you mentioned it appears the sysadmin roles override the proxy rights...If I set DBSBATCH to sysadmin role it works perfectly.
I cannot do that, this user is simply the user ID to run batch jobs....
October 14, 2008 at 1:53 pm
Hmm, there seems to be a lot of confusion here between Windows logons, vs SQL logons. The xp_cmdshell_proxy_account is a Windows logon. So when I say "Log on to xp_cmdshell_proxy_account" I mean in Windows.
In other words, if you can logon on to Windows on the Windows Server (that hosts the SQL Server) using the xp_cmdshell_proxy_account credentials (Domain\LogonName, Password) and navigate to the directory where the xp_CmdShell commands are supposed to be executed, then it should work from within SQL Server also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 2:03 pm
Sorry my examples were not clear I meant to say 'Log into the Server where SQL server resides'
I understand the difference I am just going through our change management to see if any permissions were changed on either server, sql or group policy...
thanks again for your help
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply