SQL 2008R2 Linked Servers

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”

  1. Login as “sa” into SQL Server on Server A.
  2. Go to Server Objects/Linked Servers.
  3. Right click, New Linked Server.
  4. Note: If linked server is of type SQL Server, then the linked server name is also the network name of the server.
  5. 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”
  6. Choose Server type: SQL Server.
  7. Go to Security.
  8. Click Add.
  9. Local login: sa
  10. Impersonate: not checked
  11. Remote user: sa
  12. Remote password: sa password on remote server
  13. 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)

References:

Linking Servers

Accessing External Data

Identifying a Data Source by Using a Linked Server Name

sp_addlinkedserver (Transact-SQL)

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: