AZURE SQL SERVER AND MANAGED IDENTITY | SQL Connection String Without Username and Password
HTML-код
- Опубликовано: 15 сен 2024
- 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...
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...
EF Core (First Class Support for Azure AD Token) github.com/dot...
Image by Darwin Laganzon from Pixabay - pixabay.com/il...
![Eminem - Like My Shit [Official Audio]](http://i.ytimg.com/vi/vx_IMvFkrfA/mqdefault.jpg)
Source Code Available here - github.com/rahulpnath/Blog/tree/master/SqlMSI
thanks heaps!
This is the best video in regards to managed identity, so helpful.
Glad you think so Sun!
Brilliant! Would love to watch more videos, thank you
Awesome, thank you!
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!
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
Thank you soooo much Rahul for providing Azure content for free. Very very useful for me🙏🙏🙏
Glad to hear that Ranjit! Hope you are enjoying the videos in the playlists twitter.com/rahulpnath/status/1499626419357630466?s=20&t=sbfPxWO60NsomZtqQdKyZg
Thanks RahulNath, it was easy explanation of Managed identity.
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/
Tutorial was very nice. How to validate the life time of a access token retrieved through Azure?
Nice video Rahul thanks you. Quick question is it same as Entra authentication ..? I have to move my SQL servers access from UserName/Password to Entra authentication any suggestion please
Thanks for awesome videos. Just one suggestion "Kadi has bhi liya karo" :)
Haha yes I am trying! Working on improving the content delivery, hope it's better with the latest videos 😀
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?
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?
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 RUclips'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.
@@RahulNath Yes it was deleted by RUclips. Okay thank you. I will read through the post and see if I can apply the same method.
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.
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?
@@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.
@@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.
Awesome tutorial !!. You are a life saver man. Thank you so much!
Glad it was helpful! Do check out the full series bit.ly/asp-net-core-series. I am sure you will like it.
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.
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.
really enjoying your videos to learning. keep it up
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
The best video. Thank you so much Rahul
Glad you liked it Swapna - Hope you are enjoying the series bit.ly/asp-net-core-series
@@RahulNaththanks for the reply Rahul. Would that be same if we do with Azure function?
Please help
@@swapnarenukunta6064You should be able to achieve with Functions as well. Here is a video on using Functions with managed identity ruclips.net/video/r4QFBsT2YE8/видео.html&ab_channel=RahulNath
@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?
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 ruclips.net/video/wTSu2aCoR7Y/видео.html in case you haven't watched it already. Let me know in case you have additional questions.
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.
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
@@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?
@@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
Great video. Make more like this.
Sure Chris, Will do. Gald you like them!
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?
vary helpful, thank you greatly
Hello Rahul , Were will I get the credential for the AD Password Authentication , When we grant acees to function app ?
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.
You are great . I am your fan 🙏
Many many thanks Pavan!
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?
this videos is awesome can you make a video on how to do managed identity with event grid.
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!
@@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.
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
@@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?
Sorry I did not fully understand the question - Can you please clarify which sql script you are referring to?
Thanks this video helped a lot!
Glad it helped Mark!
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?
Is this the most secure way to connect to sql? Would getting a secrets or certs be better?
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/
@@RahulNath Thanks.
Perfect..
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.'"
hard to tell without seeing it. Hope you were able to resolve the issue
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?
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?
Hi . I took out the .default in "database.windows.net/.default" and then it worked for me
@@zipzapz0p Glad you got it working Waldo!
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.
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.
Good stuff!
Glad you enjoyed it! Do check out the series bit.ly/asp-net-core-series
How to connect using managed identity? And not the token?
Can you provide more details on what you are trying to do?
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.
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!
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.
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.
@@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.
@@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.
ruclips.net/video/VEjYW14WKRM/видео.html
Let know in case you need further help with this.
@@RahulNath Thanks bro. U r a Champ.
@@pradeepjuyal2475 Thank you 😀
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!
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
@@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?
@@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?
@@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.
@@andriiliashenko7875 Does this help github.com/dotnet/efcore/issues/18028
Do we write test cases for this
Can you expand a bit more? What are you planning to test for?
U r very fast😢
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.