February 18, 2009 at 11:48 am
Hello all,
After looking to several posts on the forum i really couldnt find the solution to my problem.
Case:
Local PC:
Personal Desktop with
Windows XP SP3
SQL Server 2008 EE
SQL Server Agent Log on as NT AUTHORITY/Network service
SQL Server Log on as DeskTopName\Administrator
Remote PC
Secondary Server
Windows Server 2003
Attached to Domain 'XXXX'
SQL Server 2008 EE
SQL Server Agent Log on as Local System
SQL Server Log on as Local System
Local PC is linked to the Remote PC
Linked server was tested using this T-SQL
BEGIN TRY
EXEC sp_testlinkedserver N'linked_server_name';
END TRY
BEGIN CATCH
PRINT 'Linked Server not available';
ROLLBACK;
RETURN;
END CATCH
I have a job that looks some data stored on the Local PC and check it against the Remote PC and inserts or Updates, but when i run the job the message of error i keep getting is
Message
Executed as user: NT AUTHORITY\LOCAL SERVICE. Login failed for user 'sa'. [SQLSTATE 28000] (Error 18456). The step failed.
I even created a new login, then a proxy for it and used it to own the job but still same thing.
But the tricky thing is if i run the script on the SSMS query window it runs smooth, but with the job i keep on getting same error.
If any more info needed to get to the point of this issue i have please let me know and thanks in advance
February 18, 2009 at 11:54 am
SQL Server Agent Log on as NT AUTHORITY/Network service
Change the above account to local systems and it will run but it is high permission account.
Kind regards,
Gift Peddie
February 18, 2009 at 12:06 pm
Thanks for the quick reply,
I have changed that before and it did not worked, even tho i changed the value again and same message came up
Message
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user 'sa'. [SQLSTATE 28000] (Error 18456). The step failed.
im running a new T-SQL i found which is
SET NOCOUNT ON;
CREATE TABLE #foo
(
dbname SYSNAME NULL
);
INSERT #foo
EXEC master..xp_cmdshell
'osql
-S
-dMaster
-U
-P
-Q"SELECT Name FROM sysdatabases"';
IF EXISTS
(
SELECT 1
FROM #foo
WHERE LTRIM(RTRIM(dbname)) = N'master'
)
BEGIN
PRINT 'Feel free to use linked server.';
END
ELSE
BEGIN
PRINT 'Linked server not available.';
END
DROP TABLE #foo;
and it says linked server not available now but with the other T-SQL it worked....
this is strange....
February 18, 2009 at 12:16 pm
What the error is saying is the job is running in the context of the Agent using network service account which cannot connect to the remote server. The reason the jobs runs in the context of the Agent and not SA. And you need to make sure xp_cmdshell is enabled because it is disabled by default. So check SAC in 2005 and configuration manager in 2008 before running the code.
Kind regards,
Gift Peddie
February 18, 2009 at 12:24 pm
Hello again
i ran this script
To enable xp_cmdshell, run the following code:
USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
it worked this...
Ran again the job and still nothing....
Thanks for the help, even tho im still looking to fix this doing some google
February 18, 2009 at 12:34 pm
Per the thread below you have not configured link server with the relevant permissions needed to run the code in the remote server. That is the account in one server does not have permissions to run the code in the other.
http://www.sqlservercentral.com/Forums/Topic550147-145-1.aspx
Kind regards,
Gift Peddie
February 18, 2009 at 12:41 pm
There are a few different ways to set up Linked Server security. Be sure you understand the different ways and how they are used
February 18, 2009 at 1:09 pm
Thanks guys, i could solve my problem thanks to the post u gave me.
All was giving the right security in the linked servers
Give remote login
February 18, 2009 at 1:13 pm
What Steve was saying is in the security section of linked server you can choose to run it without security.
Glad to see your problem is resolved.
Kind regards,
Gift Peddie
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply