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