Solved

Bulk insert into ASPNET membership table

Posted on 2011-03-09
1
1,096 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 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CSS for Popup in ASP.NET 4 48
CSS question 16 62
ASP.NET Load html part first, then start function 2 38
I am new to using JSON in SQL Server 2 57
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

734 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