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

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.

https://social.technet.microsoft.com/Forums/en-US/2c8b7561-1b97-438d-a56e-797d8b4bb60b/tde-encrypted-database-wont-mount-how-can-i-disable-tde-to-run-the-db-on-sql-standard?forum=sqlsecurity

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. 

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.

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.

Things to remember when configuring Exchange Hybrid to Office 365 and Exchange Online

Tip 1 – Office 365 should be used as a smart host for external mail routing

The Hybrid Configuration Wizard creates connectors on the Exchange Server to route mail to and from Office 365. By default, the on premise Send Connector is configured to only route mail destined for the “tenantname.mail.onmicrosoft.com” SMTP namespace.

This Send Connector is configured to use MX records lookups to find its next hop to route mail to this SMTP domain. I have had far better results if the Send Connector is manually configured to send all mail for “tenantname.mail.onmicrosoft.com” to the tenant’s public MX record target. In theory it should make no difference!

For clarity you modify the Send Connector on the Exchange Server it is usually labelled “Outbound to Office 365” if the HCW was used to configure your server. You can also find the MX record target by using MX Toolbox.

You will notice the smart host authentication option is set to None. Office 365 will use the federation certificate in place to authenticate the server, therefore no authentication is required on the Send Connector. 

Tip 2 – The Exchange Hybrid Configuration Wizard does not complete the job

If you have on premise Exchange Server before adopting Office 365, it is likely that you will have Send Connectors already in place to route mail to the Internet. During the design phases you must decide how you are going to architecture your mail flow. Personally I think it’s best to route all email from the Internet through Exchange Online, then down to the Exchange Server if the mail enabled recipient resides on premise. I also think routing outbound mail (for all domains) should be done through Exchange Online. This is something that the HCW does not do during the wizard led set up.

A new Send Connector is created labelled “Outbound to Office 365” which routes mail to the tenant SMTP domain for hybrid mail flow. My recommendation would be to edit this Send Connector and add the all domains asterisks. With this configuration all mail for all domains will be pushed to Exchange Online for routing. 

All of your design decisions should be led by requirements, however in a simple environment this will cover most basis. It does however, become complicated if you bring third party mail hygiene solutions into the mix.

Tip 3 – Active Directory attributes matter in hybrid for proper mail flow

Active Directory attributes are used to route mail in a hybrid environment. The attribute “targetAddress” is the most important for internal mail flow to work correctly. If you have mailboxes on premise and in Office 365 this is needed for your AD users who have a mailbox in Office 365.

On premise mailbox users do not have the “targetAddress” populated, however if you complete a mailbox migration to Office 365 and commit the changes, Exchange will populate this attribute in AD to ensure internal mail flow will work correctly.

One project I worked on had Office 365 before it had Active Directory. In this case you might have to do some work to manually populate the AD attributes for mail flow to work. In most scenarios AD and Exchange will be in place long before Office 365. Another important point to remember is that when you have on premise Exchange, this should be used to administer mailboxes (including creating new Office 365 mailboxes), this way all the attributes required will be populated for you.

I’ve written a dedicated blog post to cover AD attributes in Exchange Hybrid

http://blog.ryanbetts.co.uk/2018/09/understanding-ad-attributes-in-exchange.html

Another useful post, how to add targetAddress and proxyAddress with Powershell to multiple objects, this could be useful if you have incorrectly created Office 365 mailboxes without using on premise Exchange in hybrid.

http://blog.ryanbetts.co.uk/2018/10/update-all-user-targetaddress-and.html

In addition to the above, if you incorrectly create an Office 365 mailbox you might need to run the command, before your cloud mailboxes work correctly. This command should be run from the Exchange Server.

Enable-RemoteMailbox “user” –RemoteRoutingAddress “email.address@tenant.onmicrosoft.com”

This command will also ensure any cloud mailboxes which were created before the Exchange Server show up inside the on premise ECP under Recipients.

Tip 4 -  Autodiscover must point to the on premises Exchange in hybrid

Although there is conflicting documentation on this, the Exchange Deployment Assistant states that in hybrid environments the external autodiscover records must point towards the on premise Exchange Server. This will cause the Office 365 portal to show an error as it’s expecting all records to be pointing to it. After testing autodiscover will not work for on premise mailboxes if the external records point to Office 365. I generally create an internal CNAME record as well pointing to the internal Exchange Server.

Tip 5 – Network rules are important in hybrid

For hybrid to work SMTP is important. The Exchange Server must be able to communicate with Office 365 inbound and outbound on TCP 25. You can update your firewall rules to only allow the known Office 365 IP’s to be accepted for SMTP.

https://docs.microsoft.com/en-us/office365/enterprise/urls-and-ip-address-ranges

You can use the following command to test this from either side.

Test-NetConnection –ComputerName “fqdn” –Port 25

Configure IP Whitelist for Network Devices to Send Mail to Exchange Server without SMTP Auth

This is very easy and is done using a new Recieve Connector. 

Login to the ECP and click Mail Flow, then Recieve Connectors. Create a new Recieve Connector.

