June 4, 2020 at 1:01 am
Hi ,
Added source and Target servers as Linked servers in Intermittent Server(Third Server).And Trying to execute the below script through sqlserveragent job in Intermittent server then its failing with below error.
Same script executes fine through SSMS in Intermittent server.Unique user\login created with sysadmin access in all three server.
Same user set for Job owner .
Script:
insert into [TargetServer].[TargetDatabase].[dbo].[TargetTable]
select * from [SourceTable].[SourceDatabase].[dbo].[SourceTable]
ErrorMessage:
Executed as user: NT SERVICE\SQLSERVERAGENT. The OLE DB provider "SQLNCLI11" for linked server "SourceServer" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "SourceServer". [SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI11" for linked server "xxxx" returned message "Invalid authorization specification". [SQLSTATE 01000] (Error 7412). The step failed.
Any suggestions Please.
Thank you !!
June 4, 2020 at 4:00 pm
First, this sounds like a huge security risk. linked servers with sysadmin permissions on them sounds like a good way for someone to toss a "drop database" or a shutdown command across the link.
That being said, just so I understand the setup properly, your linked server is set up with a specific, hard-coded username/password pair, correct? OR is it running as the currently logged in user. If it is hard-coded, it should work no matter who runs it. If it is running as the currently logged in user, you would need to make sure the SQL Agent Service is running as that user.
Looking at the start of that error message, what I expect is happening is the linked server is set up to run as the currently logged in user, and your SQL Agent Service is set to run as NT Service\SQLSERVERAGENT. This is a local account and would be a different account across all of the servers. I'd recommend making it a Windows account (from AD). Failing that, you would need to set something up for authenticating such as running "EXECUTE AS" at the start of your job.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 4, 2020 at 5:45 pm
If the user that owns the agent job is a member of sysadmin - the job will run under the context of the agent service account. If the job is owned by a user that is NOT a member of sysadmin it will run under the context of that user.
With that said - you can override the user definition in the linked server so that the local login uses a remote login to access the linked server. For that you would put the 'NT SERVICE\SQLSERVERAGENT' as the local login and specify the remote user name and password to be used. This would require a SQL account be setup on the linked server with the appropriate permissions.
You have several options:
Either approach - you want to insure that the login/user that has access to the remote server is setup with minimum requirements. That login should only have access to what is needed to support the processes it will be running.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 7, 2020 at 12:12 am
Thank you !!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply