I want to connect Server A with Server B, so I can query a table on a database on Server B from a database on Server A. This is called a “distributed query”
- Login as “sa” into SQL Server on Server A.
- Go to Server Objects/Linked Servers.
- Right click, New Linked Server.
- Note: If linked server is of type SQL Server, then the linked server name is also the network name of the server.
- Enter Linked server name: “Server B” network name (or IP address). If your SQL Server is a named instance, it would be “SERVER_NAME\INSTANCE_NAME” or “SERVER_IP\INSTANCE_NAME”
- Choose Server type: SQL Server.
- Go to Security.
- Click Add.
- Local login: sa
- Impersonate: not checked
- Remote user: sa
- Remote password: sa password on remote server
- Click OK
Now you can write a distributed sql query from a database on Server A, like this:
select * from [linked_server_name].db_name.schema_name.table_name
However, the setup describe above is not the most secure. Use of “sa” login shoul be disabled on both servers. It’s recommended that you create a login for each database, with the minimum permissions required, and use this login as remote user (11) and remote password (12) described above.
Of course, you can also do this using a SQL script (sp_addlinkedserver)