Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 20, 2007

Microsoft Windows PowerShell and SQL Server 2005 WMI Providers - Part 1 - Page 2

By DatabaseJournal.com Staff

by Yan Pan

Manage SQL Server client alias

SQL Server client aliases make user connections easier, faster and more convenient. Each alias saves all of the information you need to connect to a SQL Server, such as the server name and the client protocol used to connect to a server. By using an alias, you do not need to enter the information each time you connect. You can also use an easy-to-remember nickname that is different from the actual server name.

SqlServerAlias is the class for alias. To view a list of aliases defined on the local computer, type:

PS > Get-WmiObject `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class SqlServerAlias


__GENUS          : 2
__CLASS          : SqlServerAlias
__SUPERCLASS     :
__DYNASTY        : SqlServerAlias
__RELPATH        : SqlServerAlias.AliasName="ALIAS1"
__PROPERTY_COUNT : 4
__DERIVATION     : {}
__SERVER         : DPP-PANYAN01
__NAMESPACE      : root\Microsoft\SqlServer\ComputerManagement
__PATH           : \\DPP-PANYAN01\root\Microsoft\SqlServer\
         ComputerManagement:SqlServerAlias.AliasName="ALIAS1"
AliasName        : ALIAS1
ConnectionString : 7001
ProtocolName     : tcp
ServerName       : SERVER1

In this example, only an alias ALIAS1 is defined on the local computer. For a remote computer, use the –computerName parameter to specify the remote computer name.

The properties and methods that can be applied to the SqlServerAlias class are shown below.

PS > Get-WmiObject `
   -namespace root\Microsoft\SqlServer\ComputerManagement `
   -class SqlServerAlias | Get-Member -memberType ScriptMethod


   TypeName: System.Management.ManagementObject#root\Microsoft\
   	SqlServer\ComputerManagement\SqlServerAlias

Name		MemberType		Definition
----		----------		----------
ConvertFromDateTime	ScriptMethod	System.Object 
				ConvertFromDateTime();
ConvertToDateTime	ScriptMethod	System.Object 
				ConvertToDateTime();
Delete		ScriptMethod	System.Object Delete();
GetType		ScriptMethod	System.Object GetType();
Put		ScriptMethod	System.Object Put();

The Delete method can be used to delete an existing alias. For example, if we want to delete the Alias ALIAS1 we have above, we can run this script.

$oldalias=Get-WmiObject `
   -namespace root\Microsoft\SqlServer\ComputerManagement `
   -class SqlServerAlias `
   -filter "AliasName='ALIAS1'"
$oldalias.Delete()

Get-WmiObject can only be used to manage the existing SQL Server client aliases. To create a new alias, we need to use New-Object to create objects from the SMO classes. Before PowerShell can call any .NET class, the assembly containing the .NET class must be loaded first. By default, PowerShell loads a small list of assemblies. This command gets the list of loaded assemblies.

PS > [System.AppDomain]::CurrentDomain.GetAssemblies() | 
  ForEach-Object { split-path $_.Location -leaf } | Sort-Object
Microsoft.PowerShell.Commands.Management.dll
Microsoft.PowerShell.Commands.Utility.dll
Microsoft.PowerShell.ConsoleHost.dll
Microsoft.PowerShell.Security.dll
mscorlib.dll
System.Configuration.Install.dll
System.Data.dll
System.DirectoryServices.dll
System.dll
System.Management.Automation.dll
System.Management.dll
System.Xml.dll

As you can see, the Microsoft.SqlServer.Smo.dll assembly is not loaded by default. To load the assembly, run this command.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

After the assembly is loaded, we first need to create an object that represents the WMI installation on an instance of a Microsoft SQL Server client because an alias must reside on a SQL Server client instance. This object will be the parent of the alias we will create.

$strComputer='.'  # '.' or 'localhost' for local computer.
$objComputer=New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer 
$strComputer

Then we create an alias object from the Microsoft.SqlServer.Management.Smo.Wmi.ServerAlias class. This alias is under the ManagedComptuer object we created.

$newalias=New-Object ("Microsoft.SqlServer.Management.Smo.Wmi.ServerAlias")
$newalias.Parent=$objComputer

Each alias has many properties, such as the name of the server to connect to and the client protocol, that need to be populated before it can be created. In this example, we create an alias that connects to the named instance remoteComputer\Instance1 using the TCP/IP protocol.

$newalias.Name='myalias' # myalias is the new alias name
# remoteComputer\Instance1 is the SQL Server instance the alias points to
$newalias.ServerName='remoteComputer\Instance1' 
# 7001 is the port the SQL Server instance remoteComputer\Instance1 is listening on
$newalias.ConnectionString=7001
$newalias.ProtocolName='tcp' 
$newalias.Create()

The complete script is in CreateServerAlias.ps1.

Manage server network protocols

The ServerNetworkProtocolProperty class represents the server network protocols. To view the list of properties associated with each network protocol, type:

PS > Get-WmiObject `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class ServerNetworkProtocolProperty `
	-filter "InstanceName = 'MSSQLSERVER'" | Select ProtocolName, PropertyName
, IPAddressName


