Setting Up an MS Access 32 bit Data Source in ColdFusion

If you see this error when verifying an MS Access data source in ColdFusion Administrator:

Connection verification failed for data source: <data-source-name> java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][SequeLink Server]The specified data source is not defined.

The root cause was that: java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][SequeLink Server]The specified data source is not defined.

Then you need to follow these steps to properly set up an MS Access 32 bit Data Source on a 64 bit ColdFusion 10 installation running on Windows 2008 SP2 (because the add new data source function in ColdFusion 10 Admin doesn’t do correctly):

  1. Preparations
  2. Set up the 32 bit System DSN
  3. Create the Data Source in ColdFusion Administrator
  4. Verify Connection to Data Source

1 Preparations

Before we get started, here are some tips to ensure a successful setup:

  • If the Microsoft Access driver is not available, then please download and install it:
    • Copy this file to the server where ColdFusion is installed: AccessDatabaseEngine_x64.exe
    • Launch to install the driver using default location and settings
  • If the MS Access data source exists in ColdFusion Administrator, please remove it before creating it via the 32 bit ODBC Data Source Administrator
  • You will see following errors when the db subfolder in the ColdFusion installation is missing:
    • If data source deletion in ColdFusion Administrator results in a 500 Internal Server Error
    • java.io.FileNotFoundExcetption and file operation write error:
      An error occurred when performing a file operation WRITE on file C:\ColdFusion10\<instance-name>\db\slserver54\admin\<data-source-name>.inp
      The cause of this exception was: java.io.FileNotFoundExcetption: C:\ColdFusion10\<instance-name>\db\slserver54\admin\<data-source-name>.inp (The system cannot find the path specified).
    • If the db folder is missing, then copy the db folder into C:\ColdFusion10\<instance-name>. At the very least, you will need this subfolder and its contents: C:\ColdFusion10\<instance-name>\db\slserver54
    • If you have multiple ColdFusion instances, then a good place to look for a copy of the db folder is under the default cfusion instance, i.e. C:\ColdFusion10\cfusion\

2 Set up the 32 bit System DSN

To setup the 32 bit MS Access System DSN, follow these steps:

  • Log on to the server where ColdFusion is installed
  • Browse to C:\Windows\SysWOW64 and locate the 32 bit ODBC Data Source Administrator, i.e. odbcad32.exe
  • Right click this file and launch it as an administrator
  • In the ODBC Data Source Administrator dialogue, make the following selections:
    • Select the System DSN tab and click Add…
    • Select Microsoft Access Driver (*.mdb) from the drivers list and click Finish
    • In the Data Source Name input box, enter the exact data source name to be used in the ColdFusion Administrator (the name here must match)
    • Under Database, click Select… and browse to select the Microsoft Access database file, i.e. database-name.mdb
    • Click OK to close the dialogue
    • You should now see this new data source in the System DSN list
  • Confirm an entry has been added to the registry in the 32 bit node as follows:
    • Click Start and type regedit in the search box and then press Enter
    • Browse to HKEY_LOCAL_MACHINE > SOFTWARE > Wow6432Node > ODBC
    • Expand ODBC.INI node and you should see the new data source listed here

3 Create the Data Source in ColdFusion Administrator

Follow these steps to create the Microsoft Access data source in the ColdFusion Administrator:

  • Log on to the ColdFusion Administrator
  • Click Data Sources under the Data & Services section on the left pane
  • Check to see if this data source already exists. If it does, and there isn’t a registry entry for it, then delete it first before continuing.
  • In the Add New Data Source section at the top, enter and select the following:
    • Data Source Name (this must be the same name used when creating the 32 bit System DSN)
    • Driver: select Microsoft Access
    • Click Add
  • For Database File, enter the full path and file name of the Microsoft Access database (or browse to select it from the file system)
  • Leave Use Default User Name checked
  • Click Show Advanced Settings and make the following changes:
    • For Allowed SQL, de-select the following:
      • CREATE
      • DROP
      • ALTER
      • GRANT
      • REVOKE
    • Tick Enable connection validation
  • Click Submit to create this new data source
  • You will see the following error messages:
    Unable to update the NT registry.
    Variable DRIVERPATH is undefined.
  • Ignore these error messages. An entry in the registry would have been created.
  • To confirm an entry has been added to the registry in the 64 bit node, do this:
    • Click Start and type regedit in the search box and press Enter (if not already opened)
    • Browse to HKEY_LOCAL_MACHINE > SOFTWARE > ODBC
    • Expand ODBC.INI node and you should see the new data source listed here
  • Return to the ColdFusion Administrator and click Data Sources under the Data & Services section on the left pane
  • Find the new data source that you have just created, and click the tick icon to the left of the data source name to verify it.
  • You should see OK in the status column (right most) if everything is good.

