Ozzie.eu

While securing a Microsoft SQL Server instance, there are many issues you should look after. According to the official documentation, it can be viewed as a series of steps, involving four areas: the platform, authentication, objects (including data), and applications that access the system. On environments where SQL Server is used on a more “Swiss Army Knife” fashion, there is often difficulty on disabling or, at least, restricting access to the general extended stored procedures, like xp_cmdshell. These stored procedures provide an interface from an instance of SQL Server to external programs for various maintenance activities. But they also represent a security liability. The alternative suggested is if possible to use SQL CLR Integration to perform these tasks. It can be done and it works, but maybe it isn’t a desired task by the regular DBA to learn to program a .NET Framework language.
With this in mind, as far as SQL Server Agent jobs are concerned, I’d suggest creating a Powershell job step:

Bear in mind this requires a specific kind of error handling. By default the ErrorActionPreference is set to Continue, and this has implications on how errors bubble up to the SQL Server Job Server. If you run a Windows PowerShell command as a SQL Server Agent job and there are no syntax errors but the command produces an error, the SQL Server Agent job will report success. If you want an error condition to halt execution of a SQL Server Agent job or to produce an error, you'll need to add some error handling. To bubble up Windows PowerShell errors to SQL Server Agent, you'll need to set your $ErrorActionPreference = "Stop". You can check that out here.
For the processing task which are a hybrid of data manipulation and other external system related tasks I’d suggest the development of Powershell scripts that use SQL Server Management Objects (SMO) which is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.
Sure, you might say that this suggestion doesn’t help from learning to code, but this is an inevitable goal every SQL Server DBA must have as the future might have projects where the projects might have a server core platform. What then?
So why not kill two rabbits with one stone and learn your Powershell and SMO, it quite seems  the way to go. For starters, this might help.

By coincidence, as I'm writing this post, this editorial from SQLServer Central came out. What are the odds of that?

Happy coding!


Photo Credit: baboon™ via Compfight cc

While installing a third party software on my laptop running Microsoft Windows 8, the operating system informed me that it required to download and install the .NET Framework 3.5. After my consent, it failed to download the package to install. I thought nothing of it and left the task for later.

However, this began to be a recurring episode. Even the very .NET Framework setup program would fail. While installing Windows Live Essentials (I really love Live Writer), the error happened again:

The error code 0x800F0906 wasn’t something I knew about and the support link is an awful article which suggest an over the top work around.

So I tried some common sense and analogy. If you’re used to administering Windows 2008 Servers you should know that the Microsoft .NET 3.5 Framework is installed from the feature selection on the add/remove role operation for the Application Server. Windows 7 itself has features that are essential for system administration like Telnet, and yet you have to add them manually.

With these aspects in mind I went to the Windows 8 Start screen and typed “Features”. Afterwards I selected “Settings” in the search bar and the desired option appeared, called “Programs and Features”:

From the list, you just choose the .NET Framework 3.5 and choose to download from Windows Update:

Now just wait for the installation to complete:

I was all set to go. You should be too .

Microsoft SQL Server comes with a great tool for database administrators and developers alike, called Management Studio. SQL Server Management Studio is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SQL Server Management Studio combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server to developers and administrators of all skill levels.
But like most database management tools, it lacks a more automated support for some recurrent tasks that should be easier to perform.A developer will certainly wish for automatic generation of CRUD statements for a given database table. And a database administrator would surely like to run a maintenance script on all databases where it should be executed, all servers at once.
If these kind of enhancements are on your wishlist, be sure to check out the SSMS Tool pack. It’s a free add-in for SQL Server Management Studio with a great list of enhancements:

• Execution Plan Analyzer
• SQL Snippets
• Window Connection Coloring
• Tab Sessions, Window Content History, Query Execution History and Current Window History
• Format SQL
• Search Table, View or Database Data
• Run one script on multiple databases
• Copy execution plan bitmaps to clipboard or file
• Search Results in Grid Mode
• Generate Insert statements from resultsets, tables or databases
• Regions and Debug sections
• Running custom scripts from Object Explorer
• CRUD (Create, Read, Update, Delete) stored procedure generation
• New query template

You can download it from here.

Windows Server 2012is designed for the cloud from the ground up and provides a foundation for building both public and private cloud solutions to enable businesses to take advantage of the many benefits of cloud computing. Keeping that in mind, this book states that Windows Server 2012 is probably the most significant release of the Windows Server platform ever.
This book is presented as a “first look” based on the public beta release of Windows Server 2012 and is intended to help IT professionals familiarize themselves with the capabilities of the new platform. Below you can find the table of contents on this book:

Table of Contents

Chapter 1 The business need for Windows Server 2012 
The rationale behind cloud computing Making the transition
Technical requirements for successful cloud computing
Four ways Windows Server 2012 delivers value for cloud computing Foundation for building your private cloud 

Chapter 2 Foundation for building your private cloud 
A complete virtualization platform
Increase scalability and performance Business continuity for virtualized workloads 

Chapter 3 Highly available, easy-to-manage multi-server platform
Continuous availability
Cost efficiency
Management efficiency  

Chapter 4 Deploy web applications on premises and in the cloud 
 Scalable and elastic web platform
Support for open standards  

Chapter 5 Enabling the modern workstyle 
Access virtually anywhere, from any device
Full Windows experience
Enhanced security and compliance

The book can be downloaded in various formats from this official MSDN web page.

Happy reading!

Trying out Umbraco to get a feel on the ease of use and potential professional use, I got the following error when trying to start the site based on the simple starter kit :

 This was disappointing, especially because we're talking about a fresh install and I hadn't fiddled with it yet.
After some scouring on the Web taking into account Umbraco was complaining about the umbraco.cms.businesslogic.macro.MacroModel, I found the solution was to edit the starter kit template so it wouldn't use macros that were not installed. So, first step, go to the Umbraco administration site and navigate to the "Developer" screen:

Expand the "Macros" node on the tree and check what macros you've got installed and take note of their aliases:
Next navigate to the "Settings" screen:
Then choose templates and the "StarterKit Master" template:
In the editor, search for any macro which has an alias that doesn't match any of the currently installed. In my case it was the "BlogFeedRssLink":
After removing this line and saving the template, the site worked fine.

Given the task to create an external list on a Sharepoint 2010 site, the general steps to follow are the following:

• Create the external Content Type
• Create the content type CRUD operations
• Define the identifier field
• Define a list length threshold
• Create the associated list and forms
• Set the meta data store permissions and object permissions according to planned

A step by step example can be found on this post. However, having things done, I faced the following error when trying to view the list on the site:

This resulted from the fact that apparently the Business Data Connectivity Service wasn't passing the current user's  credentials to access the external data, although  the “Connect with User’s Identity” had been selected on the external content type creation.
Not being able to solve this issue, I tried to configure the data source to use the credential running the BCS service which had full access to the data. When trying to save the object on Sharepoint Designer, I got the following error:



To make the desired change, you should run the following commands on the Sharepoint Administration Powershell prompt:

PS C:\Users\SPSadmin> Get-SPServiceApplication

DisplayName          TypeName             Id
-----------          --------             --
...
User Profile Serv... User Profile Serv... 2303b602-0d87-46b8-a684-e6a884dd1071
...

PS C:\Users\SPSadmin> $bdc = Get-SPServiceApplication -id 2303b602-0d87-46b8-a684-e6a884dd1071
PS C:\Users\SPSadmin> $bdc.RevertToSelfAllowed = $true
PS C:\Users\SPSadmin> $bdc.Update()

Afterwards, restart the Business Data Connectivity Service and the list should be working fine.
For this and other Sharepoint 2010 issues I strongly recommend reading Professional SharePoint 2010 Administration.

Using Microsoft SQL Server, found this to be handy sometimes:

USE [your database name]
GO

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%given column name%'
ORDER BY schema_name, table_name;

HTH.

Using corporate servers on a security aware organization implies that the IT governance policies enforce a network distributed and update antivirus software.
The bad news are that if a fine tuning and folder rule out policy is not in place, the database servers and application server will have their performance seriously compromised. The On-access scan feature will perform its own DoS attack on the hosts.
According to the server role, the adequate folder need to be ruled out from the malware scanning engine.On this specific post, you can find a lead on what folders should be ruled out on a Microsoft Team Foundation Server.
On the database server (MS-SQL) you should obviously rule out the path to the data volumes (.mdf and .ldf files) supporting the TFS.
On the actual TFS server, the following folder should be ruled out:

• C:\Program Files\Microsoft Team Foundation Server 2010

Most frequently when deploying new scenarios, the task of network connectivity validation has to be performed.
Commonly this is performed using a command line and issuing a “telnet” connection to the desired host on a given port. This is nice when you have a single machine with a couple of connections to be tested.
When the scenario involves tens of machines with different network connection requisites between each other, the telnet procedure is a very slow process, being the typical test cycle:

1. Open command prompt.
2. Issue telnet to a given host and port.
3. On success, close window and return to step 1.
4. On failure, wait for timeout.
5. Authorize the network port that failed
6. Repeat step 2.
7. On success go to step 1.

This isn’t a time effective test cycle. The ideal test cycle would be:

1. Open command prompt.
2. Issue command or script to test all destination hosts on desired ports.
3. Check status and authorize failed network traffic.
4. Repeat step 2 and check for success.

