February 20, 2016 at 5:20 am
I have two separate machine installed with SQL server 2008R2.
Now I want to execute query from Server1 and write to Server2.
I have two physically separated machine installed with the same SQL server 2008 R2 version instance
Server -A
Machine Name: Server1
Machine Local IP Address: 10.5.20.5
Machine Login Username: User1
Machine Login password: Password1
Database Instance Name: Instance1
It is configured for mixed authentication Mode
SQL Login User=SQLUser1
SQL Login Password=SQLUser1
Installed SQL version: SQL Server 2008 R2 ( Version :10.50.1600.1)
Installed operating system: windows server 2008 R2
Server -B
Machine Name: Server2
Machine Local IP Address: 10.6.10.6
Machine Login Username: User2
Machine Login password: Password2
Database Instance Name: Instance2
It is configured for mixed authentication Mode
SQL Login User=SQLUser2
SQL Login Password=SQLUser2
Installed SQL version: SQL Server 2008 R2 ( Version :10.50.1600.1)
Installed operating system: windows server 2008 R2
Now the problem is, I am not able to create linked server at Server1…
What I did was, At Server2, enabled TCP/IP, TCP Port 1433, add this to firewall for both TCU and UDP and then use management studio at Server1 to create the linked server and follow the procedure, But I am not able to create the linked Server. Actually I am new for Linked server
Can you help me what are the requirements and steps to create the linked server at Server1 to write data to server2 database considering the above scenario?.. the two servers are in two different VLAN / Please refer the above IP address/
Thank you in advance
February 20, 2016 at 12:37 pm
assefamisganaw (2/20/2016)
I have two separate machine installed with SQL server 2008R2.Now I want to execute query from Server1 and write to Server2.
I have two physically separated machine installed with the same SQL server 2008 R2 version instance
Server -A
Machine Name: Server1
Machine Local IP Address: 10.5.20.5
Machine Login Username: User1
Machine Login password: Password1
Database Instance Name: Instance1
It is configured for mixed authentication Mode
SQL Login User=SQLUser1
SQL Login Password=SQLUser1
Installed SQL version: SQL Server 2008 R2 ( Version :10.50.1600.1)
Installed operating system: windows server 2008 R2
Server -B
Machine Name: Server2
Machine Local IP Address: 10.6.10.6
Machine Login Username: User2
Machine Login password: Password2
Database Instance Name: Instance2
It is configured for mixed authentication Mode
SQL Login User=SQLUser2
SQL Login Password=SQLUser2
Installed SQL version: SQL Server 2008 R2 ( Version :10.50.1600.1)
Installed operating system: windows server 2008 R2
Now the problem is, I am not able to create linked server at Server1…
What I did was, At Server2, enabled TCP/IP, TCP Port 1433, add this to firewall for both TCU and UDP and then use management studio at Server1 to create the linked server and follow the procedure, But I am not able to create the linked Server. Actually I am new for Linked server
Can you help me what are the requirements and steps to create the linked server at Server1 to write data to server2 database considering the above scenario?.. the two servers are in two different VLAN / Please refer the above IP address/
Thank you in advance
Step 1: get your networking right. Enlist the help of your network folks (hopefully you have support there) and get your two servers talknig to each other. I like portqry.exe to diagnose connectivity issues because you can specify the protocol, host and port to confirm connectivity.
Description of the Portqry.exe command-line utility
Step 2: much simpler...create the Linked Server:
Creating Linked Servers (SQL Server Database Engine, 2008 R2)
Don't mix up the steps...that is how you aren't sure which problem you are battling. Make sure you complete Step 1 before moving on to Step 2.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 3, 2016 at 4:49 am
Thank you for the reply.
For the environment above, is there any one who can tell me the following values in the linked server Intrerface.
Linked Sever:_________________
SQL SERVER:_________________
Other Data Source:____________
Provider:____________________
Product Name:_______________
Data Source:_________________
Provider String:_______________
Location:____________________
Catalog:_____________________
In The Security Option , can you tell me the relationship between the username and password of the two servers in the given scenario. The two servers are in different physical server found in different VLAN and networks
Thank you in Advance
March 4, 2016 at 12:30 am
When I tried to connect I am getting the following error.
TITLE: Microsoft SQL Server Management Studio
------------------------------
The test connection to the linked server failed.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI10" for linked server "XXXXX" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server " XXXXX " returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 53)
It is in the environment that I described above!
For your information:
It is configured to allow remote connections at server 2.
Any help?
March 4, 2016 at 1:18 am
Can you telnet from Server1 to Server2?
It looks like it might be trying to use Named Pipes, what are the Server 2's connectivity options set as?
March 4, 2016 at 1:48 am
assefamisganaw (3/4/2016)
When I tried to connect I am getting the following error.TITLE: Microsoft SQL Server Management Studio
------------------------------
The test connection to the linked server failed.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI10" for linked server "XXXXX" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server " XXXXX " returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 53)
It is in the environment that I described above!
For your information:
It is configured to allow remote connections at server 2.
Any help?
Did you complete the Step 1 that I mentioned, confirming connectivity?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 4, 2016 at 2:28 am
I told the Network Admin to OPEN port Number 1433 and 1434 in the firewall
Using the tool, it returns the following status from Server 2
TCP port 1433 (ms-sql-s service): LISTENING
UDP port 1434 is LISTENING
may be other port to be checked? or any help
March 4, 2016 at 2:34 am
assefamisganaw (3/4/2016)
I told the Network Admin to OPEN port Number 1433 and 1434 in the firewallUsing the tool, it returns the following status from Server 2
TCP port 1433 (ms-sql-s service): LISTENING
UDP port 1434 is LISTENING
may be other port to be checked? or any help
Named instances sometimes (possibly most times) are configured to listen on a port other than the port where the default instance runs listens by default, namely port 1433. You may want to check to see which port number Instance1 Instance2 is listening on, and that it is setup as a static port assignment (i.e. does not use Dynamic Ports). That is the port you need opened up between the servers. Also, if you find do not need 1433 have your admin close it. In any event you will want to keep UDP 1434 open otherwise the SQL Browser will not work for you.
Edit: meant to ref Instance2
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 4, 2016 at 2:36 am
Is SQL Browser running on server 2?
What are the server protocols enabled on server2?
March 4, 2016 at 2:47 am
When I use the following Value,
LINKED SERVER : Instance2
Provider : Microsoft OLE DB Provider for SQL Server
Product Name: SQLSERVER
Data Source : Server2\Instance2
Provider String: empty / No Value is given/
Catalog:Database2 / it is a database in Server2 that I want to link/
In the security part
Remote Login: SQLUser2 / it is SQL server User/
With Password: SQLUser2 /it is password fro SQL User /
The following error is generated
The linked server has been created but failed a connection test. Do you want to keep the linked server?
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
OLE DB provider "SQLNCLI10" for linked server " Instance2" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server " Instance2" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 65535)
Any Help?
March 4, 2016 at 2:52 am
Can you connect from SSMS on Server1 to the instance on Server2?
March 4, 2016 at 2:52 am
assefamisganaw (3/4/2016)
When I use the following Value,LINKED SERVER : Instance2
Provider : Microsoft OLE DB Provider for SQL Server
Product Name: SQLSERVER
Data Source : Server2\Instance2
Provider String: empty / No Value is given/
Catalog:Database2 / it is a database in Server2 that I want to link/
In the security part
Remote Login: SQLUser2 / it is SQL server User/
With Password: SQLUser2 /it is password fro SQL User /
The following error is generated
The linked server has been created but failed a connection test. Do you want to keep the linked server?
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
OLE DB provider "SQLNCLI10" for linked server " Instance2" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server " Instance2" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 65535)
Any Help?
What port is Instance2 running on?
Note I misspoke earlier and said Instance1 instead of 2. I edited my earlier post for clarity.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 4, 2016 at 2:53 am
Is SQL Browser running on server 2?
What are the server protocols enabled on server2?
---------------------------------------------
YES SQL Browser running on server 2
Protocols enabled in Server 2 are : TCP/IP, NamedPipes and Shared memory
March 4, 2016 at 3:00 am
Is SQL Browser running on server 2?
What are the server protocols enabled on server2?
YES SQL Browser running on server 2
protocols enabled on server2 are : TCP/IP,Named Pipes and Shared memory
March 4, 2016 at 3:07 am
When I tried to connect Instance 2 from Server 1 SSMS, it generates the following error
TITLE: Connect to Server
------------------------------
Cannot connect to Server 2\Instance2.
------------------------------
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply