SQL Server – Reporting Services – Provision Users

Background

We have a farm of Web Servers running Microsoft IIS and they need to connect to a Microsoft SQL Server Reporting Services and serve reports.

 

Provisioning

Security

Goal

One of the areas that we need to cover is to ensure that the Active Directory Accounts that the Web Site is running under has the right security privileges to consume reports from the RS Server.

And, so took a survey of all the Application Pools and it is easy to go in and add each account.

But, it will take time to do so in all of our environments ( Dev, Test, QA, and Production ).

 

Script

As always looked for scripts on the Net.

Bryan Keller, MSFT

Found one written by Bryan Keller way back in 2004.

Sample Script for Setting Item-level Security in Reporting Services
Link

 

Modifications

Made a few modifications and here is what we came up with

addItemSecurity.rss
'=====================================================================

'  File:     AddItemSecurity.rss

'

'  Summary:  Demonstrates a script that can be used with RS.exe to

'                set security on an item in Reporting Services.

'

'

' THIS CODE AND INFORMATION ARE PROVIDED AS IS WITHOUT WARRANTY OF ANY

' KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE

' IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

' PARTICULAR PURPOSE.

'=====================================================================*/

'

' Variables that are passed on the command line with the -v switch:

' userName the name of the user for which to add a policy

' roleName the name of the role to apply for the user (i.e. Browser, Content Manager)

' itemPath the path of the item for which you want to add the policy (i.e. /SampleReports)

' keepCurrentPolicy whether to keep the current policy and add the new one

'

' Sample command line:

' rs -i AddItemSecurity.rss -s http://localhost/reportserver -v userName=MyTestUser

'    -v roleName=Browser -v itemPath=/SampleReports -v keepCurrentPolicy=True

rem Const debug as Boolean = True 
Const debug as Boolean = False
Const CHAR_TAB As String = Microsoft.VisualBasic.vbTab

Dim CHAR_SEPARATOR As String = Microsoft.VisualBasic.StrDup(80, "*")
Dim logMessage as String
Dim colorSave  as System.ConsoleColor

Public Sub Main()

   Dim isRoot As Boolean = False

   Dim inheritParent As Boolean

   Dim policies() As Policy

   Dim newPolicies() As Policy

   Dim policy As New Policy()

   Dim roles(0) As Role
   
   Try

	   roles(0) = New Role()

	   roles(0).Name = roleName

	   policy.Roles = roles

	   policy.GroupUserName = userName

	  

	   While Not isRoot

		  ' Once the root of the catalog is reached,

		  ' stop applying policies

		  If itemPath = "/" Then

			 isRoot = True

		  End If

		  policies = rs.GetPolicies(itemPath, inheritParent)

			

		  ' If the user selects not to keep inherited or current policy,

		  ' empty the policy

		  If Not keepCurrentPolicy = "True" Then

			 policies = Nothing

		  End If

		  if (debug) then Console.WriteLine("Calling AddNewPolicy.")
		  
		  newPolicies = AddNewPolicy(policy, policies)
		  
		  if (debug) then Console.WriteLine("Calling SetPolicies.")

		  rs.SetPolicies(itemPath, newPolicies)
		  
		  if (debug) then Console.WriteLine("Calling GetParentPath" & itemPath)

		  itemPath = GetParentPath(itemPath)

	   End While

	   Console.WriteLine("Policy successfully set.")
   
   CATCH ex As Exception
   
		colorSave = Console.ForegroundColor
		
		Console.ForegroundColor = ConsoleColor.Red
   
		Console.WriteLine(CHAR_TAB & "**************************************************")
   
		Console.WriteLine(CHAR_TAB & "Exception Type    :" & CSTR(ex.GetType().ToString()) )
   
        Console.WriteLine(CHAR_TAB & "Exception Message :" & ex.Message)
		
		Console.WriteLine(CHAR_TAB & "**************************************************")		
   
		'THROW ex
		
		Console.ForegroundColor = colorSave
		
   END TRY

End Sub 'Main

  

 

' Method to parse the path of an item and retrieve

' the parent path of an item

Private Function GetParentPath(currentPath As String) As String

   Dim delimiter As String = "/"

   Dim rx As New System.Text.RegularExpressions.Regex(delimiter)

   Dim childPath As String() = rx.Split(currentPath)

   Dim parentLength As Integer
   
   parentLength  = childPath.Length - 1

   Dim parentPath(parentLength) As String

   Dim i As Integer

   For i = 0 To parentLength - 1

      parentPath(i) = childPath(i)

   Next i

   If parentPath.Length = 1 Then


	  Return "/"

   Else

	  Return String.Join("/", parentPath)

   End If

End Function 'GetParentPath

  

' Takes the policy to add and applies it to the current set

' of policies if applicable

Private Function AddNewPolicy(policyToAdd As Policy, policies() As Policy) As Policy()

   Dim list As New System.Collections.ArrayList(policies)
   Dim policy As Policy   

   If Not (policies Is Nothing) Then

      For Each policy In  policies

         If policy.GroupUserName = policyToAdd.GroupUserName Then

            Throw New Exception("The supplied User policy already exists for the item")

         End If

      Next policy


      list.Add(policyToAdd)

      Return CType(list.ToArray(GetType(Policy)), Policy())

   Else

      policies = New Policy(0) {}

      policies(0) = policyToAdd

      Return policies

   End If

End Function 'AddNewPolicy

 

applyToDev.cmd

@echo off 
setlocal enableextensions enabledelayedexpansion


set _server=DEVWS01
set _serverURL=http://%_server%/ReportServer

echo Applying AD Accounts against server %_server%

for /f %%s in ( ADUsersDemo.txt ) do (
	
	set _user=%%s
	set _ADAccount=!_user!

	echo Processing AD Account !_ADAccount!

	rs -i AddItemSecurity.rss -s %_serverURL% -v userName=!_ADAccount! -v roleName="Browser" -v itemPath="/" -v keepCurrentPolicy="True"
)

endlocal

ADUsersDemo.txt
LABDOM\WSDEMO01$
LABDOM\WSDEMO02$

Source Control

GitHub

Placed in Github here

 

Listening

Listening to a little Liyah

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s