October 2, 2008 at 10:09 am
First of all, where are the SQL Server 2008 forums?
So here's my setup. I created a linked server to a db2 database. I have a user on the db2 database called cbsdba, so I created the same user on my sql server with the same password so I could use a local login for my linked server connection. It works as expected, as the cbsdba user I can use openquery to hit my db2 database just fine. Then I put my query into an agent job, change the step to be "run as" the cbsdba user, and it fails with the following:
Executed as user: cbsdba. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274). The step failed.
So I'm guessing this has something to do with the context that the agent job is running under. The job step is owned by cbsdba user, but the whole job is owned by sa. So, I changed the whole job to be owned by cbsdba and now I get this:
Executed as user: cbsdba. The OLE DB provider "IBMDADB2" for linked server "crmdev" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "IBMDADB2" for linked server "crmdev". [SQLSTATE 42000] (Error 7303) OLE DB provider "IBMDADB2" for linked server "crmdev" returned message " SQL30082N Attempt to establish connection failed with security reason "3" ("PASSWORD MISSING"). SQLSTATE=08001 ". [SQLSTATE 01000] (Error 7412). The step failed.
Which makes no sense to me, because if anything, making the whole job be owned by cbsdba should help. But now it's acting like the cbsdba user can't authenticate across, but I created the linked server login with "locallogin = 'cbsdba'", so as I understand it the linked server should try to sign in to the DB2 system with my sql server cbsdba user credentials (which match what db2 has). I thought I understood this because it works from Management Studio, but the SQL Agent's convoluted security infrastructure seems to be getting in my way. Any ideas?
October 2, 2008 at 10:19 am
If on a security tab of a linked server you choose "Being made using current security context" - then you will receive an error described above.
Click on "Be made using this security context" and then enter login name and password for the user that you have created.
October 2, 2008 at 10:28 am
Thanks, but that didn't help. ANd it defeats the purpose...I want to only be able to access my remote db2 system using that cbsdba user. If a login not defined tries to connect, I don't want it to connect successfully. If I'm running a query as the cbsdba user on sql server, I want the linked server to try to connect to my db2 system using my sql server security context for the cbsdba user. User and password are the same on both source and desination.
Bottom line, I know this works. I just want it to work in an Agent job. I just want to know why I can't put my successful queries into a job and continue to execute them successfully. And as I understand it, a job runs under the security context of the job owner....so a job owned by cbsdba running my queries should be no different than just running those same queries while logged in to the sql database as the cbsdba user.
October 2, 2008 at 10:51 am
And as I understand it, a job runs under the security context of the job owner....so a job owned by cbsdba
You didn't specify the version of SQL server you are running, but if it is >= SQL 2005 and if a user is a member of sysadmin, then the job running under the security context of SQL Agent account.
http://www.extremeexperts.com/sql/yukon/SecurityIn2005_2.aspx
October 2, 2008 at 10:52 am
I understand that, the user is not sysadmin so it should run under it's own security context.
Running SQL Server 2008, RTM.
October 2, 2008 at 11:09 am
No. Beginning from SQL 2005 security for Agent changed.
When you run a program from a SQL Server Agent job, that running process has the permissions of the execution context configured for the job step. By default this is the SQL Server Agent service account, but it can also be a Proxy account, depending on how the job step was configured.
If proxy account had not been defined and owner of a job step is not a member of sysadmin role, then the SQL Server Agent service runs in the security context of the Local System account.
Try creating a proxy account and then change the owner of a particular step using a linked server to have owner as this proxy account.
If proxy account is defined and owner changed then this particular step in a job will be executed using security context of a proxy account.
October 2, 2008 at 11:17 am
Wow, and a proxy requires credentials etc etc. Way too much additional overhead for what I want to do. Thaks for the replies.
And thank you Microsoft for turning something that used to be so beautifully simple into a convoluted mess.
October 2, 2008 at 11:31 am
And thank you Microsoft for turning something that used to be so beautifully simple into a convoluted mess.
That would be an incorrect statement. In SQL 2000 this "simple thing" lead to a lot of security holes.
Read this http://www.microsoft.com/technet/prodtechnol/sql/2005/newsqlagent.mspx
I think that the explanations of a security reasons provided in the article are absolutely valid.
October 2, 2008 at 11:34 am
Yes, I understand the tradeoff for simplicity is enhanced security. But most people I know like(d) SQL Server because it was a quick and easy RDBMS to set up and manage for little one-off apps. If you want to really get a secure, airtight system go for Oracle or DB2.
October 2, 2008 at 11:48 am
Resolved my issue. I granted the user that owns the package SQLAgentUserRole role in the msdb database.
October 2, 2008 at 11:52 am
Resolved my issue.
It wasn't that difficult after all 🙂
August 17, 2017 at 1:18 pm
Glen Sidelnikov - Thursday, October 2, 2008 11:08 AMNo. Beginning from SQL 2005 security for Agent changed.When you run a program from a SQL Server Agent job, that running process has the permissions of the execution context configured for the job step. By default this is the SQL Server Agent service account, but it can also be a Proxy account, depending on how the job step was configured.If proxy account had not been defined and owner of a job step is not a member of sysadmin role, then the SQL Server Agent service runs in the security context of the Local System account. Try creating a proxy account and then change the owner of a particular step using a linked server to have owner as this proxy account.If proxy account is defined and owner changed then this particular step in a job will be executed using security context of a proxy account.
Thank you for this explanation, it helped me out with a similar issue of authorization failed.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply