I am very fortunate to always have the latest versions of Microsoft stuff to play with such as SQL Server 2008 R2, but occasionally something doesn’t work, and so I need to tell the relevant product team what the problem is.

In this case I was trying to get the SQL Server 2008 R2 November CTP to run with the new Managed Service Accounts in Windows Server 2008 R2 Active Directory.  They are a bit like the NTAUTHORITY/LocalService account in that they don’t have passwords that you need to specify to the service, however they are a totally different beast in that they are domain accounts  designed to be used for services, like IIS 7.5. Essentially the passwords are managed internally by active directory and are designed for use with Kerberos. For more on this have a look at this article on TechNet 

However they are so new there is no way you can create one without using PowerShell (with the active directory module loaded)..

New-ADServiceAccount SQLService

Then you tell AD where you want to use the account on the server SQL08R2Demo..

Add-ADComputerServiceAccount -Identity SQL08R2DEMO -ServiceAccount SQLService

finally you deploy it to the computer that will use it

Install-ADServiceAccount -Identity SQLService

Now you can see this account in Active Directory under the new managed service account folder..

I can now use an account like this to run IIS by going to services and running IIS with the new account (note you don’t supply a password for this account).

However when Iu try and use it to run a SQL Server service, it doesn’t show up in the SQL Server configuration tool as it doesn’t see this kind of account at all even though you can search the managed service accounts folder.  Of course if I go into services proper I can change SQL Server services to use it…

but that is not supported, as it will foul up the permissions needed to do things like backup. BTW I didn’t supply the dollar sign suffix

