SQL Server – DNS NSLOOKUP and Resolve IP

24 August,2012 by Jack Vamvas

Resolving an IP address through NSLOOKUP is a useful  trick. The NSLOOKUP tool is normally used as a command line tool to test DNS servers. There are certain circumstances in SQL Server , where it can help resolve some problems

Xp_cmdshell needs to be available to the logon , which raises questions around security. It’s normally a privilege allocated on Production servers . Careful consideration should be given to enabling the xp_cmdshell. Most Production level SQL Server Security Policies  won’t allow this privilege.

Read more on Xp_cmdshell – the most dangerous extended stored procedure on the planet

In the example , SQL Server executes xp_cmdsell , the results go into a temp table. Some string manipulation may be needed to return substrings.

I used this code to implement a Logon Trigger – to resolve an ip address. Passing the ip address returned by the ClientHost parameter – to return the computer name of the  client request and block the user from creating a user session.

 

DECLARE @xpCmd NVARCHAR(100)
DECLARE @ip varchar(100)
SET @ip  = '10.100.144.60'
SET @xpCmd = 'NSLOOKUP ' + @ip

CREATE TABLE #tmp1 
(nslookup_out NVARCHAR(100))

INSERT INTO #tmp1
EXEC master.dbo.xp_cmdshell @xpCmd

SELECT * FROM #tmp1

DROP TABLE #tmp1

----output
---Server:  ddd.mydomain.sub.net
---Address:  11.111.121.51
---NULL
---Name:    server1.mydomain.sub.net
---Address:  10.100.144.60
---NULL
---NULL

 Related Posts on managing SQL Server Security

SQL Server – Restrict SQL Server Logons by IP with EVENTDATA and SQL Logon Trigger

How to create a SQL Server Security Audit


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server – DNS NSLOOKUP and Resolve IP


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer