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.