4 Verify Connection to Data Source

As a final check to ensure that we can access this new data source, let’s do the following:

  • Create a ColdFusion file (.cfm) and enter the following:
    <!doctype html>
    <html>
    <head>
       <meta charset="utf-8">
       <title>MS Access Datasource Check</title>
    </head>
    <body>
       <h1>MS Access Datasource Check</h1>
       <cfquery name="qCheckDS" datasource="data-source-name">
          SELECT now() AS dt
       </cfquery>
       <cfdump var="#qCheckDS#" label="qCheckDS">
    </body>
    </html>
    
  • Save it somewhere on your website and browse to it.
  • If data source connection is successful, you will see this dump, that is, something similar to this:

Congratulations! You’ve just successfully created an MS Access 32-bit data source on a 64-bit ColdFusion 10 installation.

Advertisements

How to create a website system maintenance page and redirect

Let’s say we are performing a system maintenance task on the database server but not the web server will not be affected. Given that our websites are database-driven and use this database server, we want to redirect all website visits to a maintenance page during the system maintenance period.

Here are the steps:

  1. Create the site maintenance page
  2. Create a redirect rule using the IIS URL Rewrite
  3. Activate the rule at system maintenance time

Create the site maintenance page

1 Create an HTML page with the following information

  • Page title
  • Brief description of maintenance window
  • Time service resumes

Include the style inline on the page, and save this page as maintenance.html on the website root. Here’s a sample:

2 Creating the IIS URL Rewrite Redirect Rule

Let’s use the IIS Rewrite Module to create a rule to redirect all requests to the website to a maintenance page that we created in the step above:

  • Logon to the IIS Manager
  • Select the site and then URL Rewrite
  • Add Rule(s)…
  • Blank Rule under Inbound Rules
  • Enter the following for this new rule:
    • Name: Site Maintenance
    • Expand the Match URL section and enter or select the following:
      • Requested URL: Matches the pattern
      • Using: Regular Expressions
      • Pattern: (.*)
        We want to match all requests coming to the site, hence (.*)
      • Ensure Ignore case is checked
    • Expand the Conditions section and enter or select the following:
      • Logical Group: Matches All
      • Click Add and add these conditions:
        • Exclude maintenance (we want to show the maintenance.html page)
          • Condition input: {REQUEST_URI}
          • Check if input string matches: Does not match the pattern
          • Pattern: maintenance
        • Exclude images (we want to show the site banner images)
          • Condition input: {REQUEST_URI}
          • Check if input string matches: Does not match the pattern
          • Pattern: images
    • Expand the Actions section and enter or select the following:
      • Action type: Redirect
      • Redirect URL: http://<domain-name>/maintenance.html
        This is the html page named maintenance.html that you created in the step above.
      • Uncheck Append query string
      • Redirect type: Temporary (307)
  • Ensure this rule is the first one for the website (as we do not want to process any other rules if they exist). If not, move it to the top.
  • Test this new rule (preferably on a non-production server first)

3 Activate the rule at system maintenance time

On the day of the system maintenance, activate the rule using these steps:

  • Logon to the IIS Manager
  • Select the site and then URL Rewrite
  • Select the Site Maintenance rule
  • Click Enable Rule under Inbound Rules in the Actions right pane

Once maintenance is done, don’t forget to log back to IIS Manager and disable the Site Maintenance IIS Rewrite rule.

 

 

ColdFusion 11: Connection verification failed for data source

After upgrading to ColdFusion 11, verifying a data source in ColdFusion Admin (especially those where you need to specify the instance name as part of the server, i.e. Server: server-name/instance-name) will result in the following error thrown:

Connection verification failed for data source: <data-source-name>
java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Conflicting connection information. When the instance name is specified, it is invalid to specify the port number.

The root cause was that: java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Conflicting connection information. When the instance name is specified, it is invalid to specify the port number.

