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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy