Solved

Bulk insert into ASPNET membership table

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now