azure cosmos db sql role assignment

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

az cosmosdb sql role assignment

Manage Azure Cosmos DB SQL role assignments.

az cosmosdb sql role assignment create

Create a SQL role assignment under an Azure Cosmos DB account.

Create a SQL role assignment under an Azure Cosmos DB account using Role Definition Name.

Create a SQL role assignment under an Azure Cosmos DB account using Role Definition ID.

Required Parameters

Cosmosdb account name.

AAD Object ID of the principal to which this Role Assignment is being granted.

Name of resource group. You can configure the default group using az configure --defaults group=<name> .

Data plane resource path at which this Role Assignment is being granted.

Optional Parameters

Do not wait for the long-running operation to finish.

Optional for Create. Unique ID for the Role Assignment. If not provided, a new GUID will be used.

Unique ID of the Role Definition that this Role Assignment refers to.

Unique Name of the Role Definition that this Role Assignment refers to. Eg. 'Contoso Reader Role'.

Increase logging verbosity to show all debug logs.

Show this help message and exit.

Only show errors, suppressing warnings.

Output format.

JMESPath query string. See http://jmespath.org/ for more information and examples.

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID .

Increase logging verbosity. Use --debug for full debug logs.

az cosmosdb sql role assignment delete

Delete a SQL role assignment under an Azure Cosmos DB account.

Do not prompt for confirmation.

az cosmosdb sql role assignment exists

Check if an Azure Cosmos DB role assignment exists.

az cosmosdb sql role assignment list

List all SQL role assignments under an Azure Cosmos DB account.

az cosmosdb sql role assignment show

Show the properties of a SQL role assignment under an Azure Cosmos DB account.

az cosmosdb sql role assignment update

Update a SQL role assignment under an Azure Cosmos DB account.

az cosmosdb sql role assignment wait

Poll on a SQL role assignment until a specific condition is met.

Poll on a SQL role assignment until it is deleted.

Wait until created with 'provisioningState' at 'Succeeded'.

Wait until the condition satisfies a custom JMESPath query. E.g. provisioningState!='InProgress', instanceView.statuses[?code=='PowerState/running'].

Wait until deleted.

Wait until the resource exists.

Polling interval in seconds.

Maximum wait in seconds.

Wait until updated with provisioningState at 'Succeeded'.

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

Security in Azure Cosmos DB

Security is a critical concern for any system. Azure Cosmos DB offers various options for keeping your data safe and for controlling who can access it.

Access data with keys

Your Azure Cosmos DB account contains a set of keys for read-write access and a set of read only keys. If you’re developing with keys, you need to secure those credentials so that they don’t accidentally make it into source control. This guide outlines options for controlling keys rather than storing key values in application.properties .

If you must work with keys:

Store the keys in environment variables. Environment variables make it easy to store the values in a place without checking them in to source control. Azure App Service , Azure Functions , and Azure Spring Apps support environment variables as well.

Another option for storing secure values is in Azure Key Vault . For more information on working with Java and Azure Key Vault, check out our Hello World code sample with Java and Azure Key Vault .

Role-based access control

One of the ways you can secure access to your Azure Cosmos DB account is with role-based access control (RBAC) . With Azure RBAC, you can assign users, service principals, managed identities, and groups as members within a role. Azure RBAC can be managed in the section labeled Access Control (IAM) when working in the Azure portal. IAM is an acronym for Identity and Access Management.

Azure RBAC uses Azure Active Directory (Azure AD) for the authentication portion. It uses role assignments for authorization determination. By assigning roles at the resource level, you have fine-grained control over your resource access.

Azure Cosmos DB-specific RBAC roles

Azure Cosmos DB uses two built-in roles. The Cosmos DB Built-in Data Reader role includes actions for reading metadata on database accounts, reading items in a container, executing queries, and reading the change feed. The Cosmos DB Built-in Data Contributor role includes actions for reading metadata on database accounts, reading and writing with containers, and reading and writing with items.

There are no built-in Azure Cosmos DB-specific roles for:

• Creating, replacing, and deleting databases and containers.
• Replacing container throughput.
• Creating, replacing, deleting, and reading stored procedures, triggers, and user defined functions.

Custom RBAC roles

Azure Cosmos DB actions support wildcard notation at the container and item levels when creating custom role definitions for RBAC. Their actions can be referred to as:

