https://pulumi.com logo
#general
Title
# general
s

salmon-mechanic-4571

10/11/2021, 10:45 AM
Hi All, I have a question. How would you go about creating users and roles on Azure SQL Databases via Pulumi? I have created my server and database, but at the moment I'm creating users via DevOps release pipeline and Dack Pac but I'm having troubles keeping my password as a secret this way, hence I would like to do it via Pulumi instead. Hope you can help 🙂 FYI: I'm doing an Azure Typescript project
b

brave-planet-10645

10/11/2021, 10:47 AM
Since you're using a programming language, you can create the resources (server and database) using Pulumi and then write code separately to create the users and roles.
s

salmon-mechanic-4571

10/11/2021, 10:52 AM
Yes I could, but my DB is in a Vnet and the "runner" who will execute that code (typescript) would not be on that Vnet hence it cannot reach the Database via a connection string
So it would be much easier to do it via pulumi
We would be looking for something like this (just for Azure SQL in stead of MySql) https://www.pulumi.com/blog/managing-your-mysql-databases-with-pulumi/
b

brave-planet-10645

10/11/2021, 10:54 AM
The way that the mysql provider works is by running the sql scripts against your DB, so you'd need to give the "runner" the dame access
s

salmon-mechanic-4571

10/11/2021, 11:00 AM
Okay, thanks we will look in to that then. Another question, how would you set "Allow Azure resources to connect" to yes via Pulumi on SQl Server?
n

narrow-helmet-6313

10/12/2021, 5:45 AM
@salmon-mechanic-4571 We can create User or Roles for sql db using custom implementation by extending Resource and by implementing interface ResourceProvider and using ShellCommand 1. export interface DatabaseUserProviderArgs { /** * Database Instance IP Address or DNS Name. */ instance: pulumi.Input<string>; /** * Admin User ID used to login to the database instance. */ adminUserId: pulumi.Input<string>; /** * Admin User Password to login to the database instance */ adminPassword: pulumi.Input<string>; /** * New LOGIN that will be created */ loginId: pulumi.Input<string>; /** * Password for the new LOGIN that will be created */ loginPassword: pulumi.Input<string>; } 2, DatabaseResource extends Resource (Eg export class DatabaseResource extends pulumi.dynamic.Resource {         constructor(name: string, args: DatabaseProviderArgs, opts?: pulumi.CustomResourceOptions) {         super(new DatabaseProvider(), name, { ...args }, opts);     } } 3, DatabaseProvider implements ResourceProvider export class DatabaseProvider implements ResourceProvider ...... public async create(inputs: DatabaseProviderArgs): Promise<pulumi.dynamic.CreateResult> { //TODO Create the database in the instance here. Create a user in this database. let id: string = uuidv4(); ...... ...... //Initialize users inputs.userIds.forEach(userId => { let sqlCommandCreateUser: string = `sqlcmd -b -x -U${inputs.adminUserId} -P${inputs.adminPassword} -S ${inputs.instance} -d ${inputs.name} -Q "create user ${userId} for login ${userId};" -o logs/${id}-create-user-${userId}.log`; ShellUtils.executeShellCommand(id + "-createuser-" + userId, sqlCommandCreateUser); //Grant owner privs let sqlCommandGrantRole: string = `sqlcmd -b -x -U${inputs.adminUserId} -P${inputs.adminPassword} -S ${inputs.instance} -d ${inputs.name} -Q "EXEC sp_addrolemember N'db_owner', N'${userId}';" -o logs/${id}-grant-role-${userId}.log`; ShellUtils.executeShellCommand(id + "-grantrole-" + userId, sqlCommandGrantRole); }); ...... } 4. ShellUtils executeShellCommand method will have execSync to execute the command ... const response = execSync(command, { stdio : "inherit"}); ..... 5. // Finally create the resource const repoUserAbadmin = new DatabaseUserResource("abadmin-user", { instance: sqlServer.fullyQualifiedDomainName, adminUserId: 'sqlserver', adminPassword: sqlserverPasswordObj, loginId: "abadmin", loginPassword: sqlserverPasswordObj }, { dependsOn: [sqlServer, firewallRule] });
41 Views