Tuesday, July 31, 2018

SCCM reporting, SQL 2014, AG setup

SCCM reporting DB was moved to an AG cluster 2014 with SCCM reports locally on  standalone server.

Db connectivity between SCCM and AG was fine.

However, SCCM reports created would not connect to the Datasource with the error

the Datasource was connecting to ag01.mydomain.local 

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate's CN name does not match the passed value.)

Seems lots of people are experiencing this issue, and logging a case to MS didnt go too far.

Setup

SCCM server with Reporting Role
SCCM DB on a AG called for e.g AG01.mydomain.local with two node cluster where Clustername is Clu1 and Node1 and Node2 are part of the AG cluster.

After lots of searching, wireshark, tracing logs, sql server logs and stumbling on bits and pieces, the solution that worked for us is as below:

Created a Server Auth cert with CN name as AG01.mydomain.local (FQDN of AG)
and SAN with DNS names
ag01
clu1.mydomain.local
clu1
node1.mydomain.local
node1
node2.mydomain.local
node2

References:
https://support.microsoft.com/en-us/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-mi

https://thesqldude.com/2012/04/21/setting-up-ssl-encryption-for-sql-server-using-certificates-issues-tips-tricks/

This is assuming you have a pki infrastructure and can generate proper certs and the CA root certs are on the servers.

Make sure certs created have proper permissions assigned for the SQL service accounts if SQL running under SVC account.

Once you have the appropriate certs as per above.

Follow the article below

https://support.microsoft.com/en-us/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-mi

And most likely if you copied the Thumbprint as i did from the MMC console then you will need to follow this

https://support.microsoft.com/lo-la/help/2023869/sql-server-fails-to-start-with-error-17182-tdssniclient-initialization

Once the SQL services are running successfully, the issue should get resolved.

Failover was successfully tested and reporting worked fine there after.

Best of Luck

Hope it saves someone sometime... my colleague spent weeks...

Wednesday, November 22, 2017

Setup and Enable Certificate Authentication on ADFS 3



Scope:

Get ADFS to do Certificate Auth so IOS Users do not need to login using their AD creds. From Internal Wifi network and from External network. Setup Sharepoint to allow CERT based auth.
Current Setup:
ADFS farm with 2 servers on the Internal Lan and 2 WAP servers on the DMZ. Firewalls exit between External to DMZ and DMZ to Internal.
Does ADFS currently work? YES NTLM, Kerberos and Forms
X.509 isn’t enabled.
One cert used for Service communication and proxy trust adfs.contoso.com public cert via GoDaddy

Things done:

·         Allowed traffic from External to DMZ WAP servers on port 443 and 49443
·         Setup f5 with adfs template and/or VS with L4 layer no SSL offload, automap and source address persistence for 443 and 49443
·         Connectivity between WAP and ADFS servers via the Firewall on port 443 already existed.
·         Enabled Certificate Auth on ADFS for Extranet zone and Intranet zone.
·         Added for AD Claims provider claims rules (not mandatory but added it to be prepared for Office 365 if need be)
o   c:[Type == " http://schemas.microsoft.com/ws/2008/06/identity/claims/serialnumber"]  => issue(claim = c);
o   c:[Type == " http://schemas.microsoft.com/2012/12/certificatecontext/field/issuer"]  => issue(claim = c);
·         Enabled WIA only for Windows machines, Macintosh machines (not all IOS devices), Mozilla and chrome, else Ipad and iphones wont get prompted for X.509 if they connect internally (intranet). The commands below are references. Work out yourself how to run these commands and get the desired outcome. Hope you know a bit of powershell.

