Re: [Emerald] Emerald and MailSite

James Martin ( james@ficom.net )
Mon, 26 Apr 1999 22:20:20 -0500

I have virtual domains with assigned IP addresses on one physical server.
The Emerald database resides on this server as well. On another physical
server I have MailSite installed. When I add a new domain to MailSite it
complains about the new domain not having a ip address and warns that some
clients may not be able to send or receive email for this account. When I
ran verifymailuser against the account it would not list the correct domain
name. It would least the default domain instead. I think I found the
solution though in the verifymailuser and listmailuser scripts that Rudy
Komsic posted to the MailSite list:

here are both of them... ListMailUsers and VerifyMailUser:

if exists (select * from sysobjects where id =
object_id('dbo.ListMailUsers') and sysstat & 0xf = 4)
drop procedure dbo.ListMailUsers
GO

/****** Object: Stored Procedure dbo.ListMailUsers Script Date: 3/13/99
2:18:10 PM ******/
/****** Object: Stored Procedure dbo.ListMailUsers Script Date: 3/13/99
11:55:29 AM ******/
/****** Object: Stored Procedure dbo.ListMailUsers Script Date: 2/20/99
1:51:03 AM ******/
CREATE PROCEDURE ListMailUsers @esid integer AS
Select Login, Shell, EMail, d.MailDomain, Password, HomeDir
>From MasterAccounts ma, SubAccounts sa, Groups g, Domains d, AccountTypes at
Where ma.CustomerID = sa.CustomerID
AND ma.GroupID = g.GroupID
AND g.DomainID = d.DomainID
AND at.AccountType = sa.AccountType
AND at.ExternalSystemID = @esid
AND sa.active = ma.active
AND ma.active = 1
AND DateAdd(Day, (ma.Extension+ma.OverDue+1), maExpireDate) >= GetDate()
UNION
Select Login, Shell, EMail=a.Alias, d.MailDomain, Password, HomeDir
>From MasterAccounts ma, SubAccounts sa, Groups g, Domains d, Aliases a,
AccountTypes at
Where ma.CustomerID = sa.CustomerID
AND ma.GroupID = g.GroupID
AND g.DomainID = d.DomainID
AND sa.AccountID = a.AccountID
AND at.AccountType = sa.AccountType
AND at.ExternalSystemID = @esid
GO

/***** next procedure is here...

if exists (select * from sysobjects where id =
object_id('dbo.VerifyMailUser') and sysstat & 0xf = 4)
drop procedure dbo.VerifyMailUser
GO

/****** Object: Stored Procedure dbo.VerifyMailUser Script Date: 3/13/99
2:18:10 PM ******/
/****** Object: Stored Procedure dbo.VerifyMailUser Script Date: 3/13/99
11:55:29 AM ******/
/****** Object: Stored Procedure dbo.VerifyMailUser Script Date: 2/20/99
1:51:03 AM ******/
CREATE PROCEDURE VerifyMailUser @username varchar(32), @domain varchar(32),
@esid integer AS
Select Login, Shell, EMail, d.MailDomain, Password, HomeDir
>From MasterAccounts ma, SubAccounts sa, Groups g, Domains d, AccountTypes at
Where ma.CustomerID = sa.CustomerID
AND ma.GroupID = g.GroupID
AND g.DomainID = d.DomainID
AND at.AccountType = sa.AccountType
AND at.ExternalSystemID = @esid
AND sa.active = ma.active
AND ma.active = 1
AND ( Email = @username + "@" + @domain
OR ((Login=@username OR Shell=@username) AND d.MailDomain=@domain) )
AND DateAdd(Day, (ma.Extension+ma.OverDue+1), maExpireDate) >= GetDate()
UNION
Select Login, Shell, EMail=a.Alias, d.MailDomain, Password, HomeDir
>From MasterAccounts ma, SubAccounts sa, Groups g, Domains d, Aliases a,
AccountTypes at
Where ma.CustomerID = sa.CustomerID
AND ma.GroupID = g.GroupID
AND g.DomainID = d.DomainID
AND sa.AccountID = a.AccountID
AND at.AccountType = sa.AccountType
AND at.ExternalSystemID = @esid
AND a.Alias = @username + "@" + @domain

GO

I changed to these stored procedures and it appears to work after adding a
alias for the emerald account pointing to the virtual domain account. This
is rather lengthy but I hope it explains my problem.

At 07:38 PM 4/26/99 -0700, you wrote:
>James Martin wrote:
>>
>> Has anyone had any success in setting up MailSite 3.X with virtual domains
>> that do not reside on the same server that MailSite resides on. Also it
>> would appear that MailSite is using the Login field and not the Email field
>> on accounts in the default domain.
>
>I don't follow the first part? Do you mean as a forward or something?
>
>The later is known. Support has a termporary workaround for it and
>I will be working with Rockliffe on correcting this in their next
>release.
>
>--
>
>Dale E. Reed Jr. Emerald and RadiusNT
>__________________________________________
>IEA Software, Inc. www.iea-software.com
>
>

Thank You,
=+=+=+=+=+=+=+=+=+
James Martin, MCP
System Administrator
First Internet Communications, LLC
(931) 455-8611
www.ficom.net