When the instance name is specified, it is invalid to specify the port number

[Guest]

Hi,

I am using ColdFusion11 version 11,0,03,292480 with java 8 installed and am using the coldfusion API setMSSQL function to create an mssql datasource.  The datasource is a named instance and when it is created I get the below error when it tries to verify:

java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Conflicting connection information. When the instance name is specified, it is invalid to specify the port number.

The root cause was that: java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Conflicting connection information. When the instance name is specified, it is invalid to specify the port number.

I have tried removing the port argument (port end up being set to 1433) and even setting the port to an empty string (port ends up set to 0).  Can anyone help set a blank port number when creating a named instance datasource using the API please?

API call code:

<!--- initialize the admin API --->
<cfset Administrator = CreateObject("component","cfide.adminapi.administrator")/>

<!--- login is always required --->
<cfset Administrator.login(adminpassword=url.apiPass,adminuserid="admin")/>

<!--- instantiate the data source object --->
<cfset Datasource = CreateObject("component","CFIDE.adminapi.datasource")/>
<cfif url.validateonly eq "false">
    <!--- set the datasource configuration settings --->
    <cfset args = {}/>
    <cfset args.name = url.name/>
    <cfset args.database = url.database/>
    <cfset args.host = url.host/>
    <cfif url.dbport eq "null">
        <cfset args.port = ""/>
    <cfelse>
        <cfset args.port = url.port/>
    </cfif>
    <cfset args.username = url.username/>
    <cfset args.password = url.password/>
    <cfset args.sendStringParametersAsUnicode = url.unicode/>
    <cfset args.encryptpassword = true/>
    <cfset args.selectmethod = "direct"/>
     ...
    <!--- Extra arguments in here etc. --->
     ...
    <!--- create MSSQL datasource --->
    <!--- allowed roles: data & services > data sources --->
    <cfset Datasource.setMSSQL(argumentCollection=args)/>
<cfelse>
    <!--- verify datasource --->
    <cfset verified = Datasource.verifyDSN(dsn=url.name,returnMsgOnError=true)/>
    <!--- if unverified show the error message --->
    <cfif IsBoolean(verified) And verified>
        <cfheader statusCode="200" statusText="OK" />
          <h2>true</h2> 
    <cfelse>
        <cfheader statusCode="418" statusText="Message" />
           <h2>false</h2>
     </cfif>
</cfif>
    </cfif>
</cfif>

[Carl Von Stetten]

It's unusual to have a named instance on the standard SQL Server port (1433).  That is generally used by the default instance (usually named MSSQLSERVER by default).  Named instances are added to that using non-standard ports.  Are you sure the database you are attempting to connect to isn't the default database on 1433?

Also, I've had very little success connecting to named instances by name (SERVERNAME\INSTANCENAME) - I almost always have to use the SERVERNAME and port number to get them to work.  There are some tricks you can try putting the instance name in a connection string that may work, and a quick Google of "coldfusion sql server connection string" should help you.

-Carl V.

[bmyrtil]

I am having this same issue. I use my server name to connect to MSSQL. Without the port number everything works fine. However, when using the datasource API, it either inserts a 0 or 1433 for the port which results in the following error when verifying: When the instance name is specified, it is invalid to specify the port number.

Were you able to get this to work? Anyone has any idea on how to pass a blank value for port without it being converted to 0 or 1433?

[Jake_Sweeney_490]

Here is the solution - it worked fine - don't include the quotes with the code snippet.

John

Bug#3784829 - Conflicting connection information. When the instance name is specified, it is invalid...

[Shpak_Aliaksandr]

go

Sql Server Configuration Manager

C:\Windows\SysWOW64\mmc.exe /32 C:\Windows\SysWOW64\SQLServerManager13.msc

     SQL Server Configuration Manager | Microsoft Docs

you need find TPC/IP and active he;