Answered by:
SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

Question
-
I get this error when running the below powershell script; connecting to a remote server. The error occurs on during the "Extract Table List" step:
Exception calling "Fill" with "1" argument(s): "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 r
emote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"
If I run the same script while connecting to a local instance, it works fine. Any ideas why?
$dbName = "TestDB"
$dbInstance = "TestRemoteServer\" + $dbname
$expFolder = "C:\mssql\export\"
$uid = 'TESTUSER'
$pwd = 'testpass'
$tablequery = "SELECT name from sys.tables"
$connectionTemplate = "Data Source={0};User ID = $uid; Password = $pwd;;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $dbInstance, $dbName)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
Write-Host "Connection Strings"
Write-Host "Connecting to Server"
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $tablequery
$command.Connection = $connection
Write-Host "Connected to Server"
Write-Host "Extract Table List"
#Load up the Tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()
Wednesday, May 30, 2018 3:43 PM
Answers
All replies
-
Hi,
As you said that you can connect to a local instance, but when you connect to a remote instance it fails. And the error also told about this "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."
So please verify that if your remote server has been set to allow remote connections. If not, please try following steps to enable remote connections:
1. Go to SQL Server Management Studio (SSMS), in Object Explorer, right-click a server and select Properties.
2. Click the Connections node.
3. Under Remote server connections, select or clear the Allow remote connections to this server check box.
For more details, please refer to following articles:
https://imron.com/support/knowledgebase/configuring-a-sql-server-for-remote-connections/
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.Thursday, May 31, 2018 7:05 AM -
Hi Xi Jin - The instance name is correct, I can connect with the same credentials using SSMS. The server allows remotes connections. Our IT team will be checking the firewall to see if is the issue. Any other suggestions would be greatly appreciated!
Updated: IT team confirms that TCP port 1433 and UDP port 1434 are opened.
- Edited by slamsql Wednesday, June 6, 2018 3:47 PM
Wednesday, June 6, 2018 3:19 PM -
Thank you Olaf! I can't believe I missed that.
$dbInstance = "TestRemoteServer\ " + $dbname should have been $dbInstance = "TestRemoteServer\ " + "SqlInstance"Wednesday, June 6, 2018 5:11 PM