AZURE SQL SERVER AND MANAGED IDENTITY | SQL Connection String Without Username and Password

Рет қаралды 34,836

4 жыл бұрын

Azure SQL supports Azure AD authentication, which means it also supports the Managed Identity feature of Azure AD. With Managed Identity, we no longer need the User Id and Password to connect. The credential is managed automatically by Azure and allows us to connect to resources that support Azure AD authentication.
In this video, let us look at how we can use Manage Identity to connect to Azure SQL from a web application running in Azure. Once set up, all we need is the database server name and the database name to connect to the database
Related blog post: www.rahulpnath.com/blog/azure...
Azure SDK DefaultAzureCredential : Unifying How We Get Azure AD Token - • Azure SDK DefaultAzure...
Azure Managed Identity and Local Development - • Azure Managed Identity...
Managed Identity and SQL Server - docs.microsoft.com/en-us/azur...
EF Core (First Class Support for Azure AD Token) github.com/dotnet/efcore/issu...
Image by Darwin Laganzon from Pixabay - pixabay.com/illustrations/hac...

Пікірлер: 91

@RahulNath 4 жыл бұрын

Source Code Available here - github.com/rahulpnath/Blog/tree/master/SqlMSI

@neonjay80 4 жыл бұрын

thanks heaps!

@amazdanny 3 жыл бұрын

Brilliant! Would love to watch more videos, thank you

@RahulNath 3 жыл бұрын

Awesome, thank you!

@bmrreddy2002 Жыл бұрын

Hey Rahul, is the App Service IP whitelisting required? If MSI is enabled, can we just add that identity on the SQL server with certain roles?

@sunpengjie4565 2 жыл бұрын

This is the best video in regards to managed identity, so helpful.

@RahulNath 2 жыл бұрын

Glad you think so Sun!

@slotslights5872 Жыл бұрын

Thanks RahulNath, it was easy explanation of Managed identity.

@RahulNath Жыл бұрын

Glad you liked it! Do check out the full series here bit.ly/asp-net-core-series Also have an associated blog post for this www.rahulpnath.com/blog/azure-sql-server-managed-identity/

@mekarthikktk75 Жыл бұрын

Hello Rahul , Were will I get the credential for the AD Password Authentication , When we grant acees to function app ?

@123damma 2 жыл бұрын

Awesome tutorial !!. You are a life saver man. Thank you so much!

@RahulNath 2 жыл бұрын

Glad it was helpful! Do check out the full series bit.ly/asp-net-core-series. I am sure you will like it.

@Ranjittgs 2 жыл бұрын

Thank you soooo much Rahul for providing Azure content for free. Very very useful for me🙏🙏🙏

@RahulNath 2 жыл бұрын

Glad to hear that Ranjit! Hope you are enjoying the videos in the playlists twitter.com/rahulpnath/status/1499626419357630466?s=20&t=sbfPxWO60NsomZtqQdKyZg

@mohammedmus-abbelgami5394 7 ай бұрын

Hi Rahul, I have deployed Node.js app in WebApp and trying to connect sql DB and WebApp using MSI behind private endpoints I have enabled the MSI and granted WebApp access to SQL DB. Local setup seems to be working fine however I am encountering 500 internal error when hosted on WebApp any inputs?

@user-ho9we2iz4u 9 ай бұрын

Tutorial was very nice. How to validate the life time of a access token retrieved through Azure?

@oolu3340 Жыл бұрын

vary helpful, thank you greatly

@neonjay80 4 жыл бұрын

Very informative video. I also visited your blog and there are lots to learn there as well. Is the code to the entire solution available somewhere? Keep up the good work!

@RahulNath 4 жыл бұрын

Thank you and glad you liked it. Sorry missed uploading the source code earlier. You can find it here github.com/rahulpnath/Blog/tree/master/SqlMSI

@deepakmitawa8106 2 жыл бұрын

really enjoying your videos to learning. keep it up

@RahulNath 2 жыл бұрын

Glad to hear it Deepak! Here are my popular series links in case you need them twitter.com/rahulpnath/status/1499626419357630466?s=20&t=9hNe41BG30W0k4rhV9vtjg

@Sergio-Here-In-Community 3 жыл бұрын

Very good, demo of how to configure things, I like very much hand on that help see how things works, not just theory. thanks I subscribed to your channel and I will see your your videos. thanks for it.

@RahulNath 3 жыл бұрын

Thank you Sergio and happy that you found it useful. Yes with this channel I try to show how things work a bit more as I often find that helps in understanding something better. Thanks again and do drop in if you have areas that you would like to be covered.

@fahadmuhammad6338 2 жыл бұрын

