CODEWITHSUNDEEP
sql-serverconnection-stringalias
Mikhail
Mikhail

Reputation: 608

SQL Server client-side alias is not working

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?

Upvotes: 3

Views: 12161

Answers (3)

Kodak
Kodak

Reputation: 1829

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"

Upvotes: 2

Sumo
Sumo

Reputation: 4112

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.

Client Protocols

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

Alias - New Window

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

Upvotes: 2

Chains
Chains

Reputation: 13167

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

Upvotes: 1

Related Questions