Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

April, 2010

  • Windows 7 x64 proving that you can have fun and be secure

    The latest Security Intelligence Report SIRv8 has just landed with a thud in my in box, (you can get yours here).  I am not going to regurgitate it wholesale but I wanted to draw your attention to a couple of things..


    Windows x64 RTM is the most secure OS in this list closely followed by Windows Server 2008 R2.  This isn’t surprising as they share the same kernel and remember this version of Windows Server is only x64 so all the threat management that goes into the server OS bubbles up in Windows 7.  You may remember forma previous post of mine that Windows 7 x64 is rapidly becoming the gamers platform of choice, so it isn’t a niche offering and should become more popular now that Office 2010 is also available in x64.

    Another point in this report is that “breach incidents are twice as likely to occur because of human negligence. Sometimes security policy makes things worse, like the government department where my best friend works which has an arcane encryption system that generates a random password which has to be used at every reboot. The only way to remember this is to write it done so #fail on that one.  Things like BitLocker in windows 7 mean that if you do leave pen drive or a whole laptop on a train at least the data won’t be compromised, but of course civil servants love paper and that is far more easily compromised e.g. Bob Quick’s counter terrorism error which is why I think the printer is the major security risk in any IT organisation!

    Allied to this is the growing sophistication of malware particularly those actually masquerading as ant-malware solutions like these..


    Hopefully in your role as the neighbourhood IT Pro you are already aware of these and how to deal with them, but if you need a starter for ten then there is the Protect consumer orientated security site from Microsoft..


    My final point is that threats are now coming from a mix organised crime and state sponsored attacks which means big investments and smart people are behind these attacks, so we all have to be even more vigilant just to keep up.

    So have a read of the report even if’s just the overview and be safe out there!

  • Data Governance with SQL Server

    I thought it would be good to write up my talk at the IT Manager TechDays last week, as I get a fair number of questions and the information is not always easy to find. First let me explain what I mean by Governance: It is the policies, best practices, enforcement and training needed to address the compliance requirements of a business as part of its risk management strategy.

    This then leads down to a set of things you decide you need to do to be compliant. You then have a list of features in products like SQL Server which might be useful in assisting in these processes. My top features of interest in SQL Server 2008 would be:

    • Policy Based Management. This allows a DBA to test and possibly ensure that a given set of servers is in compliance with a given set of criteria e.g. compatibility level, password strength and so on. This is especially powerful when the policies are run by automated scheduled (SQL Agent jobs running PowerShell) scripts which pump the result into a SQL database which can then be consumed by Reporting Services reports. The Enterprise Policy Management Framework on CodePlex is an example of how to do this. BTW Policies can also be fired at earlier version of SQL Server (provided you have at least one installation of SQL Server 2008)
    • Transparent Database Encryption (TDE) protects the database at rest. This prevents it being copied to another location without the corresponding certificate. This doesn’t affect access to it or require any changes to applications accessing when it is in its normal location. Note this is not a replacement to, but rather compliments the encryption of cells introduced in SQL Server 2005.
    • Extensible Key management simply allows you to use your third party key management software in SQL Server, so you only have one set of keys and one program to manage them.
    • SQL Audit might not stop your data being leaked but it will show you the who , how and when without needing to be a DBA. In fact not needing to be a DBA is important as an auditor needs to be able to see what the DBA is doing. Auditing everything would create a lot of noise and might overly affect performance so the Audit UI allows fine grain control of this. Finally the audit log might need to be secure so SQL Audit can write directly to the security log.

    Of course securing and auditing SQL Server itself is only part of the story:

    • The operating system needs to be up to date and patched
    • SQL Server does not itself encrypt network traffic so you might need to
    • Securing your backup using third party encryption or TDE is important

    However how does that all that fit into the regulations and compliance requirements that exist such as European Data Protection, ISO 27001, Payment Card Industry? A key point is that Microsoft itself has to be compliant with all these standards – it holds customer data, it has mechanisms like XBOX live and BT Vision that have to process credit card payments. Moreover this all runs on SQL Server, so where possible Microsoft IT can simply share its approach to compliance, where this might be difficult for the Microsoft consultants working on clients’ projects to do.

    Your first port of call should be the whitepaper reaching compliance.  This includes sections on addressing vulnerability, defining risk management models, and managing security configurations.  There are also links to hands on labs to put help you with the features I have described above.

    There is also Microsoft’s Data Governance Portal, which includes a number of solution accelerators such as one specifically for Payment Card Industry compliance, which I mention because I get asked about it so often!

    Hopefully this will give you all the resources you need to meet your compliance needs, and give you the ammunition to debunk a few myths that may exist in your organisation leaving you to get on with the relatively painless task of configuring SQL Server for your compliance needs. If you have questions about compliance please feel free to contact me – I will try my best to get you the answer you need.

    My parting shot is that compliance is a process and not a product.

  • IT Process Automation for Microsoft System Center

    a guest post by Greg Charman, Technical Solutions Professional, Microsoft UK

    In December 2009 Microsoft acquired Opalis, a specialist provider of IT Process Automation (ITPA) software.   The Opalis product is currently in the process of being fully integrated into the System Center family of datacenter management products.

    IT Process Automation (ITPA), also known as Run Book Automation (RBA) software, provides a platform to design and run IT processes. Standardising the IT processes that underpin IT services means best practices can be deployed across the environment, regardless of the underlying management infrastructure. This is achieved by orchestrating and integrating the existing IT tools.

    Traditional IT tools support the functions of one particular IT silo, sometimes offering automation of tasks within that silo function. In reality, IT business processes cross multiple IT silos. Currently the bridge between process silos is provided by human intervention and this increases the propensity for delays, errors and incorrect rekeying of data. Opalis enables the integration and orchestration of different IT process automation tools in each of the silos to support an entire end-to-end IT business process.

    Most organisations operate heterogeneous datacentres. As a part of the System Center portfolio, Opalis workflow processes orchestrate System Center products and integrates them with non-Microsoft systems to enable interoperability across an entire datacentre. Opalis provides solutions that address the systems management needs of complex heterogeneous datacentre environments and has developed integration packs to management software from Microsoft, IBM, BMC, CA, HP, VMware, EMC, and Symantec. This enables users to automate best practices such as incident triage and remediation, service provisioning and change management process, and achieve interoperability across tools.

    The combined offering of Opalis and System Center provides the ability to orchestrate and integrate IT management through workflow and simplifies routine systems management tasks in complex heterogeneous environments by:

    • Defining and orchestrating processes across all System Center products
    • Integrating and orchestrating non-Microsoft tools as part of a complete workflow
    • Engaging with System Center Service Manager to automate the human workflow elements

    The combination of the new capabilities from System Center in 2010, such as Service Manager and Dynamic Infrastructure Toolkit for System Center (DIT-SC), with Opalis and the rest of System Center suite enables Microsoft to deliver a comprehensive suite of system management tools for “Infrastructure on Demand” requirements in heterogeneous environments.

    There is more information on Opalis + System Center at the links below and a technology roadmap fully integrating Opalis as part of the Microsoft System Center portfolio will be available shortly to clarify how System Center is becoming an increasingly powerful systems management platform for heterogeneous datacentre environments

    More information on Opalis and System Center:

    Opalis + System Center IT Process Automation example procedure

    Imagine a user has a requirement for a new virtual server which will host a business application:

    • First the user goes to the DIT-SC catalogue front end and selects a virtual machine template from the available options and requests which application must be installed on the machine and how much data storage is required.
    • Opalis picks up this request and follows the appropriate ITIL process to: Create a New Change Request Ticket in Service Manager to record this new provision request.
    • Opalis then queries Virtual Machine Manager to confirm if sufficient capacity is available to service this request. If insufficient capacity exists Opalis goes to the blade server infrastructure in the Datacentre and turns on spare blades in the Blade Rack and informs Virtual Machine Manager it has new Physical Servers as part of its cluster.
    • Opalis then checks the Storage infrastructure and determines that capacity is available and allocates a new storage area to service this provision request.
    • Opalis orchestrates Virtual Machine Manager to create a new virtual machine for this request.
    • Opalis adds this Virtual Machine to the Operations Manager estate so the machine is immediately under management.
    • Opalis orchestrates Configuration Manager to deploy the Patches, Antivirus and Business App requested to this new virtual machine.
    • Opalis orchestrates System Center Data Protection Manager to backup the new virtual machine.
    • Opalis then populates the CMDB in Service Manager with the details of the new machine, and closes the Change Request.
    • Opalis then updates DIT-SC to inform the user their request has been fulfilled and machine is now ready for use.
    • A fully automated request for provision of new infrastructure has been achieved with no human intervention required.
  • Microsoft Business Intelligence without SharePoint

    This might be a strange topic given that the release of SharePoint 2010 is just around the corner, but I wanted to respond to a couple of questions and debates I have been involved with in my temporary role in the partner team.

    I would like to start by going back to the early days of Microsoft BI, analysis services (and OLAP services before it) were just getting established but there was no analysis services client from Microsoft to give users full access to the cubes in analysis services.  There were two third party contenders out there Panorama NovaView and ProClarity.  These were both web clients and as they evolved they both developed portals form which reports and content could be accessed and changed.  They also had security baked into them on top of what was in analysis services itself.  ProClarity was acquired by Microsoft and the descendants visualisations in that tool (such as the decomposition tree) are now in SharePoint 2010 enterprise.

    Panorama continues to be a Microsoft partner and is a good choice for business who just want a web client to get at their analysis services cubes, or even to get more functionality out of PowerPivot.  They also continue to have their own portal or can integrate with SharePoint as desired.  There also another 40+ products form partners out there which also provide web access to cubes, and here is a good a list as I’ve found of them

    The other key Microsoft BI offering is reporting services which either has its own portal complete with security (Report Manager) or can be integrated into SharePoint.

    So you don’t have to use SharePoint to provide Microsoft based BI to your users, but I would submit you are going to need some sort of portal, even if this is just a set of web pages where users can see content they are allowed to see and add more content to it (again subject to security).

    However if you want to provide access to reporting and analytics to your users then SharePoint or a similar dedicated portal would be a better option as you can then provide a single point of entry and a single security mechanism to control access to BI.

    On the question of cost you could just use SharePoint Foundation (the successor to Windows SharePoint Services), and SQL Server Standard edition, this would give you reporting services integration, and the other key parts of the Microsoft BI stack (analysis services and integration services). However you won’t get the performance point monitoring and analytics (which is in SharePoint enterprise) so the money you save by doing this must be off set against the need to buy a third party tool (like those I have listed above) to replace this functionality.  You might not actually save any money and the solution could be more complex, but it might be exactly what your users need.  This is a good thing, both for business and for Microsoft:

    • Its good for business as you have a choice in which BI client tools you want to use and you can choose how locked into SharePoint you want to be.
    • It’s good for Microsoft as this ecosystem helps put Microsoft at the forefront of BI vendors and offers a wider set of capabilities than Microsoft alone can provide.

    What I would suggest is to at least have a look at SharePoint 2010 as it’s scarily easy to set up and use even for this old DBA.