In many organizations, there is a question how to block unwanted applications (viruses, torrent etc) for the proxy server Microsoft TMG. In this article I show an example of application lock "torrent" Microsoft TMG.
Background: We have a proxy server Microsoft TMG and it stores logs in Microsoft SQL
1. First, install the server "SQL Server Managment Studio"
2. Connect the "SQL Server Managment Studio" to the SQL server
create a function (convert IP address from HEX to String
http://blogs.technet.com/b/isablog/archive/2009/06/04/isa-bpa-7-and-forefront-tmg-for-windows-essential-business-server.aspx) New query - run this:
CREATE FUNCTION [dbo].[fnIpAddressToText]
(
@Ipv6Address [uniqueidentifier]
)
RETURNS varchar(40) AS
BEGIN
DECLARE @strInAddress varchar(40)
DECLARE @strOutAddress varchar(40)
SET @strInAddress = LOWER(CONVERT(varchar(40), @Ipv6Address))
SET @strOutAddress = ''
IF (SUBSTRING(@strInAddress, 10, 4) = 'ffff')
BEGIN
-- ipv4 (hex to int conversion)
DECLARE @IsNum int, @ZERO int, @IsAlpa int
set @ZERO = ASCII('0')
set @IsNum = ASCII('9')
set @IsAlpa = ASCII('a') - 10
DECLARE @intH int, @intL int
SET @intH = ASCII(SUBSTRING(@strInAddress, 1, 1))
IF (@intH <= @IsNum) SET @intH = @intH - @ZERO ELSE SET @intH = @intH - @IsAlpa
SET @intL = ASCII(SUBSTRING(@strInAddress, 2, 1))
IF (@intL <= @IsNum) SET @intL = @intL - @ZERO ELSE SET @intL = @intL - @IsAlpa
SET @strOutAddress = CONVERT(varchar(3), @intH * 16 + @intL) + '.'
SET @intH = ASCII(SUBSTRING(@strInAddress, 3, 1))
IF (@intH <= @IsNum) SET @intH = @intH - @ZERO ELSE SET @intH = @intH - @IsAlpa
SET @intL = ASCII(SUBSTRING(@strInAddress, 4, 1))
IF (@intL <= @IsNum) SET @intL = @intL - @ZERO ELSE SET @intL = @intL - @IsAlpa
SET @strOutAddress = @strOutAddress + CONVERT(varchar(3), @intH * 16 + @intL) + '.'
SET @intH = ASCII(SUBSTRING(@strInAddress, 5, 1))
IF (@intH <= @IsNum) SET @intH = @intH - @ZERO ELSE SET @intH = @intH - @IsAlpa
SET @intL = ASCII(SUBSTRING(@strInAddress, 6, 1))
IF (@intL <= @IsNum) SET @intL = @intL - @ZERO ELSE SET @intL = @intL - @IsAlpa
SET @strOutAddress = @strOutAddress + CONVERT(varchar(3), @intH * 16 + @intL) + '.'
SET @intH = ASCII(SUBSTRING(@strInAddress, 7, 1))
IF (@intH <= @IsNum) SET @intH = @intH - @ZERO ELSE SET @intH = @intH - @IsAlpa
SET @intL = ASCII(SUBSTRING(@strInAddress, 8, 1))
IF (@intL <= @IsNum) SET @intL = @intL - @ZERO ELSE SET @intL = @intL - @IsAlpa
SET @strOutAddress = @strOutAddress + CONVERT(varchar(3), @intH * 16 + @intL)
END
ELSE
BEGIN
-- ipv6
SET @strOutAddress = @strOutAddress + SUBSTRING(@strInAddress, 1, 4) + ':'
+ SUBSTRING(@strInAddress, 5, 4) + ':'
+ SUBSTRING(@strInAddress, 10, 4) + ':'
+ SUBSTRING(@strInAddress, 15, 4) + ':'
+ SUBSTRING(@strInAddress, 20, 4) + ':'
+ SUBSTRING(@strInAddress, 25, 4) + ':'
+ SUBSTRING(@strInAddress, 29, 4) + ':'
+ SUBSTRING(@strInAddress, 33, 4)
END
---- guid sample '6F9619FF-8B86-D011-B42D-FFF34FC964FF'
RETURN @strOutAddress
END
3. Create rules Microsoft TMG blocking Internet access for the computer set "deny_pcs"
4. Create folder c:\scripts and powershell script on the TMG server. In a script, you must specify:
YourSQL.blogspot.com
SQLport
YourArray
cd c:\scripts
$ServerInstance = "YourSQL.blogspot.com\msfw,SQLport"
$FPCobjs = New-Object -comObject FPC.root
$FPCobj = $FPCobjs.arrays | where {$_.Name -eq "YourArray"}
$Database = "master"
$ConnectionTimeout = 30
$QueryTimeout = 12000
$Query = "declare @dbname nvarchar(255)
declare @db cursor
set @db = CURSOR FOR select name from sys.databases where owner_sid<>0x01
USE tempdb
IF EXISTS (SELECT 1
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE TABLE_NAME like '%#SampleTableApps%')
DROP TABLE [tempdb].[dbo].[#SampleTableApps]
CREATE TABLE [tempdb].[dbo].[#SampleTableApps](
[app] [varchar](max) NULL,
[iphex] [uniqueidentifier] NULL,
[username] [varchar](max) NULL
) ON [PRIMARY]
OPEN @db
FETCH NEXT FROM @db into @dbname
WHILE (@@FETCH_STATUS = 0)
BEGIN
declare @Cmd nvarchar(max)
set @Cmd=N'IF (EXISTS (SELECT *
FROM ['+@dbname+'].INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ''dbo''
AND TABLE_NAME = ''FirewallLog''))
BEGIN
insert into [tempdb].[#SampleTableApps] (app,iphex,username) select DISTINCT clientagent, SourceIP, ClientUserName from ['+@dbname+'].dbo.FirewallLog
END
IF (EXISTS (SELECT *
FROM ['+@dbname+'].INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ''dbo''
AND TABLE_NAME = ''WebProxyLog''))
BEGIN
insert into [tempdb].[#SampleTableApps] (app,iphex,username) select DISTINCT clientagent, ClientIP, ClientUserName from ['+@dbname+'].dbo.WebProxyLog
END'
exec sp_executesql @Cmd
FETCH NEXT FROM @db INTO @dbname
END
CLOSE @db
DEALLOCATE @db
select app, [master].[dbo].fnIpAddressToText(iphex) as ip, username from [tempdb].[#SampleTableApps]"
$conn=new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$apps = $ds.Tables[0].Rows
$conn.Close()
if ($apps) {$apps | Export-Csv .\app1.csv -Encoding utf8 -NoTypeInformation}
$user_torrent = $apps | where {$_.app -match "torrent"}
$user_torrent_sort = $user_torrent | Sort username,ip -unique
if ($user_torrent_sort) {$user_torrent_sort | Export-Csv .\app_torrent.csv -Encoding utf8 -NoTypeInformation}
$CS_deny_pcs = $FPCobj.RuleElements.ComputerSets | where {$_.name -eq "deny_pcs"}
foreach ($pc in $CS_deny_pcs.computers) {
$CS_deny_pcs.computers.remove($pc.name)
}
$CS_deny_pcs.computers.save()
foreach ($line in $user_torrent_sort) {
if ($line.ip -notmatch ":") {
$CS_deny_pcs.computers.add($line.username,$line.ip)
}
}
$CS_deny_pcs.computers.save()
5. Now you can schedule the script eg each hour