1.      Execute the following command to get the current list of supported user-agents for NTLM authentication
[System.Collections.ArrayList]$UserAgents = Get-AdfsProperties | select -ExpandProperty WIASupportedUserAgents
2.      Execute the following command to inject the user agent into a temporary array of user agents already added to ADFS.
$UserAgents.Add(“Mozilla/5.0 (X11”) and Mozilla/5.0 (Macintosh AND Mozilla/5.0 (Windows NT
3.      Execute the following command to commit the change.
Set-ADFSProperties -WIASupportedUserAgents $UserAgents

·         Make sure all your ROOT CAs and Intermediate CAs are installed on local machines in the correct containers.
·         Check ADFS certs are the same on ADFS and WAP
o   Get-adfssslcertificate
o   Get-webapplicationproxysslcertificate
·         IOS devices need all the organisation CA certs on the devices along with the user cert. So the Browsers that you would use to auth using Cert auth. Else you wont get prompted for Cert selection.

Things that were wrong or went wrong:

Adfs.contoso.com cert was renewed after the initial ADFS farm installation. This was done via the MMC console and ADFS console, by created new CSR, get a new public cert from Godaddy CA and then importing and enabling it for service communication on ADFS. After a lot of hours of troubleshooting I found out that the certificate was installed and although ADFS for NTLM, Kerberos and WIA was working fine, X.509 wasn’t working. Would not get a prompt for choosing a cert.
Turns out, if you renew and import the adfs ssl cert via the gui, the following parameter in bold is not flagged correctly for the SSL binding on port 49443
Hostname:port                : adfs.metrotrains.com.au:49443
    Certificate Hash             : b9852b75fc0ba578f1a141172dcbf0e328950140
    Application ID               : {5d89a20c-beab-4389-9447-324788eb944a}
    Certificate Store Name       : MY
    Verify Client Certificate Revocation : Enabled
    Verify Revocation Using Cached Client Certificate Only : Disabled
    Usage Check                  : Enabled
    Revocation Freshness Time    : 0
    URL Retrieval Timeout        : 0
    Ctl Identifier               : (null)
    Ctl Store Name               : AdfsTrustedDevices
    DS Mapper Usage              : Disabled
    Negotiate Client Certificate : Disabled (should be enabled)

To fix it I had to set the ADFS ssl cert again using powershell
                Set-AdfsSslCertificate -Thumbprint
This fixed the issue of not getting prompted for cert when trying to connect to ADFS from the internal network.
The same fix above applies if you don’t get a prompt for user cert when connecting to the WAP boxes. The commands are a bit different.
Get-webapplicationproxysslcertificate to check if the WAP is using the same cert as the ADFS one which is adfs.contoso.com
If it ISN’t you won’t get prompted for user cert when connecting from outside to the WAP and the page will just sit there forever. To fix it,
Run
Install-WebApplicationProxy -CertificateThumbprint “adfs.contoso.com cert thumbprint” -FederationServiceName “adfs.contoso.com

NOTES:
Restart ADFS services after EVERY change you make on the ADFS server. The cert changes need to be performed on every server in the farm. Same applies for the WAP server and WAP also has an additional WAP service that needs to be restarted.
The above task was accomplished with help of various forums. All the articles I looked at were referring to user cert auth as part of MFA, however, I needed user cert auth as a separate signon option.

The articles, links and forums that were helpful with accomplishing the above task were:

Disclaimer:


I’m no expert at ADFS or SAML. I’m still learning. The above solution might not be the perfect fit or so called best practice but it works.
Hopefully someone out there who is facing a similar challenge might find the above useful.
Cheers





Wednesday, December 14, 2016

System Center 2016 - SCOM - Operations Manager Installation issues, SQL 2014 cannot create database

Setup is unable to create database on SQL server instance ''. Please make sure the current user has permissions to create database on the SQL Server instance specified. 

The  above is what i was getting when trying to attempt to install SCOM 2016 using the installer and getting to the Operations Manager settings window.
Im using SQL alias,
it detected and acknowledged the SQL alias. However, kept giving me an error dialog with the above error message.

Spent few hours, trying to check every possible permission,
gave SA permission on SQL, local admin on SQL and SCOM mgmt server where i was running the installation. No Luck...

Digging further using SQL profiler and help from someone who knows SQL and app dev.. it seemed the installer was looking for an AG name as part of the checks. I'm assuming SCOM isntaller was designed such that if it detects SQL 2014 it assumes its an AG setup. In my case it wasnt.

Obviously, the error message indicated nothing to represent that.

Anyways, got more help from a friendly DBA, who provided me an SQL AG.

Using SQL alias pointing to the AG, I tried another attempt.

New issue...

the SQL server with AG is running on Mount Points.
So now the installer is complaining that the F:\SQL_DB drive and F:\SQL_LOG drive do not have enough space. which is correct, F: drive is the mount point drive.
What now..
To get around it, I shared the SQL_DB and SQL_LOG folders after reading forums where users had similar issues.

https://social.technet.microsoft.com/Forums/en-US/c3992d51-07dd-4560-a0d6-c44fa67e1b2a/scom-2012-r2-with-sql-2014-and-alwayson-installation-error?forum=operationsmanagergeneral

\\sqlserver\sql_db1
\\sqlserver\sql_log1

Did the trick.

Same issues were encountered and addressed in the OPS DW settings dialog.
Hope the above helps someone... Best of luck

Monday, June 23, 2014

Oracle RAC installation on Windows 2008 R2 - User equivalence and Remote Registry Gotcha

Setup:
2 Windows 2008R2 ent servers which have identical local accounts created for Oracle installation for e.g. svc_oracle with identical password setup as per the Oracle RAC admin guide.

All UAC and policy changes are implemented.

However for Remote registry to work using the identical local accounts.

You need to do the following on each rac node.

1. Open Regedit


2.Then navigate to the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System
 
 
3. Enter a new value called LocalAccountTokenFilterPolicy and set it to type DWORD(32-bit).  Set the value data to 1.
 
 
It is very important to set the value name correctly with no trailing 
spaces.  This new value allows remote access to the Windows operating 
system from a named account that exists on this machine as well, 
provided the passwords are the same.
 
 
This allows the oracle user/installation account/service account mentioned earlier to 
access the resources between both the nodes.
 
HAve fun...
 
 

Wednesday, September 18, 2013

"DHCP...." followed by "PXE-E53: No boot filename received"



SYMPTOM
When being started, the PXE client comes up with the PXE copyright message, then displays
DHCP....
After a while, the following error message is displayed:
PXE-E53: No boot filename received
Depending on the PXE client's system setup boot device list configuration, the PC then either stops or tries to boot from the next boot device in the system setup boot device list.
CAUSE
The "PXE-E53" error indicates that the PXE client received a reply to its DHCPDISCOVER message, but the "boot filename" information was missing in this reply.
RESOLUTION
Make sure that the "boot filename" option is present on your DHCP or BOOTP server, and that its value is set to the filename of the boot loader.
When using Microsoft DHCP server, add option 067 (Bootfile Name) to your scope. When using a Unix/Linux based (ISC) DHCP server, use the "filename" parameter for this purpose.
In the context of the BootManage Administrator, the boot loader filename is "pxboot" for PXE clients and "bpboot" for TCP/IP BOOT-PROM clients. So, if you have exclusively PXE clients, set the boot filename option to the value "pxboot". If you have exclusively TCP/IP BOOT-PROM clients, set the boot filename option to the value "bpboot". In a mixed PXE and TCP/IP BOOT-PROM client environment, you must configure your DHCP or BOOTP server so that it provides the PXE clients with the "pxboot" boot loader, and the TCP/IP BOOT-PROM clients with the "bpboot" boot loader.
____ IMPORTANT Thing to note:

We were trying to troubleshoot the above issue with the recommendation above, however, make sure you can see the dhcp server leasing out the ips to the machine you are trying to pxe boot.
Secondly and most important, if they are 64 bit client, run the command below

WDSUTIL /set-server /architecturediscovery:yes

Setting up ADFS Farm with ADFS proxy and F5 Load balancer....


 I'm no expert at ADFS and/or Certificates.... so feel free to correct me at various places.

You can use self signed certs for Token signing, however at startup i had to use the same SSL for all.
I started off with a self signed SSL cert to test my setup. then replaced it with 3rd party.

Would recommend checking out SQL database options before installation, to plan. I didn't have a need for SQL database as oppose to WID.

I have followed a articles from Technet and other various blogs, the links to these articles is at the bottom... 










ADFS Implementation & Installation guide
Contents


ADFS SERVERS:

Name
Location
IpAddress
Subnet Mask
Gateway
FIRSTINTERNALSERVER
SER1
10.48.9.123
255.255.252.0
10.48.9.1
SECONDINTERNALSERVER
SER2
10.48.10.123
255.255.252.0
10.48.10.1
FIRSTPROXYSERVER
SER1
172.16.200.123
255.255.254.0
172.16.200.1
SECONDPROXYSERVER
SER2
172.16.201.123
255.255.254.0
172.16.201.1
ADFS VIP INTERNAL
LOAD BALANCED
10.48.9.233


ADFS VIP DMZ
LOAD BALANCED
172.16.200.233




DNS CONFIGURATION:

Internal DNS =   adfs.fabrikam.com 10.48.9.233
External DNS =  adfs.fabrikam.com  201.204.193.84

SSL CERTIFICATE:

Obtained SSL certificate from from a 3rd party CA for adfs.fabrikam.com  

Service Account Setup for ADFS:

  1. Create a dedicated user/service account in the Active Directory forest that is located in the identity provider organization. This account is necessary for the Kerberos authentication protocol to work in a farm scenario and to allow pass-through authentication on each of the federation servers. Use this account only for the purposes of the federation server farm.
The account svc_adfs has been created for this purpose
  1. Edit the user account properties, and select the Password never expires check box. This action ensures that this service account's function is not interrupted as a result of domain password change requirements.




  1. Because the application pool identity for the AD FS AppPool is running as a domain user/service account, you must configure the Service Principal Name (SPN) for that account in the domain with the Setspn.exe command-line tool. Setspn.exe is installed by default on computers running Windows Server 2008. Run the following command on a computer that is joined to the same domain where the user/service account resides:
  2. setspn -a host/

For example, in a scenario in which all federation servers are clustered under the Domain Name System (DNS) host name fs.fabrikam.com and the service account name that is assigned to the AD FS AppPool is named adfs2farm, type the command as follows, and then press ENTER:
setspn -a host/adfs.fabrikam.com svc_adfs

Installing Federation Server Farm:


Download ADFS 2.0 setup file


First server in the ADFS Farm:

·         When you launch the install program, click Next.



·         Accept the license and click Next.


·         On the Server Role screen, choose Federation Server and click Next.


·         The wizard will automatically install the required prerequisites.  Click Next to begin the installation.


·         When the installation is complete,  uncheck “Start the AD FS 2.0…..”


·         Install the Godaddy certificate for adfs.fabrikam.com to local computer account.
·         On IIS make sure the default Website has a 443 binding and set to use the adfs.fabrikam.com certificate.

Configuring AD FS

Now that we have the certificate installed, we can start the AD FS configuration. To launch the AD FS configuration wizard, just go into Administrative Tools and click on AD FS 2.0 Management.



·         When the AD FS Management Console opens, click the AD FS 2.0 Federation Server Configuration Wizard Link.



·         Select the option to Create a new Federation Service




·         On the next screen select New federation server farm. 




On the Federation Service name, choose the adfs.fabrikam.com certificate to use.




You must then specify a Service Account in Active Directory that will be used by AD FS.
Service account: Svc_adfs




On the Summary Screen review the changes that will be made and click next to begin the configuration.




When the installation is complete, click Close.






Second server in the ADFS Farm:

·         Install ADFS 2.0 using the setup file.
·         Before configuring the second node, export the Export the cert from the first ADFS Server in the Farm. NOTE: This setup is very important, as I was getting Thumbprint errors for the SSL cert while setting up the second node without following the steps below…
1. Open the Certificate MMC console.
·         Log on to the original ADFS server which contains the service communications certificate with the private key.
·         Open the Start Menu and type “MMC” in the search box and press enter.
·         When the console opens click “File” and select “Add/Remove Snapin”.
·         Select “Certificates” from available snap ins and click the “Add” button to move to the “Selected Snapins” window and click “OK”.
·         When the “Certificate Snap-in” windows appears, select the “Computer Account” radio button and click “Next”.
·         On the “Select Computer” window, select the “Local Computer” radio button.
·         You will now see that it has been added to the selected snap-ins.  Click “OK”.
2. Now that you have the local certificate MMC open you can start to Export the cert.
·         Expand “Certificates (Local Computer)” then expand “Personal” and highlight “Certificates”.
·         Right click the certificate to be exported (in my case adfs.pipe2text.com), select “All Tasks” then “Export” from the menu.
·         Click “Next” on the “Welcome to the Certificate Export Wizard” screen.
·         On the “Export Private Key” screen Select “Yes, Export Private Key” and click “Next”.
·          On the “Export File Format” screen Select the “Personal Information Exchange = PKCS #12 (.PFX)” radio button and Check off “Include all certificates in the certification path if possible” and “Export all extended properties”. Make sure “Delete the private key if export is successful” is deselected. Click “Next”.
·         On the “Password” screen, enter a password and make note of it (This is the password you will use when importing the cert to the new server).
·         On the “File to Export” enter a name and location for the file and click “Next”.
·         On the “Completing the Certificate Wizard” screen review your settings and Click “Finish”.
·         Retrieve the cert file and copy it to the new ADFS server you will be adding to your farm.

·         Use the previously saved certificate
With the Certificate name:
ADFS_FirstInternalServer.pfx
·         Import the above certificate to Localcomputer\personal\certificates
·         Bind the Imported Cert to the Default Website
1. Open the IIS Manager and right click the “Default Website” and select “Edit Bindings”.
2. Under “Type” choose “https” and under “SSL Certificate” choose the cert that you imported and click “OK”.
3. You will now see “https” and “443″ in the list of site bindings. Click “Close”.

·         Launch the ADFS Configuration Wizard
·         On the Welcome page, verify that Add a federation server to an existing Federation Service is selected, and then click Next.
·         If the AD FS database that you selected already exists, the Existing AD FS Configuration Database Detected page appears. If that occurs, click Delete database, and then click Next
·         On the Specify the Primary Federation Server and Service Account page, under Primary federation server name, type the computer name of the primary federation server in the farm, and then click Browse. In the Browse dialog box, locate the domain account that is used as the service account by all other federation servers in the existing federation server farm, and then click OK. Type the password and confirm it, and then click Next:
·         On the Ready to Apply Settings page, review the details. If the settings appear to be correct, click Next to begin configuring AD FS with these settings.
·         On the Configuration Results page, review the results. When all the configuration steps are finished, click Close to exit the wizard.

Installing ADFS Proxy Server:


·         Run setup for ADFS 2.0 and install ADFS
·         Import the certificate exported earlier from the Federation server to Localcomputer\personal\certificates
·         Bind the Imported Cert to the Default Website
1. Open the IIS Manager and right click the “Default Website” and select “Edit Bindings”.
2. Under “Type” choose “https” and under “SSL Certificate” choose the cert that you imported and click “OK”.
3. You will now see “https” and “443″ in the list of site bindings. Click “Close”.
·         Run the ADFS Configuration Wizard.
·         On the Welcome page, click Next.
·         On the Specify Federation Service Name page, under Federation Service name, type “adfs.fabrikam.com”
·         Uncheck Use an HTTP proxy server when sending requests to this Federation Service check box, under HTTP proxy server address type the address of the proxy server, click Test Connection to verify connectivity, and then click Next.
·         When you are prompted, enter ‘fabrikam\svc_adfs’ and password.
·         On the Ready to Apply Settings page, review the details. If the settings appear to be correct, click Next to begin configuring this computer with these proxy settings.
·         On the Configuration Results page, review the results. When all the configuration steps are finished, click Close to exit the wizard.

Failover for ADFS:


The WID database on the primary server is read/write and the WID database on the secondary server(s) are read-only. Changes made to the configuration are made only on the primary Federation Server and those changes are replicated (5 minutes interval by default) to the secondary servers via WID database synchronization.

In the event that the primary Federation Server becomes unavailable and will not be brought back online, the administrator needs to promote one of the secondary Federation Servers to primary for the farm.

·         Command to run on the secondary server which you want to make primary:
Add-PsSnapin Microsoft.Adfs.PowerShell
Set-AdfsSyncProperties -Role PrimaryComputer

Now that you have set a new Primary Federation Server, you need to configure the other Secondary Federation Servers to sync with the new Primary Federation Server

·         Command to run on the other farm member servers:
Add-PsSnapin Microsoft.Adfs.Powershell
Set-AdfsSyncProperties -Role SecondaryComputer -PrimaryComputerName {FQDN of the Primary Federation Server}



Error Fixes:


In the event you get an error message when try to start the ADFS service, like
"Windows could not start the AD FS 2.0 Windows Service service on Local Computer - Error 1053"

Try the fix below:
1. Navigate to C:\Program Files\Active Directory Federation Services 2.0
2. Find and open Microsoft.IdentityServer.Servicehost.exe.config file
3. Modify it as follows:
under tag, add this line:


So it would look like this:



 References:

When to Create a Federation Server Farm:
http://technet.microsoft.com/en-us/library/dd807062(v=ws.10).aspx

How to change ADFS Service communication certificate after initial installation:

http://social.msdn.microsoft.com/Forums/vstudio/en-US/acad4d8a-898a-4113-b608-bf322f45282e/how-to-change-adfs-service-communication-certificate-after-initial-installation

AD FS 2.0: How to Set the Primary Federation Server in a WID Farm (move ADFS role to another server) - Fatshark's Personal Blog

http://www.edunnewijk.nl/fatshark/index.php?/archives/465-AD-FS-2.0-How-to-Set-the-Primary-Federation-Server-in-a-WID-Farm-move-ADFS-role-to-another-server.html
Adding an additional ADFS Server to your ADFS Farm when using SQL for the Configuration Database

 http://pipe2text.com/?page_id=395


 Verifying ADFS Computer Settings and Connectivity:
http://technet.microsoft.com/en-us/library/cc778709(v=ws.10).aspx