I followed all the steps, i did got token but facing this exception when try to connect to Sql Server => SqlException: Login failed for user ''. The server is not currently configured to accept this token.

@pankajutubepankaj 2 жыл бұрын

@Rahul Nath, learned a lot from your videos. I have a query. How to achieve passwordless connection string in .Net worker process. I want to deploy .net core 5 windows service to multiple VMs. The service does some Azure DB related work. Is it possible to have passwordless connection string in this scenario?

@RahulNath 2 жыл бұрын

Glad you are enjoying the videos Pankaj. With Azure password less connections are achievable using Managed Identity, like I show in this video. I have also written about it here www.rahulpnath.com/blog/defaultazurecredential-from-azure-sdk/ You can check the services that support it here docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/services-support-managed-identities?WT.mc_id=AZ-MVP-5003875 If the services that you are using is not supported then the next best choice is to use Azure App Configuration or Key Vault. I talk about the different ways here kzfaq.info/get/bejne/rbqDqJWSqNW2Z40.html in case you haven't watched it already. Let me know in case you have additional questions.

@chrismcclure4264 3 жыл бұрын

Great video. Make more like this.

@RahulNath 3 жыл бұрын

Sure Chris, Will do. Gald you like them!

@swapnarenukunta6064 8 ай бұрын

The best video. Thank you so much Rahul

@RahulNath 8 ай бұрын

Glad you liked it Swapna - Hope you are enjoying the series bit.ly/asp-net-core-series

@swapnarenukunta6064 8 ай бұрын

@@RahulNaththanks for the reply Rahul. Would that be same if we do with Azure function? Please help

@RahulNath 8 ай бұрын

@@swapnarenukunta6064You should be able to achieve with Functions as well. Here is a video on using Functions with managed identity kzfaq.info/get/bejne/qJqBeaWkuZi9dWw.html&ab_channel=RahulNath

@seanwauchope 2 жыл бұрын

Hi Rahul, great tutorial. I followed this and was able to connect. One issue is that the token expires after a certain time. How would you refresh the token automatically?

@RahulNath 2 жыл бұрын

Glad you like it Sean. The DefaultAzureCredential token should get refreshed automatically. What error are you getting and when/where? In this blog post, I also talk about handling secret rotation towards the end www.rahulpnath.com/blog/connect-net-core-to-azure-key-vault-in-ten-minutes/ Is that something you are looking for?

@RahulNath 2 жыл бұрын

Saw your reply comment in email, but by the time I came here, it's gone. Curious if you had deleted it or it's KZfaq's spam detection algorithm. Have heard a few other people tell me about this. As for the token it might be you are using a single instance, in that case, you will have to refresh it as I show in the post above. Hope it helps you.

@seanwauchope 2 жыл бұрын

@@RahulNath Yes it was deleted by KZfaq. Okay thank you. I will read through the post and see if I can apply the same method.

@shivakumar-lc1if 3 жыл бұрын

Very Helpful, Informative and straight to the Point. Thank you for the awesome video. Also Rahul, can you also please let know, do we still need the AD user added in the Azure Admin, after granting the 'App Service' Read-write permissions in the SQL. I tried after removing the AD user, application is not connecting to SQL from Azure hosted (It is working with the User in AD). It will be great if you could help understand what is happening under the hood. Thanks Again.

@RahulNath 3 жыл бұрын

Thanks Shiva and Good point - probably it does requre the AD user added in. I never tried removing it before. Is there a reason why you don't need the user in there?

@srujannallandhigal6469 2 жыл бұрын

@@RahulNath Being an admin for a dev db can be ok. But if you are admin for a prod db, we will be able to login to the database using ssms with sql authentication using mfa by using just the email id used for cloud login. That can provoke a crazy security issue.

@RahulNath 2 жыл бұрын

@@srujannallandhigal6469 You don't need to be an admin - the identity that requires access to the database will need to be given the appropriate premissions required. Hope that makes it clear. Happy to help if you need more details.

@bhagyashreechaudhari7030 2 жыл бұрын

In my application i m using SSO(Single Sign-On), and want to use Azure Key vault to store SQL Server password. Could you help on it how could i proceed? *Note - i dont have access of Azure Active Directory, without it how could i do it?

@geneuglijk 3 жыл бұрын

Thanks this video helped a lot!

@RahulNath 3 жыл бұрын

Glad it helped Mark!

@pavankumars9313 2 жыл бұрын

You are great . I am your fan 🙏

@RahulNath 2 жыл бұрын

Many many thanks Pavan!

@minhcdao 2 жыл бұрын

Hi Rahul, is it possible to get accessToken for Azure SQL DB using Azure.Identity.DefaultAzureCredential from the current authenticated user (after OpenID login) on the server? It's double-hop use case. Thanks.

