Check ip and dns name

Question

T-sql

Is it possible to check if ip-address and dns name is the same ?

An IP address and DNS name are never the same. You're asking how to check whether an apple and an orange are the same type of fruit. Could you please clarify what you're trying to do? — jamieb, Feb 17 '10 at 08:13
He is asking if IP resolves to DNS name and vice versa. In some cases (especially for VPN users who connect and disconnect very often) it's possible that if you do IP to DNS and DNS to IP it won't be the same. But i guess in his case he just wants to know how to resolve IP to DNS and DNS to IP. — MadBoy, Feb 17 '10 at 08:55
jamieb, Ok, I have some network device. User can set an address of this device by ip-address or by dns name. I don't want to have duplicate addresses (ip-address corresponds to dns-name and vice versa) so I must to check it (t-sql). — Alexander Stalt, Feb 17 '10 at 09:05
did any of these answers help you? or are you looking for something else/more? — KM., Feb 18 '10 at 21:40

score 3 · Answer 1 · edited May 23 '17 at 10:27

Resolving the hostname to an IP address is something that can be easily done within a CLR stored procedure.

If you are new to CLR, you may want to start from the following articles:

As for the code to resolve the hostname, this can be easily done in C# as follows:

string hostname = "stackoverflow.com";
IPAddress[] ipList = Dns.GetHostAddresses(hostname);

foreach (IPAddress ip in ipList)
{
   // ... check each ip with an IP address you pass
   //     as a parameter.
}

If you are wondering why you receive a list of IP addresses, you may be interested in checking out the following Stack Overflow post:

Dns.GetHostEntry returns multiple IP addresses

score 2 · Accepted Answer · answered Feb 17 '10 at 08:50

HOST_NAME() returns the client's computer name.

This code resolves its ip address using ping and xp_cmdshell:

set nocount on
declare @ip varchar(255), @cmd varchar(100) 
set @cmd = 'ping -n 1 ' + HOST_NAME() 
create table #temptb (grabfield varchar(255)) 
insert into #temptb exec master.dbo.xp_cmdshell @cmd 
select @ip = substring(grabfield,  charindex('[',grabfield)+1,
 charindex(']',grabfield)-charindex('[',grabfield)-1) from #temptb  where left(grabfield,7) = 'Pinging' 
print @ip 
drop table #temptb
set nocount off

Source: http://www.sqlservercentral.com/Forums/Topic150196-8-1.aspx

score -1 · Answer 3 · answered Feb 17 '10 at 12:41

you can use this query to get a lot of info about the connection:

SELECT
    c.*,s.*
    FROM sys.dm_exec_sessions                 s
        INNER JOIN sys.dm_exec_connections    c ON s.session_id=c.session_id
    WHERE s.session_id = @@SPID

sys.dm_exec_connections.client_net_address = Host address of the client connecting to this server.

sys.dm_exec_connections.local_net_address = Represents the IP address on the server that this connection targeted. Available only for connections using the TCP transport provider.

you can try to see if there are duplicates IPs using a query like this:

SELECT
    c.client_net_address, COUNT(*) AS CountOf
    FROM sys.dm_exec_connections   c
    GROUP BY c.client_net_address
    HAVING count(*)>1

Check ip and dns name

3 Answers3