SQL Server client-side alias is not working

Question

The legacy vb6 application uses hardcoded connection strings like

Provider=SQLOLEDB.1;User ID=USER_NAME;password=USER_PASSWORD;Initial Catalog=DB_NAME;Data Source=OLD_SERVER_NAME;Network Library=DBMSSOCN

The goal is to forward this application to the NEW_SERVER_NAME with specific port. I've created alias but it is not working, application continues to use old server.

However if remove part Network Library=DBMSSOCN (I did it in test application) everything works fine.

Is there any chance to make it work with original connection string?

Answer 1

DBMSSOCN refers to the network library used for the connection. In this case, TCP/IP. Aliases can be configured for Named Pipes, TCP/IP, and VIA. When you remove the DBMSSOCN setting, it falls back to Named Pipes instead of TCP/IP. Make sure of two things (both in SQL Server Configuration Manager under the 32-bit and 64-bit SQL Native Client Configuration sections):

• Under Client Protocols, ensure TCP/IP is enabled.

• Under Aliases, make sure the alias you create is for the specific network library your connection string specifies. In your case, TCP/IP.

You may need to install the SQL Native Client 10.0 on the application server and change the connection string to use that version of the client before this will work. To install the new client, you'll need to install the SQL Server Tools from the installation of SQL Server 2008. The new connection string may look like the following (example is standard security from ConnectionStrings.com):

Provider=SQLNCLI10;Server=ServerAlias;Database=myDataBase;Uid=myUsername;Pwd=myPassword

Answer 2

cliconfg.exe which can be used to configure client alias when run on 64b machine only creates entry for 64b programs; if you don't have sql tools and want to create alias for both 32 and 64b programs create following entries in registry (below are contents of reg file with TCP alias):

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]
"oldserver\\oldinstance"="DBMSSOCN,newserver\\newinstance"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo]
"oldserver\\oldinstance"="DBMSSOCN,newserver\\newinstance"