Automating SQL Server Configuration with PowerShell and WMI

I've been working recently with provisioning of Windows Server/SQL Server machines in AWS. I never imagined how hard it is to automate Windows and SQL Server configuration. After several days, I figured out how to do all that I needed, and since this was very stressful and painful, I'm here to share that knowledge :)

This link helped me a lot to know what I needed to configure in Windows and SQL Server. Check out the answer from kkcan at the end of the page for a complete list of the required steps.

Alright, I know what to do, but the main question is: how to automate all of this? Then, I found that PowerShell and WMI (Windows Management Instrumentation) really are life savers!

PS: I'm using the scripts below to configure a Windows Server 2012 R2 machine with SQL Server 2014 Express. If you're using other SQL Server version, pay attention to some details that may be slightly different, like the instance name or the WMI namespace.


Enable mixed mode authentication

This configuration allows the server authentication to be done with either SQL Server or Windows Authentication. If you had to do this manually, you would do the following steps:

  • Open SQL Server Management Studio
  • Log in using Windows authentication
  • Right click the database and select "Properties"
  • Click the "Security" section
  • Enable the "SQL Server and Windows Authentication mode" option

mixed mode

Actually, the easier way I found to automate this was to run the xp_instance_regwrite stored procedure, in order to change this setting in the Windows registry. The advantage of this approach is that you don't need to restart the SQL Server process. Here is the script, which you can run with sqlcmd:


Enable TCP

To enable TCP manually, you would need to:

  • Open SQL Server Configuration Manager
  • Select "SQL Server Network Configuration"
  • Click on "Protocols for MSSQLSERVER"
  • Right click on "TCP/IP" and select "Enable"

enabletcp

With PowerShell, this is easy to automate using the sqlps module. Check it out:


Configure TCP properties (IP1, IPAll)

These TCP properties can be accessed in the previous screen, by doing a right click on "TCP/IP" and selecting "Properties".

ip1

I needed to make sure that IP1 is enabled, with "dynamic ports" equal to zero and "tcp port" equal to 1433. We can automate this with WMI (Windows Management Instrumentation. WMI scripts are written in VBScript (I know, it sucks, but it's really useful) and are used to instrumentate the configuration of some programs.

The script below configures the IP1 settings. Please pay attention to the connection string ("\.\root\Microsoft\SqlServer\ComputerManagement12"). In SQL Server 2014, the path is "ComputerManagement12", while in SQL Server 2012 is "ComputerManagement11", and in SQL Server 2008 is "ComputerManagement10", and so on.

For the IPAll configuration, I needed to make sure that "dynamic ports" is zero and that "tcp port" is 1433. The script is very similar:


Windows Firewall

To allow SQL Server access, you need to open the port 1433 in Windows Firewall. This can also be done easily with netsh.exe, but I chose the PowerShell way:


Extras

There are some helpful PowerShell scripts that I've been using to do other stuff. I'll describe some of them below.

Set access rule on directory

I'm using SQLCC to calculate code coverage of T-SQL stored procedures (this will be the subject of a future blog post), and one of the things I needed while configuring the tool was to set the MSSQLSERVER user as owner of a directory.

The following script adds full access for the "NT SERVICE\MSSQLSERVER" user to a directory:


Zip folder

You can use this PowerShell script to zip a given folder:


Unzip file

To unzip a file, you can use this script:


Reset Administrator password

When I was working with some WinRM stuff, I needed to change the "Administrator" user password, and this script did the trick:


And that's it! Don't forget to check out the links in the References section.

Please feel free to post a comment or to get in touch (my contacts are listed at the end of the page) :)


References

http://www.powershellmagazine.com/2013/02/19/pstip-adding-firewall-rules-in-windows-8-and-server-2012/
http://stackoverflow.com/questions/9138172/enable-tcp-ip-remote-connections-to-sql-server-express-already-installed-databas
https://msdn.microsoft.com/en-us/library/ms191294.aspx
http://stackoverflow.com/questions/2266697/changing-sql-server-settings-programmatically
https://msdn.microsoft.com/en-us/library/ms179354.aspx
https://msdn.microsoft.com/en-us/library/ms974579.aspx


About the author: Stefan is a QA Engineer from Rio de Janeiro, Brazil. B.Sc. in Computer Science at the Federal University of Rio de Janeiro (UFRJ) and MBA in Software Quality Assurance at the same institution. Agile Testing and DevOps enthusiast. Spoke at events like Agile Brazil, QCon Rio, TDC (The Developer's Conference), Scrum Gathering Rio and MobileConf.

stefanfk@gmail.com | Twitter | LinkedIn | pt-br blog