Multiple ports listed in SQL Server connection string

Question

I have a legacy VB6 app where the servername, databasename, username, etc are defined in an INI file, but the port number for the connection string (the default 1433) is hard coded in the app. It's being moved to a new sql server back end that runs off a different port number. I'm trying to avoid having to alter and recompile the application which entails signifigant retesting, documentation, etc. I tried altering the INI file so that for the new server I have put in: SERVERNAME\INSTANCE,NEWPORTNUMBER

This effectively builds the connection with Data Source = SERVERNAME\INSTANCE,NEWPORTNUMBER,1433;

This appears to work correctly as it connects to the database when I run the app. It appears to me that the ,1433 portion is being ignored. Is this a valid assumption or will this cause me some problem I'm not seeing here?

EDIT: The string way the connection string is built in the VB6 code is:

ConnectString = "Provider=MSDataShape;Trusted_Connection=Yes;Data Source=" & SERVER & ",1433;Initial Catalog=" & DATABASE & ";Data Provider=SQLOLEDB.1;Extended Properties=""Network=DBMSSOCN"""

with the SERVER & DATABASE values pulled from the INI file.

Answer 1

I'm not sure that every library that uses the connection string would necessarily parse it the same way. I would think there could be one library that parses that connection string and just drops off the last port number and another that throws an error for an invalid port number perhaps. What libraries are using that connection string?

If you're using integrated security, then maybe you could set the Data Source in the INI file to say, "SERVERNAME,PORT; Password=" and let the SQL server ignore the Password key which is unused with integrated security. That is if the code that constructs the connection string doesn't check for stuff like that. Oh, Will Rickards said this already also it seems.

Answer 2

Port 1433 would normally override the INSTANCENAME. (MS blog Reference and another)

3 options I see:

• The instance name is being ignored, and you're connecting to the default instance on port 1433
• The named instance listens on port 1433
• You have a client alias (not convinced about this one)

Answer 3

Can we see the code that creates the connection string?

Is there a network reason you need to use TCP/IP to connect to sql server?

My guess is that you just need to include a ; after the servername/instance name and just leave the port number out altogether. This will cause the port number to be extraneous data in the connection string. Which I think is just ignored. You can test creating an connection string yourself by creating a test.udl file and double clicking it - follow the wizard. After your done the connection string is in the udl file which you can view with notepad.

And of course if you are looking for the syntax for connection strings, you can look them up on connectionstrings.com

Answer 4

1. Download a free HEX editor
2. Save a copy of your original exe somewhere safe
3. Open the exe in the HEX editor
4. Find the 1433 and change it to your new port number

Once it works, you don't have to retest everything (like you would on a recompile)
If the new port number is not four digits it is trickier (path of least resistance: change it to a four digit port)

I have changed connection strings this way in the past. A VB6 exe always uses DBCS so you may have to play with the hex editor until you figure out how to use the search feature in the right way.

If the port number is stored as an integer it may be trickier to find, but still possible (look for strings near it for your clues).

Answer 5

Apparently this app is quickly hacked together. Try to hack it back with a simple connect-string-injection like SERVER="{your_server},{your_port};FooBar="