Configure a linked server using SQL Server Management Studio

A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. Use the following procedure to create a new linked server using SQL Server Management Studio (SSMS).

1. Start SSMS and connect to the database engine.

2. In the Object Explorer pane, navigate to Server Objects > Linked Servers.

3. Right-click Linked Servers and click New Linked Server.

   The New Linked Server window opens.

4. Complete the fields on the General page.

   Option	Description
   Linked server	Required. Enter the name to identify the linked server being created.
   Provider	Required. Select PI SQL Client.
   Data source	Required. Enter the name of the AF Database.
   Provider string	Optional. Corresponds to the UDL Extended Properties attribute. You can set PI SQL Client OLEDB specific initialization properties. For example, Integrated Security=SSPI.
   Location	Required. Enter the PI AF server name.
   Catalog	Optional. Specify the default catalog for the connection.

   

5. On the Security page, specify the authentication information using one of the following methods.

   • To use a Trusted Connection, add Integrated Security=SSPI to the Provider string on the General page and select Be made using the login's current security context.

   • To add a specific user to the Local server login to remote server login mappings table, click Add, select the user in the Local Login column, and select the Impersonate check box.

   Note: The authentication information can be specified separately for each SQL Server user or once for all SQL Server users.

   

6. On the Server Options page, set the following options to True.

   Option	Description
   Collation Compatible	Enables SQL Server to send comparisons on character columns to PI SQL Client OLEDB. SQL Server assumes that all characters in the linked server are compatible with the local server, with regard to character set and collation sequence (or sort order).
   Data Access	Enables a linked server for distributed query access.
   Use Remote Collation	Allows the collation of remote columns to be used for SQL Server data sources, and the collation specified in collation name to be used for non-SQL Server data sources.

   

7. Click OK.