For BI purposes I have been working on a tool chain that fetches data from a wide variety of systems in mostly intermediate Excel steps to eventually import all of this into SQL Server and do the real smart BI magic.

Using the OPENROWSET command to Connect from SQL directly to an Excel file with the Microsoft.ACE.OLEDB.12.0 driver. However, from time to time the column configuration of the SQL tables would change and be incorrectly detected.

Today I finally found that there is a specific behavior in the driver that can be changes with the registry key TypeGuessRows under:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

This forces the driver to look further then the default 8 rows for determining the data type. This is important if you have only small amounts of text in the first 8 rows but after that longer amounts of text that would require the field to be NTEXT instead of NVARCHAR(255).

Today I was struggling with linking a Microsoft Excel file to my SQL Server for importing data from Excel files. Being stuck with the error:

Cannot initialize the data source object of OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".

The main issues with MS SQL Server and Excel as Linked Server objects are in the areas of:

• 32/64 bit incompatibility between Office and SQL Server
• Wrong OLE DB driver or not installed
• Additional options required for the OLD DB Provider (Dynamic Parameter and Allow Inprogress)
• Access rights to the TEMP folder of the process running SQL Server.

In my case the service is running as NT Service\MSSQL$CAPACITYCENTER. Based on the information found I verified modify access rights for this account under

• C:\Users\MSSQL$CAPACITYCENTER\AppData\Local\Temp

All was supposed to be ok, but the error remained. So I decided to run Sysinternals Process Monitor to check the SQLServr.exe process for failing file activities:

Although the user MSSQL$CAPACITYCENTER has Full Control on this folder, the LOCAL Service and NETWORK SERVICE account also need Write and Modify permissions to this folder. After adding these the error resolved.

I was starting to have more and more stability issues with my WSUS servers, clean-up scripts timing out and downloads hanging. So I tried to reset the entire server, but that failed with a login error that I couldn’t solve. So let’s go all the way, remove the entire feature and re-install it…

Only problem was that during the post-install scrip I was stuck at the error:

Fatal Error: The system cannot find the path specified.

Looking at the detailed logs this was at the moment that the server tried to connect to IIS. It took a lot of searching to find a post from the System Center Blog that hinted on an issue of the installation missing the default web site with ID 1.

After re-creating that the installation continued just fine…

At my office we have a custom tool for creating project folders and assigning users access rights to these folders. As best practice the access rights to these folders are managed through a project specific group that users become member of. Downside is that you need to logoff and logon again to re-evaluate your group memberships to get the actual access.

Today I found out about a small tool called klist. It is an Microsoft tool for managing the Kerberos ticket cache and appears to be present by default on Windows. (At least on my Windows 10 Pro)
With the purge argument all tickets of the current logon session can be deleted forcing Windows to logon again and re-evaluate group membership.

klist purge

More information from Microsoft about klist on Technet.

In Windows 10 you can select a VPN connection from the Network Tray icon. But instead of dialing the connection directly it takes you to the VPN screen where you have to find the correct VPN connection and hit dial. It is no big deal but just feels slow using the mouse.

While Googling for a way to dial a VPN connection with PowerShell I found that Rasdial.exe can be used to dial an already configured VPN connection and also terminate it when done. Launcing this from the Windows Run box is easy.

rasdial "Connection Name"
rasdial "Connection Name" /Disconnect

It can happen that the Windows Offline File Cache becomes corrupt. A simple solution that I used to do was just have it reset the cache by setting the registry key HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\CSC\FormatDatabase to 1.

Although this works, it removes all offline data and requires a full sync of all offline files which can take time.

Continue reading →

My default Windows Update routine is that I install the updates on the WSUS servers as the very last steps. By then I have already updated all my other servers. This month I was in for an surprise when both WSUS servers no longer functioned correctly.

Apparently the KB3159706 update needs manual steps that I did overlook when installing this update. As best practice my WSUS servers are running on a Core version of the OS, so the provided steps from Microsoft needs some translation. Since I also use SSL I need to do the additional configuration steps for the bindings too.

Luckily p0w3rsh3ll already posted a good blog post for all these steps on Windows 2012 R2 server core.

I was having an issue with my agenda which appeared empty in the Outlook Web App using Google Chrome. On my phone I was still seeing all appointments and also in Outlook.

Strange this was that it was working correctly up to a few weeks ago. The issue appears to be in Google Chrome since version 38.0.2125.101. It is a known issue filed under 409858 and  421440.

There is also a workaround for it from Charles Wastell. Microsoft has taken action for this on their Office 365 environment as mentioned by Charles Wastell.

However it looks like Google is going to correct it in their Chrome browser and the patch should be come available for the 38 version somewhere this week.

Update: The issue has been solved in Google Chrome version 38.0.2125.111.

I had Plex Media Server already running for some time as a Service using srvany.exe from the Resource Kit. Additionally I managed to also configure it to use a Service Account. It was working but not perfect, there were too many hacks to get it working correct.

PMS as Service

Now that there is a better solution from Plex called PMS as Service I changed my configuration. I re-used the Service account that I was already using but starting the service failed. A quick look at the event log provided the essential information of the issue:

Service cannot be started. System.UnauthorizedAccessException: Access to the path 'C:\ProgramData\Plex Service\plexServiceLog.txt' is denied.
 at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
 at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
 at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
 at System.IO.StreamWriter.CreateFile(String path, Boolean append, Boolean checkHost)
 at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize, Boolean checkHost)
 at System.IO.StreamWriter..ctor(String path, Boolean append)
 at System.IO.File.AppendText(String path)
 at PlexMediaServer...

Access Rights Issue

To fix this, I’ve configured the required user rights for the Plex Service Account:

The installer from Microsoft Office supports integration of Updates by placing the MSP files in the Updates folder of the installation medium.

The challenge is more in getting all the update files. Fortunate there are a few great community users that have created WHDownloader for downloading these files and they also maintain the list of applicable updates.

Download the Updates

1. Run WHDownloader.
2. Click the button in the upper left to download all latest update lists.
3. Select the Office 2013 version.
4. Configure a Download Target folder.
5. Select all General Updates and Hotfixes for downloading. Tip, right click and Select All.
6. Let it download, this can take some time…

WHDownloader Office 2013 Post SP1

Extract MSP Files

The downloaded files are all self extracting executables that can be extracted through a few simple command line parameters:

update.exe /quiet /extract:C:\Users\Administrator\Downloads\Updates\MSP\

To do this for all scripts, use a PowerShell script like:

$files = Get-ChildItem "C:\Users\Administrator\Downloads\Updates\"

foreach($file in $files)
{
& $file.FullName /quiet /extract:"C:\Users\Administrator\Downloads\Updates\MSP\"
}

Move all extracted files into the Update folder of the Office Installation Media and done!

Confirmation

The installer will pick up the files automatically. To confirm, review the installed updates list after the installation. It should list all updates as installed: