How to retrieve machine IP adress via SQL Stored Procedure

Question

Hi, I wanted to know if we can retrieve IPAddresses of any machine (can be local or remote) via Microsoft SQL Stored Procedure WITHOUT using INSERT EXECUTE.

Answer 1

All you need as a starting point is:

SELECT client_net_address, local_net_address FROM sys.dm_exec_connections

Obviously, it needs a bit more work but sys.dm_exec_connections has both server and client IP addresses.

Edit:

I suggest you look at CLR code fi you want to gather information that SQL Server simply does not have within your restrictions.

Any pure T-SQL solution requires running a stored proc such as xp_cmdshell which requires INSERT EXEC.

A parameterised stored proc would have to be custom extended one to avoid INSERT EXEC.