ProtocolName	PropertyName	IPAddressName
------------	------------	-------------
Sm		Enabled	
Np		PipeName
Np		Enabled	
Tcp		KeepAlive
Tcp		Enabled	
Tcp		ListenOnAllIPs
Tcp		NoDelay
Tcp		IpAddress		IP1
Tcp		TcpPort		IP1
Tcp		TcpDynamicPorts	IP1
Tcp		Enabled		IP1
Tcp		Active		IP1
Tcp		IpAddress		IP2
Tcp		TcpPort		IP2
Tcp		TcpDynamicPorts	IP2
Tcp		Enabled		IP2
Tcp		Active		IP2
Tcp		IpAddress		IP3
Tcp		TcpPort		IP3
Tcp		TcpDynamicPorts	IP3
Tcp		Enabled		IP3
Tcp		Active		IP3
Tcp		IpAddress		IP4
Tcp		TcpPort		IP4
Tcp		TcpDynamicPorts	IP4
Tcp		Enabled		IP4
Tcp		Active		IP4
Tcp		IpAddress		IP5
Tcp		TcpPort		IP5
Tcp		TcpDynamicPorts	IP5
Tcp		Enabled		IP5
Tcp		Active		IP5
Tcp		TcpPort		IPAll
Tcp		TcpDynamicPorts	IPAll
Via		DefaultServerPort
Via		ListenInfo
Via		Enabled	

As we can see from the above, besides the common property Enabled, each protocol has its own properties. The Named Pipe protocol has a property called PipeName, which specifies the named pipe on which SQL Server listens. The TCP/IP protocol is associated with all of the IP addresses on the computer, including the loopback address, 127.0.0.1, in the format IP1, IP2, up to IPAll. IPAll denotes all of the IP addresses.

By default, the ListenOnAllIPs property is enabled and SQL Server listens on all of the IP addresses. The settings of the IPAll address apply to all of the IP addresses.

To prevent hackers from using the default port 1433 to “Slam” the default instance, you might want to change its port number from 1433 to another port, for example, 3660. The script ChangeDefaultPortNumber.ps1 is shown here.

$wmi=Get-WmiObject `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class ServerNetworkProtocolProperty `
	-filter "PropertyName='TcpPort' and IPAddressName='IPAll' and InstanceName='MSSQLSERVER'"

$wmi.SetStringValue(3660)

$sqlservice = Get-WmiObject `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class SqlService -filter "ServiceName='MSSQLSERVER'"

$sqlservice.StopService()

$sqlservice.StartService()

# Confirm the default port number has been changed
Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement `
	-class ServerNetworkProtocolProperty `
	-filter "PropertyName='TcpPort' and IPAddressName='IPAll' and InstanceName='MSSQLSERVER'" | Select PropertyStrVal

The script first uses Get-Wmiobject to create an object associated with the TcpPort property of the IPAll address of the default instance. Then it invokes the SetStringValue method to change the property value from 1433 to 3660. However, the change is not effective until the default instance is restarted, so the script restarts the default instance. Finally, the script prints out the value of the TcpPort property to confirm the change.

If you want to assign different ports to different IP address, then you can disable the ListenOnAllIPs property and configure the settings of each IP address. The DisableListenOnAllIPs.ps1 script is shown here.

# remoteComputer is the name of the computer hosts the remote instance
$strComputer = "remoteComputer"
$strInstanceName = "Instance1" # The remote SQL Server instance name

$wmi=Get-WmiObject -computerName $strComputer `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class ServerNetworkProtocolProperty `
	-filter "PropertyName='ListenOnAllIPs' and InstanceName='$strInstanceName'"

$wmi.SetFlag(0) 

$sqlservice = Get-WmiObject `
	-computerName $strComputer `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class SqlService `
	-filter "ServiceName='MSSQL`$$strInstanceName'"

$sqlservice.StopService() 

$sqlservice.StartService() 

$wmi=Get-WmiObject -computerName $strComputer `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class ServerNetworkProtocolProperty `
	-filter "PropertyName='ListenOnAllIPs' and InstanceName='$strInstanceName'" 

# Confirm the ListenOnAllIPs property has been disabled.
Write-Host "The value of the ListenOnAllIPs property is set to " $wmi.PropertyNumVal

The script first uses Get-Wmiobject to create an object associated with the ListenOnAllIPs property. Then it disables the ListenOnAllIPs property using the SetFlag method to set the Boolean property value. Since the change does not take effect until the corresponding SQL Server service is restarted, the script restarts the service. Finally, the script confirms the change by printing out the value of the ListenOnAllIPs property.

Conclusion

We have illustrated above the power of Windows PowerShell in conjunction with the WMI Provider for Configuration Management. In the next article, we will show you how to monitor events in an instance of SQL Server using Windows PowerShell and the WMI Provider for Server Events.

About the author:
Yan Pan is certified in Microsoft database certifications, including MCTS in SQL Server 2005 and MCDBA. She has more than 5 years of experience in SQL Server administration and Analysis Server. Currently, Yan is the primary SQL Server DBA in one of the top finance companies on Wall Street. She can be reached at yanpandba@yahoo.com



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date