I needed a
way to store multiple Azure credentials for use in various PowerShell scripts.
Since most of them are run by SQL Agent jobs and it was mandatory that the password
for each account gets changed regularly, I thought I would use SQL as my
credential manager. The DB is TDE encrypted, but I also wanted to encrypt the
passwords, but on the other hand, keep the scripting effort low. With this
solution, I ended up with a few lines of code, I need to insert at the top of
each script. Also, I don’t need to save any tokens and keyfiles to disk.
First, I
create a table to store the credentials
USE [Admin]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cred](
[UserName] [varchar](255) NOT NULL,
[PasswordString] [varchar](max) NULL,
[Password] [varbinary](max) NULL,
[Usage] [varchar](10) NOT NULL,
[modified] [date] NULL,
CONSTRAINT [PK_cred] PRIMARY KEY CLUSTERED
(
[UserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I already
had a master key, but if you don’t, it’s time to create one
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'P@$$w0rd';
GO
Next, I
need to create a certificate
CREATE CERTIFICATE PMcert
WITH SUBJECT = 'Password Management';
GO
And then the
symmetric key for encryption and decryption
CREATE SYMMETRIC KEY SQLkey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE PMcert;
GO
Now let’s create
a script user if we don’t already have one and a decryption role and
authenticate them to use the key and certificate
CREATE USER scriptUser FOR LOGIN scriptUser;
GO
CREATE ROLE decrypt AUTHORIZATION scriptUser;
GO
GRANT VIEW DEFINITION ON SYMMETRIC KEY::SQLkey TO decrypt;
GO
GRANT VIEW DEFINITION ON Certificate::PMcert TO decrypt;
GO
GRANT CONTROL ON Certificate::PMcert TO decrypt;
GO
Next, I
create a trigger that encrypts the password string, deletes the plain text
password, and sets the modified date
CREATE TRIGGER dbo.updateCreds
ON dbo.cred
AFTER UPDATE,INSERT
AS
BEGIN
OPEN SYMMETRIC KEY SQLkey
DECRYPTION BY CERTIFICATE PMcert;
UPDATE dbo.cred
SET modified = SYSDATETIME(),
Password = EncryptByKey(Key_GUID('SQLkey'), i.PasswordString, 1, CONVERT(varbinary, i.Usage)),
PasswordString = NULL
FROM Inserted i
WHERE dbo.cred.UserName = i.UserName
CLOSE SYMMETRIC KEY SQLkey;
END
GO
ALTER TABLE dbo.cred ENABLE TRIGGER updateCreds;
GO
Finally, I create
a stored procedure, to retrieve decrypted passwords
CREATE PROCEDURE getCreds
AS
BEGIN
OPEN SYMMETRIC KEY SQLkey
DECRYPTION BY CERTIFICATE PMcert
SELECT UserName, CONVERT(varchar(max), DecryptByKey(Password, 1, CONVERT(varbinary, Usage))) AS 'PasswordString', Usage, modified FROM cred
CLOSE SYMMETRIC KEY SQLkey
RETURN
END
GO
GRANT EXECUTE ON getCreds TO decrypt;
GO
Now here’s a
three-liner to add to the start of my PowerShell scripts to build credential
objects, picking the right credential by filtering on the “usage” attribute
$cd = Invoke-Sqlcmd -Database "Admin" -Query "exec getCreds" -ServerInstance "server" | where usage -eq "AAD" | select UserName,PasswordString
$password = ConvertTo-SecureString $cd.PasswordString -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential ($cd.UserName, $password)
I can now
just pass the $cred variable to any cmdlet that accepts
a PSCredential input.
Lastly, I
use the following script to regularly change the passwords, determining their
age based on the modified date
Add-Type -AssemblyName System.Web
$SQLserver = "server"
$SQLdatabase = "Admin"
$today = Get-Date
$cds = Invoke-Sqlcmd -Database $SQLdatabase -Query "exec getCreds" -ServerInstance $SQLserver
foreach($cd in $cds) #loop over all credentials
{
$age = $today.Subtract($cd.modified).Days
if($age -gt 21) #if password oder than 21 days, update password
{
$UPN = $cd.UserName
$PW = $cd.PasswordString
$newPW = [System.Web.Security.Membership]::GeneratePassword(64, 5) #generate new password
$password = ConvertTo-SecureString $cd.PasswordString -AsPlainText -Force
$newpassword = ConvertTo-SecureString $newPW -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential ($cd.UserName, $password)
$Connection = Connect-AzureAD -Credential $cred #connect to AzureAd
if($Connection.Account.Id -eq $UPN)
{
Update-AzureADSignedInUserPassword -CurrentPassword $password -NewPassword $newpassword -ErrorAction Stop #update password on AzureAD
$upd = " UPDATE dbo.cred SET PasswordString = '$newPW' WHERE UserName = '$UPN'"
Invoke-Sqlcmd -Query $upd -ServerInstance $SQLserver -Database $SQLdatabase #write new password to DB
}
}
}
This might necessitate
filtering on the usage attribute, if not all credentials in the database are
Azure accounts or are handled separately for other reasons.
