none
Syntax when connecting from client to MS SQL Server using non-standard port RRS feed

  • Question

  • Hi!

    Done extensive search but always getting results on how to change port on the actual SQL server, which isn't what I need to do. That port has already been changed from standard 1433 to other port number.

    In a PowerShell script:

    Can connect from Machine A to SQL server over standard port 1433 (tried opening it on the SQL server).

    Can not connect from Machine B to SQL server over other  port.

    To check things out:

    I can telnet:

    Getting ESTABLISHED on [other_port]

    and ODBC connect from Machine B to SQL server:

    In "Microsoft SQL Server DSN Configuration" using driver "SQL Server" - System DSN -> Next - button Client Configuration, changed to other port. Getting "TESTS COMPLETED SUCCESSFULLY!"

    Running netstat -bfo -n 10 -p TCP I can see that there is a connection ESTABLISHED from odbcad32.exe to remoteserver:[other port]. I verified the Process ID and it's indeed the ODBC.

    Trying ODBC - Driver: SQL Server Native Client 11.0 -> Server: [SERVER_NAME],[other_port]

    Seems to work. So it's "comma" to be used in that driver?

    Third driver available is ODBC Driver 13 for SQL Server. During test I read "Microsoft ODBC Driver for SQL Server Version 14.00.1000". I used comma. Netstat shows ESTABLISHED.

    You have to wait a while for the connection to disappear in netstat which I did in the above tests, between the three ODBC drivers tested.

    Which of these three drivers (SQL Server, SQL Server Native  Client 11.0, ODBC Driver 13 for SQL Server) is PowerShell using?


    Is PowerShell using any other ports behind the scenes (can't see any in Netstat?).

    PowerShell 5.1.

    Machine B to SQL Server, diving into the script:

    Getting error:

    Multiple ambiguous overloads found for "Fill" and the argument count: "1".

    at line:20 char 1

    + `$SqlAdapter.Fill($Dataset)

    ...

    Which is not very accurate? I suspect network errors, eventhough the error states differently.

    Are there any implication not using standard port 1433 as it works from machine A to SQL Server?

    Have a nice day!

    Wednesday, March 11, 2020 9:07 AM

All replies

  • NO idea what you are trying to ask.

    What are your connection strings in PowerShell?  Have you tested the DSN with the test utility?



    \_(ツ)_/

    Wednesday, March 11, 2020 9:56 AM
  • In the DSN connection string use this for the server:

    Server=tcp:ServerName,1265


    \_(ツ)_/

    Wednesday, March 11, 2020 10:11 AM
  • Here are all of the variations of connection strings for SQLserver

    https://www.connectionstrings.com/sql-server-2012/

    Test examples:

    # SQLClient conenction
    $conn = [System.Data.SqlClient.SqlConnection]::New($connectionString)
    $conn.Open()
    
    # ODBC connection
    $conn = [System.Data.Odbc.OdbcConnection]::new($connectionString)
    $conn.Open()
    


    \_(ツ)_/

    Wednesday, March 11, 2020 10:24 AM
  • Hi!

    Tried, no go.

    $SQLServ = "[server_name],1265"
    
    $SQLDBname = "Name_of_db"
    
    $SQLQuery = "SELECT * FROM MyTable;"
    
    #Connection String
    
    $SqlConn = New-Object System.Data.SqlClient.SqlConnection
    
    $SqlConn.ConnectionString = "Server = $SQLServ; Database = $SQLDBName; Integrated Security = True"
    
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    
    $SqlCmd.CommandText = $SQLQuery
    
    $SqlCmd.Connection = $SqlConnection
    
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    
    $SqlAdapter.SelectCommand.SqlCmd
    
    $Result = New-Object System.Data.DataSet
    
    # All ok until now, next issuing:
    
    $SqlAdapter.Fill($Dataset) # No go, getting the ambiguous error
    
    #Close the connection
    
    $SqlConnection.Close()
    
    #Display result
    
    $Result.Tables[0] # Returns n:o of rows in the table
    
    Above script works fine from Machine A to SQL Server over port 1433 (got both 1433 and "other port" open on SQL Server)
    
    Tried changing $SQLServ per Your suggestion  to:
    
    $SQLServ = "TCP:[server_name],1265"


    (1265 is something else in our environment).

    Cheers


    • Edited by tobbo166 Wednesday, March 11, 2020 12:09 PM
    Wednesday, March 11, 2020 10:25 AM
  • Please post code correctly in this forum. What you have posted in unreadable in most browsers.


    \_(ツ)_/

    Wednesday, March 11, 2020 10:28 AM
  • Sorry. Updated above. Thanks,
    Wednesday, March 11, 2020 12:09 PM
  • Not formatted very well.

    You are using the SQLClient which does not use an ODBC DSN connection string.  With SQLClient we are using an ADO connection string.

    To reroute the port it is necessary to create an alias for the server and use that as the DataSource.  The Alias will allow a different port.

    Use the test code I posted to get the connection string correct.  You can use the base string but change the datasource to the alias and you are set.


    \_(ツ)_/

    Wednesday, March 11, 2020 12:19 PM
  • I am now sorted thanks. I take it System.Data.SqlClient.SqlConnection is faster than System.Data.Odbc.OdbcConnection so will go for that. Comma works fine, e.g.:

    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Server=[name_of_sql-server],1234;Database=funkyDB;Integrated Security=True;"
    $conn.Open()

    • Edited by tobbo166 Wednesday, March 11, 2020 1:50 PM
    Wednesday, March 11, 2020 1:48 PM
  • This would be the more acceptable method for returning table data:

    $server = '[server_name],1265'
    $dbname = 'Name_of_db'
    $query = 'SELECT * FROM MyTable;'
    
    $connectionString = "Server=$server;Database=$dbname;Integrated Security=True"
    $conn = [System.Data.SqlClient.SqlConnection]::New($connectionString)
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $query
    $rdr = $cmd.ExecuteReader()
    $dt = [System.Data.DataTable]::New()
    $dt.Load($rdr)
    $conn.Close()
    
    $dt.Rows.Count

    The "adapter" method is designed to be used for other things.


    \_(ツ)_/


    • Edited by jrv Wednesday, March 11, 2020 3:32 PM
    Wednesday, March 11, 2020 3:32 PM