Creating DotNetNuke Host Account Via SQL > Mitchel's Personal Web Portal

Current Articles | Categories | Search | Syndication

Creating DotNetNuke Host Account Via SQL
By Mitchel Sellers on Friday, February 23, 2007 @ 1:00 AM

1443 Views :: 12 Comments ::

Everyone gets locked out of their system at one time or another, especially if you have multiple users working with the same user account. This can be a nightmare to resolve but I hope to make everything a bit easier with this quick tutorial on how to create a new DotNetNuke host account via the database.

Requirements

For these instructions to work properly you MUST know the password of at least one user account in your portal. This can be any user account as long as it is for the same application! Also you should be running DotNetNuke 4.4.1 as these instructions have NOT been tested on any other versions.

Disclaimer

I am providing this example as exactly that an example. Anytime you directly modify the database you are introducing a level of risk. Please take the appropriate safety measures and backup your database before proceeding, I cannot be liable for any data loss that may occur from you performing these options.

Obtain Needed Information

The first step of the process to creating a new host account via the database is to obtain some needed information. We need the "Application Name" for your DotNetNuke installation, typically this is simply "dotnetnuke", it is set in your web config under the membership element. The remaining items we will need are the "password", "passwordSalt" and "PasswordFormat" values from the aspnet membership table. Below is a query to obtain this information. NOTE: replace the text TestUser with the username of your known working user account.

SELECT m.password, m.passwordsalt, m.passwordformat
FROM aspnet_users u
INNER JOIN aspnet_membership m
ON (u.userid = m.userid)
WHERE u.UserName = 'TestUser'

Execute Scripts to Create Account

Now we simply combine our desired new information and the existing information that we obtained to create the user account. Below is a listing of the SQL Code that I used to create the account in my portal. You will need to substitute your own values for the various properties. Also, I am using the long format to accomplish this goal by declaring each of the variables prior to calling the stored procedure, this is simply done to make the code easier to read.

DECLARE @ApplicationName nvarchar(256)
SET @ApplicationName = 'DotNetNuke'
DECLARE @UserName nvarchar(256)
SET @UserName = 'NewUserAccount' --The new user
DECLARE @Password nvarchar(128)
SET @Password = 'EZxbtno0LG5cdFpiJrENwIKArCC3ZqE+VnxF6TV10LbS+C9bxxBBgw=='
DECLARE @PasswordSalt nvarchar(128)
SET @PasswordSalt = 'XEW7SCZvyAx4pr1S/TdoTg=='
DECLARE @Email nvarchar(256)
SET @Email = 'TestingAccount@test.com' --You can set this to whatever you want
DECLARE @PasswordQuestion nvarchar(256)
SET @PasswordQuestion = ''
DECLARE @PasswordAnswer nvarchar(128)
SET @PasswordAnswer = ''
DECLARE @IsApproved bit
SET @IsApproved = 1
DECLARE @CurrentTimeUtc datetime
SET @CurrentTimeUtc = GETDATE()
DECLARE @CreateDate datetime
SET @CreateDate = @CurrentTimeUtc
DECLARE @UniqueEmail int
SET @UniqueEmail = 0
DECLARE @PasswordFormat int
SET @PasswordFormat = 2 --NOTE: Value from existing user!
Declare @UserId uniqueidentifier

--Make the stored procedure call
EXEC dbo.aspnet_Membership_CreateUser @ApplicationName, @Username, @Password,
                @PasswordSalt, @email, @passwordquestion, @PasswordAnswer,
                @IsApproved, @CurrentTimeUtc, @CreateDate, @UniqueEmail,
                @PasswordFormat, @UserId

--Display the new user id
SELECT 'New User Account Id ', @userId

--Insert the record into the DotNetNuke users table
INSERT INTO users (Username, FirstName, LastName, IsSuperUser, Email,
                    DisplayName, UpdatePassword)
    VALUES(@Username, 'New', 'Account', 1, @Email, 'New Account', 0)

If everything worked correctly you should see a result set in query analyser (Or SQL Management Studio) that shows New User Id and a GUID value.

Logging In!

You should now be able to navigate to your DNN site and login with the new user account and the password from the account you copied. Upon login you should have full host permissions!

If you have any questions or comments regarding this please let me know! This is considered a last resort, however, it is something that can be used in times of need.


	Share/Save This Article
	Use the below controls to save this article to one of many popular social bookmarking sites!


	Article Rating
	12345

Article Comments

By KC@1st5ive.com @ Wednesday, March 07, 2007 2:18 AM

Mitchel: Thanks for the information. I didn't test it, but I will bookmark the permalink for safe keeping. Another suggestion is for people to use the free program from roboform.com to keep all your identities/passwords in one place. I love it. Keep up the good work. See you on dnn.com or visit my place @ www.1st5ive.com and blog.1st5ive.com .

By Merete Mace @ Friday, March 09, 2007 11:19 AM

I was so happy to find this post!! Just what I need. But I'm not so SQL snart, and it tells me:
"Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query."
after entering the secound script in my Query analyser.
What to do? Thank you!

By mitchel.sellers@gmail.com @ Friday, March 09, 2007 11:20 AM

The SQL Information should provide a line number listing the line that the error occured on...can you tell me what that was.

Also, which version of DNN are you using?

By jimdish@gmail.com @ Thursday, March 15, 2007 10:30 PM

First off..thank you very much. Ok...

I ran the first script and obtained the pass, passwordsalt, and password format. I entered those values into the next script. I then ran it and I got the following:

Under "New User Account" - 1 record(s) affected. Then to the right it readys (NULL).

I ran these scripts in the query analyzer in my db. I went in and the password for the new account did not work. Do you know what I may have done wrong?

By mitchel.sellers@gmail.com @ Thursday, March 15, 2007 10:32 PM

My only guess would be that there might be an existing account with the same username.

I am not at my development machine so I cannot look at the inner workings of the Stored Procedure that is called so I cant see exactly what might cause the null value that that is where I would start looking.

By sunils @ Sunday, May 13, 2007 9:50 AM

Hello Mitch,
I posted this in the DNN forum, but just in case some people missed it, then here is again.
I tried this technique but found that this only works if I give the new account a 'SuperUser' role. Otherwise, despite the fact that it creates the entry in the users, aspnet_users and aspnet_membership tables, when I try to log in, it doesn't recognise the new user. If I create the new user account as superuser I can log in. Then if I change it to a non-superuser, the login fails. I'm not quite sure what the relationship between the login and the tables is. I thought that the users table was read to determine if a user account existed and the aspnet_membership table was used to check the password. Am I missing something?

sunils

By Mitchel Sellers @ Sunday, May 13, 2007 4:58 PM

Yes, what you explain is normal. For a regular user account to be able to login you must ALSO have an entry in the UserPortals table. That maps the login to a specific portal.

Superusers do not have that need since they have full access to ALL portals. I can look at posting a solution to insert a regular user sometime this week. It is a simple addition to the UserPortals table though.

By sunils @ Sunday, May 13, 2007 11:10 PM

I look forward to it. One thing that I was concerned about was how is the security of the user affected if more than one user then has the same salt and password? The user can update the password within the site, but not the salt. Would this not compromise security? In fact, maybe part of the question should be, what is the salt used for?

Sunil

By Mark Channon @ Monday, December 17, 2007 1:05 PM

Brilliant, thanks for this post, just saved me loads of time! Took about 5 minutes.

By Larry Daniele @ Wednesday, February 20, 2008 1:13 AM

Thanks for taking the time to document this essential information. I can verify that it works with DotNetNuke 4.8.0.

By Mitchel Sellers @ Wednesday, February 20, 2008 4:50 AM

Yes it will!

By Hakan @ Saturday, February 23, 2008 12:40 AM

I run the new host account query in my query analyzer. It worked good on first portal. But when i tried the secont portal, it didn't work good. it create host account but i couldn't log in the portal. How can i create a host account at all portals that password is same.

Click here to post a comment