Removing IP lookup from Greylisting
Document #:10545
Applies To:
Synopsis:
Large mail provider such as Office365 and Gmail often use a different Source IP for each attempt to deliver email destined for your server.
By default Greylisting will reject email from a previously unseen [sender, receiver, IP] triplet (see related documents below for more information on how Greylisting works)
This can cause significant delays in receiving email from these providers as the delivery must be re-attempted a number of times until a known IP is used again.
More Information:
To get around the Greylisting delays from large email providers it is often disabled on the sever. However users who run a SQL Server configuration backend can edit the Greylist check stored procedure to exclude the IP from the check so that only the [sender, receiver] pair is checked to see if it has been seen before.
While this change will reduce the effeteness of Greylisting, it provides a compromise between no protection and long delays.
To remove the IP from the check run the following script against your MailSite DB:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[LookupGreylistTriplet] (
@HostAddress int,
@ReturnPathLocalPart varchar(30),
@ReturnPathDomain varchar(30),
@RecipientLocalPart varchar(30),
@RecipientDomain varchar(30)
) AS
BEGIN
DECLARE @Now datetime
SELECT @Now = GETUTCDATE()
DECLARE
@CreateDate datetime
DECLARE
@ModifyDate datetime
SET NOCOUNT ON
SET TRANSACTION ISOLATION
LEVEL SERIALIZABLE
BEGIN TRAN
-- Update an
existing record (if there is one)
UPDATE
dbo.GreylistTriplets
SET
@CreateDate =
CreateDate,
@ModifyDate =
@Now,
ModifyDate =
@Now
WHERE
ReturnPathLocalPart =
@ReturnPathLocalPart AND
ReturnPathDomain =
@ReturnPathDomain AND
RecipientLocalPart =
@RecipientLocalPart AND
RecipientDomain =
@RecipientDomain
-- If no rows
updated then we need to insert a new record
IF @@ROWCOUNT = 0
BEGIN
SELECT
@CreateDate = @Now,
@ModifyDate = @Now
INSERT dbo.GreylistTriplets (
HostAddress,
ReturnPathLocalPart,
ReturnPathDomain,
RecipientLocalPart,
RecipientDomain,
CreateDate,
ModifyDate)
VALUES (
@HostAddress,
@ReturnPathLocalPart,
@ReturnPathDomain,
@RecipientLocalPart,
@RecipientDomain,
@Now,
@Now)
END
COMMIT TRAN
-- Return the
updated/created record
SELECT
@HostAddress AS
HostAddress,
@ReturnPathLocalPart AS ReturnPathLocalPart,
@ReturnPathDomain AS
ReturnPathDomain,
@RecipientLocalPart AS
RecipientLocalPart,
@RecipientDomain AS
RecipientDomain,
datediff(ss, '1970-01-01 00:00:00.000',
@CreateDate) AS
CreateDate,
datediff(ss, '1970-01-01 00:00:00.000',
@ModifyDate) AS
ModifyDate
END
To revert it to normal run this script against the SQL DB or
use the SQL Configuration connector in the MailSite console to refresh the
stored procedures.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[LookupGreylistTriplet] (
@HostAddress int,
@ReturnPathLocalPart varchar(30),
@ReturnPathDomain varchar(30),
@RecipientLocalPart varchar(30),
@RecipientDomain varchar(30)
) AS
BEGIN
DECLARE @Now datetime
SELECT @Now = GETUTCDATE()
DECLARE @CreateDate
datetime
DECLARE
@ModifyDate datetime
SET NOCOUNT ON
SET TRANSACTION ISOLATION
LEVEL SERIALIZABLE
BEGIN TRAN
-- Update an
existing record (if there is one)
UPDATE
dbo.GreylistTriplets
SET
@CreateDate =
CreateDate,
@ModifyDate =
@Now,
ModifyDate =
@Now
WHERE
HostAddress =
@HostAddress AND
ReturnPathLocalPart =
@ReturnPathLocalPart AND
ReturnPathDomain =
@ReturnPathDomain AND
RecipientLocalPart =
@RecipientLocalPart AND
RecipientDomain =
@RecipientDomain
-- If no rows
updated then we need to insert a new record
IF @@ROWCOUNT = 0
BEGIN
SELECT
@CreateDate = @Now,
@ModifyDate = @Now
INSERT dbo.GreylistTriplets (
HostAddress,
ReturnPathLocalPart,
ReturnPathDomain,
RecipientLocalPart,
RecipientDomain,
CreateDate,
ModifyDate)
VALUES (
@HostAddress,
@ReturnPathLocalPart,
@ReturnPathDomain,
@RecipientLocalPart,
@RecipientDomain,
@Now,
@Now)
END
COMMIT TRAN
-- Return the updated/created
record
SELECT
@HostAddress AS
HostAddress,
@ReturnPathLocalPart AS ReturnPathLocalPart,
@ReturnPathDomain AS
ReturnPathDomain,
@RecipientLocalPart AS
RecipientLocalPart,
@RecipientDomain AS
RecipientDomain,
datediff(ss, '1970-01-01 00:00:00.000',
@CreateDate) AS
CreateDate,
datediff(ss, '1970-01-01 00:00:00.000',
@ModifyDate) AS
ModifyDate
END
Related:
See these other knowledge base documents:
Last revised 2015-1-23