Check ip and dns name

Question

T-sql

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

Answer 1

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:

• Writing Your First CLR Stored Procedure
• MSDN - Introduction to SQL Server CLR Integration

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

Answer 2

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

Answer 3

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