I just wanted to know the term I am looking for what it is called so I can research further.
Say I have an C# application that connects to an SQL server located somewhere in a network via connection string. What is the communication layer/protocol that happens in between? For sure the machine name specified in the connection string is converted to an IP address first so there definitely is a DNS query happening beforehand. How does it know that the application is allowed to access the database? Is it in behalf of the user logged in to the machine where the application is being run?
Can anyone shed some light for me on this?
Good interesting question, The answer again lies how client/Server communication happens?, when you want to communicate to SQL server by default it listens on tcp/ip port 1433...and if you configure your sql server for allow remote connections....then the client can communicate to the sql server by specifying the name of the instance on a box .....
From the connection string...if the provided credentials are valid then he will be given access to use the database else he wont.
I hope this helps!.
You are referring to a Data Access Layer (DAL), there are many different styles and types of DAL including a pattern from Microsoft.
I usually create a standard SQL ID (userid and password) for each application (database), this allows you to manage your users internal to the app and extend the credential functionality without having to involve active directory. This is a personal preference I have found useful.