x

How can I automate SSRS email setting config?

Hi all,

just a quick question regarding SQL Server Reporting Services: I have to configure email settings for 100+ SSRS and want to automate it, but I have no clue how to achieve this. Maybe there's a powershell solution, but I didn't found anything useful. Side note: SSRS is standalone, not Sharepoint-integrated.

I'd be great if someone has a hint for me.

Regards and have a nice weekend Dirk

more ▼

asked Sep 07, 2012 at 11:35 AM in Default

DirkHondong gravatar image

DirkHondong
1.3k 15 17 19

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Hi again,

no blog post yet, but I wanted to publish the solution I got from a different forum. Thx to Olaf Helper, who also provides a lot of nice scripts in the technet scripts forum. Either take a look at social.technet.com or just at the code below (not formatted properly, sorry)

   # Configuration Settings:
[String] $serverName = "MyServer";
[String] $instanceName = "MyInstanceName";
[String] $version = "v10";   # v9 = 2005, v10 = 2008/R2, v11 = 2012

[String] $wmiNamespace = "root\Microsoft\SqlServer\ReportServer\RS_$instanceName\$version";
[String] $rsNamespace = $wmiNamespace + "\Admin"; 
Clear-Host;

$cfg = Get-WmiObject `
       -namespace $rsNamespace `
       -class "MSReportServer_ConfigurationSetting" `
    -ComputerName $serverName;

Write-Host $cfg.SMTPServer $cfg.SenderEmailAddress;
$emailOld = $cfg.SenderEmailAddress;

$result = $cfg.SetEmailConfiguration($true, "NewSMTPSrv", $emailOld);
if ($result.HRESULT -eq 0) 
{ Write-Host "Success" } 
else
{ Write-Host "Error!" -BackgroundColor Red } 

Write-Host "Done!";

This is the basis for my solution to hit more than one server.

One little note from my side: If you need to edit the SendEmailToUserAlias tag (if users need the capability to edit the To adress), then you probably have to do a string replace inside the config file. I have found no description in technet to edit this through WMI. I suppose that there's no class.

Regards Dirk

more ▼

answered Sep 20, 2012 at 09:37 AM

DirkHondong gravatar image

DirkHondong
1.3k 15 17 19

Can I mark my own answer now as the solution?
Sep 20, 2012 at 09:44 PM DirkHondong
(comments are locked)
10|1200 characters needed characters left

This is an overview of the WMI provider for SSRS, although it is for SQL 2005 it probably relates to higher versions.

This goes through accessing the SSRS WMI provider and using PowerShell with it.

You can probably take terms out of the above articles to do a bit more searching, most of my hits were focused on service management not actually changing configurations.

more ▼

answered Sep 07, 2012 at 12:41 PM

Shawn_Melton gravatar image

Shawn_Melton
5.4k 20 21 29

I'll take a look at it.

Thx and have a great weekend Dirk
Sep 07, 2012 at 02:09 PM DirkHondong
Blog the results - it could come in handy for someone else!
Sep 07, 2012 at 02:18 PM ThomasRushton ♦
I'll do. But I think it will take some time. Won't spent my weekend solving this issue :)
Sep 07, 2012 at 02:50 PM DirkHondong
(comments are locked)
10|1200 characters needed characters left

I've found a way to solve this. Not very elegant, but it seems to work (did a little test today with positive result) and it's really simple:

Until now, there's no email config setting at all in my SSRS and I need only a minimal configuration (e.g. setting SMTP Server and a FROM adress). So I'll open the config files with powershell, perform a string replace and write the files back.

Regards Dirk
more ▼

answered Sep 11, 2012 at 05:42 PM

DirkHondong gravatar image

DirkHondong
1.3k 15 17 19

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x558
x15
x4

asked: Sep 07, 2012 at 11:35 AM

Seen: 1072 times

Last Updated: Sep 20, 2012 at 09:44 PM