Udostępnij za pośrednictwem

Connection Alias

  • Artykuł

SQL Server client stack has a facility to let user define connection aliases. Connection Alias can usually help user make connection eaiser, faster and with more convenience. However, if it's not used properly, it sometimes results in connectivity issues which might be difficult to isolate.

User can take advantage of connection alias in both MDAC and SNAC. The information is saved in registry. There are two tools can be used to define alias. One is SQL Server Connection Manager shipped with SQL Server 2005. The other one is SQL Server Client Network Utility shipped with Windows and can be found
at C:WINDOWSsystem32cliconfg.exe.

All connection alias information can be found under the following registry:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerClientConnectTo

Basically, alias can be used to:
1) define a easy-to-remember (or use) name of a real SQL Server name.
2) redirect SQL connection with SQL Server to another SQL Server instance on any machine.
3) make connection to SQL Server using a specific protocol (e.g. TCP, Named Pipe, etc) with specific parameter (e.g. TCP port, pipe name).

Case 3) is probably the most frequent usage. Because an alias always has a pre-defined protocol, it can speed up your connection. When making new connections, SQL server client try various protocols (Shared Memory, TCP, Named Pipe, VIA, etc) in a sequence. There is a pre-defined default protocols sequence, but user can change the sequence using the tools mentioned above. SQL Server Connection Manager is for SNAC and SQL Server Client Network Utility is for MDAC. If a connection fails with one protocol, we may try connection with another protocols if there are still time for login. So, if a user knows which protocol the server is listening on and they want to used it, he can define alias to use that protocol directly, saving the
time to try and fail with other protocols. Also, he can chose a protocol which has better performance on his system.

Two use cases:
a) A user can make connection to SQL Server named instance without having to specify the instance name in applications. For example, you have a named SQL Server instance NewInst on machine MyHost. You can define MyHostAlias to use Named Pipe protocol with the following Pipe name \MyHostpipeMSSQL$NewInstsqlquery,

b) A user wants to use TCP protocol and has already configured the SQL Server to listen a specific port (e.g. 3456) rather than the default port 1433. Then, he can define a alias to do so. User can also pecify IP address directly in alias, thus saving some time on DNS query.

In the above cases, since the TCP port and/or Pipe Name is known and have been saved with alias, user does not have to rely on SQL Browser to discovery the server, which saves time and exposes less security risk as well.

As you can see, alias has a lot of benefits. However, if not used properly, you may see issues hard to trouble-shoot. For example, you defined an alias called MyHost and let it connect to a SQL Server on MyHost using TCP. Later, your DBA decides to turn off TCP and let the server only listen on NP or change TCP port number, you may not be able to connect to MyHost unless you remember an alias is defined and delete/redefine the alias. It's usually very hard for a user to realize the alias is the root cause of the issue.

So, as a suggestion, you'd better always put some special words in your alias so that you know it's an alias at anytime.  

Xinwei Hong, SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Comments

  • Anonymous
    January 08, 2007
    Pour faire à l'article de Fabrice , je viens de trouver (enfin) l'outil de configuration réseau pour

  • Anonymous
    February 01, 2007
    I'm having issues using System.Transactions together with aliases. If I use an alias, the TransactionScope always promoted the transaction to a distributed transaction (DTC), but when using the actual servername (default instance), it correctly uses local transactions. Can you explain the reason for that? (I use SQL 2005)

  • Anonymous
    February 15, 2007
    I'm not familar with DTC. Check this webpage. http://www.vbdotnetheaven.com/UploadFile/mosessaur/TransactionScope12072006232808PM/TransactionScope.aspx It says: "If the first connection that you open in a transaction scope is to anything other than a SQL Server 2005 database, the transaction scope promotes the local transaction to a distributed transaction immediately. This immediate promotion occurs because the resource managers for these other databases do not support automatic promotion of local transactions to distributed transactions." It seems somehow it does consider a connection to an alias as to a SQL Server 2005 database. That's my guess though.

  • Anonymous
    February 28, 2007
    We are attempting to use a Content Switch VIP (Virtual IP) for ODBC DSN's pointing to Named SQL Instances in a Cluster.  This is so we can fail over to a Cluster located in a Disaster Recovery facility without amending DSN's (or Alias config) on several App Servers. It actually works fine, as long as we specify the Ports to connect to.  It seems the VIP's are incompatible with SQL Browser listening on UDP 1434.  Just wondering if anyone else has had same experience - maybe we're missing something?

  • Anonymous
    May 23, 2007
    I am deploying a Visual Basic 6.0 application who need a DSN. I will like make the DSN programatically using Windows Scripting Host. The problem tha i find, is tha by scripting i can not select the protocoll, who i wish use. Always use the default protocoll, it is Name Pipes. I will like establish TCP/IP how default protocoll programatically. Could you help me how i can stablish them, without use cliconf.exe. Thanks for you explanation about the SQL Server use protocoll, in the conection time.

  • Anonymous
    May 23, 2007
    You can force the protocol by using one of the protocol prefixes in front of the server name. For for example if the server is machine1, you can say:   Server=tcp:machine1 There are other prefixes, np for named pipes and lpc for local shared memory protocol. You can also specify the port like so:     Server=tcp:machine1,5555

  • Anonymous
    June 15, 2007
    The comment has been removed

  • Anonymous
    June 18, 2007
    Alias is used by client stack, so you have to add the alias on every client machine. The server does not (or does not need to) know the alias.

  • Anonymous
    July 29, 2007
    I love blog comments. They keep you honest and earnest. In response to my blog post Database Mirroring

  • Anonymous
    July 09, 2008
    Hi, Where the information you enter in client network utility is stored? In the registry? Can I change the server name for an alias programmatically? We have sharepoint 2007 using sql aliases, and the sql backend databases are mirrored. Since, sharepoint is not mirror aware we have a requirement to change the server name to new principle server (in case of failover) and this needs to be done automatically by a service running on sharepoint side when it detects there is a failover on sql mirror? Can somebody answer please..

  • Anonymous
    July 16, 2008
    Yes. You can configure alias programmatically. Please see http://blogs.msdn.com/sql_protocols/archive/2006/04/08/572057.aspx The alias entry is stored in registry as I mentioned in the post. Thanks. Xinwei

  • Anonymous
    December 14, 2008
    Can you explain a bit more about your statement. "SQLClient does not read information from registry and thus won't use alias" What do you mean by SQLClient I assume you are not referring to SNAC.

  • Anonymous
    December 15, 2008
    Yes, I meant SQLClient provider, not SNAC. But, my statement was wrong. SQLClient also reads and uses Alias info. I have updated the text. Thanks.

  • Anonymous
    September 25, 2009
    This posting and the thread is excellent. After spending hours and trying different combination suggested here, I was able to get the SQL Enterprise Manager to connect to the SQL server successfully. Environment that I had: Windows 2003 Standard Server R2 32 Bit English Firewall was On in the server MS SQL 2005 32 Bit I was using Windows XP client. The solution: Finally, the reason it worked is when I added sqlbrowser.exe and sqlservr.exe in the exception list in the Windows Firewall. I hope this helps. ... DB Please feel free to contact if you require assistance.

  • Anonymous
    March 05, 2012
    hey! i m getting a problem in connecting to local host. the error is: TITLE: Connect to Server

Cannot connect to (local).

ADDITIONAL INFORMATION: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

so what should I do? please lemme know its very urgent for me to connect to sql server.