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?

Solution 1

The default TCP/IP port is 1433, but that's configurable. Here's a step-by-step that (hopefully) addresses at least that part of the problem you're facing. http://msdn.microsoft.com/en-us/library/ms177440.aspx

As for your alias, you might make sure that you created it on the client, not sql-server. (Not saying you did anything wrong, but I see that mistake sometimes...) http://support.microsoft.com/kb/289573

Solution 2

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

Solution 3

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"

Comments

[Mikhail]

Yes, I did it on the client and for both x64 and x86 configurations. I'm just wondering why alias works without "Network Library=DBMSSOCN" part of connection string and doesn't work otherwise. The problem I couldn't easily modify legacy application and remove this part. And I don't understand why it has such effect. There is no problem with alias. In my test application I have the same problem - it works without mentioned part and stops working with it. And it is easy to reproduce.

[Mikhail]

I'm using "SQL Server Client Network Utility". TCP/IP is enabled, alias is using TCP/IP. And again, alias working properly if I remove mentioned part. Process Monitor shows only TCP/IP connections.

[Sumo]

And, maybe a stupid question, but are you're creating the alias on the server where the application lives or are you creating it on the database server?

[Mikhail]

On the application server. Database server is on different box.

[Mikhail]

So, just to summarize: app + short connection string + alias -> new server; app + long connection string + alias -> old server. It is like this parts forces app to ignore alias completely. Previously it was default instance of server, not it is named on specific port. Alias uses server name + port.

[Sumo]

I'm wondering if you need to use the SQL Native Client v10 on the application server to correctly connect to the SQL 2008 box via OLE.

[Mikhail]

I've already installed SQL Server 2008 Native Client to application server if you are talking about that. Maybe it should be removed instead? Does it have any configuration tool? The problem is that connection string change is last resort and I need to avoid it with standard tools.

[Sumo]

Then, you should have the SQL Server Configuration Manager tool. If not, you will need to re-run the SQL Server installation on the app server and possibly choose more client options (not sure exactly which offhand at the moment).