dbasecret/dbasecret.ps1
|
function add-dbaSecret{ [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidUsingConvertToSecureStringWithPlainText", "")] param( [string]$name, [pscredential]$credential, [byte[]]$key, [switch]$force, [string]$server = ".", [string]$database = "master" ) # create vault table $query=@" if not exists ( select [name] from sys.databases where [name]='$database' ) select [param]='database', [value]='not found'; else begin select [param]='database', [value]='dbaship'; if not exists( select [name] from sys.tables where object_id = object_id('dbo.dbaSecret_vault') ) begin create table dbo.dbaSecret_vault( valult_id int identity primary key, [name] varchar(500), [UserName] varchar(1000), [value] varchar(1000), [once] tinyint default(1), last_updated datetime default ( getdate() ) ); revoke select, insert, update, delete on object::dbaSecret_vault from public; select [param]='table', value='dbaSecret_vault'; end end "@ $res = invoke-sqlcmd ` -ServerInstance $server ` -Database master ` -TrustServerCertificate ` -Query $query $db = get-shipValue $res 'database' # this is for database other than master if( $db -ne 'dbaship' ){ throw "Error: database not found..." out-shipMsg -flush } # verify if secret name already exists $cred1 = get-dbaSecret $name -checkonly if( $cred1 -and !$force.IsPresent ){ throw "Error: secret already exists..." } # if key is not provided, get a randome one if( !$key ){ $key = get-dbaSecretKey } $keystr = "( $($key -join ',') )" # encrypt $secure = ConvertTo-SecureString -String $credential.UserName -AsPlainText $username = ConvertFrom-SecureString -SecureString $secure -Key $key $value = ConvertFrom-SecureString -SecureString $credential.Password -Key $key $query = @" merge into dbaSecret_vault tgt using ( select [name]='$name', [username]='$username', [value]='$value' )src on tgt.[name] = src.[name] when matched then update set [username] = src.[username], [value] = src.[value], last_updated = getdate() when not matched by target then insert( [name], [username], [value] ) values( src.[name], src.[username], src.[value] ); select [param]='$name', [value]='PSCredential' union all select [param]='`$key =', [value]='$keystr'; "@ invoke-sqlcmd ` -ServerInstance $server ` -Database $database ` -TrustServerCertificate ` -Query $query ` -Verbose out-shipMsg -flush } function get-dbaSecret{ param( [string]$name, [byte[]]$key, [string]$server = ".", [switch]$checkonly, [string]$database = "master" ) $query = @" declare @username varchar(max), @value varchar(max),@once tinyint; select @username = [username], @value = [value], @once from dbo.dbaSecret_vault where [name] = '$name'; select [param] = 'username', [value] = isnull(@username,'') union all select [param] = 'value', [value] = @value union all select [param] = 'once', [value] = @once; "@ $res = invoke-sqlcmd ` -ServerInstance $server ` -Database $database ` -TrustServerCertificate ` -Query $query ` -Verbose # collect username $encrypted_name = get-shipValue $res 'username' # check if exists only if( $checkonly.IsPresent ){ if( $null -eq $encrypted_name -or $encrypted_name -eq "" -or !$encrypted_name ){ return $false }else{ return $true } } if( !$key ){ throw 'Error: invalid key...' } if( !(get-dbaSecret $name -checkonly) ){ throw 'secret not found...' } # decript username and value $secure_username = ConvertTo-SecureString -String $encrypted_name -Key $key -ErrorAction SilentlyContinue if( $null -eq $secure_username ){ throw 'Error: invalid key...' } $username = ConvertFrom-SecureString -SecureString $secure_username -AsPlainText $encrypted_secure = get-shipValue $res 'value' $secure = ConvertTo-SecureString -String $encrypted_secure -Key $key # create credential from username and value [pscredential]$newCredential = New-Object System.Management.Automation.PSCredential( $username, $secure ) $once = get-shipValue $res 'once' if( $once -eq '1'){ $query = @" delete from dbo.dbaSecret_vault where [name] = '$name'; "@ invoke-sqlcmd ` -ServerInstance $server ` -Database $database ` -TrustServerCertificate ` -Query $query ` -Verbose } return $newCredential } function remove-dbaSecret{ [CmdletBinding(SupportsShouldProcess)] param( [string]$name, [byte[]]$key, [string]$server = ".", [string]$database = "master" ) # verify if secret name already exists if( !(get-dbaSecret $name -checkonly) ){ throw "Error: secret not found" } $cred2 = get-dbaSecret $name $key if( !$cred2 ){ throw 'invalid key...' } $query = @" delete v from dbo.dbaSecret_vault v where [name] = '$name'; select [param]='rowcount', [value]=@@rowcount; "@ $res = invoke-sqlcmd ` -ServerInstance $server ` -Database $database ` -TrustServerCertificate ` -Query $query ` -Verbose $rows = get-shipValue $res 'rowcount' if( $rows -gt 0 ){ out-shipMsg $name 'deleted' }else{ #out-shipMsg $name write-output "Error: unable to delete $name" } out-shipMsg -flush } function get-dbaSecretKey{ # build a randon 256 (32-byte) array for encryption $newKey = New-Object Byte[] 32 [Security.Cryptography.RNGCryptoServiceProvider]::Create().GetBytes($newKey) return $newKey <# $bytes = New-Object byte[] 32 [System.Security.Cryptography.RandomNumberGenerator]::Create().GetBytes($bytes) $key = [BitConverter]::ToString($bytes).Replace("-", [string]::Empty) Write-Host "Generated AES Key: $key" #> } |