Solved

Bulk insert into ASPNET membership table

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

770 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