• Microsoft.DocumentDB/databaseAccounts/sqlDatabases/containers/*
• Microsoft.DocumentDB/databaseAccounts/sqlDatabases/containers/items/*

There are three scopes for role assignments:

• Account level ( / )
• Database level ( /dbs/<database-name> )
• Container level ( /dbs/<database-name>/colls/<container-name> )

Using RBAC alone

You can configure Azure Cosmos DB to use only RBAC and not allow the use of primary and secondary keys. Using RBAC alone can be done as part of creating or updating your Azure Cosmos DB account by using Azure Resource Manager templates, then setting the disableLocalAuth property to true .

Managed identities

You can use a managed identity for authentication purposes between Azure resources. Services that support managed identities for Azure resources include Azure App Service, Azure Spring Apps, Azure Logic Apps, and Azure Functions. For a comprehensive list of services that support managed identities, see what Azure services support managed identity . These services are used throughout this guide. Rather than having to rely on keys, you can use managed identities to grant roles via RBAC. When you create a managed identity, it lives with the resource. When the resource is deleted, the managed identity is also deleted.

Suppose a microservice runs in Azure Spring Apps. The microservice wants to write to Azure Cosmos DB and needs the key. You can grant the managed identity access to read the keys via the Cosmos DB Account Reader role. Managed identities are seen on the screen for RBAC.

Azure Key Vault

When working in applications, you may be tempted to store key information in application.properties . However, if this file gets checked into source control, anyone who has access to your source code can access your key. Rather than running the risk of exposing credentials, you should store them securely in Azure Key Vault. When storing values in Azure Key Vault, you can use either vault access policies or secure things via RBAC. You can grant permissions to users, service principals, managed identities, and groups.

Networking security

Another approach to securing Azure Cosmos DB is through networking. Options include:

• IP firewall
• Virtual network support
• Private endpoints using Azure Private Link

Encrypting data

Data stored in Azure Cosmos DB is automatically encrypted with service-managed keys. Service-managed keys are keys maintained by Microsoft. If you want to add another layer of encryption, you can bring your own customer-managed keys. These keys must be stored in Azure Key Vault. Customer-managed keys must be configured during account creation. There are no extra charges for enabling the feature for your own customer-managed key; however, there may be an increase in processor usage for encryption and decryption. This cost will be seen in the RU (Request Units) cost.

For more information about setting up customer-managed keys, see configure customer-managed keys .

Azure Cosmos DB backs up your data. If you need to restore this data with a customer-managed key, the encryption key must be available in Azure Key Vault.

Next | Get started with Java and Azure Key Vault

Navigation Menu

Search code, repositories, users, issues, pull requests..., provide feedback.

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly.

To see all available qualifiers, see our documentation .

• Notifications You must be signed in to change notification settings

Get-AzCosmosDBSqlRoleAssignment.md

Latest commit, file metadata and controls.

Get-AzCosmosDBSqlRoleAssignment

Gets the CosmosDB Sql Role Assignment.

ByNameParameterSet (Default)

Byparentobjectparameterset, description.

The Get-AzCosmosDBSqlRoleAssignment cmdlet gets the list of all existing CosmosDB Sql Role Assignments for a given ResourceGroupName, AccountName and gets a single CosmosDB Sql Role Assignment for a given ResourceGroupName, AccountName, Id. Id can be either fully qualified or just the Guid.

-AccountName

Name of the Cosmos DB database account.

-DefaultProfile

The credentials, account, tenant, and subscription used for communication with Azure.

Role Assignment Id.

-ParentObject

Cosmos DB Account object

-ResourceGroupName

Name of resource group.

CommonParameters

This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters .

Microsoft.Azure.Commands.CosmosDB.Models.PSSqlRoleAssignmentGetResults

Related links.

• Azure Classic
• SqlRoleAssignment

Azure Classic v5.80.0, Jun 10 24

We recommend using Azure Native .

azure.cosmosdb.SqlRoleAssignment

Explore with Pulumi AI

On this page

• Request a Change

Manages a Cosmos DB SQL Role Assignment.

Example Usage

Create sqlroleassignment resource.

Resources are created with functions called constructors. To learn more about declaring and configuring resources, see Resources .

Constructor syntax

The following reference example uses placeholder values for all input properties .

SqlRoleAssignment Resource Properties

To learn more about resource properties and how to use them, see Inputs and Outputs in the Architecture and Concepts docs.

The SqlRoleAssignment resource accepts the following input properties:

All input properties are implicitly available as output properties. Additionally, the SqlRoleAssignment resource produces the following output properties:

Look up Existing SqlRoleAssignment Resource

Get an existing SqlRoleAssignment resource’s state with the given name, ID, and optional extra properties used to qualify the lookup.

Cosmos DB SQL Role Assignments can be imported using the resource id , e.g.

To learn more about importing existing cloud resources, see Importing resources .

Package Details

Unleashing the Potential of Generative AI in Azure SQL Database

Sanjay Mishra

June 4th, 2024 0 1

Generative AI is not just a technological advancement; it’s a paradigm shift that is redefining the landscape of customer interaction. Azure SQL Database stands at the forefront of this revolution, offering unparalleled opportunities to harness the power of “your data” in crafting state-of-the-art applications.

As we stand at the cusp of an AI renaissance, it’s clear that AI-enabled applications are becoming the new norm, from personal devices to the vast expanse of the cloud. Yet, it’s not the technology alone that sets you apart—it’s how you leverage it with your unique datasets. Your data is the key to delivering personalized experiences that your competitors simply cannot replicate. In this era, AI is the great equalizer, but your data is the ultimate differentiator.

Consider the innovative application pattern of Retrieval Augmented Generation (RAG). By creating vector embeddings from your data and employing similarity searches, coupled with the reasoning capabilities of a Large Language Model (LLM), you can craft experiences that resonate deeply with your customers’ needs.

Take inspiration from Walmart’s groundbreaking approach, as showcased in their CES 2024 Keynote [2]. They’ve transformed their shopping app into an intuitive platform where customers can effortlessly discover the perfect products for any occasion, like hosting a Superbowl party, with just a single search.

I thought it would be interesting to try building such an app. Should be easy, isn’t it? Prompt an LLM with your query and be done! Football season is long gone, but it is the season for the graduation parties, so I decided to look for “help me plan a graduation party”.

Being a database person, I set out to do this using T-SQL. We need to build a prompt with our search query, add some instructions to the LLM, and then send the prompt to an LLM to gather and synthesize the result for us. I created an Azure Open AI deployment for Completions API with GPT-4o, and used the simple yet powerful stored procedure sp_invoke_external_rest_endpoint from the T-SQL code to call the model.

The response comes back as:

Observe the response from the LLM.

• It does give some good suggestions, such as decorations, food, drinks and even recommends having a DJ at the party. Nice!
• Note that the product IDs are just made up. The LLM lacks information about which set of specific products to consider for this query and provides rather generic answers.

While the generic answer is somewhat useful but doesn’t help further; because now I need to find out which store to go to pick up the balloons and the decorations, and which specific decoration items to pick for the occasion, and so on.

This approach (sending a prompt to an LLM with my query) does provide a search result, but nothing more than that. It doesn’t fulfil my quest.

What can a retailer like Walmart do better? It has all the data about its products. And, that’s where lies the differentiator. You can look into your products data and find applicable items for the occasion, and then use the reasoning power of the LLM on this subset of the data to provide you with the best information to fulfil your quest. We will do this in three steps:

Step 1: Get Products data

Step 2: Find products from this dataset that are applicable (in other words, “similar”) to the occasion you have in mind, using vector similarity search

Step 3: Use LLM to reason over the data, and synthesize a response using the products that are available to buy in the retailer catalog

Step 1: Get Products Data

I found that a subset of Walmart products data is available on Kaggle [3]. It is a csv file with 30000 products, and 14 columns / attributes for each product. It is under CC0: Public Domain license.

I downloaded the file, and loaded the data into a table in Azure SQL Database.

Step 2: Find applicable products

To find the products applicable to our occasion, we will use vector similarity search. The new vector capabilities introduced in Azure SQL Database is a game changer here. You don’t need to take your data to a specialized vector store to perform vector similarity search. Taking the data from your operational data store (Azure SQL Database, in this case) to a specialized vector store for performing vector similarity search will increase solution cost and complexity, not to mention the need to move data back and forth between two disparate systems, with all the security, compliance and performance implications that come with data movement.

Step 2.1: Generate Embeddings on your data

The first step is to generate embeddings on the relevant columns of the table (for all the rows in the table) and store them in the database. We will use an Embeddings model of Azure Open AI (please note the difference between the two deployments of Azure Open AI — one for Completions API, and the other for Embeddings) to create embeddings.

[This is a one-time effort. You need to generate embedding for a data item once. If the data changes, you will need to recreate the embedding with the new data and update the corresponding embedding value. If new rows get inserted into the table, you will create embeddings for those new rows and store in the database.]

For our scenario, the most important information corresponding to a product’s relevance to an occasion are the “ product_name ”  and the “ description ” columns. We will create vector embedding for the concatenated text of these two columns in each row, and store the embeddings alongside each row of the table.

To store vector embeddings, we will use a column of type varbinary(8000) (Please note, in the private preview release of the vector capability in Azure SQL Database, vector embeddings are stored in varbinary(8000) columns. This could change in future releases).

First, we will alter the table to add a column to store embeddings.

Next step is to create embeddings for each row in the description column, and store in the newly added embeddings column.

The stored procedure below takes a string (such as the description of a product), makes an API call to a large language model for embeddings, and then returns the embedding.

The key ingredient is the sp_invoke_external_rest_endpoint stored procedure, that enables you to call any REST endpoint (such as Azure Open AI or Open AI endpoints, PowerBI, Azure Functions, etc.) from within T-SQL code. Read more about it here .

We will call the create_embeddings stored procedure we just created, and create embeddings for each product description, one row at a time:

Now we have a products table with data about each product and enriched with embedding vectors.

Step 2.2: Find products that are “similar” to our query

To find “similar” products, we will first create a vector embedding that represents our search text. We will do that using the create_embeddings stored procedure we created above.

Then, we will compare the vector embedding for our search text with the vector embeddings for the products in the table, using the vector distance function newly introduced in Azure SQL Database (Read more about the vector functions here . We used cosine distance in this example, you have other choices as well.). And pick the Top 10 products (we could pick top 20 or top 50 as well) that satisfy our search.

The results returned:

Step 3: Reason with your data and synthesize

Instead of returning the Top 10 rows we obtained above, we will use the LLM to reason over it and provide us with synthesized data for our query.

To do this, we will use the deployment Completions API with GPT-4o model we used at the beginning of this article, and pass our search query and the top 10 output we have obtained above. We will also pass to the LLM an explanation of what data will be provided and how it should be used and how it the LLM should behave. Again we will use the immensely powerful and useful stored procedure sp_invoke_external_rest_endpoint we used earlier.

• It gives recommendations for specific products in the store.
• The product IDs are actual product ids from the database that you find in the store.

Using “your data” in the search definitely improved the relevance of the output and enhanced customer experience.

In summary, the fusion of Generative AI with Azure SQL Database is not just about building applications; it’s about creating experiences that are as unique as your data. It’s time to step into the future and redefine what’s possible with AI, and your business data. After all, AI is the equalizer, and your data is true differentiator!

Call To Action

• Sign up for the Private Preview of vector capabilities in Azure SQL Database: https://aka.ms/azuresql-vector-eap
• Apply the techniques learned here to your data
• Filter the products that are only available in a store in your zipcode
• Generate a cost estimate for the all the items recommended by the application
• I used T-SQL to build this. If your favorite programming language is, say Python, you can build using Python as well. Try it out.
• I didn’t show the step to load the downloaded file into Azure SQL Database. It is an interesting exercise in itself. Try it out.
• Try this out for the next occasion for your family, say Father’s Day, or Grandma’s Birthday
• Check the SQL AI samples at aka.ms/sqlaisamples . The code in this article will be uploaded there soon.

[1] Walmart unveils new generative AI-powered capabilities for shoppers and associates

[2] Walmart Keynote at CES 2024! (The whole video is very exciting. If you want to skip to the part that inspired the example used in this blog, skip to 29:10 ).

[3] Walmart Product Data 2019

[4] sp_invoke_external_rest_endpoint

[5]  Vector Support in Azure SQL Database

Sanjay Mishra Director, Product Management, Azure SQL

Leave a comment Cancel reply

Log in to start the discussion.

Insert/edit link

Enter the destination URL

Or link to existing content

• Stack Overflow Public questions & answers
• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Talent Build your employer brand
• Advertising Reach developers & technologists worldwide
• Labs The future of collective knowledge sharing
• About the company

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

Creating several roles definition and assignment with Bicep template for Azure Cosmos DB

I try to create two role definitions and two role assignments to one Azure CosmosDB SQL API account using Bicep template.

I decompiled below arm template with az bicep decompile : https://github.com/Azure/azure-quickstart-templates/blob/master/101-cosmosdb-sql-rbac/azuredeploy.json

and I got below:

which works, but only when I have one role definition and assignment .

When I try deploy it together with below:

I have got below error:

I tried also iterating instead of having separated resources, using nested modules for each role, but it does not help.

UPDATE 2021-06-02

I tried also deployed json file, but result the same. I attached arm template :

• azure-cosmosdb
• azure-cosmosdb-sqlapi
• azure-bicep

• 1 Have you tried deploying as an ARM template to see if you get the same error? –  Mark Brown Jun 1, 2021 at 16:09
• @MarkBrown - I believe it has something to do with Cosmos DB Resource Provider. I noticed that when I perform any resource management operation (like setting tags) on a Cosmos DB account, its status changes to "Updating" and remain like that for some time (from few seconds to few minutes). Any management operation performed during that time results in an error. Same thing does not happen with other resource providers (like Storage accounts for example). –  Gaurav Mantri Jun 2, 2021 at 2:21
• Its unclear if this is a problem with the RP or not. That's why I'm asking if the ARM template that this bicep file was built from works. If the template throws the same error, then maybe an issue. If not, then the problem is likely with the bicep file. –  Mark Brown Jun 2, 2021 at 2:44
• @MarkBrown Hey Mark, thank you for answers. I attached atm template. Result is the same. Maybe you can point sth. –  gorrch Jun 2, 2021 at 6:53
• @MarkBrown I also tried moving role definition and role assignment to other module, but I can not pass parent resource. –  gorrch Jun 2, 2021 at 9:10

3 Answers 3

Currently the Cosmos resource provider only allows you to create one of these at a time. That restriction will be removed in the near future.

As a workaround, chain the second role definition on the previous role assignment so they get created sequentially. This updated ARM template should do the trick.

• 2 You mentioned "That restriction will be removed in the near future." - it has been about four months so just checking in to see when this will be removed. We need to add n number of assignments so we want to use a copy loop, but keep hitting the PreConditionFailed error. (We cant chain them since they are dynamic) –  briandunnington Nov 5, 2021 at 0:49
• "That restriction will be removed in the near future." - is there an issue we could follow for progress? messing with dependencies really hurts in my biceps with conditional resources, loops, etc. –  Imre Pühvel Aug 2, 2022 at 13:07

To Brian's question above - I was able to resolve mentioned problem related to copy usage by adding @batchSize(1) decorator before the loop(for both role assignment and its parent resource).

This way it deploys resources one by one:

Try using dependsOn:[] - https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/resource-dependencies#explicit-dependency between each of the assignments.

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Sign up or log in

Post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy .

Not the answer you're looking for? Browse other questions tagged azure azure-cosmosdb azure-cosmosdb-sqlapi azure-bicep or ask your own question .

• Featured on Meta
• The 2024 Developer Survey Is Live
• The return of Staging Ground to Stack Overflow
• The [tax] tag is being burninated
• Policy: Generative AI (e.g., ChatGPT) is banned

Hot Network Questions

• Has ever a country by its own volition refused to join United Nations, or those which havent joined it's because they aren't recognized as such by UN?
• Why a truly uninformative prior does not exist?
• Why is array access not an infix operator?
• Was it known in ancient Rome and Greece that boiling water made it safe to drink and if so, what was the theory behind this?
• Are there any jobs that are forbidden by law to convicted felons?
• Word for a country declaring independence from an empire
• Resolving conflicts
• Is it rational for heterosexuals to be proud that they were born heterosexual?
• z score total for fixed alpha and beta in sample size calculation of mean differences
• How much of an advantage is it to have high acceleration in space combat
• Universal PCB enclosure: what are these cylinders with holes for?
• Is it allowed to use patents for new inventions?
• How to animate an impossible image
• Why at 1 Corinthians 10:9 does the Jehovah's Witnesses NWT use the word "Jehovah" when the Greek uses "Christos/kurion" referring to Jesus Christ?
• What does "far right tilt" actually mean in the context of the EU in 2024?
• How to make Bash remove quotes after parameter expansion?
• What did the old woman say in "73 Yards"?
• Looking for some words or phrases
• It suggests vs It would suggest
• How to see face value by name?
• Windows Server 2022 Support for Intel X520-DA2
• C++ static vector revised implementation
• How can I sign up for standby on an earlier flight at Pune International Airport?
• A trigonometric equation: how hard could it be?