What are good practices for granting database permissions to a web service connection?

Question

I have been searching for articles and SQL script examples that would demonstrate how to securely and conveniently solve one of the most common scenarios - connecting from a .Net Core Entity Framework based web application to an SQL database.

But somehow I could not find any coherent step-by-step guide from a reputable source.

Let's assume the following:

• I cannot use integrated Windows auth in the connection string and must use username and password based auth (because hosting on a Linux server and the DB is on a different Windows server)

• the web service will need your usual minimum set of permissions - connect to the database, read data, write data, delete data, execute stored procedures

While reading many tutorials, I find there are multiple ways to manage the connection permissions. To avoid this question being too broad, I'll list my current choices as I understand them (please correct me if I'm missing something).

Users and logins:

• create a login and a user for the database
• create a database-only user without a login (not sure if this is applicable to a web app and connection string, but still it's a feature that I've seen being used)

Assigning permissions:

• assign the user to some fixed SQL role (db_datareader, db_datawriter AND also will have to grant EXECUTE permission)
• grant all fixed permissions
• create a custom role (let's say, db_web_apps) with required permissions

Which choices are better (more secure and easier to manage in general) and recommended by SQL DBAs?

I think every database administrator should have a template script handy for quickly adding a new user with minimum required permissions every time when developers ask for a new connection for their shiny new web app.

If you know a good, reliable tutorial or GitHub / Gist example that explains what and why is being done that way or a script that you yourself have used for years without any issues in production environments, I'll really appreciate if you could share it.

Answer 1

Create a role in the database and assign the required privileges to the role. Don't use the fixed database roles. Instead grant permissions directly to objects, schemas, or the entire database if necessary. Like this:

create role trusted_app_role
grant select, insert, update, delete, execute 
  on schema::dbo to trusted_app_role

That will grant the role full DML permissions on all the objects in the default dbo schema. So if you have any tables or procedures you don't want the app to have access to, just create them in a different schema, say, admin. This way you never have to fiddle with permissions as you add objects. The fixed database roles predate schema-based permissions, and aren't really needed any more.

For your application's identity, add Active Directory or Azure Active Directory (Azure SQL) identities to this role, or, if you can't, add SQL Users to the role.

If you are on Azure SQL, you should normally use a database user without a login. On SQL Server you can only add "contained database users" if you enable Partial Database Containment. Which you can do, but is incompatible with Change Tracking and Change Data Capture, so it's a tradeoff.

So normally for SQL Server you still create a login and map the user to the login. EG:

create login web_service_user with password = '5X+jeuAB6kmhw85R/AxAg'
create user web_service_user for login web_service_user

And then add that user to your role

alter role trusted_app_role add member web_service_user