Search code examples
sql-serverdatabase-administration

Identifying the IP,PORT and Instance of a SQL Server Instance

I am trying to connect to SQL server instance in a remote system(a VPN has been set up between my machine and the server). The IP address I am supposed to connect to is 192.168.1.X, which I can ping from my machine. When I RDP into the server hosting the SQL server and run in Sql server studio:

USE master GO xp_readerrorlog 0, 1, N'Server is listening on', 'any', NULL, NULL, N'asc' GO

I get the following: LogDate ProcessInfo Text 2016-03-05 12:21:37.630 Server Server is listening on [ 'any' 12345], 2016-03-05 12:21:37.630 Server Server is listening on [ 'any' 12345].

Question 1: Does this mean that the server is listening to any queries in ipv6 and ipv4 IP for the port 12345?

Question 2: When I check the SQL TCP/IP setting in the host server, I see that the ipv6 IP is 127.0.0.Y. Should it not be 192.168.1.X? How do I find out what it the IP I am supposed to connect to?

TCP Dynamic ports

Solution

  • RDP into the server, and then run the SQL Configuration Manager. Open up SQL Native Client 11.0 Configuration (or whatever version it is if not 11) and select Client Protocols under it. In the right-hand pan el, select TCP/IP, right-click it and select Properties.

    enter image description here

    You can see that it shows the port it's ljstening on (1433 here, which is the default). If the port is not 1433 you can sepcify it in the connection string as Data Source=servername,portnumber e.g.Data Source=MyServerName,12345 (if 12345 is the port).

    BTW, check that TCP/IP is enabled: if it's not then the port doesn't really matter!