EWS & .NET how to get started

Getting started with Exchange Web Service (EWS)

Firstly you might want to configure your security groups as per my article titled “Exchange Web Service (EWS) configuration” then return and continue.

EWS is a powerful way to access Microsoft Exchange over https. Doing this via .NET is a doddle.

I am going to assume you know how to add a web reference to your .net project.

your exchange server will be publishing EWS on the following URL

https://<DomainName|IPAddress|Servaername>/EWS/Services.wsdl

All you need to do is add a webreference to your .NET project by pointing to the above url. The important thing to remember is that you will actually be using a different URL when it comes to binding in code. The url shape will be like this

https://<DomainaName|IPAddress|Servaername>/EWS/exchange.asmx

Once you have your web reference in place, and in this example we will be calling the reference EWS, the first thing you are going to need to do is declare a new instance like so

The GetNewEWSBinding() returns a binding. The binding is on one of the mailboxes within the saEWSImpersonatable security group mentioned in the article previously mentioned at the begining of this one.
The following version of the function has a default binding mailbox defined in a variable at the package level of the SSIS package this script task resides in; it also checks for an InTest switch to swap between two mailboxes when in test or production. Binding credentials can be configured manually for a specific user, this user must be a member of the security group sgEWSImpersonate as described in the article mentioned at the beginning of this one. Finally there is an override optional parameter to provide if you wish to specify a specific mailbox to bind to instead of the default.
You can amend this to your requirements

As soon as you have your binding you are ready to start working with the mailbox.

In my next article on EWS, I will discuss how to declare your EWS binding objects using object initialisers, you will be able to declare and run requests against EWS using less code.
ie Dim x as object With {.parameter = value, .parameter = value}
Example

Exchange Web Service (EWS) configuration

Exchange Web Service (EWS) configuration

The idea here is to create two groups within the Active Directory. The first group will contain the Mailbox accounts you wish to allow access and manipulation of objects within the mailbox (sgEWSImpersonatable). The second will contain the accounts you wish to allow access to the accounts within the first group (sgEWSImpersonate).

What we want to do is

  • AD – Create a security group (sgEWSImpersonateAble), this group will hold the accounts we want to be able to impersonate (eg testAccounts, devsystems etc etc)
  • AD – Create a security group (sgEWSImpersonate), this group will hold the accounts we want to allow impersonation of the accounts in the group sgEWSImpersonateAble
  • EX – Create a Scope (scopeEWSImpersonate), this scope we use to link the ApplicationImpersonation Exchange role to the security group created in the previous step. . Ie we assign the scope to the security group sgEWSImpersonateAble
  • EX – Create a RoleAssignment (mraEWSImpersonation) this Management Role Assignment will be used to tie the ApplicationImpersonation role to the scope. this then compeltes the loop between AD and Exchange

Follow these steps

  1. Create the Security Group in AD (it can be mail enable or not, it makes no difference)
    Group Name: sgEWSImpersonateAble
    Group Description: Exchange Web Service Impersonation, accounts in this group will grant members of the group sgEWSImpersonate impersonation ability via Exchange Web Service calls
    Group Members: TestAccounts, testsqlmailuser, etc,etc,etc
  2. Create the Security Group in AD (it can be mail enable or not, it makes no difference)
    Group Name: sgEWSImpersonate
    Group Description: Exchange Web Service Impersonation, accounts in this group be able to impersonate members of the group sgEWSImpersonateAble via Exchange Web Service (EWS) calls
    Group Members: Developer1,Developer2, Sysadmin1, svcAccount, etc,etc
  3. Create the Scope (This is a one time only requirement to run) In Exchange Management Powershell console run the following, this will link the scope to the groupGet the location of the security group we created for the accounts to impersonate

    >$sgEWSImpersonateAble = $(Get-DistributionGroup sgEWSImperonateAble).Identity.DistinguishedName

    verify we have it by looking at the vaariable
    >$sgEWSImpersonateAble
    CN=sgEWSImpersonateAble,OU=OrganisationalUnitContainingTheGroup,DC=DomainName,DC=local

    Now Create the Scope linking it to the group
    >New-ManagementScope -Name:scopeEWSImpersonate -RecipientRestrictionFilter:”MemberOfGroup -eq ‘$sgEWSImpersonateAble'”

  4. Create the Role Assignment (to link the scope to the group containing the accounts we want to allow impersonation to)>New-ManagementRoleAssignment –Name:mraEWSImpersonation –Role:ApplicationImpersonation –SecurityGroup “sgEWSImpersonate” –CustomRecipientWriteScope: scopeEWSImpersonate

