Adventures in Machine Learning

Secure Your Data with SQL Server’s Contained Database

Anto the SQL Server Contained Database

Databases are one of the most critical components of modern-day computing, often containing sensitive and critical data. Organizations must make sure that their databases are secure and protect them from vulnerabilities and potential attacks.

Microsoft SQL Server, one of the most popular database management systems, provides a solution for this in the form of a contained database. In this article, we will discuss what a contained database is, its advantages, and how to create one.

Definition of a Contained Database

A contained database is a SQL Server database that comes with all the metadata required to support the database’s operations and user-defined configurations within the database itself. Every object in a contained database is self-contained, meaning that it contains all the necessary dependencies and can operate independently of other databases or instances in the SQL Server deployment.

Advantages of Using a Contained Database

A significant advantage of using a contained database is the enhanced security it provides. In traditional SQL Server architectures, users and their corresponding passwords are stored in the master system database.

This means that if the master database is compromised, user accounts and passwords from all other databases are also at risk. However, with a contained database, users and their respective passwords are stored within the database itself, improving the database’s security and reducing the chance of unauthorized access.

Additionally, a contained database supports orphan SQL logins. An orphan SQL login occurs when a user connects to a contained database but has no corresponding login in the SQL Server instance.

With a contained database, orphan SQL logins can be easily resolved, reducing administrative overhead.

Creating a Contained Database

Creating a contained database is easy. However, there are a few things that you must first do to enable containment features on your SQL Server instance:

1. Run sp_configure to check the value of the contained database authentication option on the server:

SELECT name, value, value_in_use 
FROM sys.configurations 
WHERE name = 'contained database authentication'

2. If the above query returns a “0” as the value of the “contained database authentication” option, you must enable it by running the following command:

sp_configure 'contained database authentication', 1
GO
RECONFIGURE
GO

3. Once containment is enabled on your SQL Server instance, you can create a contained database using T-SQL:

CREATE DATABASE [MyFirstContainedDatabase]
CONTAINMENT = PARTIAL;
GO

Notice that we have specified the “CONTAINMENT” parameter as “PARTIAL.” This means that only some of the database components will be contained, and not all. Specify “CONTAINMENT” as “FULL” if you want to contain the entire database.

Verifying If a Database is a Contained Database

You can verify whether a database is a contained database by running the following query:

SELECT name, containment_desc 
FROM sys.databases;

If a value of “PARTIAL” or “FULL” is returned under “containment_desc,” the database is a contained database. If “NONE” is returned, the database is not contained.

Conclusion

Contained databases are an essential feature in Microsoft SQL Server, providing enhanced security and reducing administrative overhead. Creating a contained database is a straightforward process, and once containment is enabled on your SQL Server instance, you can create one quickly using T-SQL.

Verify if your database is a contained database by running a query on the sys.databases catalog view. By leveraging contained databases in your SQL Server deployment, your organization can operate more securely and with more agility.

Creating and Managing Users in a Contained Database

In the previous sections, we discussed what a contained database is, its advantages, and how to create one. In this article, we will discuss how to create and manage users in a contained database and how to connect to a contained database using SQL Server Management Studio (SSMS).

Creating a User in a Regular Database

Before we discuss how to create a user in a contained database, it’s crucial to understand how to create a user in a regular database. To create a user in a regular database, you need to do two things: create a login and create a user associated with that login.

First, create a login using the CREATE LOGIN statement. The login is used to authenticate a connection to SQL Server.

Here is an example:

CREATE LOGIN [myuser] WITH PASSWORD = 'mypassword';

Next, create a user associated with the login using the CREATE USER statement. This step allows you to assign a specific database role to the user.

Here is an example:

USE [mydatabase];
CREATE USER [myuser] FOR LOGIN [myuser];
EXEC sp_addrolemember N'db_datareader', N'myuser';

This creates a user “myuser” in the database “mydatabase” and assigns the “datareader” role to the user.

Creating a User in a Contained Database

In a contained database, the user is created directly within the database. This means that the user is self-contained and doesn’t rely on a login at the instance level.

Here is an example of creating a user directly in a contained database:

USE [mycontaineddatabase];
CREATE USER [myuser] WITH PASSWORD = 'mypassword';

Note that we don’t need to create a login here since we are creating a user in a contained database. This user can only access the contained database and is not linked to a login.

Listing All Users in a Contained Database

To list all the users in a contained database, you can use the following query:

SELECT * FROM sys.database_principals WHERE type = 'S';

This query lists all the users in the database that have a type of “S,” which stands for “SQL user.”

Connecting to a Contained Database Using SSMS

Connecting to a contained database is the same as connecting to any regular database. However, since the users in a contained database are self-contained and don’t rely on a login at the instance level, you need to specify the database user and password to connect to a contained database.

