{"id":89732,"date":"2021-02-01T17:00:00","date_gmt":"2021-02-01T17:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=89732"},"modified":"2021-02-01T21:03:19","modified_gmt":"2021-02-01T21:03:19","slug":"azure-sql-and-managed-identity","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/azure-sql-and-managed-identity\/","title":{"rendered":"Azure SQL and Managed Identity"},"content":{"rendered":"

Azure SQL<\/strong> has a close relationship with Azure Storage<\/strong>. Features like Polybase, backups, extended events and more make use of Azure Storage.<\/p>\n

On Azure SQL Database, probably the most common use is Extended Events<\/strong>. When we create a file target, we need to point to the Azure Storage URL where the file will be stored.<\/p>\n

Using Keys<\/h2>\n

In order to access Azure Storage, we need to control the authentication. The most common way is to use credentials, creating a credential with the storage SAS<\/strong> key<\/p>\n

. \"Graphical<\/p>\n

Once we generate the SAS<\/strong> key, we create the credential object inside our database. For this we have Database Scoped a new feature introduced exactly for these situations, allowing us to create the credential inside the database, instead of at server level.<\/p>\n

The statement to create the credential would be like this:<\/p>\n

CREATE<\/span>\u00a0DATABASE<\/span>\u00a0scoped<\/span>\u00a0credential<\/span>
\n[https:\/\/dummystorageaccount.blob.core.windows.net\/extendedevents]<\/span>\u00a0WITH<\/span>\u00a0IDENTITY<\/span>
\n=<\/span>‘SHARED\u00a0ACCESS\u00a0SIGNATURE’<\/span>,<\/span>\u00a0secret<\/span>\u00a0=<\/span>
\n‘sv=2019-12-12&ss=b&srt=sco&sp=rwdlacx&se=2021-12-30T20:03:34Z&st=2021-01-22T12:03:34Z&spr=https&sig=kMj8oq7bKderywanUcYN9vE3ebx0GxOaj3N7NU%2BMdgE%3D’<\/span>\u00a0 <\/span><\/div>\n

The Identity value is fixed, to identify the kind of key we are using, a SAS<\/strong> key. The name needs to be the URL to the container, but not always, it depends on what feature will be using this credential.<\/p>\n

Some more recent features are prepared to use credentials, receiving the name of the credential as a parameter. In these cases, the credential can have whatever name we would like.<\/p>\n

However, older features, such as Extended Events<\/strong>, are not prepared to be linked to a credential. This is solved by ensuring the credential name has the same name of the path used by the feature. In this way, SQL Server identifies which credential should be used for that access.<\/p>\n

Avoiding Keys<\/h2>\n

This method ends up spreading SAS<\/strong> keys all around. We can’t really tell this is unsafe, since the credentials are kept encrypted using the SQL Server encryption system<\/a>, but it would be better if we could make the authentication without directly using a key or password.<\/p>\n

That’s what we can achieve using Managed Identities<\/strong>. Each service on Azure can have its own identity registered with Azure Active Directory. Once the identity is stablished, we can use Role Based security (RBAC<\/strong>) to set permissions for the service, avoiding the use of passwords or keys.<\/p>\n

Azure SQL Database doesn’t have a control on the UI to set the managed identity, but we can easily do it using PowerShell in the cloud shell on the portal.<\/p>\n

\"Graphical<\/p>\n

The statement to set the managed identity is like this:<\/p>\n

<\/p>\n

Set-AzSqlServer -ResourceGroupName <<resourcegroup>> -ServerName <<sqlservername>> -AssignIdentity\r\n<\/pre>\n
<\/span><\/p>\n
Setting Identity Permissions<\/h2>\n
Once we execute this statement, the server gets an identity, and we can use this identity to control the access to the Azure Storage. The next step is to give permission to this identity.<\/p>\n
When dealing with RBAC<\/strong> permissions it’s important to mind there are two kinds of permissions: Object permissions, for managing the object, and data permission, to access the data within the object. In this case, we need the data permissions, so SQL Server will be able to write the extended events file to the blob storage. We can set the Managed Identity<\/strong> with the permission Storage Blob Data Contributor<\/strong>, which is different than Storage Blob Contributor<\/strong>.<\/p>\n
On the storage account, we access the tab Access Control (IAM)<\/strong> and click the Add<\/strong> button. A new window will open, where we configure the identity and permission. The permission is set on Role<\/strong> dropdown, we just choose it. We type on the Select<\/strong> text box to filter the identities. The Manage Identity<\/strong> will have the same name as the SQL Server we created. Once found, we select it and complete the creation of the role assignment.<\/p>\n
\"Graphical<\/p>\n
Next step is creating a credential in the database to use the managed identity. This time we don\u2019t have a secret for the credential, we only define the type of Identity we will use:<\/p>\n
 CREATE<\/span>\u00a0DATABASE<\/span>\u00a0scoped<\/span>\u00a0credential<\/span> 
\n[https:\/\/dummystorageaccount.blob.core.windows.net\/extendedevents]<\/span>\u00a0WITH<\/span>\u00a0IDENTITY<\/span> 
\n=<\/span>\u00a0‘Managed\u00a0Identity’<\/span>;<\/span>\u00a0 <\/span><\/div>\n
Creating the XE Session<\/h2>\n
This is only an example of an Extended Events session pointing to the blob storage. It will use the credential to authenticate and, in our example, will be using the SQL Server Managed Identity.<\/p>\n
 CREATE\u00a0EVENT<\/span>\u00a0SESSION<\/span>\u00a0[queries]<\/span>\u00a0ON<\/span>\u00a0DATABASE<\/span> 
\nADD<\/span>\u00a0EVENT<\/span>\u00a0sqlserver<\/span>.<\/span>sql_statement_completed<\/span>(<\/span> 
\nACTION<\/span>(<\/span>sqlserver<\/span>.<\/span>client_app_name<\/span>,<\/span>sqlserver<\/span>.<\/span>client_hostname<\/span>,<\/span>sqlserver<\/span>.<\/span>context_info<\/span>,<\/span>sqlserver<\/span>.<\/span>database_id<\/span>,<\/span>sqlserver<\/span>.<\/span>database_name<\/span>,<\/span>sqlserver<\/span>.<\/span>username<\/span>)<\/span>)<\/span>,<\/span> 
\nADD<\/span>\u00a0EVENT<\/span>\u00a0sqlserver<\/span>.<\/span>sql_statement_starting<\/span>(<\/span>SET<\/span>\u00a0collect_statement<\/span>=<\/span>(<\/span>1<\/span>)<\/span> 
\nACTION<\/span>(<\/span>sqlserver<\/span>.<\/span>client_app_name<\/span>,<\/span>sqlserver<\/span>.<\/span>client_hostname<\/span>,<\/span>sqlserver<\/span>.<\/span>database_id<\/span>,<\/span>sqlserver<\/span>.<\/span>database_name<\/span>,<\/span>sqlserver<\/span>.<\/span>username<\/span>)<\/span>)<\/span> 
\nADD<\/span>\u00a0TARGET<\/span>\u00a0package0<\/span>.<\/span>event_file<\/span>(<\/span>SET<\/span>\u00a0filename<\/span>=<\/span>N’https:\/\/dummystorageaccount.blob.core.windows.net\/extendedevents\/queries\/’<\/span>)<\/span> 
\nWITH<\/span>\u00a0(<\/span>STARTUP_STATE<\/span>=<\/span>ON<\/span>)<\/span>GO<\/span> <\/span><\/div>\n
Conclusion<\/h2>\n
Managed Identities can make our cloud environment safer, removing keys and passwords from our control.<\/p>\n","protected":false},"excerpt":{"rendered":"
Azure SQL has a close relationship with Azure Storage. Features like Polybase, backups, extended events and more make use of Azure Storage. On Azure SQL Database, probably the most common use is Extended Events. When we create a file target, we need to point to the Azure Storage URL where the file will be stored….…<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[5364,136322,136323,136324,4162],"coauthors":[6810],"class_list":["post-89732","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-azure","tag-azure-sql","tag-credential","tag-managed-identity","tag-microsoft"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89732","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=89732"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89732\/revisions"}],"predecessor-version":[{"id":89740,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89732\/revisions\/89740"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=89732"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=89732"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=89732"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=89732"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}