All posts by andre

MS SQL TSQL PadLeft

Ever wanted a PadLeft for SQL server
Here’s the next best thing, a scalar function we wrote and implemented into all our databases
Enjoy

Windows 8 to Windows 8.1 with redirected user profile folders

You’re here probably because you received this error

Sorry, it looks like this PC can’t run Windows 8.1. This might be because the Users or Program Files folder is being redirected to another partition

The Solution in a nutshell

  • Edit registry keys to re point to system drive
  • Add New Admin account to system
  • Login as new Admin account
  • edit user registry keys to re point to local system users folder
  • Run batch file to create junctions between local system user folders and redirected user folder
  • Restart, login as admin and run windows update to 8.1
  • Once complete edit registry to re point chosen user profile folders back across to redirected drive
  • The user folders that are redirected again must have the AppData hidden folder folder copied across to the redirected user folder fro the system drive

The solution in more detail
Basically we need to trick the updater that the profiles are local. We do this in a few steps

  1. Edit some registry keys
  2. Create a new admin account, restart and login to it
  3. Edit some more registry keys
  4. Create some junctions to point from local profiles to actual profiles
  5. Restart login again as the admin account and run updates

So let’s get started
You might want to save this page as a “.mht” file to the root of your C: so you can refer back to this as you progress
Edit registry
Run regedit as admin and goto
HKLM\SOFTWARE\Microsot\Windows NT\CurrentVersion\ProfileList
Mine looks like this
ProfileList HKLM
Edit the ProfilesDirectory Key back to “%SystemDrive%\Users”
ProfilesDirectory HKLM
Create a new account and mark is as administrator
Restart and login with the new account
Go back into an admin regedit and adjust the remaining keys so they look like this
RESET ProfileList keys HKLM

As you can see I have lots of local logins (I develop apps and as such lots of services login to my machine) so I built a batch file to create the junctions from local to actual user profile folders
For users with no gaps in their name
mklink /J C:\Users\Andre E:\Users\Andre
for users with gaps
mklink /J “C:\Users\.NET v4.5” “E:\Users\.NET v4.5”
My Batch file ended up here c:\MKLinks.bat and looked like this
mklink /J C:\Users\Mcx1-HOME-PC E:\Users\Mcx1-HOME-PC
mklink /J C:\Users\UpdatusUser E:\Users\UpdatusUser
mklink /J C:\Users\Andre E:\Users\Andre
mklink /J “C:\Users\Classic .NET AppPool” “E:\Users\Classic .NET AppPool”
mklink /J “C:\Users\.NET v4.5” “E:\Users\.NET v4.5”
mklink /J “C:\Users\.NET v2.0” “E:\Users\.NET v2.0”
mklink /J “C:\Users\.NET v4.5 Classic” “E:\Users\.NET v4.5 Classic”
mklink /J “C:\Users\.NET v2.0 Classic” “E:\Users\.NET v2.0 Classic”
mklink /J C:\Users\Default E:\Users\Default
mklink /J C:\Users\Public E:\Users\Public

Now we are ready to execute the batch file, so open an elevated command prompt, navigate to the location you saved the batch file and run it.

Next we must adjust the “ProfileImagePath” string value in each user profile sub key of ProfileList (the keys typically start with S-1-5-xx-xxxxxxxxxx-xxxxxxxxxx-xxxxxxxxxx-xxxx) so they point back to the local system drive (C:) like this one

Return Profiles back to system drive locations

Once you are sure you have completed these instructions restart once more, login as the admin account and run windows update.

Once completed, every profile will be put back onto the C:\Users and your folder junctions will have been replaced by these user folders.
There is a simple solution to bring things back across to your redirected drive, editing the users registry key and copying the hidden AppData folder from the C:\Users\Username folder to the target user profile folder on the redirected drive you are trying to use.

I ended up leaving all of my profiles on C and only redirected my profile, which incidentally was far too large to reside on the C:

asp.net ReportViewerControl The size of the request headers is too long

Bad Request – Request Too Long

HTTP Error 400. The size of the request headers is too long.

When you use the “Microsoft.ReportViewer.WebForms”control in asp.net