Follow these steps to resolve the above error:

  1. Edit the data source
  2. Expand Show Advanced Settings
  3. In the Connection String box, enter: allowPortWithNamedInstance=true
  4. Click Submit
  5. You should now see the message: data source updated successfully

Repeat the above steps for all data sources with the same entry for its Server, i.e. those with values of server-name/instance-name.

Upgrading to ColdFusion 11 from 10

Upgrading to ColdFusion 11 from 10, on the same machine where ColdFusion 10 exists, is simple and error-free with careful planning. Here are the steps on how to successfully upgrade to Adobe ColdFusion 11 from 10:

  • Download ColdFusion 11 from Adobe (need to sign to Adobe, sign up first if this is your first time)
  • Run as administrator ColdFusion_11_WWEJ_win64.exe to kick off the ColdFusion 11 installer.
  • Follow the installation wizard to complete the first stage of the installation
  • Launch the Configuration Wizard to complete the second stage of the installation

Environment

  • Windows 7 Professional (should also work on Windows 2008) 64 bit
  • Existing install of ColdFusion 10
  • IIS web server

Windows Features

Before you launch the ColdFusion installer, please ensure you turn on the following Windows features (in particular the Application Development features):

  • ASP.NET
  • CGI
  • ISAPI Extensions
  • ISAPI Filters

Please refer to web page for more detailed instruction: https://msdn.microsoft.com/en-us/library/hh167503(v=nav.70).aspx

Here are some screenshots of the above selections from Windows Server 2012 R2:

Installation Steps

Here are the screenshots from the stage one installation wizard:

coldfusion11-install-local-1

coldfusion11-install-local-3

Enter the serial number if you have one or later after the install. You can also select the 30-day trial as indicated above. After 30 days, it will revert to the Developer Edition if you don’t enter a serial number.

coldfusion11-install-local-4

coldfusion11-install-local-5

Development Profile:
Use this profile only for development purposes. Note that features like Server Debugging and RDS are enabled by default for this profile.

Production Profile:
Use this profile for production purposes. All debug features and RDS are disabled for this profile.

