March 11, 2013 at 2:07 pm
Any suggestions are welcome.
I am calling DTEXEC like this in the context of database A.
'"E:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe Mypackage"'
The package does execute.
The source query within the package calls a procedure in database A. The connection manager within the package is within the context of database A.
The package executes EXEC MySchema.MyProcA
Within procedure MySchema.MyProcA a separate procedure in called. This procedure is in database B.
So within procedure MySchema.MyProcA I do my cross database execution call.
EXEC databaseB.schema.MyProcB
The error message
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "schema.MyProcB: NT user not authorized".
I assume the account I need to permissions is the SQL Server service account. Is this correct?
March 12, 2013 at 7:13 am
I assume you're using xp_cmdshell. If so, then is the account running xp_cmdshell in the sysadmin Role? If yes, then yes, the SQL Server service account is making the connection to the database. See how convoluted that is though? Why use xp_cmdshell? Can you move this process out of the database?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 12, 2013 at 11:26 am
Thank you for responding to my narrative.
Yes I am using xp_cmdshell. I get the same error when I add the SQL Server service account to the sysadmin, that was one of my troubleshooting steps.
I also tried granting execute permissions to the public role.
>>an you move this process out of the database?
I guess I could schedule a job to call the package directly. I have been a proponent of dtexec called by xp_cmdshell. Its powerful stuff but maybe I need to revisit my thinking here. I understand you will be able to call a package using tsql in 2012
>>See how convoluted that is though
Yes I do see. I think the convolution involves the call of the procedure in database A which calls a procedure in databaseB from the package itself.
March 12, 2013 at 11:59 am
Chrissy321 (3/12/2013)
Thank you for responding to my narrative.Yes I am using xp_cmdshell. I get the same error when I add the SQL Server service account to the sysadmin, that was one of my troubleshooting steps.
That's not the whole set of options. Do you have a proxy account setup? If so and the process calling the stored proc that calls xp_cmdshell is not in the sysadmin Role then the SSIS package will be executed in the context of the proxy account and not the SQL Server service account.
>>an you move this process out of the database?
I guess I could schedule a job to call the package directly. I have been a proponent of dtexec called by xp_cmdshell. Its powerful stuff but maybe I need to revisit my thinking here. I understand you will be able to call a package using tsql in 2012
Yes, and it is wrapped up into a much tighter solution from a security perspective. Using xp_cmdshell kind of leaves us to our own devices, which can be good and bad. In SQL 2012 things are a bit more structured in this area.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 12, 2013 at 3:31 pm
I have eliminated xp_cmdshell and file output but the error remains.
Let's call my two databases vendor and work. I want to call my vendors proc but not create objects in their database.
USE Work
CREATE PROCEDURE myProc
AS
DECLARE
@SessionGuid uniqueidentifier
-- establish the session credentials
exec Vendor.User.SetGuid @SessionGuid
Runs in SSMS but not as a job.
Executed as user: MyDomain\SQL_Agent_Service_Account. Vendor.User.SetGuid @SessionGuid: NT user not authorized [SQLSTATE 42000] (Error 50000). The step failed.
In the job step the 'run as' is blank and there are no options.
March 12, 2013 at 3:42 pm
I also get the same error in a job when I past the code directly into a test job step and set the jobs step to my vendors database.
March 12, 2013 at 4:28 pm
After looking at my vendors procedure I saw that they were checking if the NT user existed in their system. After adding my SQL Server Agent account as a user in the their system it all worked.
So it looks like that was a non-SQL Server error message thrown up by my vendor. Thanks OPC, sorry for the bother.
March 12, 2013 at 4:36 pm
No bother at all. Happy you got it sorted.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply