Removing IP lookup from Greylisting 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.

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