Increase the security of your SQL Server with Contained Users

Contained Users :)

(This post is machine-translated. A proper translation is in the works)

Control of access to databases is something that disturbs the life of developers, IT Pros and DBAs. Well, since ever!

You know the story of connection string with user and password rolling to and fro? Who never, huh? Alegre

Besides the obvious problems with this — unauthorized access to data that are often confidential, there is another aggravating factor: the logins in SQL Server always happen at the server level. In other words, there is always the risk of a malicious user to do what must master database of SQL Server.

Did you know that SQL Server 2012 has introduced a new feature that reduces – and – that risk? Come meet new users contained (contained users).

Historically, the process of creating users in SQL Server has always been done in two steps. At first we create a login so that the user can connect to the server:

CREATE LOGIN username WITH PASSWORD = ' userpassword '

This login is created in the master database, which contains all of the SQL Server system information (and. It is therefore extremely sensitive). Next, create a user in each of the databases that this login should have access:

USE DATABASE foo
GO
CREATE username FOR LOGIN ' username '

The whole problem is the creation of the login in the master database. If, by chance, the lockdown of the account is not properly done, in the case of an invasion (via SQL Injection, for example), the attacker would have access to the master database and from there make huge damage (like the good old DROP TABLE that accompanies virtually any SQL Injection attack).

Enabling the use of users contained

The change introduced in SQL Server 2012 is to contain (limit) a user to a single database. In other words, we can now create a user without a login associated with it. Thus, this user does not have access to the master, but only to the designated database. In the case of an invasion, the damage can be contained – at worst – only the database that is being accessed. To use users contained you must configure your environment. This process varies depending on what you are using – SQL Server (on-premises) or SQL Azure (cloud).

SQL Server (on-premises)

If you use a SQL Server on-premises, so it is necessary to have the 2012 version or higher. In addition, it is necessary to activate the feature of users contained:

sp_configure ' contained database authentication ', 1;
GO
RECONFIGURE;
GO

SQL Azure (cloud)

Already in SQL Azure, your server is in compatibility mode V12. Visit the Azure Portal, navigate to your server and select the Latest SQL Database Update (1).

Option in the Azure Portal to update a SQL Server Azure
467

Do the update (if necessary) and the contained resource users will be enabled automatically.

Using users contained

Once the resource is available, use the command CREATE USER to create its users contained:

USE DATABASE foo
GO
CREATE USER username WITH PASSWORD = ' userpassword ';
GO

Now, just change your connection strings to use that user. As the user exists only in the context of the database, don’t forget to inform the Bank right in the moment of the connection:

Server = servername; Database = db_name; User ID = username; Password = userPassword

To find out more

Contained Database Users-Making Your Database Portable

contained database authentication Server Configuration Option

A hug,
Igor

Author: Igor Abade

Igor Abade V. Leite ([email protected]) is a Visual Studio ALM MVP (Microsoft Most Valuable Professional) since 2006. Speaker at various Software Development community events (TechEd Brasil, The Developers’ Conference, DevOps Summit Brasil, Agile Brazil, Visual Studio Summit, QCON among others), has also written articles in magazines and websites such as MSDN Brazil. Since March/2011 is one of the owners of Lambda3, a Brazilian consulting company specialized in ALM, software development and training. Visit his blog about VS ALM at http://www.tshooter.com.br/ and follow him on Twitter @igorabade.

Leave your comment!