A couple of weeks ago, I shared the scripts I use to prepare a brand new VM to run SQL Server.  At the time, I noted that I could only get up to the point where the server was ready for SQL and that I was unable to overcome some issues with installing SQL Server remotely.

I have finally found a way to get around those issues, and I’ve put together a script for remotely deploying SQL Server using PowerShell and WinRM.  This script is designed to be used as part of a workflow in vCenter Orchestrator that will allow admins and, eventually, developers to provision their own SQL Server instances.

There are two scripts that are required for this process, and they borrow techniques from the other provisioning scripts that I’ve written.  I would highly recommend reading my previous articles on provisioning a VM and preparing a VM for SQL Server before trying out these scripts.

The first script is the Install-SQLServer script that should be included with the SQL Server files that are copied to the new server.  This is the script that will run on the local machine and install SQL.

The other script is the script that will run on your jump box or scripting server called Invoke-SQLInstall.  In my environment, this script is executed on my scripting server by vCenter Orchestrator and invokes the SQL Server installation using WinRM on my new SQL Server.

Kerberos and SQL Server Installations

While I was building this script, I ran into a lot of issues when trying to get SQL Server to install remotely using WinRM.  The install would fail, and the setup log would point to an error that the account or the computer wasn’t trusted for delegation.

The Kerberos “Second Hop” issue was causing the install to fail, and most of the workarounds for getting around this issue, such as using Start-Process to launch a new PowerShell session or using a local batch file to install under other credentials, would not work inside of a WinRM session.

There is one other option that I had considered, but I didn’t pursue it at the time because I thought it was a security risk.

CredSSP

Microsoft introduced a new secruity delegation method years back to work around some of the limitations of Kerberos.  This new security delegation method,  called the Credential Security Service Provider or CredSSP for short, was designed specifically to address the Kerberos second hop issue.

The issue with CredSSP is that it can be configured to delegate credentials to any computer on the domain through group policy.  We don’t want or need that, and credentials should only be delegated to the computer that we’re working on for the short time that it will need them.

It is actually fairly easy to configure CredSSP on the SQL Server at runtime and to turn it off when we’re done, and the script will take care of both tasks when installing SQL Server.

Custom Roles

SQL Server has a number of roles and features that can be selected during installation.  Many of these roles have very specific functions and aren’t suited for general purpose database servers, and they shouldn’t be installed if you aren’t going to use them.

What makes this more complicated is that some features are instance specific, such as the database engine and Reporting Services, while others are not instance specific and only need to be installed once.

Since each instance and/or each SQL Server may need different features installed, the script was designed with roles in mind.  Each role  is an element in a PowerShell Switch statement that contains the SQL command-line installation string.   It may also contain other commands that might be needed such as the Windows Firewall cmdlets to allow incoming connections to the SQL instance.

This design choice allows the script to be flexible and adapt to the changing needs of the business and the environment.

Get the Scripts

The scripts are available on my Github page with the rest of my provisioning scripts.