The two most annoying things about telnet are that you need to close and open a new window on success and you have to wait for timeout on failure.
To surpass these annoyances, one can use this Powershell function “Test-Port”, available from the Microsoft Technet Script Center Repository.
The syntax of the base usage is really simple. To test conecctivity to this site:

Test-port -computer blog.ozzie.eu -port 80

To address the fact of the ever growing shared network resources to be tested, an Excel spreadsheet can be used to generate the test battery script. Here’s an example you can view and download:





Afterwards, you just copy/paste the powershell column to a .ps1 file. I’ve also included a regular command prompt test column.


HTH.

After three and a half years, time came to update a personal project hosted on Codeplex. It’s called Windows Monitoring.
This project is meant to provide programmers with a set of reusable classes, gathered into one assembly, to develop custom system monitoring consoles for windows server systems.
The base set provides classes with methods to check:

• Network ping
• HTTP server running
• FTP server running
• Disk space on system hard drive
• Database availability

Open for evolution is the adittion of methods related to Mail Servers, such as Exchange, or Domain controllers.
I took the dust off the Codeplex account, logged in using Visual Studio 2010 together with Team Explorer and migrated the code from .NET 2.0 onto 4.0.
The update projects and source code can be browsed and downloaded. If you don’t have Visual Studio, but some other IDE, you can download the assembly on the recommended release and use the tester source code as a usage example.
Happy coding!

Given the task of migrating a Sharepoint 2010 web application, from quality assurance to the live environment, our team backed up the site using the Powershell  Cmdlet "Backup-SPSite". Afterwards, we copied the backup file to the live farm and executed the "Restore-SPSite" Cmdlet. The following error message appeared:

Restore-SPSite : Your backup is from a different version of

Microsoft SharePoint Foundation and cannot be restored to a 

server running the current version. The backup file should be 

restored to a server with version '4.0.145.0' or later.

After comparing the patch level on both farms, a missing security update was identified on the live farm. Having multiple applications hosted on the farm, the update roll-out was not an option.
So one of my colleagues found that the old school "stsadm" command allows the backup and restore of Sharepoint sites without checking the build number on the farm.
The solution was to use the following commands:

1. On the Q&A server, open a command prompt and enter "cd C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN>"
2. Execute the following command:

3. STSADM.EXE -o backup -url http://server/site 
   -filename backup.dat

4. Copy the resulting file to the live farm.
5. On the live farm, open a command prompt and enter "cd C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN>"
6. Execute the following command:

7. STSADM.EXE -o restore -url http://server/site -filename 
   backup.dat -overwrite

8. Open the desired URL and check for your site's correct update.

HTH.

Thanks to Nelson!

Installing a server instance of Microsoft's Sharepoint 2010 Server, I faced the following problem: the server had no Internet access and Sharepoint's setup relies on downloading the pre-requisites from Microsoft, through the public Web.

The solution is to download the pre-requisites software and copy them to the server. Here's a list for future reference:

• Microsoft SQL Server 2008 Native Client


• Microsoft Sync Framework Runtime v1.0


• Windows Identity Foundation 


• Microsoft Chart Controls for Microsoft .NET Framework 3.5


• Microsoft SQL Server 2008 Analysis Services ADOMD.NET


