Follow the below steps to create a Linked server in SQL SERVER.
Prerequisite for creation of linked server -
1. Connectivity should be there between the two servers and port 1433 or the port on which destination
SQL SERVER is running should be open in firewall on destination server from source server so that
connection can be established from source to destination server.
2. Account that will be used for connecting from source to destination server should be added in the
destination server with the required rights.
Step 1 : - Connect to source server and go to Server objects -> Linked Server -> New Linked Server
Step 2 :- If your destination server is SQLSERVER then mention the destination server name in the Linked server name. If the Server is running on any other port other than 1433 then please mention the port number also ( Server Name, Port Number).
Step 3 : - Enter the login credentials that will be used to connect to destination SQL SERVER. This will specify how the linked server will be authenticated.
Not be made
Specify that a connection will not be made for logins not defined in the list.
Be made without using a security context
Specify that a connection will be made without using a security context for logins not defined in the list.
Be made using the 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. 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.
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. The remote login must be a SQL Server Authentication login on the remote server.
Step 4 :- Click OK and linked server is created. You can view the linked server at Server Objects -> Linked Server.
Step 5 :- Verify if the linked server created is able to access the destination server from source server. Right click the linked server and select test connection. It will be successful if it show connection succeeded.
Step 6 :- For querying the destination server name use the FQDN name.
FQDN name is ServerName.DatabaseName.SchemaName.ObjectName.
Prerequisite for creation of linked server -
1. Connectivity should be there between the two servers and port 1433 or the port on which destination
SQL SERVER is running should be open in firewall on destination server from source server so that
connection can be established from source to destination server.
2. Account that will be used for connecting from source to destination server should be added in the
destination server with the required rights.
Step 1 : - Connect to source server and go to Server objects -> Linked Server -> New Linked Server
Step 2 :- If your destination server is SQLSERVER then mention the destination server name in the Linked server name. If the Server is running on any other port other than 1433 then please mention the port number also ( Server Name, Port Number).
Step 3 : - Enter the login credentials that will be used to connect to destination SQL SERVER. This will specify how the linked server will be authenticated.
Step 4 :- Click OK and linked server is created. You can view the linked server at Server Objects -> Linked Server.
Step 5 :- Verify if the linked server created is able to access the destination server from source server. Right click the linked server and select test connection. It will be successful if it show connection succeeded.
Step 6 :- For querying the destination server name use the FQDN name.
FQDN name is ServerName.DatabaseName.SchemaName.ObjectName.
No comments:
Post a Comment