To connect to a contained database, open SSMS and enter the server name, username, and password in the connection window. Then, select the database you want to connect to from the “Connection Properties” tab.

Here are the step-by-step instructions:

  1. Open SSMS and click on the “Connect” button.
  2. In the “Connect to Server” window, enter the server name and select the appropriate authentication mode.
  3. If you’re using SQL Server Authentication, enter the username and password for the contained database.
  4. Click on the “Options” button to expand the connection properties.
  5. In the “Connection Properties” tab, select the database you want to connect to from the dropdown list.
  6. Click on the “Connect” button to establish the connection. If the connection is successful, you will be connected to the contained database, and you can start working with the data.

Conclusion

In this article, we discussed how to create and manage users in a contained database and how to connect to a contained database using SQL Server Management Studio (SSMS). We covered the differences between creating a user in a regular database and creating a user in a contained database and how to list all users in a contained database.

Finally, we discussed the step-by-step process to connect to a contained database using SSMS. By following these guidelines, you’ll be able to create, manage, and connect to contained databases with ease, providing more security and autonomy for your organization’s data.

Converting a Regular Database to a Contained Database

In the previous sections, we discussed what a contained database is, its advantages, and how to create and manage users in a contained database. In this article, we will discuss how to convert a regular database to a contained database.

Creating a Regular Database Inventory

Before we begin to convert a regular database to a contained database, it’s essential to create an inventory of all the objects in the database. This step is crucial because it helps to identify database users that rely on a login at the instance level.

Here is an example of how to create a regular database inventory:

  1. Create a backup of the regular database.
  2. Create a new test environment.
  3. Restore the database backup to the test environment.
  4. Execute the following commands to create a list of all logins and users in the database:
  5. USE [myregulardatabase];
    SELECT name,type_desc FROM sys.database_principals WHERE type IN ('S','U');

    This command lists all of the database users and their corresponding types. The “S” type represents SQL users, and the “U” type represents Windows users.

  6. Execute the following command to create a list of all objects in the database (tables, views, procedures, etc.):
  7. USE [myregulardatabase];
    SELECT name, type_desc FROM sys.objects;

    This command lists all the objects in the database and their corresponding types.

  8. Review the output generated by these commands and save them for future reference.

Converting a Regular Database to a Contained Database

Once you have created an inventory of all objects, logins, and users in the database, you can begin the process of converting the database to a contained database. Here are the steps to convert a regular database to a contained database:

  1. Backup the regular database.
  2. Create a new, empty database with containment enabled.
  3. CREATE DATABASE [mycontaineddatabase] CONTAINMENT = PARTIAL;

    This command creates a new, empty contained database.

  4. Execute the following command to set the containment level of the database to “PARTIAL”:
  5. ALTER DATABASE [mycontaineddatabase] SET CONTAINMENT = PARTIAL;
  6. Then, execute the following command to convert each user in the regular database to a contained database user:
  7. USE [mycontaineddatabase];
    EXEC sp_migrate_user_to_contained @username='myuser';

    This command converts the specified user to a contained database user. You must run this command for each user in the regular database.

    Note that this process does not migrate objects or schema. It only migrates users and their corresponding passwords to the contained database.

Converting a Database User to a Contained Database User Using sp_migrate_user_to_contained

The sp_migrate_user_to_contained system stored procedure is used to convert a user in a non-contained database to a contained database user. Here is an example of how to use the sp_migrate_user_to_contained stored procedure to convert a database user:

USE [mycontaineddatabase];
EXEC sp_migrate_user_to_contained @username='myuser';

This command migrates the “myuser” user to a contained database user.

When you use sp_migrate_user_to_contained, SQL Server examines the user’s dependencies and migrates all dependent entities to the contained database. It also creates a new user in the contained database and migrates the user’s permissions to the new user.

Conclusion

In this article, we discussed how to convert a regular database to a contained database. We started by creating an inventory of all objects, logins, and users in the regular database to identify database users that rely on a login at the instance level.

Then, we discussed the steps to convert a regular database to a contained database. Finally, we covered how to use the sp_migrate_user_to_contained stored procedure to convert a database user to a contained database user.

By following these guidelines, you can convert a regular database to a contained database, providing enhanced security and improving your organization’s data infrastructure. In this article, we discussed how to create, manage, and connect to a contained database in SQL Server, as well as how to convert a regular database to a contained database.

A contained database offers enhanced security since all metadata and configurations are stored within the database itself, and users and passwords are self-contained. Creating and managing users in a contained database is straightforward, and connecting to one using SQL Server Management Studio only requires specifying the database user and password.

Converting a regular database to a contained database requires creating a regular database inventory, altering the database to contain partial containment, and migrating each user to a contained database user. By leveraging contained databases in your SQL Server deployment, your organization will benefit from improved security, reduced administrative overhead, and more agile data infrastructure.

Popular Posts