How can I create a contained user on an Azure SQL database using C#?

Question

I need to create a contained user for my Azure SQL database using C#. I have the following code already for creating the database:

// Login to Azure
var credentials = UserTokenProvider.LoginSilentAsync(clientId, domainName, username, password).Result;

// Create client
SqlManagementClient client = new SqlManagementClient(credentials)
{
    SubscriptionId = subscriptionId
};

// Database parameters
var databaseParameters = new Microsoft.Azure.Management.Sql.Models.Database()
{
    Location = "ukwest",
    ElasticPoolId = elasticPoolId,

};

// Create database
var dbResponse = client.Databases.CreateOrUpdate(resourceGroupName, serverName, databaseName, databaseParameters);

I have found the following SQL script that can be used to create a contained user using SQL management studio but this requires that you manually connect to the database that you want to create the user for:

CREATE USER [databaseUser] WITH PASSWORD = 'xxxxxxxxxxx';
ALTER ROLE [db_datareader] ADD MEMBER [databaseUser]
ALTER ROLE [db_datawriter] ADD MEMBER [databaseUser]

So how can I use the SQL script within my C# code to connect the database after it has been created to then create the contained SQL user for that database, or is there some equivalent code available in Microsoft.Azure.Management.Sql.SqlManagementClient for doing this?

Conor Cunningham MSFT · Accepted Answer · 2019-06-23 20:30:53Z

4

Answer recommended by Microsoft Azure Collective

Conceptually, you should think about Azure SQL Database as having (at least) 2 different layers at which you can operate:

There is a REST API for control operations such as creating a database, changing its reservation size, restoring a copy, etc.
There is a T-SQL interface for operations within a database container, from creating tables to inserting rows, etc.

There can be some overlap across these two surfaces - in traditional SQL Server, all of it is exposed in the latter T-SQL interface. Some of those commands are enabled in Azure SQL Database and they are internally calling the REST API for you.

Note that operations to the REST API use the credentials associated with Azure's Portal infrastructure. Within the database, you can have mappings into SQL logins/users, but you can also have SQL logins/users that are not associated at all with the Azure identities.

Net-net: you should connect to the database using the C# SQLClient and run those commands using an authenticated user with enough permissions (in your case, likely the administrator account or sa) to get the new user and role information set up.

Here's a an example on how to set up C# SQLClient

answered Jun 23, 2019 at 20:30

Conor Cunningham MSFT

4,6292 gold badges18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bonio Over a year ago

Thanks, I will give that a try. I found the following example for using SQLClient with an Azure database which is a bit more readable and also specifies how to connect to an Azure database: learn.microsoft.com/en-us/azure/sql-database/…

Collectives™ on Stack Overflow

How can I create a contained user on an Azure SQL database using C#?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related