What is the MSSQL Server IP address?

  • Often, you will need to get the Microsoft SQL Server IP address, may it be for registration, copy protection,...
  • The simplest is to use the Microsoft Windows function of ipconfig.
  • xp_cmdshell allows you to run all kind of DOS command.
  • Before using xp_cmdshell, you will need to enable it.
  • Enabling xp_cmdshell in SQL Server is a security risk!
  • Only members of the sysadmin role can execute xp_cmdshell, but you can grant that right to other users.

Enabling xp_cmdshell

  1. EXEC SP_CONFIGURE 'show advanced options', 1
  2. go
  3. RECONFIGURE
  4. go
  5. EXEC SP_CONFIGURE 'xp_cmdshell', 1
  6. go
  7. RECONFIGURE
  8. go
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

Getting the IP address

  1. CREATE TABLE #ipconfig(captured_line VARCHAR(255))
  2. INSERT INTO #ipconfig
  3.   EXECUTE xp_cmdshell 'ipconfig /all'
  4. go
(26 row(s) affected)

The principle is fairly basic:

  1. Create a temporary table to capture the output, line 1: create table #ipconfig(captured_line varchar(255)). The # indicates that the table ipconfig is a temporary table that will be store in the tempdb database.
  2. Run the command, line 3: execute xp_cmdshell 'ipconfig /all'
  3. Save the output of the command to a temporary table with line 2: insert into #ipconfig. The insert must be done before the execute of the command. This will capture the output the the DOS shell command.

Data used

  1. SELECT captured_line FROM #ipconfig;
  2. go
captured_line
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Windows IP Configuration



   Host Name . . . . . . . . . . . . : SQLServer

   Primary Dns Suffix  . . . . . . . : 

   Node Type . . . . . . . . . . . . : Unknown

   IP Routing Enabled. . . . . . . . : No

   WINS Proxy Enabled. . . . . . . . : No

   DNS Suffix Search List. . . . . . : sqlhacks.com



Ethernet adapter Local Area Connection 3:



   Connection-specific DNS Suffix  . : sqlhacks.com

   Description . . . . . . . . . . . : VMware Accelerated AMD PCNet Adapter #2

   Physical Address. . . . . . . . . : 00-0C-29-05-CE-F4

   DHCP Enabled. . . . . . . . . . . : Yes

   Autoconfiguration Enabled . . . . : Yes

   IP Address. . . . . . . . . . . . : 192.168.2.117

   Subnet Mask . . . . . . . . . . . : 255.255.255.0

   Default Gateway . . . . . . . . . : 192.168.2.1

   DHCP Server . . . . . . . . . . . : 192.168.2.1

   DNS Servers . . . . . . . . . . . : 192.168.2.14

                                       209.53.4.130

   Lease Obtained. . . . . . . . . . : Monday, November 05, 2007 1:06:11 PM

   Lease Expires . . . . . . . . . . : Wednesday, November 07, 2007 1:06:11 PM

NULL

(26 row(s) affected)

Analyze the IP address

  1. SELECT captured_line FROM #ipconfig
  2. WHERE captured_line like '%IP Address%';
  3. go
captured_line
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   IP Address. . . . . . . . . . . . : 192.168.2.117


(1 row(s) affected)
  • We know that the IP address is on the line that contains: IP Address

Parse the IP Address

  1. SELECT CAST(PARSENAME(SUBSTRING(captured_line,40,15),4) AS VARCHAR(4)) AS '1st octet',
  2.        CAST(PARSENAME(SUBSTRING(captured_line,40,15),3) AS VARCHAR(3)) AS '2nd octet',
  3.        CAST(PARSENAME(SUBSTRING(captured_line,40,15),2) AS VARCHAR(3)) AS '3rd octet',
  4.        CAST(PARSENAME(SUBSTRING(captured_line,40,15),1) AS VARCHAR(3)) AS '4th octet'
  5. FROM #ipconfig WHERE captured_line like '%IP Address%';
1st octet 2nd octet 3rd octet 4th octet
--------- --------- --------- ---------
192       168       2         117

(1 row(s) affected)
  1. The IP Address is divided in 4 Octets.
  2. Line 1: the cast() is to make sure that display is readable, instead of the standard 255 character wide.
  3. The parsename divides any 4 partname that is separated by a period. It was originally designed for getting the table elements, such as owner, schema, table...
  • Don't forget to drop the temporary table, so that the script will run properly next time: 
drop table #ipconfig