Partially contained databases and Windows Logins – best practices?

In versions of SQL Server prior to 2012, in order for anyone to log in to a database using Windows credentials, you first had to create a server-level login, them map that login to a user for each database.

In 2012/2014+ my understanding is that this is not necessary, and that a user can be granted access to a database merely by granting their domain login permissions on the database directly.

Is there any reason why we as SQL Server Admins should define a server-level login for a Windows account if that account isn’t intended to have any specific server-level role (backupoperator, sysadmin, etc)? Is the best practice now to simply forego the server level login definition for Windows accounts altogether, unless you want to grant that login a server-level role?

I was going to just comment the link to the SQL Server 2012 Best Practice security white paper…but found out Microsoft took it off the Internet for some reason. You might be able to find a cached version of it some where online, but I will paste in the contents of contained database section of the document here.

There is also a BOL article for Security Best Practices with Contained Databases, this may be why the white paper was taken off but not sure.

Author of this whitepaper: Bob Beauchemin, SQLSkills; Published January 2012; Applies to SQL Server 2012

Contained Databases and Authentication

SQL Server 2012 introduced the concept of contained databases. A
contained database is a database that includes all of the settings and
metadata needed for its operation with dependencies on the instance.
From a security perspective, a contained database makes it easier to
have user accounts that are limited to a single database. SQL Server
2012 supports partially contained databases; it does not yet support
full containment. Although contained databases allow more control by
the “application administrator” they do have security repercussions.

Contained databases are interesting for a security point of view in
that they allow defining users with authentication privileges, i.e.
users who can log directly into a contained database without a
corresponding login. Contained databases support two types of users:
Windows users and groups that can directly connect to the database and
do not need logins, and users with a password where the password is
authenticated by the database, not the instance. This is not permitted
by default; the instance administrator must specifically allow it by
setting the “contained database authentication” configuration option
on. To disallow connections to specific contained databases but allow
access to other contained databases, a login trigger can be used.

Allowing direct connection of users to a database changes the
effective threat-level of some existing permissions. For example, the
permission ALTER ANY USER in a contained database gives permission to
add user-based access to an instance through a contained database.
Logins with ALTER ANY DATABASE or CONTROL DATABASE permission could
also set containment on a database and add instance access through
users. In addition, contained database users can connect to other
database in the instance if the guest account is enabled.

Although you can use the system stored procedure
sp_migrate_user_to_contained to migrate a database user mapped to a
SQL login to a contained database user-with-password, contained
databases’ user-with-password does not use password history and
expiration policies as SQL logins do, although password complexity
checks are still used. Therefore, attaching a contained database could
allow weak passwords. In addition, the password hashes for these
passwords are stored in the database, not in master. Anyone with
access to the database file could perform a dictionary attack on a
separate, unaudited instance.

There exists the possibility of conflicts, if a login and contained
database user have the same name. The rule for resolving this conflict
is that if an initial catalog is specified in the connection string
and it’s the contained database, access is checking against the user
based principal, not the login. To ameliorate this possibility, do not
create conflicting names or specify a contained database as an initial
catalog in a connection string. In addition, members of the sysadmin
role should not use initial catalog in a connection string.

Best practices for contained databases

• Use the default (off) setting for contained database authentication and only turn this setting on if it is required.
• Protect backups of contained databases using passwords.
• Audit the capabilities of users and modules in contained databases.
• Audit logins that have the ability to set containment, if contained database authentication is allowed.
• Disable the guest account on databases that share an instance with contained databases.
• Take care to avoid login/user-with-login naming conflicts
• Avoid connection strings with initial catalog if contained database authentication is permitted.