Well, through some sweat and blood, I finally figured it out!!! My first caviet...I am NOT a SQL guy, so anything that could be done better can be certainly be changed.
Now, a couple of notes:
Windows 2003 Server, DNN 4.3.5, SQLExpress
I didn't really want to change the core code, so I added a call from the adduser sp to a new one. That way it is just one line of code to the core sp. Next, I created an sp to take the entered text for the username and password and push it to the external database, an email application called hMail in my case. But there was more...to integrate the deletion of accounts and changing of passwords, I knew that the userid's must match. So in my new sp, I did a lookup for the account that was just created and used the userid to make them the same. Finally, my email app uses MD5 encryption for the password, so I had to find a extended stored procedure for MD5 creation and add that to get the hash (http://www.codeproject.com/database/xp_md5.asp). With that said, here is my code (I only have the add user done right now, I will work on the deletion and change password this week):
1. Added the "MD5 Hash SQL Server Extended Stored Procedure" from this site:
http://www.codeproject.com/database/xp_md5.asp
2. Added this line to the very bottom of DotNetNuke.dbo.AddUser stored procedure:
-- This Executes a stored procedure called AddUser_Email and
-- passes the values for @Username and @UpdatePassword to it.
EXEC DotNetNuke.dbo.AddUser_Email @Username, @UpdatePassword
3. Create a new stored procedure called AddUser_Email with the following code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddUser_Email]
-- brings in variables from the registration page and AddUser Stored Procedure
@Username varchar(255),
@UpdatePassword varchar(255)
AS
-- Declaring variables, passing UserID for other admin functions.
-- Since I am using this for email, I need to append the domain name to the username.
DECLARE @UserID int
DECLARE @Domain varchar(255)
-- This returnd the UserID for the newly created account.
SELECT @UserID = UserID
FROM Users
WHERE Username = @Username
-- Have to do this so we can write to the AccountID field which is the key.
SET IDENTITY_INSERT hMail.dbo.hm_accounts ON
-- Statically set variable to append to username to make complete email address.
SET @Domain = '@HSGWorld.com'
-- Declaring another variable for the MD5 Function
DECLARE @accountpassword CHAR(32)
-- Send the text entered in the password field on the registration page to the encryption function
-- Hash is returned as variable
EXEC master.dbo.xp_md5 @UpdatePassword, -1, @accountpassword OUTPUT
-- Finally, write to the email Database the fields I needed.
-- Note the @Username+@Domain to make the complete email address.
-- And the @UserID pulled from the DNN Database.
INSERT INTO hMail.dbo.hm_accounts (accountid, accountdomainid, accountadminlevel, accountaddress, accountpassword, accountactive, accountisad, accountmaxsize, accountvacationmessageon, accountpwencryption)
VALUES (@UserID, 1, 0, @Username+@Domain, @accountpassword, 1, 0, 0, 0, 2)
-- This closes the key field.
SET IDENTITY_INSERT hMail.dbo.hm_accounts OFF
That's it. Now...not sure what you need to do to your script, but the INSERT INTO and VALUES command should work for just about anything you need.
I'll post more when I finish it.
OH...in case you are wondering, I am using an aspx "app" called SmartWebMail. It is the IMAP client side for the email system. I installed it and threw it into an iframe to show it on a page. Maybe one of the module guru's would take this and make it into a nice module HINT HINT!!
The hMail server is the backend, running on my server and using the SQL EXpress DB
And then the integration part from the directions above.
Hope that helps!!