0

I have a SQL statement that merges 2 databases together, from 2 different servers (SQL Server 2012 for both).
The two servers are linked, so when executing the statement in a query window from Management Studio with the SA account it works perfectly.

However, I need to automatize this. I created a Job in the SQL Server Agent, and I keep getting an authentication error. This is because the user that is executing the SQL Server Agent service doesn't have the SA rights on both servers.

This is the error:

The OLE DB provider 'providerName' for the connection server 'serverName' reported an error. Authentication failed.

I tried setting a superadmin user from the Active Directory with all rights on both servers to execute the SQL Server Agent, but I still get the error.

Is there any way to fix this?

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Val.B
  • 93
  • 1
  • 2
  • 13
  • 1
    You need to choose whther you want to use stored credentials or carry on credentials from the execution contex. Heve a look on this blog post for more details: https://blog.sqlauthority.com/2015/10/03/sql-server-linked-server-creation-error-ole-db-provider-sqlncli11-for-linked-server-returned-message-invalid-authorization-specification/ – Jan Oct 23 '17 at 09:32
  • Wow it worked. Going to run some additional tests but it looks good. Thank you so much – Val.B Oct 23 '17 at 09:41
  • How the security of linked server is set? – sepupic Oct 23 '17 at 09:46
  • Though I have to add that in addition to using the stored credentials, I also had to set up and use a proxy to execute the job. – Val.B Oct 23 '17 at 09:52

1 Answers1

1

You can store your linked server credentials with your linked server definition (forbid my picture editing skills ;-)):

enter image description here

Alternatively - and much more preferabele from security point of view - setup same account on both servers (you can then limit the account privileges as you need) and use the third option ('Be made using the login's current security context')

Jan
  • 1,905
  • 17
  • 41
  • 1
    This maps EVERYONE to sa on linked server, it's just HORRIBLE – sepupic Oct 23 '17 at 09:43
  • Yeah this worked for me, but I chose the 3rd option "Using the login's current security context". I also had to set up and use a proxy to execute the job. Thanks! – Val.B Oct 23 '17 at 09:56