Archive

Posts Tagged ‘Powershell’

BTDF: System.OutOfMemoryException while deploying using Powershell Remoting

July 17, 2012 Leave a comment

Recently when I tried to deploy a BizTalk solution packaged with BTDF (BizTalk Deployment Framework) in combination with Powershell Remoting I encountered this error:

Error: Failed to add resource(s).

Change requests failed for some resources.

BizTalkAssemblyResourceManager failed to complete end type change request.

Exception of type ‘System.OutOfMemoryException’ was thrown.

 

I checked the memory on the remote server and even restarted it but it didn’t help, I still encounter this error.

As it turns out, as well as I’ve suspected it’s the memory of the powershell. To increase the size of the memory, run the PowerShell Command prompt on the target server and enter the ff:

Set-Item WSMan:\localhost\Shell\MaxMemoryPerShellMB 512

Or set it even to higher.

 

 

[Powershell] Executing an sql query and returning a disconnected data using Powershell

April 17, 2012 Leave a comment

Below is a script that will execute an sql query and returns a disconnected data (DataTable) using Powershell.

<#
.SYNOPSIS
	Queries a data source and returns a disconnected data (DataTable)

.DESCRIPTION
	Queries a data source and returns a disconnected data (DataTable)

.NOTES
	File Name: Get-DbDataTable.ps1
	Author: Randy Aldrich Paulo

.EXAMPLE
	Get-DbDataTable "Data Source=.;Integrated Security=SSPI;Initial Catalog=DatabaseName" "SELECT TOP 10 * From Table1"
#>

function Get-DbDataTable
(
	[string]$connectionString,
	[string]$query,
	[System.Data.CommandType]$commandType=[System.Data.CommandType]::Text,
	[string]$providerName = "System.Data.SqlClient"	
)
{
	#Initialize
	$databaseFactory = [System.Data.Common.DbProviderFactories]::GetFactory($providerName)
	$dbConnection = $databaseFactory.CreateConnection()
	$dbConnection.ConnectionString = $connectionString

	#Create Command
	$dbCommand = $databaseFactory.CreateCommand()
	$dbCommand.Connection = $dbConnection
	$dbCommand.CommandText =$query
	$dbCommand.CommandType = $commandType

	#Create Data Adapter
	$dbAdapter = $databaseFactory.CreateDataAdapter();
	$dbAdapter.SelectCommand = $dbCommand

	$dataTable = New-Object System.Data.DataTable

	try
	{
		#Fill with Results
		$dbAdapter.Fill($dataTable)
	}
	catch { 
		throw 
		}
	finally { 
			if($dbConnection.ConnectionState -ne [System.Data.ConnectionState]::Closed) 
			{ 
				$dbConnection.Close() 
			}
		}
	return $dataTable
}

[Powershell] Calling NET USE to create shared folders on remote server using Powershell

March 1, 2012 Leave a comment

Below is the powershell script sample on how to call NET USE command to create a shared folder on any remote server.


$netShareSettings+= @{	"Server"="TargetServer";
                         "Path"="E:\MyFolderToBeShared";
			 "ShareName" = "SharedFolder";
			  "UserAndPermission" = ("Domain\user1;FULL","Domain\User2;FULL");
		      }

