SOLVING AUTHENTICATION ERRORS ON MS SQL WHILE SETTING UP XENDESKTOP 7.6

Source: My Virtual Vision by Kees Baggerman

DuringSQL a lab setup of XenDesktop 7.6 I used a Microsoft SQL 2008 R2 instance which I installed before while setting up the rest of my lab environment. While the database setup worked seamlessly for other environments it seemed that I couldn’t access the SQL server from the XenDesktop Setup wizard.

I first tried the obvious things, using a service account didn’t help. After that I tried the SA account (just to see if it was an actual issue with the rights on the service account) but that didn’t work either.

The issue:

I couldn’t create the database from the XenDesktop wizard, I tried several accounts but they couldn’t connect to the database or didn’t have the rights the access the database server.

Troubleshooting:

Apparently changing the user didn’t had the effect I wanted, I logged on to the SQL just to make sure my SA password was still valid and it was because I was able to logon to the SQL management Studio with the SA credentials. Because I was already logged on the SQL server I just went on opening the event viewer and found the following errors:

Screen Shot 2014-11-10 at 10.29.05

SQL

My friend Google then found the following topic:SSPI handshake failed with error code 0x8009030c, which led me to the How to Configure an SPN for SQL Server Site Database Servers. It seems that during the installation of SQL the SPNs for SQL server weren’t registered.

Solving this issue:

With the command ‘setspn -L %hostname%’ you can list the SPNs that are registered for a certain server.

v_3_Screen Shot 2014-11-10 at 10.29.43_v2

When I did this for my SQL server it didn’t list the SQL services so I had to register the SPN manually. Again I googled and found the following article:Register a Service Principal Name for Kerberos Connections.

This article described the following switches to manually register the SPN:

To register the SPN manually, the administrator must use the Setspn.exe tool that is provided with the Microsoft Windows Server 2003 Support Tools. For more information, see the Windows Server 2003 Service Pack 1 Support Tools KB article.
Setspn.exe is a command line tool that enables you to read, modify, and delete the Service Principal Names (SPN) directory property. This tool also enables you to view the current SPNs, reset the account’s default SPNs, and add or delete supplemental SPNs.
The following example illustrates the syntax used to register manually register an SPN for a TCP/IP connection.
setspn -A MSSQLSvc/myhost.redmond.microsoft.com:1433 accountname
Note If an SPN already exists, it must be deleted before it can be reregistered.You do this by using the setspn command together with the -D switch. The following examples illustrate how to manually register a new instance-based SPN.For a default instance, use:
setspn -A MSSQLSvc/myhost.redmond.microsoft.com accountnameFor a named instance, use:
setspn -A MSSQLSvc/myhost.redmond.microsoft.com:instancename accountname

So I ran the command:

‘setspn -a MSSQLSvc/SQL001:1433 administrator’

The following screen output appeared:

Screen Shot 2014-11-10 at 10.30.01

After I registered the SPN for the SQL Server I listed the SPNs of the server again and the SQL service was registered. After a reboot I was able to connect to the database from the XenDesktop wizard.

Decommissioning an Old Certification Authority without affecting Previously Issued Certificates and then Switching Operations to a New One

