Thursday, October 24, 2013

My Windows 8.1 Post Install Tips

After updating my Surface RT device and laptop to Windows 8.1 I thought I would share my experience. I will note that with both I chose to do a clean install.

Overall Experience
Firstly my overall experience has been exceptional. For the Surface RT install I simply accessed the update from the store using the direct link, took a bit for the update to start to download but I believe that was the Store app in 8.1 Preview causing that, between 1 to 2 hours later I was running Windows 8.1. Signed in with my Microsoft Account and all my settings were sync'd, then installed my apps from the store which was easy because of the new view which can filter to show only "your apps not installed on this pc".

For my laptop it was another painless install. Probably enhanced because of the deployment method adopted by Microsoft IT but still it was exceptional to see what can be achieved when deployment is managed in an efficient method. Anyway 1 hour later I was running Windows 8.1 and restoring my documents from my backup (for all the fun that went with that see my other post).

My Post Install Recommendations
These are my tips to complete once you have upgraded/installed Windows 8.1
  1. Link your Microsoft Account with your domain/local account if you didn't sign in with that account. This will give you the full experience with synchronization across your devices.
  2. Set PowerShell as the default for the Win+X menu over Command Prompt. If you are like me a frequent user of PowerShell then this will save plenty of time. And if you aren't using PowerShell yet, well get with the times you can still use native windows commands the same as you would in the command prompt.
    The following is where this setting is controlled in the TaskBar Properties

    Then this is the result, on the left is the default view, on the right is the view with PowerShell. This mention is accessed either by right clicking the Windows Logo in the bottom left corner or pressing Windows Key (Win) + X.
  3. Adjust the Power Settings for what the power buttons do. I like to take advantage of the fast hibernate feature in Windows 8 as I am often on the road, so I pick that over Sleep for what the power button does.

I will continue to add to this blog as I find more recommendations and tips.

Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Windows 7 File Recovery feature removed from Windows 8.1

As you "should" be aware keeping a backup of your important documents and files is critical to managing a PC, Server, Device. For me I have achieved this through the Windows 7 File Recovery feature in both Windows 7 and Windows 8 with an external hard drive or network storage as the backup destination.

In Windows 8 the File History feature was brought in to replace the Windows 7 File Recovery (aka Backup and Restore) feature. Well in Windows 8.1 this feature has now been removed.

So if like me you have used that feature to perform your pre-upgrade backup with the plan to then restore the files to your new Windows 8.1 system, then sadly it is not as simple as it use to be. However it is possible and here are the steps that I performed.

  1. Create a VM on your computer using the Hyper-V feature and install the Windows 7 or Windows 8 OS (but not 8.1)
    Alternatively if you have a Windows 7 or Windows 8 computer just use that. I was fortunate that I had a Windows 7 VM already built and just imported that into my laptop.
  2. If the backup is on an External HDD then attached the External Drive containing the backup to the computer. If using a Windows 7 VM then you will need to set the drive to offline before you can attach it to the VM.
  3. Start the VM (if required)
  4. Open "Backup and Recovery" on Windows 7 or "Windows 7 File Recovery" on Windows 8.
  5. Use the wizard to restore files from an existing backup and locate the backup on your external drive or network location.
  6. Select the files/folders that you wish to recover
  7. Now the important part, set the restore location to a sub folder on the same External HDD (ensure you have enough space). If you aren't using a VM you might be able to restore directly to your new Windows 8.1 computer over the network but I was using a VM without a network adapter attached.
  8. Once the restore completes, detach the external drive from the VM or other computer. Shut down the VM as it is no longer required (if appropriate)
  9. Attach the External HDD to the Windows 8.1 computer and manually copy all the files to the required locations.
  10. Finally setup Windows File History so you have backups of your documents again in a format that is supported by Windows 8.1 and easily recover files in the future.


Of cause if you are using the Windows 8 File History feature then ignore all of this as that feature is still current in Windows 8.1 and I will be updating all of my backups to use that from here on out :)

Monday, October 21, 2013

Tips for securing xp_cmdshell (when you really really have to use it)

If you have ever read anything about securing SQL server one of the most common threats that is called out is the use of xp_cmdshell. While it is generally accepted that this is a bad thing to enable within a production environment, there are some ligament cases where business, application, or probably more accurately legacy processes, require the use of xp_cmdshell. When we do enable this feature it is important to ensure that security is strictly locked down to prevent unwanted access to sensitive areas on the server or greater still, malicious actions on local and network components.

I was recently asked what my recommendations would be for securely implementing xp_cmdshell, and in my experience here are the steps to perform this:

Step 1
By default xp_cmdshell will execute under the context of the SQL Server service account, therefore the first step in reducing any risk is ensure that the account used for the SQL Server service is aligned to best practises. Such recommendations include the use of an account which:
    - Is not a local or domain administrator
    - Is a domain account where network resources may be required by aspects of the SQL environment (e.g. copying backup files to a network path)
    - Has minimal local and domain privileges
    - Has been configured using the SQL Server Configuration Manager

To completely secure the environment a separate account should also be used for the SQL Agent service with the same recommendations.

Step 2
Ensure that only the required users are members of the SQL Server SysAdmin server level role. Any members of this role will be able to execute xp_cmdshell and therefore allowed access to all aspects of the server that the SQL Service Account can access.

Step 3
Create a xp_cmdshell proxy account following the instructions at http://technet.microsoft.com/en-us/library/ms175046.aspx
This proxy account should be a unique domain user and separate to the SQL Server Service Account. It will be used when non-sysadmin SQL Logins execute xp_cmdshell and therefore an even restricted Access Control List (ACL) can be configured on the SQL Server and Network resources for that specific account. This will assist in significantly reducing the footprint area which is vulnerable to threat by xp_cmdshell.

Step 4
Grant the required permissions to specific non-sysadmin SQL Logins who require the ability to execute xp_cmdshell using the syntax: GRANT exec ON xp_cmdshell TO ''
This will ensure that only the SQL Logins which you have configured as either members of the SysAdmins built-in role, or explicitly granted execute permissions will be able to access the system via xp_cmdshell.
To view which SQL Logins have been granted permissions for xp_cmdshell run the following TSQL:












USE master;
GO
SELECT sys.schemas.name AS [schema_name]
, AllObjects.name AS [object_name]
, sys.database_permissions.permission_name
, sys.database_permissions.state_desc
, sys.database_principals.name AS [granted_to_principal_name]
FROM sys.database_permissions
INNER JOIN sys.database_principals ON sys.database_principals.principal_id = sys.database_permissions.grantee_principal_id
INNER JOIN (
        SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
        FROM sys.objects
        UNION
        SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
        FROM sys.system_objects
) AllObjects ON AllObjects.object_id = sys.database_permissions.major_id
LEFT JOIN sys.schemas ON sys.schemas.schema_id = AllObjects.schema_id
WHERE sys.schemas.name = 'sys'
AND AllObjects.name = 'xp_cmdshell'
ORDER BY sys.schemas.name
, AllObjects.name
, sys.database_principals.name
, sys.database_permissions.class;

 
Final Step
Start reviewing the processes, code, and reasons for using xp_cmdshell to determine if a more secure method could be used (e.g. PowerShell) to achieve the same outcome. Some might argue this should be the first step, but lets be realistic, you cannot always change an applications behavior especially if it is provided by a 3rd party so while this step definitely needs to be performed it may be the most difficult and longest of them all.



Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.