265 lines
12 KiB
PowerShell
265 lines
12 KiB
PowerShell
# Generate-SeedSql.ps1
|
|
# Generates seed_admins.sql with properly hashed passwords for ASP.NET Core Identity.
|
|
#
|
|
# Usage:
|
|
# .\Generate-SeedSql.ps1
|
|
#
|
|
# Requires: PowerShell 5.1+ on .NET Framework 4.7.2+ OR PowerShell 7+
|
|
|
|
function New-IdentityPasswordHash {
|
|
param([string]$Password)
|
|
|
|
# 16-byte random salt
|
|
$salt = [byte[]]::new(16)
|
|
$rng = [System.Security.Cryptography.RandomNumberGenerator]::Create()
|
|
$rng.GetBytes($salt)
|
|
$rng.Dispose()
|
|
|
|
# PBKDF2-HMAC-SHA256, 100,000 iterations, 32-byte output
|
|
$pbkdf2 = [System.Security.Cryptography.Rfc2898DeriveBytes]::new(
|
|
$Password, $salt, 100000,
|
|
[System.Security.Cryptography.HashAlgorithmName]::SHA256)
|
|
$subkey = $pbkdf2.GetBytes(32)
|
|
$pbkdf2.Dispose()
|
|
|
|
# ASP.NET Core Identity v3 wire format (61 bytes):
|
|
# [0] = 0x01 (version marker)
|
|
# [1-4] = 0x00000001 (PRF = HMACSHA256, big-endian uint32)
|
|
# [5-8] = 0x000186A0 (iterations = 100000, big-endian uint32)
|
|
# [9-12] = 0x00000010 (salt length = 16, big-endian uint32)
|
|
# [13-28] = salt bytes
|
|
# [29-60] = subkey bytes
|
|
$buf = [byte[]]::new(61)
|
|
$buf[0] = 0x01
|
|
$buf[1] = 0x00; $buf[2] = 0x00; $buf[3] = 0x00; $buf[4] = 0x01
|
|
$buf[5] = 0x00; $buf[6] = 0x01; $buf[7] = 0x86; $buf[8] = 0xA0
|
|
$buf[9] = 0x00; $buf[10] = 0x00; $buf[11] = 0x00; $buf[12] = 0x10
|
|
[Array]::Copy($salt, 0, $buf, 13, 16)
|
|
[Array]::Copy($subkey, 0, $buf, 29, 32)
|
|
|
|
return [Convert]::ToBase64String($buf)
|
|
}
|
|
|
|
# ---- Generate GUIDs and hashes -----------------------------------------------
|
|
|
|
$roleSuperAdmin = [Guid]::NewGuid().ToString()
|
|
$roleAdmin = [Guid]::NewGuid().ToString()
|
|
$roleManager = [Guid]::NewGuid().ToString()
|
|
$roleEmployee = [Guid]::NewGuid().ToString()
|
|
$roleShopFloor = [Guid]::NewGuid().ToString()
|
|
$roleReadOnly = [Guid]::NewGuid().ToString()
|
|
|
|
$user1Id = [Guid]::NewGuid().ToString()
|
|
$user2Id = [Guid]::NewGuid().ToString()
|
|
$user1Stamp = [Guid]::NewGuid().ToString()
|
|
$user2Stamp = [Guid]::NewGuid().ToString()
|
|
$user1Security = [Guid]::NewGuid().ToString()
|
|
$user2Security = [Guid]::NewGuid().ToString()
|
|
|
|
$now = (Get-Date).ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss")
|
|
|
|
Write-Host "Hashing SuperAdmin123! ..." -ForegroundColor Cyan
|
|
$hash1 = New-IdentityPasswordHash "SuperAdmin123!"
|
|
Write-Host "Hashing Admin123! ..." -ForegroundColor Cyan
|
|
$hash2 = New-IdentityPasswordHash "Admin123!"
|
|
Write-Host "Done. Writing SQL file..." -ForegroundColor Cyan
|
|
|
|
# ---- Build SQL ---------------------------------------------------------------
|
|
|
|
$outFile = Join-Path $PSScriptRoot "seed_admins.sql"
|
|
|
|
$lines = [System.Collections.Generic.List[string]]::new()
|
|
|
|
$lines.Add("-- =============================================================================")
|
|
$lines.Add("-- PowderCoating App - Initial Seed SQL")
|
|
$lines.Add("-- Generated: $now UTC")
|
|
$lines.Add("--")
|
|
$lines.Add("-- Run this against your Azure SQL database when startup seeding fails.")
|
|
$lines.Add("-- All inserts are guarded with IF NOT EXISTS and are safe to re-run.")
|
|
$lines.Add("-- =============================================================================")
|
|
$lines.Add("")
|
|
$lines.Add("SET NOCOUNT ON;")
|
|
$lines.Add("GO")
|
|
$lines.Add("")
|
|
$lines.Add("-- 1. Default Company")
|
|
$lines.Add("")
|
|
$lines.Add("IF NOT EXISTS (SELECT 1 FROM Companies WHERE CompanyCode = 'DEMO')")
|
|
$lines.Add("BEGIN")
|
|
$lines.Add(" SET IDENTITY_INSERT Companies ON;")
|
|
$lines.Add("")
|
|
$lines.Add(" INSERT INTO Companies (")
|
|
$lines.Add(" Id, CompanyId, CompanyName, CompanyCode,")
|
|
$lines.Add(" PrimaryContactName, PrimaryContactEmail, Phone,")
|
|
$lines.Add(" Address, City, State, ZipCode,")
|
|
$lines.Add(" IsActive, SubscriptionPlan, SubscriptionStatus,")
|
|
$lines.Add(" SubscriptionStartDate, TimeZone,")
|
|
$lines.Add(" CreatedAt, IsDeleted")
|
|
$lines.Add(" ) VALUES (")
|
|
$lines.Add(" 1, 1, 'Demo Company', 'DEMO',")
|
|
$lines.Add(" 'Admin User', 'admin@demo.com', '(555) 123-4567',")
|
|
$lines.Add(" '123 Demo Street', 'Demo City', 'CA', '90210',")
|
|
$lines.Add(" 1, 2, 0,")
|
|
$lines.Add(" '$now', 'America/New_York',")
|
|
$lines.Add(" '$now', 0")
|
|
$lines.Add(" );")
|
|
$lines.Add("")
|
|
$lines.Add(" SET IDENTITY_INSERT Companies OFF;")
|
|
$lines.Add(" PRINT 'Company inserted.';")
|
|
$lines.Add("END")
|
|
$lines.Add("ELSE")
|
|
$lines.Add(" PRINT 'Company already exists - skipped.';")
|
|
$lines.Add("GO")
|
|
$lines.Add("")
|
|
$lines.Add("-- 2. Roles")
|
|
$lines.Add("")
|
|
$lines.Add("IF NOT EXISTS (SELECT 1 FROM AspNetRoles WHERE NormalizedName = 'SUPERADMIN')")
|
|
$lines.Add(" INSERT INTO AspNetRoles (Id, Name, NormalizedName, ConcurrencyStamp)")
|
|
$lines.Add(" VALUES ('$roleSuperAdmin', 'SuperAdmin', 'SUPERADMIN', NEWID());")
|
|
$lines.Add("")
|
|
$lines.Add("IF NOT EXISTS (SELECT 1 FROM AspNetRoles WHERE NormalizedName = 'ADMINISTRATOR')")
|
|
$lines.Add(" INSERT INTO AspNetRoles (Id, Name, NormalizedName, ConcurrencyStamp)")
|
|
$lines.Add(" VALUES ('$roleAdmin', 'Administrator', 'ADMINISTRATOR', NEWID());")
|
|
$lines.Add("")
|
|
$lines.Add("IF NOT EXISTS (SELECT 1 FROM AspNetRoles WHERE NormalizedName = 'MANAGER')")
|
|
$lines.Add(" INSERT INTO AspNetRoles (Id, Name, NormalizedName, ConcurrencyStamp)")
|
|
$lines.Add(" VALUES ('$roleManager', 'Manager', 'MANAGER', NEWID());")
|
|
$lines.Add("")
|
|
$lines.Add("IF NOT EXISTS (SELECT 1 FROM AspNetRoles WHERE NormalizedName = 'EMPLOYEE')")
|
|
$lines.Add(" INSERT INTO AspNetRoles (Id, Name, NormalizedName, ConcurrencyStamp)")
|
|
$lines.Add(" VALUES ('$roleEmployee', 'Employee', 'EMPLOYEE', NEWID());")
|
|
$lines.Add("")
|
|
$lines.Add("IF NOT EXISTS (SELECT 1 FROM AspNetRoles WHERE NormalizedName = 'SHOPFLOOR')")
|
|
$lines.Add(" INSERT INTO AspNetRoles (Id, Name, NormalizedName, ConcurrencyStamp)")
|
|
$lines.Add(" VALUES ('$roleShopFloor', 'ShopFloor', 'SHOPFLOOR', NEWID());")
|
|
$lines.Add("")
|
|
$lines.Add("IF NOT EXISTS (SELECT 1 FROM AspNetRoles WHERE NormalizedName = 'READONLY')")
|
|
$lines.Add(" INSERT INTO AspNetRoles (Id, Name, NormalizedName, ConcurrencyStamp)")
|
|
$lines.Add(" VALUES ('$roleReadOnly', 'ReadOnly', 'READONLY', NEWID());")
|
|
$lines.Add("")
|
|
$lines.Add("PRINT 'Roles inserted.';")
|
|
$lines.Add("GO")
|
|
$lines.Add("")
|
|
$lines.Add("-- 3. SuperAdmin Users")
|
|
$lines.Add("")
|
|
$lines.Add("-- User 1: superadmin@powdercoating.com / SuperAdmin123!")
|
|
$lines.Add("IF NOT EXISTS (SELECT 1 FROM AspNetUsers WHERE NormalizedEmail = 'SUPERADMIN@POWDERCOATING.COM')")
|
|
$lines.Add("BEGIN")
|
|
$lines.Add(" INSERT INTO AspNetUsers (")
|
|
$lines.Add(" Id, UserName, NormalizedUserName, Email, NormalizedEmail,")
|
|
$lines.Add(" EmailConfirmed, PasswordHash, SecurityStamp, ConcurrencyStamp,")
|
|
$lines.Add(" PhoneNumber, PhoneNumberConfirmed, TwoFactorEnabled,")
|
|
$lines.Add(" LockoutEnd, LockoutEnabled, AccessFailedCount,")
|
|
$lines.Add(" CompanyId, CompanyRole,")
|
|
$lines.Add(" FirstName, LastName, EmployeeNumber,")
|
|
$lines.Add(" HireDate, IsActive,")
|
|
$lines.Add(" Department, Position, HourlyRate,")
|
|
$lines.Add(" Theme, DateFormat, TimeZone, SidebarColor,")
|
|
$lines.Add(" CanViewShopFloor, CanManageJobs, CanManageInventory, CanManageCustomers,")
|
|
$lines.Add(" CanCreateQuotes, CanApproveQuotes, CanManageCalendar, CanViewCalendar,")
|
|
$lines.Add(" CanManageProducts, CanViewProducts, CanManageEquipment,")
|
|
$lines.Add(" CanManageSuppliers, CanManageMaintenance,")
|
|
$lines.Add(" CreatedAt")
|
|
$lines.Add(" ) VALUES (")
|
|
$lines.Add(" '$user1Id',")
|
|
$lines.Add(" 'superadmin@powdercoating.com', 'SUPERADMIN@POWDERCOATING.COM',")
|
|
$lines.Add(" 'superadmin@powdercoating.com', 'SUPERADMIN@POWDERCOATING.COM',")
|
|
$lines.Add(" 1, '$hash1', '$user1Security', '$user1Stamp',")
|
|
$lines.Add(" NULL, 0, 0, NULL, 1, 0,")
|
|
$lines.Add(" 1, NULL,")
|
|
$lines.Add(" 'Super', 'Admin', 'SA-001',")
|
|
$lines.Add(" '$now', 1,")
|
|
$lines.Add(" 'Platform', 'Super Administrator', 0,")
|
|
$lines.Add(" 'light', 'MM/dd/yyyy', 'America/New_York', 'ocean',")
|
|
$lines.Add(" 1, 1, 1, 1,")
|
|
$lines.Add(" 1, 1, 1, 1,")
|
|
$lines.Add(" 1, 1, 1,")
|
|
$lines.Add(" 1, 1,")
|
|
$lines.Add(" '$now'")
|
|
$lines.Add(" );")
|
|
$lines.Add(" PRINT 'User superadmin@powdercoating.com inserted.';")
|
|
$lines.Add("END")
|
|
$lines.Add("ELSE")
|
|
$lines.Add(" PRINT 'User superadmin@powdercoating.com already exists - skipped.';")
|
|
$lines.Add("GO")
|
|
$lines.Add("")
|
|
$lines.Add("-- User 2: admin@powdercoating.com / Admin123!")
|
|
$lines.Add("IF NOT EXISTS (SELECT 1 FROM AspNetUsers WHERE NormalizedEmail = 'ADMIN@POWDERCOATING.COM')")
|
|
$lines.Add("BEGIN")
|
|
$lines.Add(" INSERT INTO AspNetUsers (")
|
|
$lines.Add(" Id, UserName, NormalizedUserName, Email, NormalizedEmail,")
|
|
$lines.Add(" EmailConfirmed, PasswordHash, SecurityStamp, ConcurrencyStamp,")
|
|
$lines.Add(" PhoneNumber, PhoneNumberConfirmed, TwoFactorEnabled,")
|
|
$lines.Add(" LockoutEnd, LockoutEnabled, AccessFailedCount,")
|
|
$lines.Add(" CompanyId, CompanyRole,")
|
|
$lines.Add(" FirstName, LastName, EmployeeNumber,")
|
|
$lines.Add(" HireDate, IsActive,")
|
|
$lines.Add(" Department, Position, HourlyRate,")
|
|
$lines.Add(" Theme, DateFormat, TimeZone, SidebarColor,")
|
|
$lines.Add(" CanViewShopFloor, CanManageJobs, CanManageInventory, CanManageCustomers,")
|
|
$lines.Add(" CanCreateQuotes, CanApproveQuotes, CanManageCalendar, CanViewCalendar,")
|
|
$lines.Add(" CanManageProducts, CanViewProducts, CanManageEquipment,")
|
|
$lines.Add(" CanManageSuppliers, CanManageMaintenance,")
|
|
$lines.Add(" CreatedAt")
|
|
$lines.Add(" ) VALUES (")
|
|
$lines.Add(" '$user2Id',")
|
|
$lines.Add(" 'admin@powdercoating.com', 'ADMIN@POWDERCOATING.COM',")
|
|
$lines.Add(" 'admin@powdercoating.com', 'ADMIN@POWDERCOATING.COM',")
|
|
$lines.Add(" 1, '$hash2', '$user2Security', '$user2Stamp',")
|
|
$lines.Add(" NULL, 0, 0, NULL, 1, 0,")
|
|
$lines.Add(" 1, NULL,")
|
|
$lines.Add(" 'Admin', 'User', 'SA-002',")
|
|
$lines.Add(" '$now', 1,")
|
|
$lines.Add(" 'Platform', 'Platform Administrator', 0,")
|
|
$lines.Add(" 'light', 'MM/dd/yyyy', 'America/New_York', 'ocean',")
|
|
$lines.Add(" 1, 1, 1, 1,")
|
|
$lines.Add(" 1, 1, 1, 1,")
|
|
$lines.Add(" 1, 1, 1,")
|
|
$lines.Add(" 1, 1,")
|
|
$lines.Add(" '$now'")
|
|
$lines.Add(" );")
|
|
$lines.Add(" PRINT 'User admin@powdercoating.com inserted.';")
|
|
$lines.Add("END")
|
|
$lines.Add("ELSE")
|
|
$lines.Add(" PRINT 'User admin@powdercoating.com already exists - skipped.';")
|
|
$lines.Add("GO")
|
|
$lines.Add("")
|
|
$lines.Add("-- 4. Assign SuperAdmin role to both users")
|
|
$lines.Add("")
|
|
$lines.Add("DECLARE @roleId NVARCHAR(450) = (SELECT Id FROM AspNetRoles WHERE NormalizedName = 'SUPERADMIN');")
|
|
$lines.Add("DECLARE @u1Id NVARCHAR(450) = (SELECT Id FROM AspNetUsers WHERE NormalizedEmail = 'SUPERADMIN@POWDERCOATING.COM');")
|
|
$lines.Add("DECLARE @u2Id NVARCHAR(450) = (SELECT Id FROM AspNetUsers WHERE NormalizedEmail = 'ADMIN@POWDERCOATING.COM');")
|
|
$lines.Add("")
|
|
$lines.Add("IF @u1Id IS NOT NULL AND @roleId IS NOT NULL")
|
|
$lines.Add(" AND NOT EXISTS (SELECT 1 FROM AspNetUserRoles WHERE UserId = @u1Id AND RoleId = @roleId)")
|
|
$lines.Add("BEGIN")
|
|
$lines.Add(" INSERT INTO AspNetUserRoles (UserId, RoleId) VALUES (@u1Id, @roleId);")
|
|
$lines.Add(" PRINT 'Role assigned to superadmin@powdercoating.com.';")
|
|
$lines.Add("END")
|
|
$lines.Add("")
|
|
$lines.Add("IF @u2Id IS NOT NULL AND @roleId IS NOT NULL")
|
|
$lines.Add(" AND NOT EXISTS (SELECT 1 FROM AspNetUserRoles WHERE UserId = @u2Id AND RoleId = @roleId)")
|
|
$lines.Add("BEGIN")
|
|
$lines.Add(" INSERT INTO AspNetUserRoles (UserId, RoleId) VALUES (@u2Id, @roleId);")
|
|
$lines.Add(" PRINT 'Role assigned to admin@powdercoating.com.';")
|
|
$lines.Add("END")
|
|
$lines.Add("GO")
|
|
$lines.Add("")
|
|
$lines.Add("PRINT '================================================';")
|
|
$lines.Add("PRINT 'Seed complete.';")
|
|
$lines.Add("PRINT 'Login 1: superadmin@powdercoating.com / SuperAdmin123!';")
|
|
$lines.Add("PRINT 'Login 2: admin@powdercoating.com / Admin123!';")
|
|
$lines.Add("PRINT '================================================';")
|
|
$lines.Add("GO")
|
|
|
|
$lines | Set-Content -Path $outFile -Encoding UTF8
|
|
|
|
Write-Host ""
|
|
Write-Host "SQL file written to: $outFile" -ForegroundColor Green
|
|
Write-Host ""
|
|
Write-Host "Run it against your Azure SQL database using:" -ForegroundColor Yellow
|
|
Write-Host " sqlcmd -S <server>.database.windows.net -d PowderCoatingDb -U <user> -P <password> -i seed_admins.sql" -ForegroundColor White
|
|
Write-Host " OR paste it into Azure Portal > Query Editor" -ForegroundColor White
|
|
Write-Host ""
|
|
Write-Host "Login 1: superadmin@powdercoating.com / SuperAdmin123!" -ForegroundColor Cyan
|
|
Write-Host "Login 2: admin@powdercoating.com / Admin123!" -ForegroundColor Cyan
|