(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?
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 data
base 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 da
tabase, 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 d
atabase. 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 mas
ter 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 th
e 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).
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