Tips for using DB user with password

     Creating DB-specific users with password on a contained DB can provide a lot of mobility for applications since it enables the possibility of moving a DB from any particular instance to another one without the need to also manually move login information.

  This new capability presents a lot of benefits, but it also implies new challenges and responsibilities for DB administrators and developers in order to deploy securely. Here I present a few tips that should be useful to make use of this new tool.

    When using Windows authentication, the only information that is stored on the DB is the SID for the principal and the NetBIOS (domain\name) representation for the user, but no password information is stored. On the other hand, when using T-SQL based user with passwords, the hashed password will be stored within the database. Use Windows authentication for DB-authenticated principals whenever is possible. The fact that these type of deployment relies on Windows for password management is a great chance for minimizing the attack surface area regarding the user credentials.

    Because the password hashes for user with password are stored within the database; while the password hashes are salted, and these hashes are not accessible through the regular catalog views, the metadata storing it would be accessible to a DBA or anyone with access to unencrypted DB files. For risk analysis purposes, we should be under the consideration that these passwords may be cracked by a sufficiently motivated adversary with such privileges.

  Also following best practices, it is highly recommended that passwords for “user with password” are unique to the DB and not shared across applications (including other DB principals), or other services. If you are considering sharing authentication information across more than one application, I strongly recommend using contained DB Windows authentication or, if not possible, consider using regular login/users in order to avoid unnecessary duplication of authentication information.

  I would like the opportunity to emphasize that it is highly discouraged to reuse passwords in multiple applications. If for any reason an adversary may get access to what may be considered a password for low-value assets, he may start trying the same login/password combination on higher value assets. Reusing login/password information in multiple places is a risky password management strategy; for a clear real-world example, we can look at the Gawker Media account information incident in December 2009, where the attackers leveraged on account information reuse to access other websites, including banking information.

  When developing a DB for any given application it is important to avoid the deployment of users with predefined passwords the same way you should avoid hard-coding passwords in your application. The risk is exactly the same in the two scenarios: If a preconfigured user with a well-known password is deployed by default, the adversaries will be able to make use of such user/password to access any deployment of the application.

   If you need to create out-of-the-box principals for your application, it is strongly recommended to define user-defined roles instead of pre-configured user with password. During the deployment of the application, it should be possible to request the end user to create users (either Windows principals or create T-SQL users with password) and add such principals to the appropriate roles during the setup process. If it is not possible to avoid the creation of preconfigured users with passwords, it is strongly recommended to not use a default password; instead, the recommendation is to set a end user-defined password during the application setup process (i.e. think of the “set SA password” step during SQL Server or Windows process).

  Once the application has been deployed and it is in use, there may be an arbitrary number of users for the systems. The ability to have DB-scoped users that can be authenticated by the database itself may be a temptation to grant permissions directly to users since the DB can be moved from one SQL Server instance to another without setting logins; but it is still recommended to grant permissions to user-defined roles and manage role memberships instead of managing permissions directly.

  I hope this tips will be helpful in securely deploy and use DB-scoped users.

  -Raul Garcia

   SDE/T

   SQL Server Engine