I had previously blogged on how to change the password of SQL Server 2005 and SQL Server 2000 instances using WMI [blog post]. I recently needed to change the passwords of my SQL Server 2008 instances for multiple instances. So, I thought it was high time I modified my script to change the passwords for SQL Server 2008 instances programmatically.
I just updated my script on Tech Net. Here is the link. For SQL Server 2008, I use the namespace:
\root\Microsoft\SqlServer\ComputerManagement10 (SQL Server 2008)
The SQL Server 2012 namespace is: \root\Microsoft\SqlServer\ComputerManagement11 (SQL Server 2012)
All the namespaces can detect lower version services as well till SQL Server 2005. Currently the script provides all the WMI namespaces available till date. You will need to modify the script or add checks in case a namepsace is not present.
If you are using SQL Server 2008 R2, then you need to have this fix applied so that SetServiceAccount can work:
2397020 FIX: “Access is denied. [0x80070005]” error message occurs in SSCM when you try to change the password of an account of SQL Server 2008 R2 in Windows Vista or in a later version of Windows http://support.microsoft.com/default.aspx?scid=kb;EN-US;2397020
If you use SetServiceAccount to change the password, then this will result in a SQL Server service restart. The script provided on the Technet Gallery is for changing both the service account and the password. If only the password needs to be changed for the service account, then use SetServiceAccountPassword. This will change the password and will not automatically restart your SQL Server service.
I have had multiple questions on how to enumerate SQL instances in your complete environment to run maintenance operations from a centralized environment. There are multiple tools which implement one of the methods or a variation of the methods described in the blog post below.
I keep referring people to this blog post. So, I thought I would put a referral of the same on my blog so that I can find it easily the next time.
Jonathan Sayce [Blog] has shown in his blog post what are the different methods to achieve this.
I wrote this script sometime back. I thought it would be a good idea to share this script. The background for writing this script was there were a bunch of SQL Server installations in the environment with the same instance names but on different boxes. The installations were pushed through programmatically and company policy was set in place which needed the TCP/IP port to be changed to a different port other than the default port i.e. 1433. The DBA obviously wouldn’t be too happy sitting and using SQL Server Configuration Manager on multiple servers doing the same monotonous task. The ask was to write a script which could be pushed to all the servers so that the port on which the SQL instance was listening on was modified programmatically.
This script changes the PORT number for a named instance of SQL Server 2008 called KATMAI to 1434. The namespace used here are:
SQL Server 2005: \root\Microsoft\SqlServer\ComputerManagement
SQL Server 2008: \root\Microsoft\SqlServer\ComputerManagement10
So if you wanted to use the script for SQL Server 2005, you would need to change the namespace to the one for SQL Server 2005. The other variables that come into play for this script are the Instance Name and the Port number:
‘Specify the instance name if any. Default is MSSQLSERVER.
‘Assumption is that we are running this for the default instance.
‘For a named instance, only provide the instance name. Eg: For a named instance LABDC\INST1, the parameter below would be strInstance = "INST1"
‘For a cluster, we need to run this script on the active node.
strInstance = "KATMAI"
‘Specify the new PORT
strPort = "1434"
So, you would have to modify the above variables for the correct Instance Name and Port number.
The script then makes use of the ServerNetworkProtocolProperty class methods to set the TCP port value.
If you need to extend this script, you can do so by creating an Array for the list of Servers in your environment and running the entire code in the script in another WHILE loop.
Another customized implementation could be if you want to change the port number for multiple instances with incremental values of Port Numbers. This can be done by querying the same namespace for getting the SQL instances on the box using SqlService. A sample WMI script is provided in the blog post below.
HOW TO DETECT SQL SERVER INSTANCES / FEATURES INSTALLED ON A MACHINE
Once you have the Instance Names, then you can again create a loop with the Port Number variable being incremented and execute the function to set the TCP/IP Port number.
Download the script from here
How to- Configure a Server to Listen on a Specific TCP Port
Happy Automation!! :)