@RahulNath 2 жыл бұрын

Hey Minh, Not sure if I fully understand the question. What are you trying to achieve? You can get the token I guess from the credentials class

@minhcdao 2 жыл бұрын

@@RahulNath Hi Rahul, thanks for your reply. Here is the use case. First, user will be authenticated using open ID connect to get in to the app (first hop). Second, when user fetches data from the db, we would want the user to run the query with his or her own account in the db Not app ID (second hop). The token I get from DefaultAzureCredential is from the user assigned MSI on Azure not the current authenticated user. I have current user claims from the open IDC. Is there a way to get db access token with current user claims?

@RahulNath 2 жыл бұрын

@@minhcdao I haven't done something similar before. Have you come across this article that does talk about User-assigned managed identity in Azure AD for Azure SQL. Will need to explore it a bit further though. docs.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-user-assigned-managed-identity?view=azuresql&WT.mc_id=AZ-MVP-5003875

@Manu245 3 жыл бұрын

Thanks for such a amazing video Rahul, I am trying in the same manner and getting the 400 bad request error, what can be the reason?

@RahulNath 3 жыл бұрын

Thank you and glad you liked the video. Where are you getting the 400 bad request error? Do you have any other exception details that you can share?

@zipzapz0p 3 жыл бұрын

Hi . I took out the .default in "database.windows.net/.default" and then it worked for me

@RahulNath 3 жыл бұрын

@@zipzapz0p Glad you got it working Waldo!

@maneeshpardeshi4032 Жыл бұрын

Perfect..

@aramosvizcarra 5 ай бұрын

Good stuff!

@RahulNath 5 ай бұрын

Glad you enjoyed it! Do check out the series bit.ly/asp-net-core-series

@ncvman 2 жыл бұрын

Is this the most secure way to connect to sql? Would getting a secrets or certs be better?

@RahulNath 2 жыл бұрын

It's secure because you don't have to manually maintain/rotate secrets. Azure infrastructure will do this for you. Check this post on different ways to manage sensitive info www.rahulpnath.com/blog/handling-application-configuration/

@ncvman 2 жыл бұрын

@@RahulNath Thanks.

@simplyk8s665 2 жыл бұрын

hi Rahul, i am getting this error. any clue? "System.Data.SqlClient.SqlException: 'Login failed for user ''. The server is not currently configured to accept this token.'"

@RahulNath 2 жыл бұрын

hard to tell without seeing it. Hope you were able to resolve the issue

@swapnilgadge8125 2 жыл бұрын

Thanks for awesome videos. Just one suggestion "Kadi has bhi liya karo" :)

@RahulNath 2 жыл бұрын

Haha yes I am trying! Working on improving the content delivery, hope it's better with the latest videos 😀

@pradeepjuyal2475 2 жыл бұрын

Can you suggest how we can manage different connectionString or other secrets for different environment(dev,test,qa,prod) for db hosted in azure VM.

@RahulNath 2 жыл бұрын

Hey Pradeep, Is this for CI/CD? You can either have it in KeyVault, Azure App Configuration or simply have them as release variables in your DevOps pipeline. Will need more information on your setup and what you are looking to achieve to suggest better. I have relevant videos on the above in this channel.

@pradeepjuyal2475 2 жыл бұрын

@@RahulNath Thanks for your response. Meanwhile I also figured it out after watching you environment variable video. We will store the different connection string/credential in KeyVault and then can set them in release variable in CI/CD release pipeline. Also, We can set the connection string in WebApp=>Configuration=>ConnectionString Tab but need to figure out how we can connect this with key vault and then use in CI/CD pipeline or the webapp will call this automatically.

@RahulNath 2 жыл бұрын

@@pradeepjuyal2475 You can directly use key vault from your application code www.rahulpnath.com/blog/connect-net-core-to-azure-key-vault-in-ten-minutes/ Or also Azure App Configuration which gives more advanced features specific to configuration. kzfaq.info/get/bejne/jKuajLpimb2vgoE.html Let know in case you need further help with this.

@pradeepjuyal2475 2 жыл бұрын

@@RahulNath Thanks bro. U r a Champ.

@RahulNath 2 жыл бұрын

@@pradeepjuyal2475 Thank you 😀

@hanimv 2 жыл бұрын

Hi, I followed your videos. First of all Many thanks. It have helped a lot. I am using .net core 5, EF core and Azure identity nuget package. From local it works. I specified the environment variables with App Registration details. But once its deployed to the app service, which have a user managed identity, it give me below error. The identity do have access to the database Error: Azure.Identity.AuthenticationFailedException: ManagedIdentityCredential authentication failed: No MSI found for specified ClientId/ResourceId.

