Important notice for Azure AD Connect customers running on SQL Server

Microsoft updated the AAD Connect release notes with an important warning for customers who use a full SQL Server deployment for Azure AD Connect.

Important
Starting with build 1.1.484, Azure AD Connect introduced a regression bug which requires sysadmin permissions to upgrade the SQL database. This bug is still present in the latest build 1.1.614. If you are upgrading to this build, you will need sysadmin permissions. Dbo permissions are not sufficient. If you attempt to upgrade Azure AD Connect without having sysadmin permissions, the upgrade will fail and Azure AD Connect will no longer function correctly afterwards. Microsoft is aware of this and is working to correct this.

This does not affect customers running localDB instances of SQL. The current version of AAD Connect is 1.1.614.0. 

Customers running SQL Server back ends are currently excluded from automatic upgrades, so it always requires a manual upgrade to a newer version of AAD Connect. That means (for now) you don't need to be worried about an auto-upgrade automatically breaking AAD Connect functionality.

Read more ...

Allowing a Service Account to Manage Its Own Service Principal Name (SPN)

As a best practice, Microsoft recommends that SQL Server be run using a domain account. This account is sometimes referred to as the SQL service account. However, when you configure SQL with a domain account you will get an event in the SQL Logs:

The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

Doing a Google or Bing Search takes you to multiple MSDN articles that have you running SETSPN to assign the SPN to the domain account that SQL server is using. This can be time consuming and subject to typos and other errors. If you don't set the SPN properly, Kerberos Authentication will not work and that stops pass through authentication from working.  See this article from the Microsoft SQL Server Protocols team for more information.

A simple and easier way to fix this is by using Active Directory Users and Computers to assign the Write Public Information permission to Self on the domain account that SQL is using, as shown below:

If you do this before installing SQL, no restarts are needed. If SQL Server is already running then you will need to restart SQL Server.

After applying this fix you will find the following event in the SQL Logs:

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/servername.domain.com ] for the SQL Server service.

This will allow SQL to register the SPN on start up, and un-register the SPN on shutdown. It also helps when you remove a SQL server from the domain, in that when you shutdown SQL the SPN is unregistered, thus helping to keep your AD database cleaner.

You can also apply this method to other services that use a SPN and you are using a domain service account to run that service.

Special thanks to my colleague, Rick Romack, at Convergent Computing for this tip!

Read more ...

Using DiskPart

This post is mainly for me, since I never can seem to remember the commands to use. :)

DiskPart is a utility that, among other things, allows you to specify the cylinder alignment of hard drives. Exchange and SQL particularly benefit from a 64KB sectors. So much so, in fact, that 64KB alignment is (finally!) the default in Windows Server 2008.

There are plenty of resources on the net that explain when and how to use it. A good explanation by the Exchange Blog can be read here and the Microsoft reference for DiskPart is here.

The commands I use are:

C:\>diskpart
Microsoft DiskPart
version 5.2.3790.1830
Copyright (C) 1999-2001 Microsoft Corporation.
On computer: Exchange2003

DISKPART> select disk 1

Disk 1 is now the selected disk.

DISKPART> create partition primary align=64
DiskPart succeeded in creating the specified partition.

Then open Disk Management, assign a drive letter to the new drive and format it as usual.

Read more ...

Fixing Side-By-Side Errors

I've run across a few servers that are throwing the following "Side By Side" error in the Application event log:

Event Type: ErrorEvent
Source: SideBySide
Event Category: None
Event ID: 59
Date: 10/12/2007
Time: 7:30:55AM
User: N/A
Computer: FDOMOPS01
Description:Generate Activation Context failed for C:\WINDOWS\WinSxS\x86_Microsoft.VC80.MFC_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_DEC6DDD2\MFC80U.DLL. Reference error message: The referenced assembly is not installed on your system.

Usually, I've found these servers to be running SQL 2005. The fix is to install the Microsoft Visual C++ 2005 Redistributable Package. It's available here:

• Microsoft Visual C++ 2005 Redistributable Package (x86)


• Microsoft Visual C++ 2005 Redistributable Package (x64)

Read more ...