Name the connector something descriptive. 

Select Frontend Transport and Custom.

In Network Adapter Bindings leave "All available IPv4" this is only the case if the Exchange Server has a single NIC. It's slightly different if your server has arms in two networks.

In Remote Network Settings, enter the IP's of the network appliances you want to be able to send mail without authentication.

Click Finish.

To test use the following Telnet commands (you run this from a server which is listed as allowed in the new Recieve Connector).

set localecho OPEN mail.domain.com 25 EHLO domain.com MAIL FROM:ise@domain.com RCPT TO:ryan.betts@domain.com NOTIFY=success,failure DATA Subject: Test from Telnet Testing . QUIT

If it's successful it should return a message similar to below.

If you want the server to be able to relay to external domains you must run another command as well.

Change it to reference your Exchange Server and Recieve Connector name, also remember and run it from Exchange Mgmt Shell or it will fail. 

Get-ReceiveConnector "EXSRV\REC CONN NAME" | Add-ADPermission -User 'NT AUTHORITY\Anonymous Logon' -ExtendedRights MS-Exch-SMTP-Accept-Any-Recipient

Show "Caution: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe." warning to users when mail originates from outside Exchange Organisation

To try and attempt to reduce phising attempts it is possible to attend a HTML warning to users when they receive emails from outside the organisation. This is overkill in many environments but some insist on it being in place. 

It's achieved using Transport Rules in Exchange Online, the code below implements a warning 

"Caution: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe."

//Create a Session to Exchange Online Write-Host "Getting the Exchange Online cmdlets" -ForegroundColor Yellow $Session = New-PSSession -ConnectionUri https://outlook.office365.com/powershell-liveid/ `     -ConfigurationName Microsoft.Exchange -Credential $credentials `     -Authentication Basic -AllowRedirection Import-PSSession $Session -AllowClobber //Create a new Transport Rule New-TransportRule -Name "External Mail Warning - Outside Organisation" -Priority 0 -FromScope "NotInOrganization" -ApplyHtmlDisclaimerFallbackAction Ignore -ApplyHtmlDisclaimerLocation "Prepend" -ApplyHtmlDisclaimerText "<div style=""background-color:#FFEB9C; width:100%; border-style: solid; border-color:#9C6500; border-width:1pt; padding:2pt; font-size:10pt; line-height:12pt; font-family:'Calibri'; color:Black; text-align: left;""><span style=""color:#9C6500; font-weight:bold;"">CAUTION:</span> This email originated from outside of the organization.  Do not click links or open attachments unless you recognize the sender and know the content is safe.</div><br>"

Disable Outlook Focused Inbox across the entire Office 365 tenant

Use the following command to disable the annoying "Focused Inbox" setting across your entire tenant. It is possible to disable it on a per user basis, but do the right thing and remove it for everyone!

$UserCredential = Get-Credential Install-Module MSOnline -Force Import-Module MSOnline $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection Import-PSSession $Session  Set-OrganizationConfig -FocusedInboxOn $false Get-OrganizationConfig //Check to see FocusedInbox is set to “false”

Update all user "targetAddress" and "proxyAddress" attributes in AD using Powershell for Exchange Hybrid

AD attributes are important when you have a hybrid setup from your on premise Exchange to Office 365, the following script will help retrospectively publish the required objects to your domain users so that hybrid mail flow works correctly.

Review this article for details on AD attributes in Exchange with hybrid connectivity.

http://blog.ryanbetts.co.uk/2018/09/understanding-ad-attributes-in-exchange.html

The following command can be used to export a list of users to a CSV file, you should change the OU path to suit your own environment. Once you have a list of users copy and paste them into a blank text document.

Get-ADUser -SearchBase "OU=Standard Users,OU=User Accounts,OU=Grand Cayman,OU=Company X,DC=domain,DC=com" -prop * -Filter * | Select samaccountname | Export-CSV C:\Users\Administrator\Desktop\Users.csv

The following command will set the "targetAddress" attribute for every user listed in the text file.

The "targetAddress" is used when on premise mailboxes try to send mail to Office 365 mailboxes.

Remember to capatilise SMTP as this will ensure it's the primary email adress. 

Get-Content C:\Users\da.ryan.betts\Desktop\O365Users.txt | % { Set-AdUser $_ -add @{targetAddress="SMTP:$_@companyx.mail.onmicrosoft.com"}}

The following command will append the FQDN, your public domain should be set as the primary so ensure caps are used with SMTP.

Get-Content C:\Users\Administrator\Desktop\O365Users.txt | % { Set-AdUser $_ -add @{proxyAddresses="SMTP:$_@companyx.com"}}

A second pass of the same command will add the additional proxy address which should be tenantname.mail.onmicrosoft.com

Get-Content C:\Users\Administrator\Desktop\O365Users.txt | % { Set-AdUser $_ -add @{proxyAddresses="smtp:$_@companyx.mail.onmicrosoft.com"}}

When you sync the AD to AAD using AD Connect the x500 entry will also appear under proxyAddresses.