• Hotfix for Microsoft Windows - KB976462 (didn't need it, was already installed)

Besides the pre~requisites don't forget, if you're deploying a new server on a farm, to download and install:

Having a custom developed Web Service hosted on Windows 2008 Server, after some fine tuning on the system, the service started to respond with a SOAP fault stating "Requested registry access is not allowed".

The origin of the error was the attempt the service made to write on the Event Log.

One of the changes we had made to the application was the application pool identity. It was running on Classic mode with a given domain account and we re-configured it to run on integrated mode with the application pool identity.

To grant rights to a given user account for writing on the Event Log, you should perform the steps to edit the registry described here:

1. Find key "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog"


2. Right-click and choose "Permissions".


3. Put in the desired account

The catch is that the application pool is running with the Application Pool Identity Account. This is a Windows user account called "IIS APPPOOL\AppPoolName", which is created when the Application Pool is created, where AppPoolName is the name of the Application Pool.

On the permission dialog, search the local host for the "IIS APPPOOL\AppPoolName" replacing "AppPoolName" for your custom given name.

After that, the Web service worked fine.

Chrome is a really good browser and one of its highlights is the speed on starting up and rendering Web pages.

Having it installed on the corporate office workstation, I noticed it was much slower and taking forever to load some really simple stuff.

After some research on Google, I found and tested successfully the following scenario:

• The proxy server is automatically configured through network policies.


• Configuring the proxy server directly, without automatic configuration, restores Chrome to its full speed.

A previous post on this blog showed how posting a SOAP request from the command line on Linux could be done.

Following the post about Invoke-Web-Request, I'll show how to achieve the same task using Windows Powershell v3 CTP 1.

The first step is to build a sample SOAP request and save it on a text file, maybe called “soap.txt”. For the sake of this example I saved it on a temporary folder "C:\Temp"

Afterwards, open a powershell command prompt window and type this in:

Invoke-WebRequest http://[your web service endpoint address] -Method Post -ContentType "text/xml" -InFile C:\Temp\soap.txt -OutFile c:\Temp\soapRes.txt

The command has the following parameters:

• method: indicating the request should be sent using HTTP POST


• content type: stating the request is an XML message


• input file: the SOAP request text file


• output file: the name and path for saving the SOAP response

After execution, the resulting SOAP response is saved on the temporary folder under the name "soapresult.txt".

Cool!

The Windows Management Framework 3.0 - Community Technology Preview (CTP) has been released by Microsoft and with it comes Powershell V3.

Included on this release of powershell comes a command I've personally been missing for ages. A command to get or just test a given URL address. The is called:
Invoke-WebRequest



A simple example of the command usage would be the get this blog's home page into a single text file:

Invoke-WebRequest http://rambletech.wordpress.com/ -OutFile c:\temp\blog.txt

Another example, more complex, would be to get this blogs RSS feed and parse it:

([xml](Invoke-WebRequest http://rambletech.wordpress.com/rss).content).rss.channel.item | Select Title, Link

To download the Windows Management Framework 3.0 CTP 1 visit this address from Microsoft.

Happy programming :)

An underrated feature from Windows 7 is its ability to boot directly from a VHD file without the need for additional virtualization software.

The advantage is evident: you can have different Windows 7 deployments on your machine without resort to virtualization software, thus using the full potential of your hardware.

Being a developer, a recurring challenge is to have a development environment setup that doesn't permanently compromise the workstation's performance. The compromise is consequence of sometimes having database and application servers, as well as different IDE all installed and running services at the same time.

Usual solution to avoid such compromise are:

• having virtual machines, dedicated to each development scope, and using them individually without installing any development software on the host. However, virtualization can have a big cost on hardware performance.


• Remote desktop to a hosted development environment instance. This limits the environment's accessibility (usually to the workplace) and is not an option for small companies.

Given this options, the VHD boot architecture seems very attractive. the downside to this option is that its only available on the Ultimate and Enterprise versions of Windows.

To find a walk-through of common scenarios of native VHD boot, visit this technet's page.

Judging by the way things progress, it's will soon be possible to run Hyper-V on a Windows client OS system.

On one of my current projects, migrating a Web application from Windows Server 2003 to 2008, the previous version of the code was migrated from .NET 2.0 to version 4.0. The binaries were assembled into executable setup files for automatic installation and deploy of the web app.

While trying to run the setup file the following error appeared: "You do not have sufficient privileges to complete this installation for all users of the machine."

The current user logged on was a local administrator so all privileges were adequate. After a short googling, the root cause was identified: User Access Control. It's also present on Windows Server 2008 and enforces notification before any changes are made to the system.

The solution:

1. Go to Start > Control Panel


2. Select User Accounts


3. Select User Accounts (again!)


4. Select Change User Account Control settings


5. Change the security level to never notify


6. Restart the machine

After a successful installation you can always revert the settings to the more secure ones.

If you use Windows 7, create a new folder on your Desktop and name it "GodMode.{ED7BA470-8E54-465E-825C-99712043E01C}". Open it to see the numerous options it gives you: administrative tools, backup and restore options, security settings.

It's a nice feature to perform super user tasks.

When using distributed transactions between distinct databases, using Microsoft Windows technology, one relies on the MSDTC - Microsoft Distributed Transaction Coordinator.

The main catch to this distributed architecture is that it works based on the hosts NetBIOS names, which is specific to the Windows operating systems and is used for network resource sharing and the like.

There are at least four relevant deployment scenarios concerning this technology:

1. Full stack on the same physical host


2. DHCP reliant network, all hosts on the same segment


3. DHCP reliant networks, with firewalls between segments


4. Static Address network with firewalls

The first scenario, full stack, isn't challenging because it assumes you have the Application Server and Databases all in the same machine. It can be imagined as the common scenario for developer machines which often have the IDE, DB Server and App Server all-in-one.

The second scenario where all hosts reside on the same network segment, usually a net mask of 255.255.255.0, requires only additional configuration if there are no WINS server available and network broadcast of packages has been disabled. Otherwise the default configuration will work fine.

Next post will discuss a configuration scenario with a firewall in between the Application Server and the Database Server.