SSRS: regional settings

When you have a website which display’s a SQL Server Reporting (SSRS) Report, you might run into  the problem that your date fields are in the format of: MM-DD-YYYY, but you want it to be DD-MM-YYYY (for me that is the dutch setting).

You can change this by changing the locale setting of APS in IIS7. Here is how:

ASP Feature setting

 

IIS7 SMTP server logging

I was wondering why my IIS7 SMTP server didn’t create any log files. According to my settings it should create log files @ C:\Windows\System32\LogFiles

Logging enabled for IIS

It seems that for this to function correctly you have to install the role service “ODBC Logging”. Here is how to do this:

  • Open Server Manager
  • Navigate to the webserver Role
  • Click “Add Role Service”
  • Activate ODBC Logging
  • Restart your SMTP Service

When you now connect to your smtp service it will create a log file.

SQL: maintenance plan delete after server rename

In a previous post I spoke about changing your database server name. If you created a maintenance plan, you might notice that the plan will fail. This is because the connection of your maintenance plan could be changed.

You can delete the maintenance plan by executing the following T-SQL (just change the Maintenance plan name:

DECLARE @PlanID AS VARCHAR(255)

BEGIN TRAN DeleteOldMaintenancePlans

SELECT @PlanID = id
FROM sysmaintplan_plans
WHERE name LIKE 'MaintenancePlan Name'

DELETE FROM sysmaintplan_log
    WHERE plan_id = @PlanID

DELETE FROM sysmaintplan_subplans
    WHERE plan_id = @PlanID

DELETE FROM sysmaintplan_plans
    WHERE id = @PlanID

IF @@ERROR = 0
 COMMIT TRAN DeleteOldMaintenancePlans
ELSE
 ROLLBACK TRAN DeleteOldMaintenancePlans

GO

After this you can manually delete the job which was associated with the maintenance plan.

Thx to SQLLearnings

Amazon EC2: EBS v.s. Instance Store

As we are having some problems running SQL databases on Amazon EC2, I decided to run some performance tests.

First of all my setup: Large Instance (EBS booted), Windows 2003 Server x64

My test tool: ATTO Disk Benchmark Tool
Note: This might not be the best disk performance test tool, but I did not know any other :-(

I created 4 setups:

  • Instance Store Striped
    You might ask yourself “instance store disks are not available when you have a EBS backed instance”. They are, but they are somewhat hidden.
    Use the command ec2-run-instances <AMI> -k <Keypair> -b "/dev/sda2=ephemeral0" -b "/dev/sda3=ephemeral1" -t m1.large -g "<security group>", read more here 
  • Normal EBS Volume
  • EBS volume consisting of 8 disks in Striped Setup (software RAID0)
  • EBS Volume consisting of 8 disks in Raid 5 Config (software RAID5)

The Results:

Write Performance (the X-axis displays block sizes in KB, Y axis displays Transfer Rate MB/s)

Write Performance

Read Performance (the X-axis displays block sizes in KB, Y axis displays Transfer Rate MB/s)

Read Performance

I let the results speak for themselves.

BPOS: auto reset user passwords with the help of PowerShell

One little drawback of BPOS is the fact that User Passwords do have an expiration policy. Don’t get me wrong, from a security perspective this is a good thing. Only it could be that some users do not have a real life user associated with them. For example the user in my previous post. When the password of this user expires the mail functionality will fail.

I had to think of something that would reset the password automatically.
First thought that came to my mind is PowerShell (also a good thing). Second was the Migration Tools command Set-MSOnlineUserPassword

btw: You can download the migration tools here: (x86) 32 bit version, (x64) 64 bit version

I also wanted this to secure my admin credentials

I am going to store the admin password encrypted in a file. To do this first of all get your credentials by issuing the command:

$credential = get-credential

This will give you a popup box in which you have to enter your MS online credentials. Now to store the password encrypted in a file, issue the command:

$credential.Password | ConvertFrom-SecureString | Set-Content <filename>
 

Look at the content of <filename>, you specified. It is encrypted.

Now how can you use this password to automatically log into you Microsoft Online environment.

$user = "admin@<yourbposdomain>"$password = Get-Content $File `
| ConvertTo-SecureString $credential = `
New-Object System.Management.Automation.PsCredential($user,$password)

Note: The back-tick (`) symbol is the PowerShell line-continuation character that allows you to continue a command on multiple lines

To store the passwords for the individual users you have is (to my knowledge) still not possible without providing some kind of encryption key (it than can be decrypted with the help of the content of the script you are making here, so extra work for nothing). If you would use the above method you would eventually get a password that is in plain text “System.Security.SecureString”.

If someone has a way to do this, I would love to hear it in the comments.

Ok, the complete script:

Note: The migration tools use a Powershell snapin which you have to load into your powershell session: Add-PSSnapin microsoft.exchange.transporter

 
# Load Migration tools snapin
Add-PSSnapin microsoft.exchange.transporter

#variabelen voor BPOS Admin
$SecurePassAdmin = "c:\securepass.enc"
$AdminUser = "<admin>@<your microsoftonline domain.com>"

#Set Admin Credentials
$PasswordAdmin = Get-Content $SecurePassAdmin | ConvertTo-SecureString
$AdminCredentials = `
New-Object System.Management.Automation.PsCredential`
($AdminUser,$PasswordAdmin)

#change password users
set-msonlineuserpassword -identity <your user> -credential `
$AdminCredentials -Password "<user password>" `
-ChangePasswordOnNextLogon $false -verbose

Now schedule this to run every month or so, and you are done.

Some trouble I ran into:

  • when scheduling the command I constantly received the error: object reference not set to an instance of an object. This bugged me some time. Powershell knew the command set-msonlinemailuserpassword but it still gave the erorr. I scheduled it under a new user I created and apparently you have to start Internet explorer first under the credentials of the user to make it work.

BPOS: configure SMTP relay

If you want your servers or your software to mail and you are on hosted exchange (BPOS), you have to do some extra work to get it to work.

Prerequisites:
An IIS Server with the SMTP Feature
A BPOS account (this will be used to authenticate against Exchange Online and to send the email)

As I am using IIS7, I need to make sure I install the IIS6 Management Compatibility. I need this for managing my SMTP Server.

IIS6 management Compatibility

  • Start the “Internet Information Services (IIS) 6.0 Manager.
  • Right click the “SMTP Virtual Server” and select properties
  • Select the tab Access and click the “Authentication” button, make sure Anonymous Authentication is selected
  • Click the relay button (still on the access tab) and select the option you want:
    a. only the list below > specify the server IP’s which can use this server as mail server
    b. All except the list below > all servers configured with this IIS server can send mail, except the one you specify
  • Select the tab “Delivery”

We will be configuring the 4 buttons you see, Outbound Security, Outbound Connections and Advanced

Delivery Tab SMTP Virtual Server

  • Outbound Security
    Here you enter the credentials of your bpos user, make sure you also activate “TLS Encryption”
     
    Outbound Security Settings
  • Outbound Connections
    In this screen you have to change the TCP Port to 587 (message submission port).

    Outbound Connections

  • Advanced
    This is where you enter the BPOS (Microsoft Online) SMTP Server as a smart host. For me this would be smtp.mail.emea.microsoftonline.com. Depending on your country this server address would be:
    North American Data Center: Smtp.mail.microsoftonline.com
    European Data Center: Smtp.mail.emea.microsoftonline.com
    Asia Pacific Data Center:  Smtp.mail.apac.microsoftonline.com

    You can optionally enter a masquerade domain.

    Advanced Delivery

  • Just to be sure: restart the SMTP Service and IIS.

    Note
    I received the error 550 5.7.1. Client does not have permissions to send as this sender. It turned out that I used the following format for the  from address username@ourdomain.nl (this was exactly the same as the user I specified in the outbound security tab). When I changed the from address to username@ourdomain.emea.microsoftonline.com it started working.
    This is because my @ourdomain.nl isn’t authoritative (still on external relay)

Tip: Changing default folder where outlook saves attachments

As I am using a Gettings Things Done system, I wanted to change the location of the default folder where outlook saves it attachments. So that whenever I save an attachment I did not have to browse to that folder but it was the folder outlook presented in it’s “ save As” dialog.

You can do this by editing the registry

  • First Close Outlook
  • open the Registry Editor (regedit.exe)
  • Navigate to the following key
    For Outlook 2003:
    HKEY_CURRENT USER\Software\Microsoft\Office\11.0\Outlook\Options

    For outlook 2007:
    HKEY_CURRENT USER\Software\Microsoft\Office\12.0\Outlook\Options

    For outlook 2010:
    HKEY_CURRENT USER\Software\Microsoft\Office\14.0\Outlook\Options

  • Create a “String Value” with the name DefaultPath
  • Double Click the DefaultPath value and insert the folder you want to be the default folder (Including the drive letter)

DefaultPath Registry Key

SQL: changing your database server name

When you change the name of your server which is running SQL, you should also make sure that SQL gets updated with this new name. Proceed with the following procedure:

  • execute the command: select @@servername
    this command will return the current servername stored in SQL
  • execute the command: sp_dropserver ‘servername‘
    in which servername is the name that you got from the previous step
  • execute the command: sp_addserver ‘new servername’
  • restart the “ sql server”  service and the “ sql server agent” service
  • execute the command: select @@servername
    to verify everything went just fine :-)