Return
-
Title
Error: "Failed to login to SQL Server (Error 49999)" -
Description
Litespeed Backup job fails with the following error exclusively on Windows 2008 servers.
Job '(DBA) Backup DB - Database_Name' : Step 2, 'Backup:
Database_Name' : Began Executing 'Date' 'Time'Msg 49999, Sev 19, State 1: Failed to login to SQL Server [SQLSTATE HY000]
SQL Server instance not using default port of 1433 but Browser service running. Named Pipes is disabled.Note that creating an Alias for Database Instance appears to resolve the issue. -
Cause
Windows 2008 uses by default IPv6 for internal TCP/IP communications causing Litespeed to attempt to connect to a local SQL instance on a IPv6 port. Connection will fail if the SQL instance is not configurred to use IPv6.
Windows 2003 on the other hand does not have this issue because it does not support IPv6 "out of the box" and communicates internally using IPv4. -
Resolution
Create a SQL alias pointing to the IP address and port used by the SQL instance or using the SQL server configuration manager applet, enable Named Pipes for both the server and client.
Additional Information:
Benefits of using aliases:
1) An alias can be used to define a user-defined name for connecting to a server -even if the IP address changes later on, there are no changes to the connection string. You just need to update the alias and the application will keep on working as before. You will not need to specify any instance name in the application connection string(s). And using an IP address directly in the alias definition can also save you some time in doing the DNS look-up.2) You can make connection to SQL Server using different protocols: TCP/IP, Named Pipes etc. and specify specific parameters like the TCP port or the pipe name etc.3) Aliases are also good for performance reasons. Since an alias has a pre-defined protocol, it can help you speed up the connection. Think about what happens when a connection is made to SQL Server from a client application. It has to try various protocols in the order that they are defined in your SQL Server Connection Manager (SQL 2005) or the SQL Server Client Network Utility (SQL 2000). If you already know which protocol and port your server is listening on, by configuring an alias you can by-pass the discovery phase that SQL Browser service goes through. In addition, if you know and have benchmarked your application using a specific protocol, you can standardize the alias technique across the board.Named Pipes protocol is disabled by default for remote connections. Only TCP is enabled.Generally, TCP/IP is preferred in a slow LAN, WAN, or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, usually much faster, frees up network stack resources, easy to use and configure. Issues that you may experience with Named Pipes includes unexpected disconnects.In SQL Server 2005, only the TCP/IP protocol supports Kerberos authentication, which overall is a better security protocol than NTLM. Named Pipes has to use NTLM.Named-pipes provide a way for inter-process communication to occur among processes running on the same machine. What named pipes give you is a way to send your data without having the performance penalty of involving the network stack. For named pipes, network communications are typically more interactive. A peer does not send data until another peer asks for it using a read command. A network read typically involves a series of peek named pipes messages before it begins to read the data. These can be very costly in a slow network and cause excessive network traffic, which in turn affects other network clients.It is also important to clarify if you are talking about local pipes or network pipes. If the server application is running locally on the computer running an instance of Microsoft SQL Server, the local Named Pipes protocol is an option. Local named pipes runs in kernel mode and is extremely fast.For TCP/IP Sockets, data transmissions are more streamlined and have less overhead. Data transmissions can also take advantage of TCP/IP Sockets performance enhancement mechanisms such as windowing, delayed acknowledgements, and so on, which can be very beneficial in a slow network. Depending on the type of applications, such performance differences can be significant. TCP/IP Sockets also support a backlog queue, which can provide a limited smoothing effect compared to named pipes that may lead to pipe busy errors when you are attempting to connect to SQL Server.In general, sockets are preferred in a slow LAN, WAN, or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, ease of use, and configuration options.