You’ve eaten too many cookies Reserved.ReportViewerWebControl.axd

If like me you use the report viewer control to display reports from SSRS to the world wide web you may have come across an ever increasing number of cookies from the control.
I don’t really have time to look into this in any detail as im working to a deadline at the moment and as such need to resolve this asap.
I think this is perhaps something to do with the fact I am using masterpages with iframes to both separate the CSS and pull out the parameters into an intermediate step.

Anyway my solution was to wipe the cookie from the request cookies collection so they dont get a chance to stack up.
Create class in you App_Code and past the following code into the file

you could adapt this to only delete if the number of cookies exceeded a certain level

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

SSMS SQL Server Management Studio Refresh Schema

Refreshing the intelli-sense (Intellisense) for the current connection to a given database can be a bug bare

There is nothing more frustrating than making additions, or changes in design to tables/views and having the management studio intellisense not pick-up the changes once you have made them. This is because the SSMS schema refresh doesn’t occur real-time not does it update local schema changes to those successfully committed to the db while you work, it occurs on close and open of the IDE.

The easiest way to refresh the schema on any connection (also note each new window is a separate connection and as such you need to refresh each window)

You can either

1) Go to Edit -> IntelliSense -> Refresh Local Cache and
OR
2) Hit Ctrl+Shift+R

Hope this helps those with the same issue

SQL 2008 SSRS Web Service access from .NET

When you try to access a report server web service to execute code you get an error similar to, where the scheme or header varies a tiny bit

The HTTP request is unauthorized with client authentication scheme ‘Basic’. The authentication header received from the server was ‘Negotiate,NTLM

Basically my situation is that we have a MS 2008 Server running SSRS outside of our domain in the DMZ. However we need to execute code on a domain machine that will connect and run over 100 reports on the SSRS Server, then dump them on a share in our domain in excel format.

To get around the negotiation problem you need to make sure the SSRS server is allowing connections configured using basic authentication

Find the file

rsreportserver.config

This is usually buried in the install folder

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer

Then change the authentication to support your desired connection authentication type

More info at MSDN
Once you have done that you should be good to connect.
Here is some sample code to get you started with connecting to your web service and pulling back a list of items

SQL Server Auto Backup Database Script

This Script Is what I use on all my database servers, its never failed and is extremely useful.

Just change the @PATH and the @BaseLine days as you see fit, paste the code into a job and run it everyday at midnight or what ever time your backups tends to run.

TFS 2010 Windows sharepoint Services Search

You type a search in the text box and hit search, you are then greated with

“Your search cannot be completed because this site is not assigned to an indexer. Contact your administrator for more information.”

You have TFS 2010 installed and everything is working, apart from the Search Services in the sharepoint portals

You try to manually start the service in the windows services console but it fails

You have followed microsoft instructions to enable and start the service but you cannot see the search service in central administration > Operations > services on server

Easily Fixed

You just need to do a repair of your WSS 3 install through programs and features. there is a catch though. When you try to perform a repair you recieve another error

“Microsoft Windows SharePoint Services 3.0 1033 Lang Pack – Error 1706th An installation package for the product Microsoft Windows SharePoint Services 3.0 1033 Lang Pack can not be found. Try the installation again using a valid copy of the installation package ‘wssmui.msi’.”

From Microsoft Download the Service Pack for Windows SharePoint Services Language Pack from here 64bit or here 32bit and install.

Now perform your repair.

Now browse to Central administration > operations > services on server and you will see as if by magic the search service is now available to configure. Click the start.

Configure Windows SharePoint Services Search Service Settings on server

Service Account: can be a local account \ or can be a domain account \
Content Access Account: can also be a local or domain account
Search Database: this is created automatically

NOTE- I suggest Using the account that you configured for the TFS to use when you were installing TFS 2010, this is because it needs full access to the SQL server and should NOT need to be an admin on the machine or domain. If you use the same account it also keep maintenance easy

Final Step – Add Search to an application database

  • goto central administration > application management > manage > content databases
  • Select the application database (on a fresh TFS instal it should be “WSS_Content”)
  • Search Server – select the only item in the drop down list (should be the server name)