Production Profile + Secure Profile:
Use this profile for a highly-secure production deployment that will allow a more fine-grained secure environment.
For details, see the secure profile guide (http://www.adobe.com/go/cf11_secureprofile).

Allowed admin IPs are the client IP addresses that can access the ColdFusion administrator.
They can be a comma separated list of IP addresses (for example, 11.181.28.54, 11.180.26.32, etc.).
IP addresses can range from 10-30, or * wild cards. Both IPv4 and IPv6 addresses are supported.

When the installation completes, please lock down your Server as per the guidelines provided in the ColdFusion Lockdown Guide (http://www.adobe.com/go/cf11-lockdown-guide).

coldfusion11-install-local-6

Only select the components you need. Here some more details when you mouse over them:

ODBC Service
Lets ColdFusion connect to ODBC data sources like Microsoft Access

Solr Service
Lets you use the Solr full text search capabilities using the cfsearch, cfindex and cfcollection tags.

PDFG Service
A webkit implementation for the cfhtmltopdf tag. This service can be use for handling PDF generation on the server.

Admin Component for Remote Start/Stop
The admin component for remotely starting/stopping the ColdFusion Server through ColdFusion Builder or Server Manager

.NET Integration Services
Lets you invoke .NET objects and assemblies from your ColdFusion applications

coldfusion11-install-local-7

coldfusion11-install-local-8

coldfusion11-install-local-9

coldfusion11-install-local-10

coldfusion11-install-local-11

I know the screenshot indicates All IIS websites was selected. However if you are installing on a server where you have an existing ColdFusion 10 installation, it is best to select Configure specific IIS website or another web server. Then click Add to create a connection for a temporary IIS website for ColdFusion 11. So prior to installation, please ensure you setup a temporary IIS website for this purpose. It can be removed afterward.

If you have an existing ColdFusion 10 installation, and selected All IIS websites or a specific IIS website which is currently configure to connect to ColdFusion 10, it will failed at the Configuration Wizard step (stage two of the installation) with an error similar to this:

HTTP Error 500.19 – Internal Server Error:
The requested page cannot be accessed because the related configuration data for the page is invalid

coldfusion11-install-local-18

The above error assume you selected Default Web Site as the specific website to connect for ColdFusion 11. This Default Web Site is also currently connected to ColdFusion 10. So either disconnect it from ColdFusion 10 first or just create a temporary IIS website to use for connecting to ColdFusion 11.

coldfusion11-install-local-12

Enter a strong password for the administrator. The requirements are:

  • at least 8 characters
  • 1 letter in upper case
  • 1 letter in lower case
  • 1 numeric
  • 1 special character

coldfusion11-install-local-13

coldfusion11-install-local-14

coldfusion11-install-local-16

coldfusion11-install-local-17

Launch the wizard and log in as the administrator to complete the configuration. In ColdFusion 11, there an option to import configurations from ColdFusion 10 if you’re installing on the same server machine. This step can be skipped if you like, but you will need to deploy the ColdFusion archives to restore the configurations you have saved after the configuration wizard completes successfully.

If you’ve made this far, then congratulations! You’ve successfully installed ColdFusion 11 on the server.

To complete the upgrade and transfer all website to ColdFusion 11, you will need to use the Web Configuration Tool to remove ColdFusion 10 connections and then add the new ColdFusion 11 connections.

Remember, if you create ColdFusion instances, you will need to run the Web Configuration Tool from their respective location, i.e. launch as administrator wsconfig.exe from <instance-name>/runtime/bin/wsconfig.exe.

Once the connections are all done, browse your website and ensure that it is working as expected.

Congratulations! You’ve upgraded ColdFusion 10 to 11.

SharePoint 2010: Local Activation Permission – DistributedCOM Error

I am performing routine checks of the SharePoint 2010 server logs, and see many of these event ID 10016 DistributedCOM error messages in the Windows System log and wonder what could be causing them. Checking the Application log and I also see MsiInstaller warning messages at around the same time related to SharePoint 2010.

The error message for event ID 10016:

The machine-default permission settings do not grant Local Activation permission for the COM Server application with CLSID {000C101C-0000-0000-C000-000000000046} to the user domain\sp-farm-account SID (guid) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.

Here’s a screen grab of the error from the System log:

distributedCOM-system-error-1

After a bit of research online, it turns out that the SharePoint 2010 Product Version Job runs at 12.45am every day. And at precisely that time every day, there were MsiInstaller warnings in the Application log and errors in the System log (event ID 10016).

So from what I have read online, here are the steps to resolve the event 10016 error that relates to distributedCOM:

  1. On the server where the above error occur, click Start > Run > type regedit to acess the registry
  2. Copy the GUID from System log General tab, i.e. 000C101C-0000-0000-C000-000000000046
  3. In the registry editor, click to select the top most branch of the registry – Computer
  4. From the menu, Edit > Find, paste in the GUID. It’ll stop at the application entry, i.e. AppID sub-branch. Note the application name on the right side pane.
  5. Start > Administrative Tools > Component Services
  6. Expand Component Services, Computers, My Computer, DCOM Config. Scroll down and find the application ( sometimes it is displayed simply as a CLSID)
  7. Right-click on the application (or CLSID 000C101C-0000-0000-C000-000000000046) noted in the previous step, select Properties and select the Security tab
  8. Ensure that the Customize radio button is selected, then click Edit in the Launch and Activation Permissions* section
  9. Add the SharePoint farm account specified in the System log error message, giving it Local Launch and Local Activation permssion (and in some requirements – remote launch / activate permission). Click OK, then OK again.
  10. Restart IIS

* If the radio buttons are greyed out in the Launch and Activation Permissions section of the Security tab, then you need to change the ownership of this registry key by following these steps:

  1. Return to the registry windows you open previously (or repeat steps 1 to 4 above)
  2. Right click on the GUID for the found application that was having issue and select Properties
  3. Click Advanced, and then the Owner tab
  4. Change the owner to local Administrators group. Click OK.
  5. Click on the local Administrators group and grant it Full Control permissions. Do not change the permissions for the TrustedInstaller.
  6. Close Component Services window and re-open it

Now return to step 8 above to grant the Launch and Activation Permissions.

Reference and full credits to:

SharePoint 2010: Application Server Admin Job Failed for Service Instance

After running the PowerShell script to complete the configuration of the Super User and Super Reader account, and rebooting the server, you may see the following error in the Windows Application log on your SharePoint 2010 servers:

Application Server Administration job failed for service instance Microsoft.Office.Server.Search.Administration.SearchServiceInstance (38410267-e262-4418-b67d-36516a88bb4b)

Reason: Object ede471fa-669d-444b-8f26-90e4dead5302-query-0 not found.

Here’s a screen capture of the error in the Application log:

app-server-admin-job-failed-service-instance

The file system cache on the SharePointer server on which the timer service is running needs to be cleared. Here are the steps as directed by Microsoft to resolve the above error:

  1. Stop the SharePoint 2010 Timer service
  2. Navigate to the cache folder
    In Windows Server 2008, the configuration cache is in found here:
    Drive:\ProgramData\Microsoft\SharePoint\Config
    Then locate the GUID folder that has the cache.ini file
    (Note: The ProgramData folder may be hidden. To view the hidden folder, change the folder options as required)
  3. Back up the cache.ini file.
  4. Delete all the XML configuration files in the GUID folder. Do this so that you can verify that the GUID folder is replaced by new XML configuration files when the cache is rebuilt.
  5. When you empty the configuration cache in the GUID folder, make sure that you do not delete the GUID folder and the Cache.ini file that is located in the GUID folder.
  6. Double-click the cache.ini file.
  7. On the Edit menu, click Select All. On the Edit menu, click Delete. Type 1, and then click Save on the File menu. On the File menu, click Exit.
  8. Start the SharePoint 2010 Timer service
    Note:  The file system cache is re-created after you perform this procedure. Make sure that you perform this procedure on all servers in the server farm.
  9. Make sure that the cache.ini file in the GUID folder now contains its previous value. For example, make sure that the value of the cache.ini file is not 1.

 

SharePoint 2010 – Object Cache Super User Not Configured

Do you see the following warning in the Windows Application log on your SharePoint 2010 servers:

Object Cache: The super user account utilized by the cache is not configured. This can increase the number of cache misses, which causes the page requests to consume unnecessary system resources.

Here’s a screen grab from the Application log to illustrate:

sharepoint-2010-object-cache-super-user-warning

Then here are the steps to resolve and banish the above warning:

First, create the user accounts by using Central Administration

  1. Verify that you have the following administrative credentials:
    • To create the user accounts in Central Administration, you must be a member of the Farm Administrators group on the computer that is running the SharePoint Central Administration Web site.
  2. On the Central Administration Web site, in the Application Management section, click Manage web applications.
  3. Click the name of the Web application that you want to configure.
  4. On the Web Applications tab, in the Policy group, click User Policy.
  5. In the Policy for Web Application window, click Add Users.
  6. From the Zones list, select All zones, and then click Next.
  7. In the Users box, type the user name for the Super User account.
  8. Click the Check Names icon to ensure that the account name can be resolved by the authentication providers on the application server.
  9. In the Choose Permissions section, check the Full Control – Has full control box.
  10. Click Finish.
  11. Repeat Steps 5 – 8 for the Super Reader account.
  12. In the Choose Permissions section, check the Full Read – Has full read-only access box.
  13. Click Finish.
  14. Make note of how the names for the Object Cache Super Reader and Object Cache Super User accounts are displayed in the User Name column. The displayed strings will be different depending on whether you are using claims authentication for the Web application.

Then, add the user accounts to the Web application by using Windows PowerShell

  1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.
  2. Copy the following code and paste it into a text editor, such as Notepad:
    $wa = Get-SPWebApplication -Identity “”
    $wa.Properties[“portalsuperuseraccount”] = “<SuperUser>”
    $wa.Properties[“portalsuperreaderaccount”] = “<SuperReader>”
    $wa.Update()
  3. Replace the following placeholders with values:
    • <WebApplication> is the name of the Web application to which the accounts will be added.
    • <SuperUser> is the account to use for the Super User account as you saw it displayed in the User Column field mentioned in Step 14 of the previous procedure.
    • <SuperReader> is account to use for the Super Reader account as you saw it displayed in the User Column field mentioned in Step 14 of the previous procedure.
  4. Save the file, naming it SetUsers.ps1
    You can use a different file name, but you must save the file ANSI-encoded as a text file whose extension is .ps1.
  5. Close the text editor.
  6. On the Start menu, click All Programs.
  7. Click Microsoft SharePoint 2010 Products and then SharePoint 2010 Management Shell.
  8. Change to the directory where you saved the file.
  9. At the Windows PowerShell command prompt, type the following command: ./SetUsers.ps1
  10. Restart Internet Information Services (IIS) via a command prompt (or IIS management console):
    C:\> iisreset /noforce