foreach($netShareSetting in $netShareSettings)
  {

   $script =
     {

	$userParam = "";

	foreach($pm in $args[0].UserAndPermission)
	{
		$arr = $pm.Split(";");
		$userName = $arr[0];
		$perm = $arr[1];
		$userParam+= "/grant:`"{0}`,{1}`" " -f $userName, $perm
	}

	$command = "net share {0}=`"{1}`" {2}" -f $args[0].ShareName,$args[0].Path, $userParam
	$msg = " Executing Command: '{0}' on '{1}'" -f $command,$args[0].Server
	$msg = Invoke-Expression $command | Out-String
	$msg = "  Results: " + $msg.Trim();
	Write-Host $msg

     }

     Invoke-Command -ScriptBlock $script -ComputerName $netShareSetting.Server -ArgumentList $netShareSetting
  }
Categories: Powershell Tags: ,

[Powershell] Missing shared custom modules when using Powershell Remoting

February 27, 2012 Leave a comment

In my previous post: Centralize Powershell Script (Modules) Repository, it describes on how to create a custom module and load it on any server. And like the title of this post indicated when you try to connect to a remote server using Powershell remoting, you’ll notice that the custom module is missing ( More information about Powershell Remoting ). This is  because it’s doing a double-hop, First hop is to connect to server (using Enter-PSSession or New-PSSession), next hop is to access the shared folder containing the custom module.

To solve this issue we need to Enable the Credential Service Security Provider (CredSSP) both on remote and host server.

#1 On the server wherein the powershell console instance is started you need to enable the client role.

#2 On the server wherein you want to connect remotely you need to enable server role.

#3 On the server wherein the powershell script is hosted you also need to enable the server role.

Run the following scripts (Run as Administrator):

Script for # 1:
Enable-WSManCredSSP -role Client -DelegateComputer <name of remote server> -force
Script for # 2:
Enable-WSManCredSSP -role Server -force
Script for # 3:
Enable-WSManCredSSP -role Server -force

Powershell remoting with CredSSP:

To connect either by Enter-PSSession or New-PSSession you need to supply extra argument:

$session = New-PSSession -ComputerName <name of remote server> -Authentication CredSSP -Credential Get-Credential

Since i used Get-Credential it would prompt for credential:

Update:

If it still doesn’t work, try restarting the Windows Remote Management (WS-Management) service.

In Run Type, services.msc

In the Name look for: Windows Remote Management (WS-Management), right click Restart.

Categories: Powershell Tags: , ,

[Powershell] Centralize Powershell Script (Modules) Repository

February 27, 2012 1 comment

In this post I will try to describe on how you can centralize your powershell scripts (custom modules) so that it can be  loaded from any servers in the system.

Note: The sample below is using Powershell v2.0 and is intended for Windows Server 2008 and up.

Centralize Powershell Repository Diagram:

Centralize Powershell Scripting Diagram

In this diagram it states that there are 3 custom modules on the powershell scripting server that can be loaded from any of the servers in the network.

Powershell Server Setup Steps:

Necessary steps needs to be undertaken to the Powershell Server is as follows:

1. Create a shared folder in the PS Server that will contain all the custom modules. Assign appropriate permissions.

2. Create/move custom modules in the shared folder. On instructions on how to create a custom module, click here.

Quick Guide: Creating Custom Powershell Module

Creating a custom script module is quite easy, you just need to create a psd1 file (contains manifest information) and psm1 file (basically contains the dot sourcing of all scripts you want to load in the module) and place it in a folder with name equal to the module name.

Sample Folder structure: The C:\Modules will be setup as shared folder.

Contents of Sample.psd1 file

#
# Module manifest for module 'Sample'
#
# Generated by: Randy Aldrich Paulo
#
# Generated on:
#

@{

# Script module or binary module file associated with this manifest
ModuleToProcess = 'Sample.psm1'

# Version number of this module.
ModuleVersion = '1.0'

# ID used to uniquely identify this module
GUID = 'c78688f252-fc04-413a-8b0e-5cfc7e78dcd6'

# Author of this module
Author = 'Randy Aldrich Paulo'

# Company or vendor of this module
CompanyName = 'My Company'

# Copyright statement for this module
Copyright = '(c) 2012 Randy Paulo.'

# Description of the functionality provided by this module
Description = 'Description'

# Minimum version of the Windows PowerShell engine required by this module
PowerShellVersion = ''

# Name of the Windows PowerShell host required by this module
PowerShellHostName = ''

# Minimum version of the Windows PowerShell host required by this module
PowerShellHostVersion = ''

# Minimum version of the .NET Framework required by this module
DotNetFrameworkVersion = ''

# Minimum version of the common language runtime (CLR) required by this module
CLRVersion = ''

# Processor architecture (None, X86, Amd64, IA64) required by this module
ProcessorArchitecture = ''

# Modules that must be imported into the global environment prior to importing this module
RequiredModules = @()

# Assemblies that must be loaded prior to importing this module
RequiredAssemblies = @()

# Script files (.ps1) that are run in the caller's environment prior to importing this module
ScriptsToProcess = @()

# Type files (.ps1xml) to be loaded when importing this module
TypesToProcess = @()

# Format files (.ps1xml) to be loaded when importing this module
FormatsToProcess = @()

# Modules to import as nested modules of the module specified in ModuleToProcess
NestedModules = @()

# Functions to export from this module
FunctionsToExport = '*'

# Cmdlets to export from this module
CmdletsToExport = '*'

# Variables to export from this module
VariablesToExport = '*'

# Aliases to export from this module
AliasesToExport = '*'

# List of all modules packaged with this module
ModuleList = @()

# List of all files packaged with this module
FileList = 'DataMgtDeployment.psm1'

# Private data to pass to the module specified in ModuleToProcess
PrivateData = ''

}

Contents of Sample.psm1 file

#
# DataMgtDeployment Module
#

$ModulePath = \\PowershellServer\Modules\Sample
# Set the location
Set-Location $ModulePath

# Reference the Scripts
. .MyFunction.ps1
. .MyFunction2.ps1

3. Update System Variable PSModulePath to include the location of custom modules

Right Click Computer -> Propeties -> Advanced Settings -> Environment Variables -> Add a semi-colon (;) at the end and add the path of the custom module (Ex. C:\Modules)

4.  To test whether the setup is correct, run the Powershell Command Prompt (As Administrator) and type:


Get-Module -ListAvailable

It should display your custom module.

 

Server(s) Setup Steps:

After the module is properly setup you can now load it on any servers on the network once the following setup steps have been performed:

1. Update System Variable PSModulePath to include the location of custom modules (shared folder)

Right Click Computer -> Propeties -> Advanced Settings -> Environment Variables -> Add a semi-colon (;) at the end and add the path of the custom module (Ex. \\PowershellServer\Modules)

2. Since we are loading the module from a shared location we need to change the execution policy to RemoteSigned. For information about execution-policy see: About Execution Policy. Execute the following script (as Administrator):


Set-ExecutionPolicy RemotedSigned -force

3. After step 2, when you try to load the custom module it will still throw an exception, to solve the problem we need to add the path of shared folder to the list of trusted sites.

Open Internet Explorer -> Tools -> Internet Options -> Go to Security Tab

Click Trusted Sites -> Sites -> In the add type: file://PowershellServer (replace it with actual servername)

Click Add -> Close -> Ok.

Now you can load the custom module from remote powershell server into any of the servers in the network (as long as you did the Server(s) Setup Steps.

Using the shared custom module in powershell remoting is possible but you need to update some settings see my next blog: [Powershell] Missing shared custom modules when using Powershell Remoting

Categories: Powershell Tags:

[Powershell] Set user permission in SSRS Item (SQL Reporting Server) using powershell

February 22, 2012 3 comments

Combining both (article1 and article2), i was able to come up with a powershell script to set user permission in SQL Reporting Services (SSRS)

<#
.SYNOPSIS
	Set user permissions in SQL Reporting Services using Web Service

.DESCRIPTION
	Set user permissions in SQL Reporting Services using Web Service

.EXAMPLE
	Add-SSRSItemSecurity -webServiceUrl "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -itemPath "MyReportFolder" -groupUserName RPAULO\User1 -role Browser

.EXAMPLE
	Add-SSRSItemSecurity -url "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -itemPath "MyReportFolder" -u RPAULO\User1 -r "Content Manager"

#>
function Add-SSRSItemSecurity
(
	[Parameter(Position=0,Mandatory=$true)]
	[Alias("url")]
	[string]$webServiceUrl,

	[Parameter(Position=1,Mandatory=$true)]
	[Alias("path")]
	[string]$itemPath,
	
	[Parameter(Position=2,Mandatory=$true)]
	[Alias("u")]
	[string]$groupUserName,
	
	[Parameter(Position=3,Mandatory=$true)]
	[Alias("r")]
	[string]$role,
	
	[Parameter(Position=2)]
	[bool]$inherit=$true
)

{
	
	#Fix item path if not starting with /
	if(!$itemPath.StartsWith("/")) { $itemPath = "/" + $itemPath}
	
	#Create Proxy
	Write-Host "[Add-SSRSItemSecurity()] Creating Proxy, connecting to : $webServiceUrl"
	$ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl -UseDefaultCredential
	
	$type = $ssrsProxy.GetType().Namespace;
	$policyType = "{0}.Policy" -f $type;
	$roleType = "{0}.Role" -f $type;
	
	Write-Host "[Add-SSRSItemSecurity()] Retrieving all existing policies."
	$policies = $ssrsProxy.GetPolicies($itemPath, [ref]$inherit);
	
	$a = 1;
	foreach($policy in $policies)
	{

		foreach($r in $policy.Roles)
		{
			$msg = "[Add-SSRSItemSecurity()]  Existing Policy # {0} Group Name: {1}, Role: {2}" -f $a, $policy.GroupUserName, $r.Name
			Write-Host $msg
		}
		$a+=1;
	}

	$msg = "[Add-SSRSItemSecurity()] Total Existing Policies: " + $policies.Length;
	Write-Host $msg
	
	$Policy = $policies | 
    Where-Object { $_.GroupUserName -eq $groupUserName } | 
    Select-Object -First 1
	
	if (-not $Policy) {
	    $Policy = New-Object ($policyType)
	    $Policy.GroupUserName = $GroupUserName
	    $Policy.Roles = @()
	    $Policies += $Policy
		$msg = "[Add-SSRSItemSecurity()] Adding new policy: '{0}'" -f $GroupUserName
		Write-Host $msg
	}

	$r = $Policy.Roles |
	    Where-Object { $_.Name -eq $role } |
	    Select-Object -First 1
	if (-not $r) {
	    $r = New-Object ($roleType)
	    $r.Name = $role
	    $Policy.Roles += $r
		$msg = "[Add-SSRSItemSecurity()] Adding new role: '{0}'" -f $role
		Write-Host $msg
	}
	
	#Set the policies
	$ssrsProxy.SetPolicies($itemPath,$policies);

}


[Powershell] How to Install/deploy SSRS (rdl files) using Powershell

February 21, 2012 19 comments

Since Powershell v2.0 introduce the new cmdlet New-WebServiceProxy, it’s no longer needed to generate a class library to deploy/undeploy SSRS file (RDL), below are two powershell scripts Install-SSRSRDL and Uninstall-SSRSRDL that can be useful when doing an automated deployment or silent installation of rdl files to Reporting Services.

Install SSRS RDL Script:


<#
.SYNOPSIS
	Installs an RDL file to SQL Reporting Server using Web Service

.DESCRIPTION
	Installs an RDL file to SQL Reporting Server using Web Service

.NOTES
	File Name: Install-SSRSRDL.ps1
	Author: Randy Aldrich Paulo
	Prerequisite: SSRS 2008, Powershell 2.0

.PARAMETER reportName
	Name of report wherein the rdl file will be save as in Report Server.
	If this is not specified it will get the name from the file (rdl) exluding the file extension.

.PARAMETER force
	If force is specified it will create the report folder if not existing
	and overwrites the report if existing.

.EXAMPLE
	Install-SSRSRDL -webServiceUrl "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -rdlFile "C:\Report.rdl" -force

.EXAMPLE
	Install-SSRSRDL "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" "C:\Report.rdl" -force

.EXAMPLE
	Install-SSRSRDL "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" "C:\Report.rdl" -force -reportName "MyReport"

.EXAMPLE
	Install-SSRSRDL "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" "C:\Report.rdl" -force -reportFolder "Reports" -reportName "MyReport"

#>
function Install-SSRSRDL
(
	[Parameter(Position=0,Mandatory=$true)]
	[Alias("url")]
	[string]$webServiceUrl,

	[ValidateScript({Test-Path $_})]
	[Parameter(Position=1,Mandatory=$true)]
	[Alias("rdl")]
	[string]$rdlFile,

	[Parameter(Position=2)]
	[Alias("folder")]
	[string]$reportFolder="",

	[Parameter(Position=3)]
	[Alias("name")]
	[string]$reportName="",

	[switch]$force
)
{
	$ErrorActionPreference="Stop"

	#Create Proxy
	Write-Host "[Install-SSRSRDL()] Creating Proxy, connecting to : $webServiceUrl"
	$ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl -UseDefaultCredential
	$reportPath = "/"

	if($force)
	{
		#Check if folder is existing, create if not found
		try
		{
			$ssrsProxy.CreateFolder($reportFolder, $reportPath, $null)
			Write-Host "[Install-SSRSRDL()] Created new folder: $reportFolder"
		}
		catch [System.Web.Services.Protocols.SoapException]
		{
			if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")
			{
				Write-Host "[Install-SSRSRDL()] Folder: $reportFolder already exists."
			}
			else
			{
				$msg = "[Install-SSRSRDL()] Error creating folder: $reportFolder. Msg: '{0}'" -f $_.Exception.Detail.InnerText
				Write-Error $msg
			}
		}

	}

	#Set reportname if blank, default will be the filename without extension
	if($reportName -eq "") { $reportName = [System.IO.Path]::GetFileNameWithoutExtension($rdlFile);}
	Write-Host "[Install-SSRSRDL()] Report name set to: $reportName"

	try
	{
		#Get Report content in bytes
		Write-Host "[Install-SSRSRDL()] Getting file content (byte) of : $rdlFile"
		$byteArray = gc $rdlFile -encoding byte
		$msg = "[Install-SSRSRDL()] Total length: {0}" -f $byteArray.Length
		Write-Host $msg

		$reportFolder = $reportPath + $reportFolder
		Write-Host "[Install-SSRSRDL()] Uploading to: $reportFolder"

		#Call Proxy to upload report
		$warnings = $ssrsProxy.CreateReport($reportName,$reportFolder,$force,$byteArray,$null)
		if($warnings.Length -eq $null) { Write-Host "[Install-SSRSRDL()] Upload Success." }
		else { $warnings | % { Write-Warning "[Install-SSRSRDL()] Warning: $_" }}
	}
	catch [System.IO.IOException]
	{
		$msg = "[Install-SSRSRDL()] Error while reading rdl file : '{0}', Message: '{1}'" -f $rdlFile, $_.Exception.Message
		Write-Error msg
	}
	catch [System.Web.Services.Protocols.SoapException]
	{
		$msg = "[Install-SSRSRDL()] Error while uploading rdl file : '{0}', Message: '{1}'" -f $rdlFile, $_.Exception.Detail.InnerText
		Write-Error $msg
	}

}

Uninstall SSRS RDL Script:


<#
.SYNOPSIS
	Uninstalls an RDL file from SQL Reporting Server using Web Service

.DESCRIPTION
	Uninstalls an RDL file from SQL Reporting Server using Web Service

.NOTES
	File Name: Uninstall-SSRSRDL.ps1
	Author: Randy Aldrich Paulo
	Prerequisite: SSRS 2008, Powershell 2.0

.EXAMPLE
	Uninstall-SSRSRDL -webServiceUrl "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -path "MyReport"

.EXAMPLE
	Uninstall-SSRSRDL -webServiceUrl "http://[ServerName]/ReportServer/ReportService2005.asmx?WSDL" -path "Reports/Report1"

#>
function Uninstall-SSRSRDL
(
	[Parameter(Position=0,Mandatory=$true)]
	[Alias("url")]
	[string]$webServiceUrl,

	[Parameter(Position=1,Mandatory=$true)]
	[Alias("path")]
	[string]$reportPath
)

{
	#Create Proxy
	Write-Host "[Uninstall-SSRSRDL()] Creating Proxy, connecting to : $webServiceUrl"
	$ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl -UseDefaultCredential

	#Set Report Folder
	if(!$reportPath.StartsWith("/")) { $reportPath = "/" + $reportPath }

	try
	{

		Write-Host "[Uninstall-SSRSRDL()] Deleting: $reportPath"
		#Call Proxy to upload report
		$ssrsProxy.DeleteItem($reportPath)
		Write-Host "[Uninstall-SSRSRDL()] Delete Success."
	}
	catch [System.Web.Services.Protocols.SoapException]
	{
		$msg = "[Uninstall-SSRSRDL()] Error while deleting report : '{0}', Message: '{1}'" -f $reportPath, $_.Exception.Detail.InnerText
		Write-Error $msg
	}

}

Install-SSRSRDL Powershell Script
Uninstall-SSRSRDL Powershell Script

For permission check this blog: https://randypaulo.wordpress.com/2012/02/22/powershell-set-user-permission-in-ssrs-item-sql-reporting-server-using-powershell/

%d bloggers like this: