Step1.In SQL Server Management Studio, open Object Explorer, expand Server Objects, right-click Linked Servers, and then click New Linked Server.
Step2.In the new window on [General] page, we need to set several parameters as below:
Linked Server: Mention name of the linked server. But name of linked server will depend on server type.If we choose Server type as SQL Server- Then name of linked server should be source server network name .If we choose Server type as other data source -then name of linked server is any thing. like Ex: TestLinkedServer.
Server Type: Server type either SQl Server or Other data source.
If Source server is SQL server only then choose server type as SQL Server. If we choose server type as SQL server then no need to fill Provider, Product name etc…
If Source server is SQL\any other server[oracle\DB2 ctc..] we need to choose Server type as Other data source.
If we choose Server type as Other data source we need to fill Provider name installed in the server, product name etc..
Provider: Select desired OLE DB data source provider from the list box.
Default installed providers we can use if source server is SQL only.
If source server is oracle\DB2 etc...we need to download and install corresponding OLDB providers into SQL server.
Product Name: Shouldn't be empty and anything can be set, e.g. Instance Name
Data Source: It should be source server network name .
Provider String:[Optional] -Type the unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to the data source. Leave this parameter empty).
Location: Type the location of the database as interpreted by the OLE DB provider.
is disabled (leave this parameter empty)
Catalog: The database Name (set your default database while connecting or leave it empty).
Step3: On Security page we need to set several parameters as below
Login mapping is a way to associate a login on the local server, with a login on the remote server.
There are two different ways a local login can be mapped to a remote login.
The first method is to impersonate, and the second is to associate the local login with a remote login and password.
Impersonate: Pass the username and password from the local login to the linked server. For SQL Server Authentication, a login with the exact same name and password must exist on the remote server. For Windows logins, the login must be a valid login on the linked server.
Note: To use impersonation, the configuration must meet the requirement for delegation.
To map a local login you would associate it with a remote login and password. The remote login needs to be a SQL Server Authenticated user on the remote server. The following screen shot 2 shows how I have mapped some local logins to remote logins on SERVER2:
In addition to mapping logins, we need to identify how logins that are not defined in the mappings would connect to the linked server.
There are four different options that can be used. These four options are the different radio buttons in the first screen shot .
1.Not be made: When you select this option, any users not identified in the login mappings will not be able to connect to the linked server.
2.Be made without using a security context: If you select this option to connect to a linked server then this has the same effect as selecting the
“Not be made” option.
3.Be made using Login’s current security context:
Specify that a connection will be made using the current security context of the login for logins not defined in the list.
If connected to the local server using Windows Authentication, your windows credentials will be used to connect to the remote server. and same login should exit on remote server. In order for this option to work, your SQL Server machine will need to be able to impersonate a local account. However, remember this requires delegation to be set up.
If connected to the local server using SQL Server Authentication, login name and password will be used to connect to the remote server. In this case a login with the exact same name and password must exist on the remote server.
4.Be made using this security context: Specify that a connection will be made using the login and password specified in the Remote login and With password boxes for logins not defined in the list. and another way to say everyone using this linked server will connect with a single remote login and password to the linked server. The remote login must be a SQL Server Authentication login on the remote server.
Step4:Optionally, to view or specify server options, click the Server Options page. And Click OK to create Linked server.
Collation Compatible: Affects Distributed Query execution against linked servers. If this option is set to true, 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).
This enables SQL Server to send comparisons on character columns to the provider. If this option is not set, SQL Server always evaluates comparisons on character columns locally.
You should only set this to “True” if you know the local collation is the same as the linked server.
Data Access: It is used to control whether you want to allow data to be accessed on the linked server.
When this option is set to “True”[Default], the linked server can be used to access data on the remote SQL Server instance. When this option is set to “False” then access to the remote server will be denied.This option is a useful way of disabling a linked server temporarily.
RPC: It is used to allow remote procedures calls “from” the linked server.
RPC Out: It is used to allow remote procedure calls “to” the linked server.
Use Remote Collation: Determines whether the collation of a remote server or of a local server will be used. If true, the collation of remote columns is used for SQL Server data sources, and the collation specified in collation name is used for non-SQL Server data sources.
If false, distributed queries will always use the default collation of the local server, while collation name and the collation of remote columns are ignored. The default is false.
Collation Name: Specifies the name of the collation used by the remote data source if use remote collation is true and the data source is not a SQL Server data source. The name must be one of the collations supported by SQL Server.
Use this option when accessing an OLE DB data source other than SQL Server, but whose collation matches one of the SQL Server collations.
Connection Timeout: It is used to specify the maximum length of time the local server should wait to obtain a connection to the linked server SQL Server instance.
If 0, use the sp_configure default remote login timeout option value. By default the server option default is 20 seconds for the “remote login timeout”.
Query Timeout: Time-out value in seconds for queries against a linked server.
If 0, use the sp_configure default remote query timeout option value.. The “remote query timeout” value defaults to 600 (10 minutes).
Enable Promotion of Distributed Transactions:
Use this option to protect the actions of a server-to-server procedure through a Microsoft Distributed Transaction Coordinator (MS DTC) transaction. When this option is TRUE, calling a remote stored procedure starts a distributed transaction and enlists the transaction with MS DTC.
Step5: Once linked server is created .. We need to Test the linked server Connection. For that Right click on linked server and choose Test Connection Option.
Step2.In the new window on [General] page, we need to set several parameters as below:
Linked Server: Mention name of the linked server. But name of linked server will depend on server type.If we choose Server type as SQL Server- Then name of linked server should be source server network name .If we choose Server type as other data source -then name of linked server is any thing. like Ex: TestLinkedServer.
Server Type: Server type either SQl Server or Other data source.
If Source server is SQL server only then choose server type as SQL Server. If we choose server type as SQL server then no need to fill Provider, Product name etc…
If Source server is SQL\any other server[oracle\DB2 ctc..] we need to choose Server type as Other data source.
If we choose Server type as Other data source we need to fill Provider name installed in the server, product name etc..
Provider: Select desired OLE DB data source provider from the list box.
Default installed providers we can use if source server is SQL only.
If source server is oracle\DB2 etc...we need to download and install corresponding OLDB providers into SQL server.
Product Name: Shouldn't be empty and anything can be set, e.g. Instance Name
Data Source: It should be source server network name .
Provider String:[Optional] -Type the unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to the data source. Leave this parameter empty).
Location: Type the location of the database as interpreted by the OLE DB provider.
is disabled (leave this parameter empty)
Catalog: The database Name (set your default database while connecting or leave it empty).
Step3: On Security page we need to set several parameters as below
Login mapping is a way to associate a login on the local server, with a login on the remote server.
There are two different ways a local login can be mapped to a remote login.
The first method is to impersonate, and the second is to associate the local login with a remote login and password.
Impersonate: Pass the username and password from the local login to the linked server. For SQL Server Authentication, a login with the exact same name and password must exist on the remote server. For Windows logins, the login must be a valid login on the linked server.
Note: To use impersonation, the configuration must meet the requirement for delegation.
To map a local login you would associate it with a remote login and password. The remote login needs to be a SQL Server Authenticated user on the remote server. The following screen shot 2 shows how I have mapped some local logins to remote logins on SERVER2:
In addition to mapping logins, we need to identify how logins that are not defined in the mappings would connect to the linked server.
There are four different options that can be used. These four options are the different radio buttons in the first screen shot .
1.Not be made: When you select this option, any users not identified in the login mappings will not be able to connect to the linked server.
2.Be made without using a security context: If you select this option to connect to a linked server then this has the same effect as selecting the
“Not be made” option.
3.Be made using Login’s current security context:
Specify that a connection will be made using the current security context of the login for logins not defined in the list.
If connected to the local server using Windows Authentication, your windows credentials will be used to connect to the remote server. and same login should exit on remote server. In order for this option to work, your SQL Server machine will need to be able to impersonate a local account. However, remember this requires delegation to be set up.
If connected to the local server using SQL Server Authentication, login name and password will be used to connect to the remote server. In this case a login with the exact same name and password must exist on the remote server.
4.Be made using this security context: Specify that a connection will be made using the login and password specified in the Remote login and With password boxes for logins not defined in the list. and another way to say everyone using this linked server will connect with a single remote login and password to the linked server. The remote login must be a SQL Server Authentication login on the remote server.
Step4:Optionally, to view or specify server options, click the Server Options page. And Click OK to create Linked server.
Collation Compatible: Affects Distributed Query execution against linked servers. If this option is set to true, 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).
This enables SQL Server to send comparisons on character columns to the provider. If this option is not set, SQL Server always evaluates comparisons on character columns locally.
You should only set this to “True” if you know the local collation is the same as the linked server.
Data Access: It is used to control whether you want to allow data to be accessed on the linked server.
When this option is set to “True”[Default], the linked server can be used to access data on the remote SQL Server instance. When this option is set to “False” then access to the remote server will be denied.This option is a useful way of disabling a linked server temporarily.
RPC: It is used to allow remote procedures calls “from” the linked server.
RPC Out: It is used to allow remote procedure calls “to” the linked server.
Use Remote Collation: Determines whether the collation of a remote server or of a local server will be used. If true, the collation of remote columns is used for SQL Server data sources, and the collation specified in collation name is used for non-SQL Server data sources.
If false, distributed queries will always use the default collation of the local server, while collation name and the collation of remote columns are ignored. The default is false.
Collation Name: Specifies the name of the collation used by the remote data source if use remote collation is true and the data source is not a SQL Server data source. The name must be one of the collations supported by SQL Server.
Use this option when accessing an OLE DB data source other than SQL Server, but whose collation matches one of the SQL Server collations.
Connection Timeout: It is used to specify the maximum length of time the local server should wait to obtain a connection to the linked server SQL Server instance.
If 0, use the sp_configure default remote login timeout option value. By default the server option default is 20 seconds for the “remote login timeout”.
Query Timeout: Time-out value in seconds for queries against a linked server.
If 0, use the sp_configure default remote query timeout option value.. The “remote query timeout” value defaults to 600 (10 minutes).
Enable Promotion of Distributed Transactions:
Use this option to protect the actions of a server-to-server procedure through a Microsoft Distributed Transaction Coordinator (MS DTC) transaction. When this option is TRUE, calling a remote stored procedure starts a distributed transaction and enlists the transaction with MS DTC.
Step5: Once linked server is created .. We need to Test the linked server Connection. For that Right click on linked server and choose Test Connection Option.
No comments:
Post a Comment