0

I need to 'swap' an old database server (Windows Server 2008, SQL Server 2005) with a newer/better one (Windows Server 2012, SQL Server 2008) I tested this swap yesterday and everything worked on the new server (PHP pages, ASP.net website) except for vbscript scripts, and a product developed in MS Access.

The reason: The connection strings require the port to be included (On the old server they did not)

I can accept this if there is no way around it, but it will mean changing a lot of scripts, and being forced to involve the third party who developed the MS Access product (It would be a breach of contract to modify the product ourselves) so I am hoping to avoid that.

The server is using the default port, and is not set to use dynamic ports.

The server used to be part of a cluster but it is no longer.

The server has two instances (one dormant, services disabled, and one active)

I have tried as best as I can to match the settings in sql configuration manager between the old server and the new server.

I have tried creating an alias.

I have tried with sql browser on, and with sql browser off.

I have tried restarting services.

I may have tried other things that I'm forgetting (this was yesterday)

The only thing I haven't tried is removing the dormant instance. The reason for this is it looks like to do that you need to go through the motions of uninstalling sql server entirely, and so far I am uncomfortable trusting a googled source suggesting this in case It removes both instances or sql server entirely.

So is there a way to make it so that I can connect to this server without requiring the port (and generally without having to modify connection strings at all)?

ConString = "Provider=SQLOLEDB;Data Source=[server ip];Initial Catalog=somedb;" & _
    "Network=DBMSSOCN;User Id=****;Password=*****" 
set conn = CreateObject("ADODB.Connection") 
Conn.connectiontimeout=500 
Conn.commandtimeout=900 
conn.open ConString
Improve this question
9
  • 1
    I assume you can't change the Data Source attribute of the connection string without the same aforementioned hassle? (You should really be using the name of the Server / Instance instead of the IP FYI.) Do you know if this SQL Server Instance is setup as a Named Instance?
    – J.D.
    Jun 30, 2022 at 12:24
  • 1
    I'm hoping to avoid any change to the connection strings. Because yes it's the same hassle. I agree with you, but these connection strings already exist with the IP (and in some cases dns name) I will check if the server is set up as a named instance.
    – MrVimes
    Jun 30, 2022 at 12:27
  • .. I believe it is a named instance. There is a name after the hostname like this - db-03-dc\wwdr ... and I have tried putting that in the conn string (didn't work) but even if it worked it's the same issue I'm trying to avoid.
    – MrVimes
    Jun 30, 2022 at 12:41
  • 1
    Yea so unfortunately that probably is your issue, the fact you're using a Named Instance. What is the port that it's running on / that works when you enter it in the connection string?
    – J.D.
    Jun 30, 2022 at 13:04
  • 1433 (so it's the default mssql port). Yeah I guess I am going to have to bite the bullet and update all the connection strings. I'm not comfortable with trying to remove the second instance.
    – MrVimes
    Jun 30, 2022 at 13:08

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.