Sql-server – Partially contained databases and Windows Logins – best practices

Securitysql serversql server 2014

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?

Best Answer

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.
Related Question