通过

Automating SQL Service Account/Password Changes

  • 项目

I received a customer request to put together some PowerShell scripts to automate the SQL service account and password changes you can do through the SQL Configuration Manager GUI.  The notes below were written using a SQL 2008R2 machine on Windows Server 2008R2.

The reference material I used to start were these links:
   https://msdn.microsoft.com/en-us/library/ms162567(v=sql.105).aspx
   https://sqlblog.com/blogs/allen_white/archive/2013/02/12/t-sql-tuesday-39-managing-your-sql-server-services-with-powershell.aspx
   https://technet.microsoft.com/en-us/library/Microsoft.SqlServer.Management.Smo.Wmi(v=sql.105).aspx

Based on what I gleaned from them, I allocated a 2k8R2 instance and gave it a try.  Most of the work is already done by Allen White (above), but I hit some bumps that I wanted to document.

In order to ensure that the WMI provider works, which is what PowerShell (or other scripting languages) uses to control SQL Server, you first need to ensure that it has been compiled.  If you don’t, your objects will have null properties (bump I was referring to).  You only need to compile the provider once on the server.  To compile it type this from an administrator prompt:
   C:\>mofcomp "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Once that is done, the WMI scriptable API is registered.

Next you’ll need to ensure that you allow PowerShell to run unsigned scripts locally (remote requires signed).  Open a PowerShell prompt as administrator and run:
   Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

You’re now set to run scripts you write on the server.  Of course to create scripts, you’ll need a text editor like notepad or PowerShell ISE.

To enumerate SQL related services on the box, you can create a script with these commands:

   [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
   $mc = new-object Microsoft.SQLServer.Management.SMO.WMI.ManagedComputer localhost

   Write "All SQL Related Services:"
   $mc.Services | select Name, Type, ServiceState, DisplayName, ServiceAccount | ft

   Write "SQL Relational DB Instances:"
   $mc.ServerInstances | select Name, ServerProtocols, State | ft

To change the password for an instance’s service account, you can create a script with these commands:
   [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
   $mc = new-object Microsoft.SQLServer.Management.SMO.WMI.ManagedComputer localhost

   $service = $mc.Services["MSSQLSERVER"]
   $service.ChangePassword("oldPassword", "newPassword")
   $service.Alter()

To change the instance’s service account, you can create a script with these commands:
   [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
   $mc = new-object Microsoft.SQLServer.Management.SMO.WMI.ManagedComputer localhost

   $service = $mc.Services["MSSQLSERVER"]
   $service.SetServiceAccount(".\TestService", "newPassword")
   $service.Alter()

Using these operations as building blocks, you can go on to create scripts more according to your environmental needs like:
                Looping through instances on a box and changing the password for each instance
                Looping through instances on a box and setting their service accounts according to some naming scheme
                Adding calls to change the SQL Agent account and password (agent is another service object)
                Starting, stoping, restarting services, and other operations

Comments

  • Anonymous
    October 12, 2016
    awesome I've been looking for this for a long time