Stephan Hurni Consultant Trivadis AG, SQL Server notes from the

SQL Server default port 1434

Port / May 11, 2021

In some installations of SQL Server, connecting to the Database Engine from another computer is not enabled unless an administrator uses Configuration Manager to enable it. To enable connections from another computer:

  1. Open SQL Server Configuration Manager, as described earlier.
  2. Using Configuration Manager, in the left pane expand SQL Server Network Configuration, and then select the instance of SQL Server that you want to connect to. The right-pane lists the connection protocols available. Shared Memory is normally enabled. It can only be used from the same computer, so most installations leave Shared Memory enabled. To connect to SQL Server from another computer you will normally use TCP/IP. If TCP/IP is not enabled, right-click TCP/IP, and then click Enable.
  3. If you changed the enabled setting for any protocol you must restart the Database Engine. In the left pane select SQL Server Services. In the right-pane, right-click the instance of the Database Engine, and then click Restart.

Testing TCP/IP Connectivity

Connecting to SQL Server by using TCP/IP requires that Windows can establish the connection. Use the ping tool to test TCP.

  1. On the Start menu, click Run. In the Run window type cmd, and then click OK.
  2. In the command prompt window, type ping and then the IP address of the computer that is running SQL Server. For example, ping 192.168.1.101 using an IPv4 address, or ping fe80::d51d:5ab5:6f09:8f48%11 using an IPv6 address. (You must replace the numbers after ping with the IP addresses on your computer which you gathered earlier.)
  3. If your network is properly configured you will receive a response such as Reply from followed by some additional information. If you receive an error such as Destination host unreachable. or Request timed out. then TCP/IP is not correctly configured. (Check that the IP address was correct and was correctly typed.) Errors at this point could indicate a problem with the client computer, the server computer, or something about the network such as a router. The internet has many resources for troubleshooting TCP/IP. A resonable place to start, is this article from 2006, How to Troubleshoot Basic TCP/IP Problems.
  4. Next, if the ping test succeeded using the IP address, test that the computer name can be resolved to the TCP/IP address. On the client computer, in the command prompt window, type ping and then the computer name of the computer that is running SQL Server. For example, ping newofficepc
  5. If you could ping the ipaddress, but noww receive an error such as Destination host unreachable. or Request timed out. you might have old (stale) name resolution information cached on the client computer. Type ipconfig /flushdns to clear the DNS (Dynamic Name Resolution) cache. Then ping the computer by name again. With the DNS cache empty, the client computer will check for the newest information about the IP address for the server computer.
  6. If your network is properly configured you will receive a response such as Reply from followed by some additional information. If you can successfully ping the server computer by IP address but receive an error such as Destination host unreachable. or Request timed out. when pinging by computer name, then name resolution is not correctly configured. (For more information, see the 2006 article previously referenced, How to Troubleshoot Basic TCP/IP Problems.) Successful name resolution is not required to connect to SQL Server, but if the computer name cannot be resolved to an IP address, then connections must be made specifying the IP address. This is not ideal, but name resolution can be fixed later.

Testing a Local Connection

Before troubleshooting a connection problem from another computer, first test your ability to connect from a client application installed on the computer that is running SQL Server. (This will keep firewall issues out of the way.) This procedure uses SQL Server Management Studio. If you do not have Management Studio installed, see Download SQL Server Management Studio (SSMS). (If you are not able to install Management Studio, you can test the connection using the sqlcmd.exe utility which is installed with the Database Engine. For information about sqlcmd.exe, see sqlcmd Utility.)

  1. Logon to the computer where SQL Server is installed, using a login that has permission to access SQL Server. (During installation, SQL Server requires at least one login to be specified as a SQL Server Administrator. If you do not know an administrator, see Connect to SQL Server When System Administrators Are Locked Out.)
  2. On the Start page, type SQL Server Management Studio, or on older versions of Windows on the Start menu, point to All Programs, point to Microsoft SQL Server, and then click SQL Server Management Studio.
  3. In the Connect to Server dialog box, in the Server type box, select Database Engine. In the Authentication box, select Windows Authentication. In the Server name box, type one of the following:
Connecting to: Type: Example:
Default instance The computer name ACCNT27
Named Instance The computer name\instance name ACCNT27\PAYROLL
Note

When connecting to a SQL Server from a client application on the same computer, the shared memory protocol is used. Shared memory is a type of local named pipe, so sometimes errors regarding pipes are encountered.

If you receive an error at this point, you will have to resolve it before proceeding. There are many possible things that could be a problem. Your login might not be authorized to connect. Your default database might be missing.

Some error messages passed to the client intentionally do not give enough information to troubleshoot the problem. This is a security feature to avoid providing an attacker with information about SQL Server. To view the complete information about the error, look in the SQL Server error log. The details are provided there. If you are receiving error 18456 Login failed for user, Books Online topic MSSQLSERVER_18456 contains additional information about error codes. And Aaron Bertrand's blog has a very extensive list of error codes at Troubleshooting Error 18456. You can view the error log with SSMS (if you can connect), in the Management section of the Object Explorer. Otherwise, you can view the error log with the Windows Notepad program. The default location varies with your version and can be changed during setup. The default location for SQL Server 2016 is C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Source: docs.microsoft.com