Invalid authorisation linked server

Question

I want to copy data from 1 table to another and they are in different servers.

I have set 2 servers as linked servers. In SQL Server Management Studio,

• Server Objects
  • Right-click Linked Servers,
  • and then New linked server.

I gave the connecting server as my serve rname SERVER2 and type as SQLSERVER.

Now I execute the query:

Insert Into Server1.Database1.dbo..Table1 (Col1, Col2) 
     Select Col1, Col2 
       From Server2.Database2..dbo.Table2

It is throwing an error message,

The OLE DB provider "SQLNCLI" for linked server "SERVER2" has returned the message "Invalid authorization specification".

I have no idea where to change the authorization.

Answer 1

In SSMS, Server Objects -> Linked Servers, Right-click on SERVER2 and choose Properties.

In the properties window, click on "Security" in the left hand panel.

In the section labeled, "For a login not defined in the list above, connections will:", choose the last option "Be made using this security context:". Then specify a login and password for an account on Server2 with appropriate permissions for the task you're trying to accomplish.