locked
SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified RRS feed

  • 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

  • $dbName = "TestDB"
    $dbInstance = "TestRemoteServer\" + $dbname

    Hello,

    The instance name must be the server name without the database name.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by slamsql Wednesday, June 6, 2018 5:09 PM
    Thursday, May 31, 2018 7:34 AM

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://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-access-server-configuration-option?view=sql-server-2017

    https://imron.com/support/knowledgebase/configuring-a-sql-server-for-remote-connections/

    Thanks,
    Xi Jin.


    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
  • $dbName = "TestDB"
    $dbInstance = "TestRemoteServer\" + $dbname

    Hello,

    The instance name must be the server name without the database name.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by slamsql Wednesday, June 6, 2018 5:09 PM
    Thursday, May 31, 2018 7:34 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