Wednesday, 22 May 2019

Archiving this Blog

If you follow me on Linkedin, you will know that I joined Microsoft was an FTE in February of 2019. As a result of this I will no longer be posting new technical content here. I intend to keep the blog live as it still gains strong unique visitor figures. 

Wednesday, 30 January 2019

Configure NLB Nodes for WAP (non domain joined)

You might run into some node-level trust issues if you are trying to configure an NLB cluster for the Web Application Proxy role. 

The best practice from Microsoft states that any servers running the Web Application Proxy role should reside in a DMZ network and not be domain joined. This brings it's own set of issues as the nodes don't automatically trust each other. 

Gone are the days of creating two local administrator accounts on two non-domain joined hosts with the same password and praying it "passes through" authentication requests. Although we are still going to do this, a few other steps must be completed for it to work. 

If you are configuring an NLB cluster on none domain joined nodes, you will probably be faced with "Access Denied" when you attempt to add the second host to the already existent cluster. This is even if you have matching local administrator credentials on both machines. I'm led to believe this is due to later versions of Windows inspecting the local SID's of user accounts instead of the username string. 

To resolve this do the following - 
  • Create a new DWORD entry for LocalAccountTokenFilterPolicy in the registry of both nodes, this disables certain parts of UAC.  The registry path is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System and for clarity the new entry should be a DWORD set to decimal and the value of 1.
  • Configure (from NLB Manager) Options > Credentials on both servers with the local admin account that has been created on each of the servers.
  • Configure the NLB cluster using node IP's and not DNS names (even if you have DNS names configured with the hosts file, I've found IP's seem to work better in a none domain joined NLB cluster).

Tuesday, 22 January 2019

DirectAccess reports Error: there is no valid certificate to be used by IPsec which chains to the root/intermediate certificate configured to be used by IPsec in the DirectAccess configuration." when device-certificates are enabled

The amount of accurate documentation on how to implement device-certificate based authentication for Direct Access clients is extremely low. If you’re implementing Direct Access from scratch I would recommend getting it working with AD credentials only, then enable device-certificates once you are confident in the general config. I recently did just this, the Direct Access server and clients were functioning correctly. The next stage was to enable Computer Certificates from Step 2 – Remote Access Server.

In my lab I only have a single tier AD CS PKI setup, therefore I selected Use Computer Certificates but did not tick Use an Intermediate Certificate. This would be required if you had a two-tier AD CS PKI.

For clarity at this point you should choose the certificate that is that is issued by your Certificate Authority. It is unclear what else is required to make IPsec work correctly. 

Once you do the above, and Group Policy refreshes you will start getting an error about IPsec not working. 

“Error: there is no valid certificate to be used by IPsec which chains to the root/intermediate certificate configured to be used by IPsec in the DirectAccess configuration.”

“Causes: the certificate has not been installed or is not valid.”

“Resolution: please ensure that a valid certificate is present in the machine store and DA server is configured to use the corresponding root certificate.”

The reason for this error is that a suitable certificate is not installed on the Direct Access server, this might seem obvious. However, the configuration step from Direct Access Step 2 – Remote Access Server does not install a certificate to make IPsec work, it simply points the Direct Access configuration at the PKI to trust for device certificates. 

With that said, you must configure a custom AD CS template with specific settings to make IPsec work for Direct Access, a certificate from this template then must be installed on all of the Direct Access servers.

To do this open up Certification Authority and click Certificate Templates.

Open Manage from Certificate Templates.

Find the default Certificate Template called RAS and IAS Server, right click it and select Duplicate Template

On the General tab give your new template a descriptive name I also select Publish Certificate in Active Directory.

Click on the Security tab and add the context Domain Computers and grant the following permissions
  • Read
  • Enroll
  • Autoenroll 

Click the Extensions tab and click Application Policies, then Edit. 

Click Add from the Edit Application Policies Extension window.

Enter a descriptive text string for the new Application Policy, do not make any alterations to the Object Identifier and click OK. 

Do not forget to do a Certificate Template to Issue to ensure the new template is available for certificate enrolment. 

The next step to fix “IPsec is not working properly.” is to enroll a certificate on each of the Direct Access servers using the new template. It should be installed under the Computer context under the Personal store on each of the Direct Access servers. 

Once this certificate is installed do a gpupdate /force on each of the Direct Access servers, the IPsec errors should disappear. 

Friday, 30 November 2018

How to silently install SQL Server 2016 Standard

If you need to quickly install SQL Server with many of the default, the following command can be used. This is useful if you are like me and often have to build up lab environments to test stuff.


Instance Name = LAB
Domain = LAB.local
SQL Server = LAB\SQLService

Wednesday, 28 November 2018

TDE encrypted database won't mount on SQL Server Standard after license upgrade, decrypt TDE database and migrate to SQL Server Standard

This is a continuation to my post on the TechNet forum, it’s effectively my notes on how to resolve the problem I initially asked to the community.

