DSM 9.0.1 – New Microsoft SQL Server enhancements Part 1

Whilst Microsoft SQL Server is still in technical preview in Data Services Manager 9.0.1, our team continues to release significant enhancements for our customers as we gravitate towards full support. As I mentioned in the DSM 9.0.1 overview post, this release includes the ability to specify a privileged AD user who can create Service Principal Names (SPNs) for database users and update DNS entries. This means that the privileged user can now do the necessary tasks in Active Directory to allow Windows Authentication to work seamlessly on MS SQL Server instances and databases which have been provisioned via DSM. You no longer need to set each database user up manually for Windows Authentication. I also mentioned that in this release, DSM may also use this privileged user to automatically update DNS entries. This privileged user is added to the Active Directory configuration in the SQL Server section of the UI. Then, when provisioning of instances begins, this AD configuration can be selected and the privileged user is used to do the automated tasks. Let’s use this post to demonstrate how to do this in detail.

Create a Privileged User for Writing SPNs

The first step is to create an Active Directory user which we will use as our DSM Privileged user. Here I am using the AD-User Powershell command to create the user. This command will prompt for a password, and will set the password to never expire. The other attributes setting enable encryption support on the account. The account must support Kerberos AES 128 and 256-bit encryption.

PS> New-ADUser sqladmin -AccountPassword (Read-Host -AsSecureString "<password>") -PasswordNeverExpires $true -Enable $true -OtherAttributes @{'msDS-SupportedEncryptionTypes'=24}

DSM uses the privileged account from the Active Directory domain object to write the Service Principal Names under the SQL Server’s identity account. This — being able to write to the servicePrincipalName property — is the only write privilege that DSM actually requires. DSM will not attempt write to other properties, and will not attempt to create users. The command shown here is granting the privileged user to be able to set SPNs on all users. If this is not desirable, the command can be modified to simply do a subset of users in AD. again, this is wrapped for visibility. Run the command on a single line in Powershell.

PS> dsacls CN=Users,DC=rainpole,DC=com /I:S /G "sqladmin:RPWP;servicePrincipalName;user"

This will generate a considerable amount of output. However, in the output, you should observe similar to the following:

Inherited to user Allow RAINPOLE\sqladmin           SPECIAL ACCESS for servicePrincipalName
                                                    WRITE PROPERTY
                                                    READ PROPERTY

Create a Privileged User for Updating DNS

I am going to use the same privileged user for updating DNS record that I used for writing SPNs. Again, I will us dsacls commands to grant this privilege. In the examples below, simply replace name of the zone and the default naming context with the names from your own domain. Start with the Forward Lookup Zone, using /G to grant access.

PS> dsacls DC=rainpole.com,CN=MicrosoftDNS,DC=DomainDNSZones,DC=rainpole,DC=com /G "sqladmin:CC;dnsNode"

PS> dsacls DC=rainpole.com,CN=MicrosoftDNS,DC=DomainDNSZones,DC=rainpole,DC=com /G "sqladmin:WO"

Repeat the same commands for the Reverse Lookup Zone, Again, simply change the DC values of “44.20.10” and the domain name (RAINPOLE) to the reverse lookup and name of your own domain in the commands below:

PS> dsacls DC=44.20.10.in-addr.arpa,CN=MicrosoftDNS,DC=DomainDNSZones,DC=rainpole,DC=com /G "sqladmin:CC;dnsNode"

PS> dsacls DC=44.20.10.in-addr.arpa,CN=MicrosoftDNS,DC=DomainDNSZones,DC=rainpole,DC=com /G "sqladmin:WO"

Great. We now have a privileged user called “sqladmin” who can write SPNs and update our DNS entries for any databases that we create.

Create Active Directory Domain in DSM

Lets now add this privileged user to our SQL Server configuration in DSM. From the DSM UI, open the MS SQL Servers section. Navigate to the Active Directory Domains tab and click “Create”. Add details about the Active Directory environment. If you want DSM to write the SPNs of the database users and automatically enable Windows Authentication on the SQL Server databases, click on the DSM Managed button associated with “Write Service Principal Names” and add the privileged user. Note that if you do not wish to give DSM access to a privileged user, you can continue to use the manual mode of assigning SPNs to users. Lastly, if the CA of your Active Directory is already in DSM’s trusted certs, there is no need to add it here. If it not already in the DSM trusted certs, you should add it to the Trusted CA section.

Once created successfully, the Active Directory Domain should look somewhat similar to this:

Create a SQL Server Instance

We can now begin to create a SQL Server Instance. Here is an example taken from my setup. I select a SQL Server version, give it a name and select an edition. Active Directory is configured, and my Privileged Account is also configured. To use it, click on the radio button next to the domain (in this example, rainpole.com). I now need to assign what can be though of as the owner of the instance. As it said, this AD user account that this SQL Server cluster will use to perform group refresh, i.e. identify group membership of users. Note that there is not leading domain prefix or suffix – it is only the user name that is added. Again, this account must support Kerberos AES 128 and 256-bit encryption. In this example, I have chosen a user called “paudie”. It is on this user that the privileged user “sqladmin” will create SPNs.

The remainder of the setup is generic DSM stuff, selecting infrastructure policy, Storage Class, VM Class, disk size, etc. But when you get to the advanced settings, you begin seeing some of the additional enhancements that we have made to SQL Server in DSM 9.0.1. A SQL Server instance can now have configuration items such as collation, mssql.conf entries and trace flags all added at provisioning time. We also support the ability to enable auditing so you can track logins.

We can also add custom vSphere tags to DSM provisioned objects in DSM 9.0.1. For example, I made a tag to identify owner of this instance, which I set to finance. This will make it easily identifiable from the vSphere client, and can be used for any number of automation purposes.

Complete the creation of the instance, . All going well, the SQL Server Instance should enter an OK / Ready state.

Verify SQL Server Instance Login

It appears that the instance is ready, but we must still verify that the user “paudie” can login successfully using Windows Auth. Before doing that, if you have access to Active Directory, you can verify that the necessary SPNs (servicePrincipalName) have been written to the attributes of user “paudie”. This is the action that will have been done by our privileged user, if setup correctly. If you see the SPNs, this should verify that the privileged user “sqladmin” is working correctly. It should look similar to the following, using the FQDN of the database, sql01.rainpole.com

If you do have access to AD, you could also check DNS to ensure that the correct forward and reverse entries have been created for the database (sql01.rainpole.com in this case) Or alternatively, use a tool such as “nslookup” or “dig” to verify that DNS is working for your database. Now connect to the database. Note that “paudie” will not be able to connect to the database initially as there is no LOGIN created for that user on the instance. The first attempt to login using Windows Authentication will fail as follows for any “user”. This example is showing a SQL Server Studio lgin attempt:

To address this, we are going to have to login as the administrator “sa” using SQL Server Authentication and create a login for “paudie”. The “sa” password is available from the instance view in DSM. Login as “sa” using SQL Authentication and run the command:

CREATE LOGIN [rainpole\paudie] FROM WINDOWS;

Make sure that the command completes successfully, e.g., here is the command being run inside MS SQL Server Studio after successfully logging into to the instance as the “sa”.

Now disconnect the “sa” user from the instance, and try once again as the user “paudie” to connect to the instance using Windows Authentication. This time the login should succeed, as shown below. User “paudie” can perform group refresh to identify group membership. The user can see all the system databases. Looks like it is working as expected.

The one part that is missing now is the DBaaS – Database as a Service – part, where users should be able to create their own databases on demand. I will show you how to do that in the next post in this series on DSM 9.0.1. Stay tuned.