Tuesday, 17 September 2013

PowerShell to Enable and Configure Database Mail across various SQL servers


Recently I was asked to configure Database Mail on all SQL Servers in one of my client environment, Instead of using SSMS or TSQL I tried to use PowerShell with SMO objects to accomplish the task faster, Find the script below I created for the same, I took reference from http://www.sqlservercentral.com/articles/Database+Mail/74429/ and created my version of script, The Script can be run for one server or set of SQL Servers listed in a file. By default the Account and Profile name will be ServerName or SQLName_InstanceName in case of named instance which you can change by just assigning right  value to the variables in the bleow script.
# Script to create database mail
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 13/09/2013
# Script Help :-
#---------------
# Parameter 1 :- "SQL" to run powershell for Single server
#      "File" to give filename with list of servers
# Parameter 2 :- Server name(if -s is the parameter 1) or Filename with fully defined path(if -f is the parameter 1)
#
# Example1:- DBMail.ps1 -Mode SQL -Server servername
# Example2:- DBMail.ps1 -Mode File -Server filename with fully define path like c:\test.txt

Param(
 [Parameter(Mandatory=$True,Position=0)]
 [String]$Mode,
 
 [Parameter(Mandatory=$True,Position=1)]
 [String]$Server
     )
Clear-Host
$ChkAccount = $null
if($Mode.ToUpper() -eq "SQL")
{
 $srt = "Server Name :- " + $Server
 $srt
 Echo "---------------------------"
 $SQLSplit = $Server.Split("\")
 $SQLcompname = $SQLSplit[0]
 if($SQLSplit[1] -ne $null)
 {
  $instancename = $SQLSplit[1]
  $ProfileName = $SQLcompname+"_"+$instancename 
 }else{$ProfileName = $SQLcompname}
 $AccountName = $ProfileName
 $smtpServer = "smtp.yourdomain"
 #Connect to the local, default instance of SQL Server.
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
 #Get a server object which corresponds to the default instance
 $srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server -ArgumentList $Server -ErrorAction Stop
 #Define the Database Mail; reference it using the Server Mail property.
 if($srv.Configuration.DatabaseMailEnabled.ConfigValue -ne 1)
 {
 $srv.Configuration.DatabaseMailEnabled.ConfigValue = 1
 $srv.Configuration.Alter()
 Write-Host -ForegroundColor DarkGreen "Database Mail enabled"
 }
 $DBMail = $srv.Mail
 $ChkProfile = $DBMail.Profiles |where{$_.name -eq $ProfileName}
 $ChkAccount = $DBMail.Accounts |where{$_.name -eq $AccountName}
 #Delete existing profile
 if ($ChkProfile -ne $null)
 {
  Write-Host -ForegroundColor Red "DB Profile $ProfileName Already Exists and will be deleted"
  Try
  {
   $ChkProfile.Drop()
  } 
  Catch
  {
   Write-host -ForegroundColor Red "ERROR [DROP EXISTING PROFILE]:"$_.Exception.Message
   Exit;
  }
  Write-Host -ForegroundColor Red "DB Profile $ProfileName Dropped"
 }
 #Delete existing account
 if ($ChkAccount -ne $null)
 {
  Write-Host -ForegroundColor Red "DB Account $AccountName Already Exists and will be deleted"
  Try
  {
  $ChkAccount.Drop()
  }
  Catch
  {
   Write-host -ForegroundColor Red "ERROR [DROP EXISTING ACCOUNT]:"$_.Exception.Message
   Exit;
  }
  Write-Host -ForegroundColor Red "DB Account $AccountName Dropped"
 }
 #Create Mail Account
 $DBAccount = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailAccount -argumentlist $DBMail,$AccountName -ErrorAction Stop
 Try
 {
 $DBAccount.Description = "SQL Alert Email Account"
 $DBAccount.DisplayName = $AccountName
 $DBAccount.EmailAddress = $AccountName+"@YourCompany.com"
 $DBAccount.Create()
 }
 Catch
 {
  Write-host -ForegroundColor Red "ERROR [CREATE ACCOUNT]:"$_.Exception.Message
  Exit;
 }
 Try
 {
 $DBAccount.MailServers.Item($Server).Rename($smtpServer)
   $DBAccount.Alter()
 }
 Catch
 {
  Write-host -ForegroundColor Red "ERROR [ADD SMTP TO ACCOUNT]:"$_.Exception.Message
  EXIT;
 }
 Write-Host -ForegroundColor DarkGreen "DB Account $AccountName Created"
 #Create Mail Profile
 $DBProfile = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailProfile -argumentlist $DBMail,$ProfileName -ErrorAction Stop
 Try
 {
 $DBProfile.Description = "SQL Alert Email Profile"
 $DBProfile.Create()
 }
 Catch
 {
  Write-host -ForegroundColor Red "ERROR [CREATE PROFILE]:"$_.Exception.Message
  EXIT;
 }
 Write-Host -ForegroundColor DarkGreen "DB Profile $ProfileName Created"
 Try
 {
 $DBProfile.AddAccount($AccountName,0)
 $DBProfile.AddPrincipal("Public",$false)
 $DBProfile.Alter()
 }
 Catch
 {
  Write-host -ForegroundColor Red "ERROR [ADD ACCOUNT TO PROFILE]:"$_.Exception.Message
  EXIT;
 }
 Write-Host -ForegroundColor DarkGreen "DB Account [$AccountName] added to Profile [$ProfileName]"
}
elseif($Mode.ToUpper() -eq "FILE")
{
 $filename = $Server
 $computers = get-content $filename
 ForEach($computer in $computers)
 {
  $SQLServer = $computer -replace '^\s+', ''
  $srt = "Server Name :- " + $SQLServer
  $srt
  Echo "---------------------------"
  $Error_srt = 0
  if(($SQLServer -ne $null) -or ($SQLServer -like '^\s+'))
  {  
   $SQLSplit = $SQLServer.Split("\")
   $SQLcompname = $SQLSplit[0]
   if($SQLSplit[1] -ne $null)
   {
    $instancename = $SQLSplit[1]
    $ProfileName = $SQLcompname+"_"+$instancename 
   }else{$ProfileName = $SQLcompname}
   $AccountName = $ProfileName
   $smtpServer = "smtp.yourdomain"
   #Connect to the local, default instance of SQL Server.
   [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
   #Get a server object which corresponds to the default instance
   Try{
     $srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server -ArgumentList $SQLServer -ErrorAction Continue
     #Define the Database Mail; reference it using the Server Mail property.
     if($srv.Configuration.DatabaseMailEnabled.ConfigValue -ne 1)
     {
      $srv.Configuration.DatabaseMailEnabled.ConfigValue = 1
      $srv.Configuration.Alter()
      Write-Host -ForegroundColor DarkGreen "Database Mail enabled"
     }
    }
   Catch {Write-host -ForegroundColor Red "ERROR [CONNECT SQL $Server]:"$_.Exception.Message;continue}
   #Define the Database Mail; reference it using the Server Mail property.
      $DBMail = $srv.Mail
   $ChkProfile = $DBMail.Profiles |where{$_.name -eq $ProfileName}
   $ChkAccount = $DBMail.Accounts |where{$_.name -eq $AccountName}
   #Delete existing profile
   if ($ChkProfile -ne $null)
   {
    Write-Host -ForegroundColor Red "DB Profile $ProfileName Already Exists and will be deleted"
    Try
    {
     $ChkProfile.Drop()
    } 
    Catch
    {
     Write-host -ForegroundColor Red "ERROR [DROP EXISTING PROFILE]:"$_.Exception.Message
     Continue
    }
    Write-Host -ForegroundColor Red "DB Profile $ProfileName Dropped"
   }
   #Delete existing account
   if ($ChkAccount -ne $null)
   {
    Write-Host -ForegroundColor Red "DB Account $AccountName Already Exists and will be deleted"
    Try
    {
     $ChkAccount.Drop()
    }
    Catch
    {
     Write-host -ForegroundColor Red "ERROR [DROP EXISTING ACCOUNT]:"$_.Exception.Message
     Continue
    }
   Write-Host -ForegroundColor Red "DB Account $AccountName Dropped"
   }
   #Create Mail Account
   $DBAccount = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailAccount -argumentlist $DBMail,$AccountName -ErrorAction Continue
   Try
   {
    $DBAccount.Description = "SQL Alert Email Account"
    $DBAccount.DisplayName = $AccountName
    $DBAccount.EmailAddress = $AccountName+"@yourcompany.com"
    $DBAccount.Create()
   }
   Catch
   {
    Write-host -ForegroundColor Red "ERROR [CREATE ACCOUNT]:"$_.Exception.Message
    continue
   }
   Try
   {
    $DBAccount.MailServers.Item($SQLServer).Rename($smtpServer)
      $DBAccount.Alter()
   }
   Catch
   {
    Write-host -ForegroundColor Red "ERROR [ADD SMTP TO ACCOUNT]:"$_.Exception.Message
    Continue
   }
   Write-Host -ForegroundColor DarkGreen "DB Account $AccountName Created"
   #Create Mail Profile
   $DBProfile = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailProfile -argumentlist $DBMail,$ProfileName -ErrorAction Stop
   Try
   {
    $DBProfile.Description = "SQL Alert Email Profile"
    $DBProfile.Create()
   }
   Catch
   {
    Write-host -ForegroundColor Red "ERROR [CREATE PROFILE]:"$_.Exception.Message
    continue
   }
   Write-Host -ForegroundColor DarkGreen "DB Profile $ProfileName Created"
   Try
   {
    $DBProfile.AddAccount($AccountName,0)
    $DBProfile.AddPrincipal("Public",$false)
    $DBProfile.Alter()
   }
   Catch
   {
    Write-host -ForegroundColor Red "ERROR [ADD ACCOUNT TO PROFILE]:"$_.Exception.Message
    Continue
   }
   Write-Host -ForegroundColor DarkGreen "DB Account [$AccountName] added to Profile [$ProfileName]"
  
  }
 }
}
else
{
Write-Host "Incorrect Paramenter, use -SQL to specify the SQL Server Name or use -FILE to specify the path of the file containing server list" -ForegroundColor Red
$uParameterHelp = "
# Script Help :-
#---------------
# Parameter 1 :- -SQL to run powershell for Single server
#      -File to give filename with list of servers
# Parameter 2 :- Server name(if -s is the parameter 1) or Filename with fully defined path(if -f is the parameter 1)
#
# Example1:- DBMail.ps1 -Mode SQL -Server servername
# Example2:- DBMail.ps1 -Mode File -Server filename with fully define path like c:\test.txt"
Write-Host $uParameterHelp -ForegroundColor Blue
}
Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Wednesday, 4 September 2013

Analysis Service connectivity error due to SQL Browser service

 
Today some of my SSIS packages were failing to connect to Analysis servicesserver with the following error, 
 
Analysis Services Processing Task: A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running.
  
This was a new package and source and destination analysis services were installed recently, Once I got the error I double checked and found the browser service was running fine on both source and destination even weird was I did not configure any port for SSAS and it was connecting to default TCP 2383 which should not have caused any issue. I double checked the SQL Port configuration again on the servers and it looked as below,
 
 
 

The Port configuration ‘0’ means the connection should go through the default port 2383; next thing I did was to find out if the TCP port 2383 was blocked under any firewall but it wasn’t.
 
Then I tried to connect to the SSASthrough management studio without forcing the port number it error out again with a similar error but what was interesting was it said the SSMS was trying to connect to <SERVERIP>:2382 and this port 2382 was refusing connection.
On analysis from MSDN TechNet’s I found 2382 was the port ID to which client connect to browser service to resolve SSAS ports similar to UDP port 1434 in database services. But still why are we not able to connect to this port?
 
My last option was to try connecting using explicit port number in SSMS like <Servername>:2383 instead of instance name and I was able to connect successfully. 
Now it eventually came down to the browser service connectivity issue, after several trial and error and reading many articles; it all pointed to the service account of the browser service, The Browser Service in my system was configured under local service, I changed it to a domain account with Admin access in the system and again it was erroring out with the same error. As a last try I changed the service account of Browser Service to “Local System“ VOILA!!!!! Connectivity was fine now and all my packages were running fine. I am not sure why this happens but thought it be of help to write it down for others having similar issue or someone to shed me some light on why this happens. 

 
After this error, I ended up changing the service account of Browser service on all my servers to Local System!!!!