@RahulNath 2 жыл бұрын

Have you set up the application and enabled Managed Identity in the Azure App? Hard to tell without seeing this. Suggest going through the video and trying the set up again to make sure no steps are missed.

@andriiliashenko7875 4 жыл бұрын

when I convert System.Data.SqlClient to Microsoft.Data.SqlClient (var conn = (Microsoft.Data.SqlClient.SqlConnection)Database.GetDbConnection();) I get error "Unable to cast object of type 'System.Data.SqlClient.SqlConnection' to type 'Microsoft.Data.SqlClient.SqlConnection". Please, any help!

@RahulNath 4 жыл бұрын

Can you check the source here to see if everything is as expected? github.com/rahulpnath/Blog/blob/master/SqlMSI/SqlMSI/QuoteContext.cs Let know if that helps. Otherwise, If you can post a sample somewhere I can take a look

@andriiliashenko7875 4 жыл бұрын

@@RahulNath in my code, in constructor ApplicationDbContext I get in this line "var conn = Database.GetDbConnection();" System.Data.SqlClient.SqlConnection. In your code you get Microsoft.Data.SqlClient.SqlConnection in that line. How can I change DbProviderFactory?

@RahulNath 4 жыл бұрын

@@andriiliashenko7875 Are you registering the Context the same way github.com/rahulpnath/Blog/blob/master/SqlMSI/SqlMSI/Startup.cs Also assuming package version etc are same. Is there a sample repo that you can share?

@andriiliashenko7875 4 жыл бұрын

@@RahulNath I registred Context using " services.AddDbContext(c => c.UseSqlServer(connectionString));" Sorry, this is private repo. I think the problem is that version .net core. I am using .net core 2.2.

@RahulNath 4 жыл бұрын

@@andriiliashenko7875 Does this help github.com/dotnet/efcore/issues/18028

@2cool238 3 жыл бұрын

this videos is awesome can you make a video on how to do managed identity with event grid.

@RahulNath 3 жыл бұрын

Thank you and glad it helped. Sure I will try and do a video some time in the future, but can't guarantee when. You can get started with this link here- docs.microsoft.com/en-us/azure/event-grid/managed-service-identity Do let know in case you any particular questions or get stuck and I can try and help!

@2cool238 3 жыл бұрын

@@RahulNath seen the doc. I am trying to use managed identity with how you used it in this video. publish on a topic from a web app using managed identity instead of using a topic key. The doc does not go into that Scenario. I am not even sure if it is possible.

@RahulNath 3 жыл бұрын

Which library are you using to connect to the EventHub? Looks like the below is possible using the 'Azure.Messaging.EventHubs' nuget package - www.nuget.org/packages/Azure.Messaging.EventHubs/ var defaultCredential = new DefaultAzureCredential(); var client = new Azure.Messaging.EventHubs.Producer.EventHubProducerClient("namespace", "hubName", defaultCredential); Let know if that helps

@2cool238 3 жыл бұрын

@@RahulNath I am using event grid. it does not have a constructor that takes AzureDefaultCredentials. It is probably not supported. I am trying to replicated what you did in this videos. in a build pipeline. I cannot for the life of me figure out how to parameterize the print. when you do this in a pipeline are you able to parameterize the app service name in the sql script?

@RahulNath 3 жыл бұрын

Sorry I did not fully understand the question - Can you please clarify which sql script you are referring to?

@rajachitturi7612 Жыл бұрын

Do we write test cases for this

@RahulNath Жыл бұрын

Can you expand a bit more? What are you planning to test for?

@srinivasadineshparupalli5139 4 жыл бұрын

I believe you should use PPT along with code demo. You are talking a lot about the concepts, its good but when they are as a part of notes in the PPT, then it attracts the attention. And also, I feel you can talk bit slow.

@RahulNath 4 жыл бұрын

Awesome thank you for the feedback! Yes a few others have mentioned about going slow. I have been working on it on the later videos and will keep trying to do that. Good point with the ppt, will try and add a slide in at places where I just talk with the code window open. In this video, did you find any specific places where a slide would have helped? Thanks again!

@calaiarasan7318 5 күн бұрын

U r very fast😢

@RahulNath 3 күн бұрын

I have an associated blog post www.rahulpnath.com/blog/azure-sql-server-managed-identity/ I talk a lot about this in other videos/posts if that helps www.rahulpnath.com/blog/defaultazurecredential-from-azure-sdk/ www.rahulpnath.com/blog/authenticating-with-azure-key-vault-using-managed-service-identity/ www.rahulpnath.com/blog/azure-managed-service-identity-and-local-development/ Let me know if I can help further.