-
Title
Error - Failed to login to SQL Server - How to add a SQL Server 2005 Alias as a new connection -
Description
Aliases may be used to create a new connection to SQL Server, when previously created connections are failing to login, allowing LiteSpeed to correctly connect and function.
Aliases may also be used when connecting to multiple SQL server instances with unique server names,for servers withlong names or an IP address or is difficult to remember or type. If SQL server is run on a different port than the standard 1433, an alias will save the user fromtrying to remember the port number or having to specify the port number.By using an alias, you can connect to a sql server instance by typing in the server name assigned in the alias.
By configuring an alias, you can bypass the discovery phase that SQL Browser service goes through for each connection, since an alias has a pre-defined protocol and the port your server is listening on,therefore speeding up the connection. -
Cause
N/A
-
Resolution
WORKAROUND 1:
The SQL Server 2005 Configuration Manager allows you to create aliases to SQL server.
1. Start| Program files| Microsoft SQL Server 2005| Configuration Manager| SQL Server Configuration Manager
2. Go down to SQL Native Client Configuration and expand it.
3. Right click on the Aliases and click New Alias. The IP address of the server is not required.If the server hosts more than oneSQL Server, you canenter the instance name.From Books Online:
To set a SQL Server alias:
1.In theObject Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
2.Right-clickSQLServerAgent, and then clickProperties.
3.In the SQL Server Agent Properties dialog box, clickConnection, and type the alias name in theAliaslocal hostserver fieldto which SQL Server Agent should connect.
NOTE: SQL Server Agent will not work correctly unless you select an alias that refers to the local instance of SQL Server.If you are using cliconfg.exe (the same is called in SQL Server 2000 when you use the SQL Server Client Network Utility), you will get a dialog box showing the alias tab. The same alias created using the SQL Server Connection Managerare automatically visible using the cliconfg.exe as well - that is because both of them read this entry from the registry. These connection aliases can be found in the registry in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectToNote: The Alias Name should be the @@Servername. The serverName of a alias should be the IP Address
-
Attachments
-
Additional Information
This Solution includes contributions from:
http://sqlblog.net/post/2008/07/11/Connecting-to-SQL-server-instance-using-an-Alias-instead-of-the-server-name-or-IP-name.aspx
http://decipherinfosys.wordpress.com/2007/11/26/using-a-server-alias-to-connect-to-sql-server-instances/http://msdn.microsoft.com/en-us/library/ms175176(SQL.90).aspx