How to get sql server ip address
-
Can any one know abt this?
How to get sql server ip address
Ping –o <SQL SERVER NAME>
what is -o ?
-
edit: when i did ping /? from a command line, i do not see a -o option.
in 2008 and above, you can get it from some of the DMV.s or from some new connection property functions as well:
--2005+
select local_net_address,* FROM sys.dm_exec_connections
--Requires SQL 2008 +
SELECT
ConnectionProperty('net_transport') AS 'net_transport',
ConnectionProperty('protocol_type') AS 'protocol_type',
ConnectionProperty('auth_scheme') AS 'auth_scheme',
ConnectionProperty('local_net_address') AS 'local_net_address',
ConnectionProperty('local_tcp_port') AS 'local_tcp_port',
ConnectionProperty('client_net_address') AS 'client_net_address',
ConnectionProperty('physical_net_transport') AS 'physical_net_transport'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell, just out of curiosity, what would the result set look like on say a clustered SQL Server instance compared to standalone? I don't have the ability to check this on a clustered instance and I'm curious if the results are different. Perhaps you have a link to a BOL article that explains in more detail?
-
Scott D. Jacobson (7/30/2012)
Lowell, just out of curiosity, what would the result set look like on say a clustered SQL Server instance compared to standalone? I don't have the ability to check this on a clustered instance and I'm curious if the results are different. Perhaps you have a link to a BOL article that explains in more detail?Scott you can see a lot of variation in there, as the tcp_ip addresses can be different for each connection, whether you have your server listening to more than one IP address, etc. and of course depending on if you are connecting locally (named pipes makes the ip address for the server return null)
when i connect to my known 2008 cluster, these are the results, which is the clusters IP address of *.235 , and not the node IP (which are *. 230 and *.231, respectively..i seem to remember a witness is on *.232
/*
--Results:
net_transport protocol_type auth_scheme local_net_address local_tcp_port client_net_address physical_net_transport
TCP TSQL SQL 192.168.0.235 1433 192.168.0.55 NULL
*/
SELECT
ConnectionProperty('net_transport') AS 'net_transport',
ConnectionProperty('protocol_type') AS 'protocol_type',
ConnectionProperty('auth_scheme') AS 'auth_scheme',
ConnectionProperty('local_net_address') AS 'local_net_address',
ConnectionProperty('local_tcp_port') AS 'local_tcp_port',
ConnectionProperty('client_net_address') AS 'client_net_address',
ConnectionProperty('physical_net_transport') AS 'physical_net_transport'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell, thank you for the reply to satisfy my curiosity.
nagkarjun1, my apologies. I realize I sort of hijacked your thread 🙁
-
Hi,
Can I ask a dumb question.. 🙂
What will happen if the value of local_net_address is not what the external users are using to connect to the DB?
For example: The server has 2 IPs: lets call it 192.168.1.1 and another 10.0.1.1 (this is the IP everyone should be using to connect to the SQL Server)
Internally and externally via port 1433 connections are successful to 10.0.1.1.
When I run the query listed above if shows 192.168.1.1 as the local_net_address and not 10.0.1.1 as I expected it to.
What would the effect of that be? Would users struggle to connect even if all necessary ports are open? Even if SQL config manager is set up correctly?
I am asking because we are experiencing issues connecting remotely and I am trying to determine if this could be the problem?
-
Check firewall settings of the remote machine.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't." -
i would not put too much value in the local ip address; SQL's configured to listen to multiple ip addresses(including 127.0.0.1), and you can review them in the server configuration tool.
if you are having connectivity issues, it's going to be at the network layer, and not something you'll se at the SQL level, i think.
i THINK the ConnectionProperty returns the first IP4 address found in the configuration; if you think it's relavant, i guess you could remove an ip and put it back, i think that might make it move to the bottom of the list. test that on a developer machine first, obviously.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply