Azure SQL DB: create users with PowerShell, parametrized SQL scripts and Azure Key Vault

Question

Goals:

Create users in an existing Azure SQL database
The username shall be passed as parameter to the SQL script that creates the user
The password is stored as secret in Azure Key Vault and shall be passed as parameter to the SQL script that creates the user
Assign certain permissions to the users created

quervernetzt · Accepted Answer · 2018-07-24 12:19:15Z

Store the passwords of the user as well as the admin in Azure Key Vault
- Add a secret to Key Vault
Create the SQL script to create the user and assign permission using dynamic SQL
- A good overview of existing permissions: Azure SQL DB Permissions

Code:

DECLARE @username VARCHAR(50) = '$(username)';
DECLARE @password VARCHAR(50) = '$(password)';
DECLARE @schema VARCHAR(50) = 'xxx';

DECLARE @query NVARCHAR(MAX) = '
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE NAME = ' + CHAR(39) + @username + CHAR(39) + ')
    BEGIN
        CREATE USER ' + @username + ' WITH PASSWORD = ' + CHAR(39) + @password + CHAR(39) + ';
        ALTER USER ' + @username + ' WITH DEFAULT_SCHEMA = ' + @schema + ';
    END

--ALTER ROLE db_datareader ADD MEMBER @username;
--ALTER ROLE db_datawriter ADD MEMBER @username;
GRANT SELECT ON SCHEMA::' + @schema + ' TO ' + @username + ';
GRANT INSERT ON SCHEMA::' + @schema + ' TO ' + @username + ';
GRANT UPDATE ON SCHEMA::' + @schema + ' TO ' + @username + ';
GRANT DELETE ON SCHEMA::' + @schema + ' TO ' + @username + ';
GRANT EXECUTE ON SCHEMA::' + @schema + ' TO ' + @username + ';
';

EXECUTE (@query);

For PowerShell:
- It is necessary to use a user and its credentials in the connection string that has the permission to create users
- The 'hack' from secure to plain password is necessary to be able the passwords in the connections string respectively in the SQL script
- It assumes that the SQL script is on the same level or some subfolder of the PowerShell script
- It is necessary to have the SQL module installed
  - Install-module -Name SqlServer -Scope CurrentUser

Code:

$passwordAdminSecure = (Get-AzureKeyVaultSecret -VaultName $keyVaultName -Name $adminSecretName).SecretValue
$passwordAdminPlain = (New-Object PSCredential "user",$passwordAdminSecure).GetNetworkCredential().Password

$dbConnectionString = "Server=tcp:$serverName.database.windows.net,1433;Initial Catalog=$dbName;Persist Security Info=False;User ID=$username;Password=$passwordAdminPlain;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

$script = Get-ChildItem -Path $PSScriptRoot -Include scriptname.sql -Recurse

$userToCreate = 'xxx'
$passwordUserSecure = (Get-AzureKeyVaultSecret -VaultName $keyVaultName -Name $userSecretName).SecretValue
$passwordUserPlain = (New-Object PSCredential "user",$passwordUserSecure).GetNetworkCredential().Password

Invoke-Sqlcmd -InputFile $script -ConnectionString $dbConnectionString -Variable username=$userToCreate, password=$passwordUserPlain -Verbose

Write-Host "Added user $userToCreate!"

Via the following query you can check existing users with the assigned permissions

Code:

SELECT DISTINCT 
    pr.principal_id, 
    pr.name, 
    pr.type_desc, 
    pr.authentication_type_desc, 
    pe.state_desc, 
    pe.permission_name
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id;

When somebody gets this running with execute sp_executesql please add your solution. I repeatedly ran into troubles with the correct notation for the parameters. Thanks

David Browne - Microsoft · Accepted Answer · 2018-07-24 14:01:32Z

I repeatedly ran into troubles with the correct notation for the parameters.

You're mixing SQLCMD variables and TSQL variables. It's tricky.

When writing SQL from PowerShell, I find it's much better to let Powershell's build the final query. Like this:

$username = "joe"
$password = "23948230948"
$schema = "dbo"


$sql = @"
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE NAME = '$username')
    BEGIN
        CREATE USER $username WITH PASSWORD = '$password';
        ALTER USER $username WITH DEFAULT_SCHEMA = $schema;
    END


GRANT SELECT ON SCHEMA::$schema TO $username;
GRANT INSERT ON SCHEMA::$schema TO $username;
GRANT UPDATE ON SCHEMA::$schema TO $username;
GRANT DELETE ON SCHEMA::$schema TO $username;
GRANT EXECUTE ON SCHEMA::$schema TO $username;
"@

invoke-sqlcmd $sql

Collectives™ on Stack Overflow

Azure SQL DB: create users with PowerShell, parametrized SQL scripts and Azure Key Vault

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related