so I have found something wrong and now I need to tell someone to fix it and the place where you do that for Microsoft stuff , irrespective of whether or not you work for them is on Connect (http://www.connect.microsoft.com).

You register suggestions and bugs by first searching to see if it’s there already and then you can create your own..

Here’s that bug, and notice that no one’s voted on it yet.  Votes are primarily there to influence feature requests, but bear in mind that what you or I consider to be a bug, maybe there by design and so could in fact really be a feature request. Anyway the more votes there are coupled with the severity of the problem the more likely it will be fixed

So if you think something needs to be changed then use the site as the product teams are pretty good but are not known for their telepathic abilities.

In the meantime I hope that this gets fixed for the release of SQL Server 2008 R2.

Update 28 November 2011

Managed Service Accounts didn't get supported in SQL Server 2008 R2, but are a feature in SQL Server 2012 as per this TechNet article.  The method is exactly the same

With the rise of the virtual machine, the quick creation of a sandbox in which developers and testers can work without fear of compromising the production environment becomes a much simpler exercise. You will see built in support for this lab management in the ultimate edition of Visual Studio Team System (VSTS) through the use of System Center Virtual Machine Manager (SCVMM).

One of the problems posed by these sorts of operations is that while it is easy to make a copy of a machine , be it physical or virtual, it’s a bit more difficult to change its identity. While you can quickly change the name of a machine you need to sysprep it to change it’s unique Security Identifier (SID). SID’s are how active directory identifies individual machines so you can’t have two on the same domain even if they have different names.

This gets more annoying when you include SQL Server in the mix. If you rename a machine that’s running SQL Server or sysprep it, it will upset the SQL Server permissions service accounts etc.  There are workarounds for this but they are tricky and not supported.

However If you use the advanced installation options in

SQL Server 2008 R2 ..

.. you can prepare an image of SQL Server which will survive a sysprep and subsequent domain join.  One you have done that you can come back to this interface and complete the install.  That’s a very manual process so what’s useful is to script all of that.  I have a separate step by step cook book to show you how to do this.

I have also posted a video on TechNetEdge so you can watch me do it (I have compressed down the installation so it only lasts 10 minutes).

The real power of this process comes into its own when you use it with a deployment technology such as Windows Deployment Services (WDS), System Center Configuration Manager (SCCM) or SCVMM as both of these allow you to run scripts as part of the deployment process, thus fully automating it. This is a topic in it’s own right which I will cover later.

To summarise SQL Server 2008 R2 is now sysprep friendly and you can try this out by downloading the November CTP

Report Builder 3 the latest version of the end user reporting tool in SQL Server 2008 R2  has built in support for Bing Maps and you can try this for yourself in the November CTP.

To help you along I have posted a 9 minute screencast to TechNet Edge and published a step by step guide here.  BTW don’t worry if you haven’t got any spatial data to hand as I have included guidance on how to get some.

My example simply shows the attendance at a few SQL Server events in the UK that I have been to..

..and is actually very straightforward. So an easy way to impress your boss or users in the many organisations that are becoming interested in understanding where things are happening.

Addendum 27th November.

I have just found out that you don't need to license Bing Maps if you you use Reporting Services as I have done here, i.e. for either aerial or the road view.  However you would have to have a license if you used Bing Maps in any other way outside of reporting services for business use.

You can access the license agreement for Bing Maps through the map control when you add the Bing Maps layer on a report  

As a graphic designer and geek with a masters around cognitive psychology I am intrigued at how little use is made of the wonderful visualisations in many modern BI tools be they from Microsoft, Oracle IBM or SAP.

Ignoring this trend, I wanted to show a new visualisation tool in SQL Server 2008 R2 called SparkLines during my session on Report Builder 3 at SQL Bits, but my shuttle was having disk issues and I hadn’t fully got everything back before I was on (but thanks to Darren Green, Chris Testa- O’Neill and Rob Farley for trying).

Anyway my demos are back in business and here’s a SparkLine report I created earlier..

SparkLines being the mini graphics which you can also do in excel. BTW I have used various sub-types in the report above to show what you can do, but you would obviously stick to one in a report like this. 

I have put created a walkthrough and samples that you can follow so please have a go (it should only need half an hour at the very most).   

The best thing about working with SQL Server is working with the other people that use it.  The SQL server experts who do most for their colleagues in the community are recognised as MVPs, so not only do they know their stuff they are willing to share their knowledge.

Of course you could argue that all this furthers their careers and/or businesses and while this might be true up to a point I would counter this by suggesting that you buy SQL Server MVP Deep Dives 

For two simple reasons, it’s an excellent example of what MVP’s are capable of producing, and because they have donated all their royalties to the WarChild Charity

Thanks to Kimberley , Paul R, Paul N , Rob, Kalen, Greg and Adam for this. and thanks to Richard Siddaway in the UK for the PowerShell chapter and for letting me know the background.

If I have convinced you to look at server core there some things you need to know to get started.  There’s a simple step by step guide on the initial release of server core here, most of which still applies to server core in R2.

• First and foremost as part of Microsoft’s 3D security philosophy Windows Server 2008 R2 (and therefore core) is secure by default i.e. locked down so you can’t remotely manage it initially.

netsh advfirewall set currentprofile settings remotemanagement enable

Once you have done this you can then remotely manage the firewall to allow other traffic in, so you can then remotely manage the server. The step by step guide has all this detail and command lines for joining the a domain setting up dhcp and so on.


• Enable Powershell.   The reason you want to do this is called James O’Neill.  He’s taken the pain out of configuring server core with his configurator on codeplex.  But before you can use this you need to turn on PowerShell using the DISM (Deployment Image Servicing and Management) tool :

dism /online /enable-feature /featurename:MicrosoftWindowsPowerShell

You’ll also need to allow PowerShell to run your scripts (more secure by default stuff)

set execution-policy remotesigned

James’ stuff will now allow you to do pretty much anything in that step by step guide but more easily.  You might also want to install the .Net framework..

dism /online /enable-feature /featurename:NetFx3-ServerCore

If 32-bit support is needed:

dism /online /enable-feature /featurename:ServerCore-WOW64

and then

dism /online /enable-feature /featurename:NetFx3-ServerCore-WOW64

Note DISM is CASE SenSitiVE

You’ll need to do all this if you want to install SQL Server 2008 or R2 and then do a command line install along the lines of ..

Setup.exe /q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER

/SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<StrongPassword>"

/SQLSYSADMINACCOUNTS="<DomainName\UserName>" /AGTSVCACCOUNT="NT AUTHORITY\Network Service"

The details of this are on Books OnLine here, but as I have said before this is NOT supported.

Next post is how to setup and manage IIS 7.5 on Server Core.

There is so much stuff on Windows7 & 2008 R2 features out there, but not so much on IIS 7.5.  The other reason it slipped off my radar was that reporting services in SQL Server 2008 directly uses http.sys for its portal and there is no longer a need to install IIS at all.  Anyway it’s now time for me to look at this in more detail as part of my series on server core.

Firstly IIS 7.5 like every other new bit of the Microsoft platform has embedded PowerShell support, meaning you can now directly manage  IIS 7.5.  Also you turn on IIS7.5 features using DISM rather than the complexity of the setup in the original server core..

where a basic install would have been

start /w pkgmgr /iu:IIS-WebServerRole;WAS-WindowsActivationService;WAS-ProcessModel

using DISM the equivalent command is

DISM –online /enable-feature /featurename:IIS-WebServerRole

From there you can then add the extra individual features you need, as from IIS 7 it is a very modular solution. For example to turn on asp.net you would use to turn on three features..

DISM –online /enable-feature /featurename:IIS-ISAPI Filter

DISM –online /enable-feature /featurename:IIS-NetFxExtensibility

DISM –online /enable-feature /featurename:IIS-ASPNET

Having done all of this the best approach would then be to remotely manage it from another machine.  Form there you can start use some of the new stuff in IIS 7.5

My top feature would be the best practices analyser.  This is primarily concerned with making sure your configuration is as secure as possible,  e.g.

• Hiding custom errors from displaying remotely, the more the attacker knows about your site the quickest and easier it is to attack, for example one of mates always rubs his hands with glee when he says the site is talking to Oracle as opposed to SQL (he wouldn’t tell me why though).
• Ensuring ssl certificates are up to date
• Ensuring you are using ssl if you have basic authentication set.

For more on this refer to this article in the Windows Server TechCenter

Other new stuff includes

• Application pre-load, so your application is already running when the user access it. To do this you need to add the following to the applicationHost.config file:

<application path="/myapp" preloadEnabled="true">
<virtualDirectory path="/" physicalPath="c:\inetpub\myapp"/>
</application>

and this to the web.config file

<preload>
<add value="default.aspx"/>
</preload>

There’s an article here to get you started with this

• Configuration Log Tracking.  This pulls in all the changes you make and logs them. BTW this is off by default and you need to turn it by going to  “Application and Service Logs->Microsoft->Windows->IIS-Configuration” in event viewer and enable tracing. BTW  IIS generates 4 kinds of events. These are administrative, operational, analytic and debug. Right click on areas in event viewer and select “enable log” for categories you want to enable tracing.
• The ability to work with multiple versions of the .net framework. While one application might need .net 2 another might need 3.5
• Built in FTP and WebDAV support.

I could go on and on here, but I would suggest you might want to check up on all the new stuff in IIS 7.5 here, and hopefully try it out by installing the role on a full or server core installation of windows server 2088 R2.  It’s also an opportunity to confuse the web developers you might be working with by understanding their world a little better, and offering to help them deploy their applications more quickly.

Last June I didi a series of interviews with Alun Rodgers of Risual about the cool stuff in Windows Server 2008. In one of these..

Alun is telling me about how Terminal Services (now known as Remote Desktop Services, RDS, in Windows Server 2008 R2) could be used to provide a secure desktop for police forces and the like.

This would mean that rather than having 2 separate systems on an officer’s desk, one for the classified network e.g. for Holmes (Home Office Large Major Enquiry System) and one for normal administrative duties, everything could be done over one network from one desktop. The upshot of this is that it saves a large pile of infrastructure, maintenance and running costs without compromising the integrity of any of the secure systems.

I did call this out again at the session that Jamie Burgess and I did on RDS at the Wembley tri-launch on 6th October. What I didn’t realise was that Alun had actually bought along Lancashire Police (the customer) to this event to talk about exactly this situation in the closing keynote session at the same event..

Ward Ralston with Colin Fizsimons & Michael Wood from Lancashire Police

If you couldn’t get there on the day, the videos and decks from that event can be found from these links..

• Read all of the Wembley presentations
• Watch sessions from the Global Virtual Launch Event

There is also a case study that has been published about the work that Risual has done for Lancashire Constabulary.

Footnote: Alun is the AL bit of Risual,  the others being RIchard Proud & SUe Denton ;they are a small but influential system integration partner.

James and I are on our way to TechEd Berlin, but we’re driving because we’re both a little bit eccentric.  Not only that we have a bit of stuff to take..

four laptops, a shuttle, a hub assorted drives, cables so that we can demo a bunch of interesting stuff in our session.

We’ll be showing as many of the following as we can cram in 75 minutes:

• Our SQL Server on Server Core (but not supported) cookbook
• Using the advanced installation options in SQL Server 2008 R2 to prepare a virtual machine which can be quickly bought on line
• Live Migration in Hyper-V2 to move a SQL Server virtual machine from Wallace-R2 to Grommit-R2, the 2 laptops in the picture above.
• Managing these virtual machines in the latest version of System Center Virtual Machine Manager (SCVMM).
• Using the enterprise policy management framework from codeplex to ensure all the SQL Servers above are configured the way we want them.

All of this will be done from the command line / powershell, and the only thing we can’t share with you is our internal iSCSI target software which so you’ll need something like StarWind or iSCSI Cake.

If you’re going to be there then the session is DAT01-IS on Monday afternoon, but if not then James and I will be making a series of 5-10 minute videos from each demo, when we get back as IS sessions aren’t recorded.

I have been at TechEd Berlin last week and when I wasn’t speaking I spent most of the time in the Learning Centre (TLC) or ask the experts. I must confess I felt a bit of a non-expert this year for two reasons;

• I have been spending time getting up to speed on the new Windows releases
• There were some real experts on the stand..

In the snap below we have Bob Beachemin from SQL Skills, Gopal Ashok and Torsten Grabs from the SQL product team in Redmond. Gopal is mr Replication and HA, while Torsten is mr StreamInsight. 

Joanne is also from Redmond while Roger is from Microsoft in Switzerland. On other days we had Omri Bahat (DBEngine), Michael Rys (XML and Spatial) and Donald Farmer (BI and specifically PowerPivot).

AJF 17/11/2009  I forgot to mention the other product superstar on the stand, Chris Baldwin who is Reporting Services.

So going to TechEd isn’t just about going to sessions it’s an opportunity to share experiences with the guys who develop the product as well as having time to do a few labs..

However it’s a tough world out there and so getting your manager to agree to letting you go was hard (however 7,300 managed it this year), so the next best thing if you’re interested in SQL Server is SQL Bits this Thursday – Saturday.

Donald farmer is presenting at this event on the Friday, and in addition there will be a whole crop of MVP’s there and actually these guys have more influence with the product team than I do.

The Saturday community day is free, while the Friday is a paid for event focusing on SQL Server 2008 and R2. Either of these would be my top tip if you need to be looking for your next career move, as the contacts you meet like the MVP’s are often aware of vacancies through the projects they are engaged on.

So I’ll hopefully see you in Cardiff!  

I promise this is the last time I mention SQL Bits this week, but I wanted to let you know I have got 25 free tickets for the paid for event this Friday which would normally cost £149 to attend. 

The venue is the Celtic Manor in Newport…

and is probably the next best thing to going to TechEd. There are four tracks;

• Data warehousing,
• Database,
• BI,
• Development,

specifically on SQL Server 2008 and R2, with an all star cast headed by Donald Farmer, and Thomas Kejser, the details of which are here.

If you can make the date and want to come please e-mail me (afryer@microsoft.com) with SQLBits in the title and I’ll get the tickets to the first 25 to respond.