It is not always intuitive on how to setup a linked server with an alias, or setup security for the linked server when it will be used via a SQL Agent Processing Job.

Here are a few notes to make this process easier.

To Setup a Linked Server with an alias it is very easy once you know how ( see the illustration below)

LinkedServer_Alias

If you will be calling objects from the linked server as part of a process invoked by SQL Server Agent then you may want to connect with a specific SQL Server User, such as below.

LinkedServer_Alias_Security

In order to connect with SQL Authentication your server must be configured to allow SQL Authentication connections, which you can check under the server security properties. Note: If your server only allows Windows Authentication the change will require you to restart the service in order for any changes to take effect.

Database_Properties_Security

 

Do you have any other tips for using linked servers?

Let’s discuss below.