For a long story short execute the following in the Exchange Management Powershell console. Replace the names to those you would prefer.

>$sgEWSImpersonateAble = $(Get-DistributionGroup sgEWSImperonateAble).Identity.DistinguishedName
>$sgEWSImpersonateAble
CN=sgEWSImpersonateAble,OU=OrganisationUnitContainingTheGroup,DC=Domain,DC=local
>New-ManagementScope -Name:scopeEWSImpersonate -RecipientRestrictionFilter:”MemberOfGroup -eq ‘$sgEWSImpersonateAble'”
>New-ManagementRoleAssignment –Name:mraEWSImpersonation –Role:ApplicationImpersonation –SecurityGroup “sgEWSImpersonate” –CustomRecipientWriteScope: scopeEWSImpersonate

Now you can add and remove people and mailboxes to and from the two groups to allow impersonation of mailboxes from accounts

References

http://msdn.microsoft.com/en-us/library/exchange/bb204095(v=exchg.140).aspx

Set-ManagementRoleAssignment
http://technet.microsoft.com/en-us/library/dd335173(v=exchg.141).aspx
New-ManagementRoleAssignment
http://technet.microsoft.com/en-us/library/dd335193(v=exchg.141).aspx
New-ManagementScope
http://technet.microsoft.com/en-us/library/dd335137(v=exchg.141).aspx
Set-ManagementScope
http://technet.microsoft.com/en-us/library/dd297996.aspx

SQL Server Monitor Changes to SCHEMA

If you need to prevent changes to your SQL Server Schema, or at least monitor them, then this post is worth reading

I needed to monitor/control changes made to SQL server databases at server instance level. There are plenty of ways of doing this but there is nothing as simple as a DDL level trigger. It’s clean, can be disabled quickly for rapid change implementation and its a central location which means deploying to sever SQL Servers or a server farm is that much easier.

So the basic requirements
1. We must be able to monitor CREATE,DROP and ALTER on Tables, Views, Stored Procedures, Triggers and Functions
2. We must be able to filter based on user, applying the rule or not
3. We must have a log of each schema change regardless of success or failure
4. We need to send an email when a change has been attempted and was disallowed

So how are we going to do this. Well we can use the ROLLBACK inside our trigger to prevent the changes from occurring, and we can use the RAISERROR to display the appropriate message we want to user/program to receive when the attempt fails. We can also take advantage of the code SQL Server query execution sequence such that after a rollback has been called in a trigger, the parent transaction has been dumped, but any further transactions will continue to be executed and committed. See here http://msdn.microsoft.com/en-us/library/ms187844(v=sql.105).aspx

Some Key fundamentals we are going to be using
First off the

, this will allow us to create the trigger at server level, so it affects all databases. It will hence be located in the Server Objects >> Triggers within SQL Server Management Studio
Next comes

which contains all the details we want about what is changing and who is trying to change it
We will be using “

” to identify the current logged in connection that is behind the event and also filter on this to make a decision to reject or approve the change.
Finally we use the

and

commands to initiate a rejection of the event, followed with our logging insert and in my case an email send method, I won’t get into the details of the mail sending in this article, however its worth mentioning that I have a fully fledged SSIS package driven mail sending engine integrated with MS Exchange, its possible to send from any mailbox to any mail group or address in the active directory or individual mail address book, you can attach files from the network or from binary within the database, you can send with importance in text format or html as this example demonstrates, Its a very powerful and very handy engine to have in the systems topology, took 3 days to write and about a month to fine tune, suddenly sending mail is very easy from anywhere in your infrastructure. Anyway I digress

Enough background, here’s the Code. You will obviously need to adjust bits to suite your needs before you use this
This should be executed against the master