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.

.\setup.exe /Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=install /FEATURES=SQL /INSTANCENAME=LAB /SQLSVCACCOUNT="LAB\SQLService" /SQLSVCPASSWORD="AppleP13"   /AGTSVCSTARTUPTYPE=Automatic /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /SQLSYSADMINACCOUNTS="LAB\Domain Administrators"


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
GO
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;
GO
BACKUP CERTIFICATE TDECert
TO FILE = 'C:\Users\ryan.betts\Desktop\Cert Export\Cert.cer'                                                         
WITH PRIVATE KEY (file='C:\Users\ryan.betts\Desktop\Cert Export\CertPRVT.pvk',
ENCRYPTION BY PASSWORD='SecretPassword');

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;
GO
CREATE MASTER KEY ENCRYPTION
       BY PASSWORD='SecretPassword';
GO


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;
GO
CREATE CERTIFICATE TDECert
  FROM FILE = 'C:\Users\da.ryan.betts\Desktop\Cert Export\Cert.cer'
  WITH PRIVATE KEY (
    FILE = N'C:\Users\da.ryan.betts\Desktop\Cert Export\CertPRVT.pvk',
 DECRYPTION BY PASSWORD = 'SecretPassword'
  );
GO

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;
GO
ALTER DATABASE dbname SET ENCRYPTION OFF;
GO
USE dbname;
GO
DROP DATABASE ENCRYPTION KEY;
GO

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.

Thursday, 15 November 2018

Understanding AD CS CRL publication periods and configure auto publish CRL to share

By default when you deploy a two-tier AD CS the default CRL publication period is 1 week. This is set on the Offline CA and the Onsite CA. 

What does this mean?

If you have the CRL publication period set to one week, a new CRL must be published and available to clients on a weekly basis of certificate revocation will fail. 

In a two-tier AD CS infrastructure there are two CRL's in created;

Offline CA CRL - this is published by the Offline CA and should be blank unless you have revoked historic Root Certificates. This CRL is only used to by the Online CA to check the validity of Root Certificate which has been issued by the Offline CA. If certificate revocation fails for the Offline CA Root Certificate, the entire AD CS will fail. This is because all certificates are signed by the top certificate in the trust chain. By default this is set to one week, which means every week an administrator will have to boot the Offline CA, publish a new CRL and then copy it to the revocation points. It is likely that your Offline CA will only issue a single certificate, to the Online CA, if this is the case there is no risk in setting the Offline CA's CRL publishing time to months, or even years. 

To do this login to the Offline CA, open Certification Authority expand the server object, right click on Revoked Certificates and select Properties. Once this is committed, a new CRL must be created which can be done if you right click Revoked Certificates and select All Tasks > Publish. 

The newly published CRL can be found at C:\Windows\System32\CertSrv\CertEnroll the CRL files should be copied to the revocation points which are usually stored in AD LDAP, HTTP or both. It's typical for a HTTP revocation point to be on the Online CA, so that it's reachable by clients. 



Online CA CRL - this is published by the Online CA and will contain any certificates that have been revoked by administrators. Revoked certificates are not listed in the CRL until a new CRL has been generated. The Online CA CRL publication period is more important than the Offline CA, this is because it's likely that certificates issued by the Online CA might actually be revoked during the course of normal business. I usually set the Online CA to have a CRL publication period of 1 month, which means a new CRL will automatically be published every month, however once published the CRL files are not automatically copied to the revocation points on disk, which are usually served over HTTP by IIS or similar. If no certificates are revoked over the period of a month, the CRL will be unchanged, however the new files must be copied into the revocation point locations or certificate revocation will fail. 

With the Online CA CRL set to 1 month I usually just build it into a monthly maintenance task that should be provided by the SysAdmin team who are managing the AD CS environment. 

A useful way to check CRL expiration times is to use pkiview.msc which gives a clear overview of the CRL's all the way up the trust chain.


Comments system

Disqus Shortname