February 13, 2010 at 12:24 pm
I have read about 50+ post on this website but not able to find the answer that I am looking for.
here is the issue: I have read only access to my LINKED Server A. I am a sysadmin role on my server B. When I run linked server query in Management studio under Server B, I can successful run the script.
So, I have created SP on my server B which include basic "Select * from...." script and I would like to schedule SP on server B to run on daily basis. When I execute my SP (test purpose) under Management studio it run perfect but I scheduled the SP and now its failing.
I have read the article and it says login has to be same where I am in sysadmin role so, i have all the access i need.
Can some one please help me or walk me through proper steps on how to schedule a SP that contain linked server queries....
I am using SQL Server 2005.
Please...please advice..
February 15, 2010 at 5:10 am
Do one thing , open job ( right click and open) and set owner's_name = login which you are using for executing that Sp in mgmt studio , if it didnt help you ,send me the job script
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 15, 2010 at 7:27 am
keyun (2/13/2010)
I have read about 50+ post on this website but not able to find the answer that I am looking for.here is the issue: I have read only access to my LINKED Server A. I am a sysadmin role on my server B. When I run linked server query in Management studio under Server B, I can successful run the script.
So, I have created SP on my server B which include basic "Select * from...." script and I would like to schedule SP on server B to run on daily basis. When I execute my SP (test purpose) under Management studio it run perfect but I scheduled the SP and now its failing.
I have read the article and it says login has to be same where I am in http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_spacer.gifsysadmin role so, i have all the access i need.
Can some one please help me or walk me through proper steps on how to schedule a SP that contain linked server queries....
I am using SQL Server 2005.
Please...please advice..
What error did you get?
Can you post the error details...
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 15, 2010 at 9:04 am
Bhuvnesh,
You mean in Job Property --> Steps --> Edit --> Advanced --> Run as User. At this screen I have following user
[dbo],
[gues],
[INFORMATION_SCHEMA],
[NT_AUTHORITY\NETWORK_SERVICE],
[NT AUTHORITY\SYSTEM],
[SERVER_B\SQLserver2005MSFTEUUser$SERVER_B$MSSQLSERVER],
[sys]
I have tried to select every single one of and try to run job but it faild every single time. I am not sure if it is login issue.
I have window Authentication access to my linked server A and server B.
The way I have created linked server is (by looking at Linked server property) under security. I have choose "Be made using the login's current security context" -- > let me know if this need to be change.
About my script:
ALTER PROC dbo.MySP
AS
Select a.*, b.*, c.*
FROM server_a.database.dbo.TableName a
inner join server_a.database.dbo.tablename1 b
on b.id = a.id
inner join server_a.database.dbo.tablename2 c
on c.id = b.id
February 15, 2010 at 9:08 am
Muthukumaran,
Here are the errors that i have received so far.
Error # 1
Message
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user 'MySystem_NT\ServerB$'. [SQLSTATE 28000] (Error 18456). The step failed.
Then I run under different user where I change user by going into property --> advanced --> Run as User. and the error was...
Message
Unable to perform a SETUSER to the requested username '##MS_AgentSigningCertificate##' because the username is invalid for database 'master'. The step failed.
I have run under all different user that i can get from Advanced tab.
The error #1 is the most common error I am receiving.
Please advice,
February 15, 2010 at 9:42 am
Are the two SQL Servers in the same domain? The errors you've posted kind of imply that they are not, in which case "in the same context" is not going to work (MyUserID on one domain is not considered the same as MyUserID on a different domain).
You may need to resort to specific mappings of users, or possibly move to SQL Server logins to make the map work.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 15, 2010 at 10:56 am
Try this, Do you have SA role in Server B? Try to create Domain account with SA role, assign that domain user as job owner, should work. If not let me know we can try other way.
EnjoY!
February 15, 2010 at 12:40 pm
Hi Matt,
Yes, you are correct they both are on different DNS. So, what are the option i have here?
Please advice,
February 15, 2010 at 12:44 pm
Hi GT,
I am new to SQL server.. I check my login under LoginProperties --> Server Roles --> here i have everysingle roles assigned it to me. ( i guess than i do have SA role ) Please advice, if i am mistaking...
So, if thats true than based on your advice, I need to create new DOMAIN ACCOUNT (User) with SA role. now, when you said that domain account that means to login to server or create a new user on SQL Server with SA role only on server and run the job under newly created user?
Please advice,
Thanks,
February 15, 2010 at 2:07 pm
keyun (2/15/2010)
Hi GT,I am new to SQL server.. I check my login under LoginProperties --> Server Roles --> here i have everysingle roles assigned it to me. ( i guess than i do have SA role ) Please advice, if i am mistaking...
So, if thats true than based on your advice, I need to create new DOMAIN ACCOUNT (User) with SA role. now, when you said that domain account that means to login to server or create a new user on SQL Server with SA role only on server and run the job under newly created user?
Please advice,
Thanks,
Can you assign SA as job owner and try? Or else you try to run the job under your account, it should work.
If you already have SA, there is no need to create SA. I was referring to create an System Admin (SA) login account if you didn't have SA login role, Sorry for Ambiguity.
EnjoY!
February 15, 2010 at 9:45 pm
Goto properties of job >> general tab>> select owner = 'Sa' or the login you want to select
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 16, 2010 at 12:45 am
keyun (2/15/2010)
Muthukumaran,Here are the errors that i have received so far.
Error # 1
Message
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user 'MySystem_NT\ServerB$'. [SQLSTATE 28000] (Error 18456). The step failed.
Then I run under different user where I change user by going into property --> advanced --> Run as User. and the error was...
Message
Unable to perform a SETUSER to the requested username '##MS_AgentSigningCertificate##' because the username is invalid for database 'master'. The step failed.
I have run under all different user that i can get from Advanced tab.
The error #1 is the most common error I am receiving.
Please advice,
Goto properties of job >> general tab>> select owner = 'Sa' or the login you want to select
keyun,
Did you solve the issue?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 16, 2010 at 9:55 am
As per your guidance. I have set the Owner of the job as SA but i am getting below error.
Message
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user 'DOMAIN_NT\SERVERB$'. [SQLSTATE 28000] (Error 18456). The step failed.
Please advice,
Keyun
February 16, 2010 at 12:46 pm
keyun (2/16/2010)
As per your guidance. I have set the Owner of the job as SA but i am getting below error.Message
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user 'DOMAIN_NT\SERVERB$'. [SQLSTATE 28000] (Error 18456). The step failed.
Please advice,
Keyun
Looks like the user is in different domain, am i correct?
February 16, 2010 at 3:07 pm
Same company but two different servers.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply