Service Broker: How can I detect the script is executed on the wanted machine?

Question

My SQL scripts are generated from templates. They contain the IP address. There can be a lot of them and the operator can by mistake execute the incorrect script. How the script can check if it was launched on the correct machine? (Otherwise, I would like to print the message and exit.)

Is it possible at all?

Thanks, Petr

Answer 1

Updated Answer

Here's a script that I found from http://weblogs.sqlteam.com/peterl/archive/2008/07/16/How-to-get-IP-address.aspx, with some modifications to better suit your needs:

DECLARE @TargetIpAddress varchar(15);
SET @TargetIpAddress = '127.0.0.1'; --<== The IP address of the server you want.

DECLARE @Interfaces TABLE
(
    RowID int IDENTITY(0, 1)
   ,Interface char(38)
   ,IP varchar(15)
);

INSERT @Interfaces ( Interface )
  EXEC master..xp_regenumkeys N'HKEY_LOCAL_MACHINE',
       N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces';

DECLARE @RowID int
       ,@IP varchar(15)
       ,@Key nvarchar(200);

SELECT @RowID = MAX(RowID)
  FROM @Interfaces;

WHILE @RowID >= 0
BEGIN
    SELECT @Key = N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces\' + Interface
     FROM @Interfaces
    WHERE RowID = @RowID;

    EXEC master..xp_regread N'HKEY_LOCAL_MACHINE', @Key, N'DhcpIPAddress', @IP OUTPUT;

    IF @IP <> '0.0.0.0'
    UPDATE @Interfaces
       SET IP = @IP
     WHERE RowID = @RowID;

    SET @RowID = @RowID - 1;
END;

IF NOT EXISTS (SELECT IP FROM @Interfaces WHERE IP = @TargetIpAddress)
BEGIN
    DECLARE @ErrorMessage varchar(2000);
    SET @ErrorMessage  = 'This is not the correct server. This server does not have an IP address of %s.';
    SET @TargetIpAddress = ISNULL(@TargetIpAddress, 'NULL');
    RAISERROR(@ErrorMessage, 16, 1, @TargetIpAddress);
END

-- The rest of the script...

---

Original Answer

It seems like using the Server\Instance name instead of the IP address would be easier to work with, and less likely to break if the server(s) were assigned different IP addresses at some later date.

-- You can get the instance name like this:
SELECT @@SERVERNAME +'\'+ @@SERVICENAME AS 'Instance';

-- Although, you might prefer this instead:
SELECT CAST(SERVERPROPERTY('MachineName') AS nvarchar(128))
      +COALESCE('\'+CAST(SERVERPROPERTY('InstanceName') AS nvarchar(128)), '');

-- NetBIOS name of the local computer on which the instance of SQL Server
-- is currently running.
-- If the instance of SQL Server is in a failover cluster and you want to obtain
-- the name of the failover clustered instance, use the MachineName property. 
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS');

You can find detailed information about the SERVERPROPERTY function at MSDN: SERVERPROPERTY (Transact-SQL). Although, this function doesn't provide any way to obtain the IP address of the server/instance - there is no built-in function that provides this information.

Answer 2

Create Procedure P_GetIPAddresses (@IPS varchar(4000) out)
as
begin
Select @IPS=''
Create TABLE #temp (Line varchar(200))
Insert #temp exec master..xp_cmdshell 'ipconfig'
Select @IPS = @IPS + Coalesce(RTRIM(Replace(SubString(Line,1,CharIndex(':',line)-1)  ,'.','')) + SubString(Line,CharIndex(':',line) ,200),'')
from #temp
where upper (Line) like '%ADRESS%'
--SELECT * from #tmp --DEBUG
DROP TABLE #temp

end 

Usage if xp_cmdshell is disabled, parsing Adresses is up to you ...

EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO


Declare @IPS Varchar(4000)

exec P_GetIPAddresses @IPS out
Select  @IPS

EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Answer 3

I could provide a xp_GetIP.dll, yet only tested with SQL-Server 2005 32-bit which you could register via

EXEC sp_addextendedproc xp_GetIP, 'C:\temp\xpGetIP.dll'

Call would be

Declare @IP varchar(100)

exec xp_GetIP @IP output
print @IP

output only : 192.168.69.69

EDIT: Working to on SQL-Server 2008 R2 64-bit als 64bit DLL

Answer 4

Sorry for reposting, but annoyed of allways missing oportunities of accessing systemadata inside SQL-Server I decided to write a Extended Stored Procedure as Bridge to WMI.

Download http://bummisoft.de/download/XP_WMI.zip

the call would be:

exec xp_wmiv3 'Select * from Win32_NetworkAdapterConfiguration where IPEnabled=TRUE'

the DLL is available for 32bit and 64bit SQLServers and free for non comercial use.

Installation:

EXEC sp_addextendedproc xp_wmiv3, '<Your Path>\XP_WMIV3_DLL.dll'

eg: EXEC sp_addextendedproc xp_wmiv3, 'C:\DLLs\XP_WMIV3_DLL.dll'

Uninstallation

EXEC sp_dropextendedproc xp_wmiv3

Usage

e.g.

exec xp_wmiv3 'SELECT * FROM Win32_Volume'
exec xp_wmiv3 'SELECT * FROM CIM_Userdevice where Name like "%HID%"'



create table #tmp(
    Domain varchar(255),
    Name varchar(255),Sid varchar(255)
)
insert into #tmp 
exec xp_wmiV2 'SELECT Domain, SID, Name FROM Win32_UserAccount where Status = "OK"'
select * from #tmp
drop table #tmp

Currently tested under SQL-Server 2005 and SQL-Server 2008.