// // <![CDATA[
try { jQuery.telligent.evolution.site.configure({baseUrl:'/',monthNames:['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'],dayNames:['Sun','Mon','Tue','Wed','Thu','Fri','Sat'],authorizationCookieName:'AuthorizationCookie',defaultErrorMessage:'An error occurred. Please try again or contact your administrator.',defaultMultiErrorMessagePrefix:'The following errors occurred: ',silverlightFileUploadEnabled:true});
jQuery.extend($.fn.evolutionUserFileTextBox.defaults,{removeText:'Remove',selectText:'Select/Upload…',noFileText:'No File Selected'});
jQuery.telligent.evolution.navigationConfirmation.configure({message:'==============================rnUnless you save before leaving this page, you will lose any changes you have made.rn=============================='});
jQuery.telligent.evolution.validation.registerExtensions({passwordInvalidMessage:'Password contains invalid chars …',passwordRegex:'^.*$',emailInvalidMessage:'Your email address is invalid.',emailRegex:'^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$',usernameInvalidMessage:'Your sign in name does not meet the requirements for this site.',usernameRegex:'^[a-zA-Z0-9_\- @\.]+$',emailsInvalidMessage:'One or more emails is invalid',urlInvalidMessage:'URL not in correct format',urlRegex:'^((http|https|mailto|mms):|/|#|~/)'});
jQuery.extend(jQuery.fn.evolutionLike.defaults,{likeText:'Like',unlikeText:'Unlike',modalTitleText:'People who like this',modalShowMoreText:'Show More',whoLikesOtherText:'{user_display_name} likes this’,whoLikesOtherTwoText:’{user_display_name} and 1 other like this’,whoLikesOtherMultipleText:’{user_display_name} and {count} others like this’,whoLikesAccessingText:’You like this’,whoLikesAccessingTwoText:’You and 1 other like this’,whoLikesAccessingMultipleText:’You and {count} others like this’});
jQuery.extend(jQuery.fn.evolutionInlineTagEditor.defaults,{editButtonText:’Edit tags’,selectTagsText:’Select tags’,saveTagsText:’Save’,cancelText:’Cancel’});
jQuery.extend(jQuery.fn.evolutionStarRating.defaults,{titles:[‘Terrible’,’Poor’,’Fair’,’Average’,’Good’,’Excellent’],ratingMessageFormat:’Average rating: {rating} out of {count} ratings.’});
jQuery.extend(jQuery.fn.evolutionModerate.defaults,{moderateLinkText:’moderate’,reportLinkText:’Flag as spam/abuse’,reportedLinkText:’Flagged as spam/abuse’,reportedNotificationMessageText:'{NAME}’s post has been flagged. Thank you for your feedback.’});
} catch(e) { };
// ]]>// // // //
Decommissioning an Old Certification Authority without affecting Previously Issued Certificates and then Switching Operations to a New One – Windows PKI blog – Site Home – TechNet Blogs

Decommissioning an Old Certification Authority without affecting Previously Issued Certificates and then Switching Operations to a New One

Jonathan Stephens posted an excellent Blog about this topic; however, it didn’t include the steps. As a result, I decided to type this Blog detailing the steps required. The following assumptions have to be met before proceeding with these steps:

1- There is a new valid Certification Authority configured

2- There is a new distribution point configured for AIA and CDP locations named http://crl.contoso.com/CertData

Steps:

1- Logon to the old Enterprise Certification Authority as an Enterprise Administrator.

2- Identify the AIA and CDP distribution points

  1. a. Open the Certification Authority Console
  2. b. Right click the Certification Authority name and click Properties
  3. c. Click the “Extensions” tab
  4. d. Document the distribution points configured for CRL Distribution Point (CDP) – as an example http://<serverDNSnname>/CertEnroll/<CANAME>CRLNameSuffix><DeltaCRLAllowed&gt;.crl which refers to local IIS installed on the server, or http://pki.contoso.com/Certenroll/<CAName><CRLNameSuffix><DeltaCRLAllowed>.crl

Note: Ignore the LDAP and C:%windir% locations

  1. e. In the “Extensions” tab, select Authority Information Access (AIA) from the drop down menu
  2. f.  Document the distribution points configured for the AIA extensions – as an example http://<ServerDNSName>/Certenroll/<ServerDNSName>_<CAName><CertificateName&gt;.crt  which refers to the local IIS installed on the server or http://pki.contoso.com/Certenroll/<ServerDNSName>_<CAName><CertificateName>.crt

Note: Ignore the LDAP and C:%windir% locations

3- Disable Delta CRL and Issue a long Certificate Revocation List (CRL)

  1. a. Open the Certification Authority Console
  2. b. Right click “Revoked Certificates”, and then click “Properties”
  3. c. Uncheck “Publish Delta CRL”
  4. d. Change the “CRL publication Interval” to 99 years and then click OK
  5. e. Open the command line with elevated privileges
  6. f.  Run Certutil –crl  to issue a new Certificate Revocation List (CRL)

4- Copy the old Certification Authority’s certificate (CRT) and certificate revocation list (CRL) files to the server hosting website http://crl.contoso.com/CertData

  1. a. On the old Certification Authority, navigate to %windir%System32CertSrvCertEnroll
  2. b. Copy the Certification Authority’s certificate (CRT) and certificate revocation list (CRL) to the directory hosting http://crl.contoso.com/CertData

5- Redirect the Authority Information Access (AIA) and Certificate Revocation List (CRL) distribution points  of the old Certification Authority to http://crl.contoso.com/certdata

  1. a. This can be done using an IIS redirect, or a DNS CNAME redirect to redirect Authority information Access (AIA) and Certificate Revocation List (CRL) of the old Certification Authority documented in steps 2.d and 2.f to the new web server http://crl.contoso.com/certdata

6- Document and remove all  certificate templates available on the old Certification Authority to prevent it from issuing new certificates

  1. a. Open the command line with elevated privileges
  2. b. Run Certutil –catemplates > c:catemplates.txt  to document all available certificate templates at the old Certification Authority
  3. c. Launch the Certification Authority console
  4. d. Navigate to “Certificate Templates”
  5. e. Highlight all templates in the right pane, right click and then click “Delete”

At this point, the old Certification Authority can’t issue any certificates, and has all of its Authority Information Access (AIA) and Certificate Revocation List (CRL) redirected to a new web site http://crl.contoso.com/CertData The next steps will detail how to document the certificates issued by templates from the old Certification Authority and how to make them available at the new Certification Authority.

7- Identify and document the certificates issued based on certificate templates by sorting the Certification Authority database

  1. a. Highlight “Issued Certificates”
  2. b. Navigate to the right, and sort by “Certificate Templates”
  3. c. Identify the certificates issued by default certificate template types
  4. d. Identify the certificates issued by custom certificate templates – any template other than the default certificate templates mentioned earlier

8- Dump the certificates based on the default certificate template types:

  1. a. Open the command line with elevated privileges
  2. b. Run Certutil -view -restrict “Certificate Template=Template” -out “SerialNumber,NotAfter,DistinguishedName,CommonName” > c:TemplateType.txt
  3. c. Examine the output of c:TemplateType.txt and document all the certificates needing immediate action – i.e. requiring issuance from the new CA infrastructure if needed such as Web SSL.
  4. d. Consult with the application administrator using the certificates to determine the best approach to replace the certificates if needed

Note: Replace Template with the correct template name.

9- Dump the certificates based on the custom certificate template types:

  1. a. Open the Certification Authority Console
  2. b. Right click “Certificate Templates” and click “Manage”
  3. c. Double click the certificate template and click on “Extensions” tab
  4. d. Click on “Certificate Template Information”
  5. e. Copy the Object Identifier (OID) number – the number will look similar to 1.3.6.1.4.1.311.21.8.12531710.13924440.6111642.16676639.10714343.69.16212521.10022553
  6. f. Open the command line with elevated privileges
  7. g. Run Certutil -view -restrict “Certificate Template=OIDNumber” -out “SerialNumber,NotAfter,DistinguishedName,CommonName” > c:CustomTemplateType.txt

Note: Replace OIDNumber with the number identified in step 9.e

  1. h. Examine the output of c:CustomTemplateType.txt and document all the certificates needing immediate action – i.e. requiring issuance from the new CA infrastructure if needed such as custom SSL certificates.
  2. i. Consult with the application administrator using the certificates to determine the best approach to replace the certificates if needed

Note: You don’t need to take any action if the certificate was auto-enrolled because the certificate holder will renew the certificate when it expires from the new CA infrastructure.

10- Enable the Certificate Templates needed based on the results of steps 7-9 on the new Certification Authority

  1. a. Logon to the new Certification Authority as an Enterprise Administrator
  2. b. Right Click “Certificate Templates”, click “New” and then click “Certificate Template to Issue”
  3. c. Choose all the certificate templates needed in the “Enable Certificate Templates” window and click “OK”

11- <Optional> At this point you can uninstall the Certification Authority Role on the old Certification Authority

  1. a. Backup the old Certification Authority using the steps outlined in Disaster Recovery Procedures for Active Directory Certificate Services (ADCS)
  2. b. Uninstall Certificate Services from the old Certification Authority
  3. c. Decommission the server unless it is running other applications

12- Once all certificates are issued by the new infrastructure, you can safely remove all the Authority Information Access (AIA) and Certificate Revocation List (CRL) files from you infrastructure by following the steps in How to Decommission a Windows Enterprise Certification Authority and How to Remove All Related Objects and from the web server hosting http://crl.contoso.com

Amer F. Kamal

Senior Premier Field Engineer

Step-By-Step: Migrating The Active Directory Certificate Service From Windows Server 2003 to 2012 R2o

// // <![CDATA[
try { jQuery.telligent.evolution.site.configure({baseUrl:'/',monthNames:['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'],dayNames:['Sun','Mon','Tue','Wed','Thu','Fri','Sat'],authorizationCookieName:'AuthorizationCookie',defaultErrorMessage:'An error occurred. Please try again or contact your administrator.',defaultMultiErrorMessagePrefix:'The following errors occurred: ',silverlightFileUploadEnabled:true});
jQuery.extend($.fn.evolutionUserFileTextBox.defaults,{removeText:'Remove',selectText:'Select/Upload…',noFileText:'No File Selected'});
jQuery.telligent.evolution.navigationConfirmation.configure({message:'==============================rnUnless you save before leaving this page, you will lose any changes you have made.rn=============================='});
jQuery.telligent.evolution.validation.registerExtensions({passwordInvalidMessage:'Password contains invalid chars …',passwordRegex:'^.*$',emailInvalidMessage:'Your email address is invalid.',emailRegex:'^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$',usernameInvalidMessage:'Your sign in name does not meet the requirements for this site.',usernameRegex:'^[a-zA-Z0-9_\- @\.]+$',emailsInvalidMessage:'One or more emails is invalid',urlInvalidMessage:'URL not in correct format',urlRegex:'^((http|https|mailto|mms):|/|#|~/)'});
jQuery.extend(jQuery.fn.evolutionLike.defaults,{likeText:'Like',unlikeText:'Unlike',modalTitleText:'People who like this',modalShowMoreText:'Show More',whoLikesOtherText:'{user_display_name} likes this’,whoLikesOtherTwoText:’{user_display_name} and 1 other like this’,whoLikesOtherMultipleText:’{user_display_name} and {count} others like this’,whoLikesAccessingText:’You like this’,whoLikesAccessingTwoText:’You and 1 other like this’,whoLikesAccessingMultipleText:’You and {count} others like this’});
jQuery.extend(jQuery.fn.evolutionInlineTagEditor.defaults,{editButtonText:’Edit tags’,selectTagsText:’Select tags’,saveTagsText:’Save’,cancelText:’Cancel’});
jQuery.extend(jQuery.fn.evolutionStarRating.defaults,{titles:[‘Terrible’,’Poor’,’Fair’,’Average’,’Good’,’Excellent’],ratingMessageFormat:’Average rating: {rating} out of {count} ratings.’});
jQuery.extend(jQuery.fn.evolutionModerate.defaults,{moderateLinkText:’moderate’,reportLinkText:’Flag as spam/abuse’,reportedLinkText:’Flagged as spam/abuse’,reportedNotificationMessageText:'{NAME}’s post has been flagged. Thank you for your feedback.’});
} catch(e) { };
// ]]>// // // // //
Step-By-Step: Migrating The Active Directory Certificate Service From Windows Server 2003 to 2012 R2 – Canadian IT Professionals – Site Home – TechNet Blogs

Step-By-Step: Migrating The Active Directory Certificate Service From Windows Server 2003 to 2012 R2

MVP Dishan Francis MVP Dishan Francis

Windows_Server_2003_Certificate_Migration

As you may be aware, support for both Windows Server 2003 and 2003 R2 is coming to end on July 14th 2015. With this in mind, IT professionals are in midst of planning migration. This guide will provide steps on migrating AD CS from Windows Server 2003 to Windows Server 2012 R2.

In this demonstration I am using following setup.

Server Name Operating System Server Roles
canitpro-casrv.canitpro.local Windows Server 2003 R2 Enterprise x86 AD CS ( Enterprise Certificate Authority )
CANITPRO-DC2K12.canitpro.local Windows Server 2012 R2 x64

Step 1: Backup Windows Server 2003 certificate authority database and its configuration

1. Log in to Windows 2003 Server as member of local administrator group

2. Go to Start > Administrative Tools > Certificate Authority

clip_image002

3. Right Click on Server Node > All Tasks > Backup CA

clip_image004

4. Then it will open the “Certification Authority Backup Wizard” and click “Next” to continue

clip_image006

5. In next window click on check boxes to select options as highlighted and click on “Browse” to provide the backup file path location where it will save the backup file. Then click on “Next” to continue

clip_image008

6. Then it will ask to provide a password to protect private key and CA certificate file. Once provided the password click on next to continue

clip_image010

7. In next window it will provide the confirmation and click on “Finish” to complete the process

Step 2: Backup CA Registry Settings

1. Click Start > Run and then type regedit and click “Ok”

clip_image012

2. Then expand the key in following path HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesCertSvc

3. Right click on “Configuration” key and click on “Export”

clip_image014

4. In next window select the path you need to save the backup file and provide a name for it. Then click on save to complete the backup

clip_image016

Now we have the backup of the CA and move these files to the new windows 2012 R2 server.

clip_image018

Step 3: Uninstall CA Service from Windows Server 2003

Now we have the backup files ready and before configure certificate services in new Windows Server 2012 r2, we can uninstall the CA services from windows 2003 server. To do that need to follow following steps.

1. Click on Start > Control Panel > Add or Remove Programs
clip_image020

2. Then click on “Add/Remove Windows Components” button
clip_image022

3. In next window remove the tick in “Certificate Services” and click on next to continue
clip_image024

4. Once its completed the process it will give the confirmation and click on “Finish”
clip_image026

With it we done with Windows Server 2003 CA services and next step to get the Windows Server 2012 CA services install and configure.

Step 4: Install Windows Server 2012 R2 Certificate Services

1. Log in to Windows Server 2012 as Domain Administrator or member of local administrator group

2. Go to Server Manager > Add roles and features
clip_image028

3. It will open up “Add roles and feature” wizard and click on next to continue
clip_image030

4. Then next window select “Role-based or Feature-based installation” and click next to continue
clip_image032

5. From the server selections keep the default selection and click on next to continue
clip_image034

6. In next window click on tick box to select “Active Directory Certificate Services” and it will pop up with window to acknowledge about required features need to be added. Click on add features to add them
clip_image036clip_image038

7. Then in features section will let it run with default. Click next to continue
clip_image040

8. In next window, it will give brief description about AD CS. Click next to continue
clip_image042

9. Then it will give option to select roles services. I have selected Certificate Authority and Certification Authority Web Enrollment. Click next to continue
clip_image044

10. Since Certification Authority Web Enrollment selected it will required IIS. So next window it will give brief description about IIS
clip_image046

11. Then in next window it gives option to add IIS role services. I will leave it default and click next to continue
clip_image048

12. Next window will give confirmation about service install and click on “Install” to start the installation process
clip_image050

13. Once installation completes you can close the wizard.

Step 5: Configure AD CS

In this step will look in to configuration and restoring the backup we created.

1. Log in to server as Enterprise Administrator

2. Go to Server Manager > AD CS
clip_image052

3. In right hand panel it will show message as following screenshot and click on “More”
clip_image054

4. It will open up window and click on “Configure Active Directory Certificate Service ……”
clip_image056

5. It will open role configuration wizard, it gives option to change the credential, in here I already log in as Enterprise administrator so I will leave the default and click next to continue
clip_image058

6. In next window it asking which service you like to configure. Select “Certification Authority”, “Certification Authority Web Enrollment” options and click next to continue
clip_image060

7. It will be Enterprise CA so in next window select the Enterprise CA as the setup type and click next to continue
clip_image062

8. Next window select “Root CA” as the CA type and click next to continue
clip_image064

9. The next option is very important on the configuration. If its new installation we will only need to create new private key. But since it’s a migration process we already made a backup of private key. So in here select the options as highlighted in screenshot. Then click on next to continue
clip_image066

10. In next window click on “Import” button
clip_image068

11. In here it will give option to select the key we backup during the backup process from windows 2003 server. Brows and select the key from the backup we made and provide the password we used for protection. Then click ok
clip_image070

12. Then it will import the key successfully and in window select the imported certificate and click next to continue
clip_image072

13. Next window we can define certificate database path. In here I will leave it default and click next to continue
clip_image074

14. Then in next window it will provide the configuration confirmation and click on configure to proceed with the process
clip_image076

15. Once its completed click on close to exit from the configuration wizard

Step 6: Restore CA Backup

Now it’s comes to the most important part of the process which is to restore the CA backup we made from Windows Server 2003.

1. Go To Server Manager > Tools > Certification Authority
clip_image078

2. Then right click on server node > All Tasks > Restore CA
clip_image080

3. Then it will ask if it’s okay to stop the certificate service in order to proceed. Click ok
clip_image082

4. It will open up Certification Authority Restore Wizard, click next to continue
clip_image084

5. In next window brows the folder where we stored backup and select it. Then also select the options as I did in below. Later click next to continue
clip_image086

6. Next window give option to enter the password we used to protect private key during the backup process. Once its enter click next to continue
clip_image088

7. In next window click “Finish” to complete the import process
clip_image090

8. Once its completed system will ask if it’s okay to start the certificate service again. Please proceed with it to bring service back online

Step 7: Restore Registry info

During the CA backup process we also backup registry key. It’s time to restore it. To do it open the folder which contains the backup reg key. Then double click on the key.

1. Then click yes to proceed with registry key restore
clip_image092

2. Once completed it will give confirmation about the restore
clip_image094

Step 8: Reissue Certificate Templates

We have done with the migration process and now it’s time to reissue the certificates. I had template setup in windows 2003 environment called “PC Certificate” which will issue the certificates to the domain computers. Let’s see how I can reissue them.

1. Open the Certification Authority Snap-in

2. Right click on Certificate Templates Folder > New > Certificate Template to Reissue
clip_image096

3. From the certificate templates list click on the appropriate certificate template and click ok
clip_image098

Step 9: Test the CA

In here I already had certificate template setup for the PC and set it to auto enroll. For the testing purposes I have setup windows 8 pc called demo1 and added it to canitpro.local domain. Once it’s loaded first time in server I open certification authority snap in and once I expanded the “Issued Certificate” section I can clearly see the new certificate it issued for the PC.

clip_image100

So this confirms the migration is successful.