I had a SQL Server deployed in production which was installed using evaluation licenses. This was because at the time of install the volume license agreements were not signed off. As the evaluation licenses were due to expire I looked at the process of activating SQL Server with the customer’s volume license keys. If you have ever done this, you will know the process is basically to use the volume license media to upgrade the evaluation installation to the edition your volume license covers.
SQL Server, when installed in evaluation mode makes all the features of SQL Server Enterprise available. This caused a problem as one of the production databases had been encrypted with TDE.

TDE is a SQL Server feature only available in Enterprise edition. Once the license upgrade to Standard was complete the TDE encrypted database refused to mount as the feature was no longer licensed.

The process to resolve this is as follows (at a high level)
  • ·         Install new evaluation instance of SQL Server (which will give Enterprise).
  • ·         Export the original encryption key from the source instance.
  • ·         Create the new master key on the new instance.
  • ·         Import the original encryption key into the new instance.
  • ·         Copy the original database file and log to a new location.
  • ·         Attach the original database file and log to the new instance.
  • ·         Reconfigure application to utilise new SQL Server instance.

The above steps resolve the issue and will get the database back online. However, unless you want to run on unlicensed software, or pay to upgrade to SQL Server Enterprise, you will have to get the database running on the production SQL Server Standard instance. To do this the database will need decrypted, backed up and restored in order to remove the TDE encryption.

USE master
SELECT * FROM sys.certificates
SELECT * FROM sys.dm_database_encryption_keys
 WHERE encryption_state = 3;

You should see the certificate used to encrypt the database listed below. In my case it was called “TDECert”, this is needed for the next command.

Use the following command to export the encryption key with the private key. In the BACKUP CERTIFICATE statement remember and list the name of your certificate which is in the output above.

USE master;
TO FILE = 'C:\Users\ryan.betts\Desktop\Cert Export\Cert.cer'                                                         
WITH PRIVATE KEY (file='C:\Users\ryan.betts\Desktop\Cert Export\CertPRVT.pvk',

Once the query has successfully completed the folder will be populated with the certificate and corresponding private key. 

Install a new SQL Server instance, I have selected Developer. The Developer edition of SQL Server is for testing purpose, but has all the features of SQL Server Enterprise.

To ensure your database mounts on the new temporary instance, ensure you install the instance with the correct database collation. I don’t believe the collation can be changed without reinstalling the instance.

 Once you have a new instance of SQL Server installed, a new Master Database Key must be created. This can be done using the following code, this should be run as a query from the new instance.

USE master;
       BY PASSWORD='SecretPassword';

Once you have the new SQL instance installed, you must use the following command to import the certificate and private key which was used to encrypt the TDE database.

USE master;
  FROM FILE = 'C:\Users\da.ryan.betts\Desktop\Cert Export\Cert.cer'
    FILE = N'C:\Users\da.ryan.betts\Desktop\Cert Export\CertPRVT.pvk',

Now copy the original database and log files to a new location. This will protect the originals in case you need to revert back to them.

Open SQL Server Management Studio, expand the instance, then expand Databases. Right click and select Attach. 

Click Add and browse for the MDF file which was copied to the new location. If the log file is available, it should automatically detect that. Click Ok.

If all has gone well the database should now mount. 

If you have moved the database to a new SQL Server instance, your application front end will need reconfigured to point to the new instance name. Although all applications are different this is typically done with an ODBC connector in Windows.

The instance name comes after the server name for example SQLServer01\Instance01.

Now that the database is mounted and accessible, we can disable the TDE encryption and begin the database encryption process. The following command can be used to do this.

USE master;
USE dbname;

Once this has completed, you can check its worked by right clicking on the database and selecting Tasks > Manage Database Encryption. If the database is decrypted the Set Database Encryption On option should be unticked. 

Now run a standard SQL backup job, copy the backup file to the original production instance and perform a restore. Reconfigure the application to point to the original instance and you have now disabled TDE and the database will run on SQL Server Standard.

VMware ESXi 6.7 fails to connect VM web console with "You have reached the maximum number of connected consoles: 40. Please contact your administrator."

VMware ESXi 6.7 fails to connect VM web console with "You have reached the maximum number of connected consoles: 40. Please contact your administrator."

I tried disconnecting all the open session to vCenter which never helped. 

I also tried to restarting the VM which also never helped. I managed to narrow it down to being a problem with the VM and not vCenter, because the web console was working for other VM's. 

The fix for me was to shutdown the VM, remove the VM from the vCenter inventory (don't delete from disk), then Register it from the datastore again. Then the error disappeared. 

Friday, 16 November 2018

Customise the AD Connect sync scheduler time window

By default AD Connect automatically syncs to Azure AD every 30 minutes. Although this works fine in most companies, it could be considered too long a period if there is a high rate of change within an organisation. 

To alter the default sync period use Powershell

Set-ADSyncScheduler -CustomizedSyncCycleInterval 00:05:00 

The above command sets the sync period to 5 mins. It's important that you do not set the sync period too low, so that sync jobs don't begin to clash. A delta sync should not take a full 5 minutes unless the AD of a large enterprise scale.

Comments system

Disqus Shortname