Mark B. – tech

How can I get the IP Address of the connected client in SQL Server ?

source : http://www.sqlserver2005.de/Articles/3/


In SQL Server 2005 there are more sophisticated ways to get the IPAddress, one is querying the dynamic management view sys.dm_exec_connections.

SELECT *
FROM sys.dm_exec_connections

Though you can see that in some cases NO IP Address is displayed in there, for example if the TCP protocol is NOT used for connecting (in the case below the Shared Memory protocol is used instead).

Therefore you can use the new features of a the integrated CLR and User definied functions. In the following example I wanted to show you two simple ways to get the IPAddress using functionalities of the .NET Framework:

Getting a single address

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetSingleIpaddress(string Hostname)
{
IPAddress[] ipadresses = System.Net.Dns.GetHostAddresses(Hostname);
if (ipadresses.Length > 0)
return ipadresses[0].ToString();
else return null;
}

Deployed on the server, it can it be used with:

SELECT dbo.GetSingleIpaddress(HOST_NAME())

which evaluates to:

Getting all available IPaddresses for the client

 [Microsoft.SqlServer.Server.SqlFunction(TableDefinition=”IPAdress NVARCHAR(17)”,
FillRowMethodName=”InitIpAdresses”)          ]
public static IEnumerable GetIpadresses(string Hostname)
{
return System.Net.Dns.GetHostAddresses(Hostname);
}public static void InitIpAdresses(object obj, out string _IPAddress)
{
IPAddress ipaddress = (IPAddress)obj;
_IPAddress = ipaddress.ToString();
}

Deployed on the server,it can it be used with:

SELECT * FROM dbo.GetIpadresses(HOST_NAME())

which evaluates to:

The performance ist significant better, the responsive ping needs about 20ms, the non-responsive ping needs about 1sec, so choosing this solution could be a great benefit for you, because you even used this for inline functions.

Advertisements
This entry was published on July 25, 2012 at 11:45 am and is filed under IT, IT - Tutorial, News / Headlines / Update, Programming, Tutorial. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: