[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Bulk insert into ASPNET membership table

Posted on 2011-03-09
1
Medium Priority
?
1,126 Views
Last Modified: 2012-05-11
I have a set of ASPNETDB tables that I am trying to migrate exsiting user data to. I have over a 6000 usernames, passwords, etc. to bulk insert into the ASPNETDB from an older application.  I have inserted all 6000+ usernames into the aspnet_Users table, complete with new GUIDs for the userid. Now I need to insert the records into thr aspnet_Membership table. With this insert I want to generate a new password for the users. My thought is to set everyones password to one password, then later on handle everyone changing their password when they log in for the first time to the new application. I'm having trouble figuraing out how to inert the records from the aspnet_Users table with the hashed password and passwordsalt properly. I feel like I'm half done but I need to get the password and passwordsalt in correctly. Any help is appreciated.
0
Comment
Question by:dodgerfan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 20

Accepted Solution

by:
Marten Rune earned 2000 total points
ID: 35093757
sp_help_revlogin gives the hash, but it wont work. You'll have to change password another way.

This is from sp_help_revlogin
***********************************************************
/* sp_help_revlogin script
** Generated Mar 10 2011 12:19PM on Q11109 */
 
 
-- Login: Testperson
CREATE LOGIN [Testperson] WITH PASSWORD = 0x01009FC60F6D54572F67B2F9A7AD0A85B17E39E04A9DF8C9B8B0 HASHED, SID = 0xDA96DE5683A9044881D6C330677B51A3, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
/* sp_help_revlogin script
** Generated Mar 10 2011 12:19PM on Q11109 */
 
 
-- Login: Testperson2
CREATE LOGIN [Testperson2] WITH PASSWORD = 0x0100841926CF01D34BF9F0174C3F090E11C59ED355891C3EF356 HASHED, SID = 0x104386C1FB8A9A49A3DDE9E7D2A4247F, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
***********************************************************

The same password is set for both Testperson and Testperson2. as you can see the hash is different. It's probably (and correctly) salted with the userSID and UserName. So this will not work!

You could batch set passwords later by scripting if you have all accounts.

By using:
USE [master]
GO
ALTER LOGIN [EnumeratSQLAccountsHere] WITH PASSWORD=N'YourDesiredPasswordHere'
GO

i e you need to loop this with different [EnumeratSQLAccountsHere] names.
You could use the exec command, like:

******************************************************
USE [master]
GO

declare @userNameVariable sysname

SET @userNameVariable = 'testperson'

exec('
ALTER LOGIN [' + @userNameVariable + '] WITH PASSWORD=N''YourDesiredPasswordHere''
')
******************************************************

You'll need to loop and alter:
SET @userNameVariable = 'testperson'
for each account name in your file.

This whole conclusion only applies to SQL Server accounts, not domain/nt local accounts.

Regarda Marten
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question