DSM 9.0.1 – New Microsoft SQL Server enhancements Part 2
In my most recent post, we saw how to setup a privileged user which would write SPNs for database users to enable Windows Authentication. We also saw how this user could update DNS entries automatically when a database is created. We followed these steps and provisioned a SQL Server instance. Once the instance was provisioned, we create a login for the owner and saw how the user could successfully login to the instance using Windows Auth. This is all great, but really one of the key points of DSM is self-service or DBaaS, database as a service. Therefore, what I want to do in this follow up post is to show you how an Active Directory (AD) user can provision their own SQL Server database on an existing SQL Server instance. Because this is DSM 9.0.1, the DSM Admin must ensure that this database provisioner is a member of a Namespace. The DSM Admin must also ensure that the Namespace is associated with a Data Service Policy that contains a SQL Server instance which will be used to provision SQL Server databases.
Verify status of Active Directory and SQL Server Instance
Before starting anything, check that the Active Directory Domain is in a ready state. You have to be a DSM Admin to do this step.
Check the status of the SQL Server instance at the same time. Both look good, so we can proceed with checking the user’s configuration.
Checking Namespace and Permissions
I already know that the user that I want to have create the database, “junchi”, is a member of an AD Group that I have already imported into DSM. You can check this via the Permissions view as a DSM Admin. The user “junchi” has been added to DSM via the “finance-ns” namespace. This means that “junchi” can login to the DSM portal as a DSM user, and will have access to whatever resources the “finance-ns” namespace has been granted through Data Service Policies. For “junchi” to create a SQL Server database, at least one SQL Server instance needs to have been associated with the “finance-ns” namespace via a Data Service Policy.
Data Service Policy for SQL Server databases
Let’s assume that there is currently no Data Service Policies (DSP) that allows users in the “finance-ns” namespace to provision SQL Server databases. That means that a DSM Admin will have to create one. Here are the steps to do that. Step 1 is to assign the DSP a name, and optional description and to choose the Data Service. In this case, it is SQL Server.
Step 2 involves selecting one or more namespaces to associate with the DSP. We are only interested in user “junchi” being able to provision SQL Server databases, so we only choose that namespace that he is a member, namely “finance-ns”.
We now select which SQL Server instances that we want the users in the namespace to provision databases on. As I only have a single SQL Server instance, sql01, and that is the only one I am selecting. However, you could have many SQL Server instances in your environment, and this is where these would be selected (or not).
The remaining options do not need explaining, and are easy to understand. Create the DSP. We are not ready to attempt a database provisioning and experience DBaaS.
Login as DSM User / Provision a database
Logout at the DSM Admin and login as the DSM user, in this case “junchi”.
Select SQL Server from the navigation pane, and then select the Database tab. Since “junchi” is only in one namespace for SQL Server database provisioning, there is only a single namespace option to choose from. Next, give the database a name which I have given as “junchi-db01”. The placement for this new database is also determined by the DSP, so as there is only one SQL Server instance, it is automatically chosen.
Next, the database owner is offered a choice. Do they wish to use SQL Authentication or Windows Authentication. If they wish to use Windows Authentication, then all that is needed is the username. This time, ensure that the username is qualified with the domain name, thus rainpole\junchi is provided as the owner.
If everything is working, the database should get created and come online very quickly.
Verify database is accessible by user
The final step is to ensure that this user can now login to the database. You may encounter an error if you try to use MS SQL Server Studio, such as shown below. This could be because MS SQL Server Studio is trying to connect to the System Databases (master) by default, which user “junchi” has no access to.
You will need to open the options of the Connection in MS SQL Server and change the database connection from <default> to the name of the database created by the user. In this example, the database was called “junchi-db01”. After making the change, try to connect once again.
Success! We have been able to connect to our new database “junchi-db01” as the user “junchi” using Windows Authentication. Note that this user has no visibility into the System databases, such as master, model, etc.
As you have seen, to connect a Windows user to a SQL Server User Database. you don’t need to CREATE LOGIN .. FROM WINDOWS. DSM is using the “container authentication” feature where the users don’t need to have corresponding LOGINs in the server.
That completes the post. Hopefully you can see how powerful the integration into SQL Server provisioning is becoming. I look forward to eventually being able to announce full support for this feature at some time in the future. For now, we would love to get your feedback on what we have achieved so far and what additional features we need to have added.
