Accessing SQL Server contained databases from Linux and UNIX

Contents

Contained databases overview

Contained databases, introduced in SQL Server 2012, simplify the process of moving databases from one SQL Server instance to another by removing dependencies on the instance such as SQL Server logins.

To facilitate the separation of logins from the instance, SQL Server 2012 and later support authentication at database level as well as server level. Server-level authentication is used to validate users who specify a SQL Server login when connecting. Database-level authentication is used to validate users who specify a contained database and a contained user when connecting.

Contained users are created and stored in the contained database rather the instance's master database. (The master database holds all the system-level information for a SQL Server instance, including login accounts.) Contained users cannot change database.

This example SQL creates a contained user. As the CREATE USER statement specifies a user and a password, it can only be run in a contained database:

USE AdventureWorks2008R2;

CREATE USER my_contained_user
WITH PASSWORD = 'my_contained_password';

GRANT SELECT ON OBJECT::HumanResources.Employee TO my_contained_user;

Creating contained users helps separate the database from the SQL Server instance, which enables the database to be easily moved to another instance of SQL Server.

Contained users, like SQL Server logins, can be SQL Server users (which requires the SQL Server instance to permit SQL Server Authentication) or can be based on Windows users and groups.

To try out the contained database feature, we used our SQL Server ODBC driver for Linux and UNIX platforms.

We used the AdventureWorks database as our contained database.

To set up our contained database, we installed Adventureworks in our SQL Server 2012 instance and then:

  1. Enabled contained databases in the SQL Server instance.
  2. Converted AdventureWorks into a contained database.

These tasks can be done either by using SQL or the SQL Server Management Studio interface. Both methods are described in the Microsoft SQL Server documentation.

Connecting to a contained database as a contained user

The SQL Server ODBC driver allows the default database to use for the connection to be specified. It can therefore be used to connect to a contained database as a contained user, extending the availability of this SQL Server feature to Linux and UNIX platforms. If the contained database is not specified on connection, server level authentication is used to validate the specified user, which in the case of a contained user will fail.

To connect to the contained database from our Linux machine, we created an SQL Server ODBC driver data source in /etc/odbc.ini. In the data source, we specified the:

The ODBC data source used is shown here:

[mssql-2012-contained-database-dsn]
Driver              = Easysoft ODBC-SQL Server
Server              = my_windows_machine_1/my_sql_server_2012_instance
User                = my_contained_user
Password            = my_contained_password
Database            = AdventureWorks2008R2
Trusted_Connection  = No

The contained user we created was a SQL Server user rather than a Windows user. We therefore ensured that SQL Server authentication was used to validate the user by setting Trusted_Connection to No.

To access the contained database, we used isql, the sample ODBC application included in the SQL Server ODBC driver distribution:

$ cd /usr/local/easysoft/unixODBC/bin
$ ./isql.sh -v mssql-2012-contained-database-dsn
SQL> select Top (5) LoginID, JobTitle, HireDate
     from HumanResources.Employee

+--------------------------+------------------------------------------+
| LoginID                  | JobTitle                     | HireDate  | 
+--------------------------+------------------------------------------+
| adventure-works\ken0     | Chief Executive Officer      | 2003-02-15|
| adventure-works\terri0   | Vice President of Engineering| 2002-03-03|
| adventure-works\roberto0 | Engineering Manager          | 2001-12-12|
| adventure-works\rob0     | Senior Tool Designer         | 2002-01-05|
| adventure-works\gail0    | Design Engineer              | 2002-02-06|
+--------------------------+------------------------------------------+
SQLRowCount returns -1
5 rows fetched

Even though my_contained_user is a valid user, attempting to connect to the data source without specifying the contained database failed with the error:

[28000][unixODBC][Easysoft][SQL Server Driver][SQL Server]
Login failed for user 'my_contained_user'.
[ISQL]ERROR: Could not SQLConnect

This is because the login is stored in the database and can only be used connect to that database. If the database is not specified, SQL Server will default to the master database and attempt to authenticate the contained user at instance level, which fails.

The database is specified with the Database data source attribute:

Database = AdventureWorks2008R2

As expected, attempting to use another database as this contained user fails:

SQL> use AdventureWorks2008R2TransparentDateEncryption
[08004][Easysoft][SQL Server Driver 10][SQL Server]The server principal
"S-1-9-3-1998181186-1277790759-2408800444-3028360873." is not able
to access the database AdventureWorks2008R2TransparentDateEncryption"
under the current security context.

Moving the contained database to another SQL Server instance

To test the portability of our sample contained database, we moved it to another SQL Server instance, in which contained databases were enabled. There was no need to recreate the contained user. Contained users are stored with the database and therefore move with the database.

To connect to the contained database, we had to make one change to our data source: we edited the Server attribute to specify the SQL Server instance that was now serving the contained database:

[mssql-contained-database-dsn]
Driver              = Easysoft ODBC-SQL Server
Server              = my_windows_machine_2/my_sql_server_instance
User                = my_contained_user
Password            = my_contained_password
Database            = AdventureWorks2008R2
Trusted_Connection  = No

Again, isql was used to test the data source:

$ cd /usr/local/easysoft/unixODBC/bin
$ ./isql.sh -v mssql-2012-contained-database-dsn
SQL> select Top (5) LoginID, JobTitle, HireDate
     from HumanResources.Employee

+--------------------------+------------------------------------------+
| LoginID                  | JobTitle                     | HireDate  | 
+--------------------------+------------------------------------------+
| adventure-works\ken0     | Chief Executive Officer      | 2003-02-15|
| adventure-works\terri0   | Vice President of Engineering| 2002-03-03|
| adventure-works\roberto0 | Engineering Manager          | 2001-12-12|
| adventure-works\rob0     | Senior Tool Designer         | 2002-01-05|
| adventure-works\gail0    | Design Engineer              | 2002-02-06|
+--------------------------+------------------------------------------+
SQLRowCount returns -1
5 rows fetched

Resources


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.