|By James Blaha||
|September 11, 2003 12:00 AM EDT||
In this article, I'll show you how to set up a ColdFusion 5 server and a Microsoft SQL Server 7.0 that will execute a DTS package through the ColdFusion server. The main objective is to create a DTS package that will result in file output that will be delivered to a network UNC path or mapped drive using a set of stored procedures executed by ColdFusion.
The configuration for this setup will also allow a ColdFusion server to properly propagate user rights across networked servers and domains for using shared access.
If you're new to DTS packages, I'll introduce them later. Using DTS packages helps leverage the power of SQL Server performing many easy tasks without having to do any ColdFusion coding. For example, you can have a DTS package query a bunch of tables and output the results to a Microsoft Access database, an Excel file, or simply place the information in some kind of text-delimited file.
Although this guide focuses on the use of a ColdFusion 5 server and SQL Server 7.0, you can easily apply this information to a ColdFusion MX server and SQL Server 2000.
This guide is designed for a broad spectrum of users ranging from the ColdFusion novice/enthusiast to the advanced application developer. It is also geared toward helping IT support staff and database administrators who may also play a role in the need to configure this setup, which will allow your ColdFusion server to become a DBMS-integrated powerhouse. My approach to this subject is simple; it will be taken from the top down.
What you'll learn along the way:
1. What is DTS?
2. How to configure ColdFusion server services.
3. How permissions are passed between the servers.
4. How to configure SQL Server database permissions and services.
5. How to map a network drive in Windows.
6. How to create a simple DTS package in SQL Server.
7. How to install and create stored procedures in SQL Server.
8. How to test configurations in both SQL Server and ColdFusion.
What Is DTS?
If you're not familiar with the Data Transformation Services (DTS) and why you should use the feature or what it can do for you, a brief explanation follows. For more information, reference the Microsoft Books Online for SQL Server, which installs with SQL Server.
The Data Transformation Services (DTS) feature is included with SQL Server and has an import and export wizard that allows you to easily import, export, validate, and transform data. DTS can also copy schema and data between relational databases.
A DTS package created by the DTS Import and DTS Export wizards can also be used to import, export, and transform data between a Microsoft SQL Server database and other data sources, including:
The DTS Import and DTS Export wizards allow the user to:
This article will show you how to create DTS packages and call them from within ColdFusion.
How to Configure ColdFusion Application Server
Starting with the basics, the ColdFusion server needs to have the right permissions to access the file(s) produced from the SQL Server output. This requires that your ColdFusion server service run under an account that has permissions to the UNC path or network drive mappings you wish to connect to, and also that it can read and write to that destination. The service is actually named ColdFusion Application Server in CF5 and ColdFusion MX Application Server in CFMX.
Read the Macromedia TechNote, "Running ColdFusion as a Specific User," www.macromedia.com/support/coldfusion/ts/ documents/tn17279.htm, for information on how to perform the ColdFusion service configuration. I will note that in our Windows 2000 Server environment there was no need to touch the Windows registry as suggested in the TechNote. We gave the local user account Administrator rights to the Windows server so it just propagated through the server.
Place the same local user account on all the servers involved and make sure that the accounts use exactly the same user name and password. Unless you have a domain ID that has access to all your other domains through a trusted domain account ID, using a local user account on the server is your best choice.
Figure 1 is a screen shot of the Windows Services window, where you change the server services logon account information for your server's service(s).
Start by opening your Windows service control panel. You need to edit the service for the "ColdFusion Application Server (or ColdFusion MX Application Server)." Double click on the service and select the tab for "Log On," then click browse, and select the local user account that has rights to the UNC or network drive mapping share locations that your ColdFusion Server needs to access. For example, this would be an account that has rights to the folder that your DTS packages will be outputting to. You may have to add this account to your Windows server if it's not already set up. Click "Apply" then "OK" and restart the service.
Figure 2 shows a basic flow of how permissions from the user IDs are passed for the processes involved in this configuration. These permissions can be tricky if you don't have a clear understanding of user rights and file sharing. The permissions are being passed from the user IDs that are attached to the services for ColdFusion and SQL Server.
When you are executing a DTS package directly from the SQL Server Enterprise Manager it normally uses the rights from your SQL Server Enterprise Manager that you logged in with and the current user account that you're logged in with on the Windows computer or server. This is the case only if you haven't modified your SQL Server services from the original installation. It's also why it may be a good idea to map your shares before you set up your connections or if you're just testing your production SQL Server from a remote computer that has the Enterprise Manager installed. It's not a good idea to use mapped drives in a production environment since they may increase the possibilities of a hacker gaining access to your data.
When ColdFusion tries to execute a CFFILE or CFDIRECTORY tag you need to have your ColdFusion Server service set up, which specifies the same user account on the ColdFusion service as the UNC path or mapping you wish to access. This is because ColdFusion passes the user ID of the ColdFusion Application Server (or ColdFusion MX Application Server) service to the folder UNC path or drive mapping via the SMB protocol. People with Linux SAMBA server experience can better explain this one. I can only tell you this is how it works! Check out www.samba.org for more information.
When your SQL Server has a DTS package executed through a scheduled job (not a ColdFusion scheduled task) the user rights from the SQLServer Agent are passed to the UNC path. When you execute a DTS package directly on the SQL Server, you are using the actual MSSQLServer service logon ID. Confusing, right?
Use a local server account on the ColdFusion Application Server (ColdFusion MX Application Server) service. It's highly recommended that you use a local server account instead of using a domain account ID for your Windows share(s) and the service for ColdFusion. By using a local account on the server, you can easily access other servers outside the domain of your ColdFusion server. The one exception here may be if you have a domain ID account that has access to all your other domains through a trusted domain account. (Explaining this further is beyond the scope of this article.)
Testing Your ColdFusion Application Server and Share Permissions
To test what you have done so far, use the test code shown in Listing 1 on your ColdFusion server. You'll need to edit the code for your UNC path or drive mappings, and you'll also need some file or set of files handy to test with. Any will do. In the example below, I'm using PDF files.
Warning: Always end your UNC DIRECTORY paths or DRIVE mapping locations with a backslash "\" in CF tags pointing to directories. Otherwise your permissions passed from the ColdFusion server service will pass only to the base folder and not propagate down to subfolders.
Once you have this piece of functionality working, you're ready to move on to the next set of steps.
Configuring SQL Server Database Permissions and Services
Complete this section logged onto the SQL Server as an "SA" account:
1. Using SQL Server "Enterprise Manager" your USER Login account for the local SQL Server has to have proper rights to the database to create a DTS package(s) on your SQL Server. You don't need server SA privileges for this, but you do need to have "db_owner" rights to create a DTS package! Set up a login account for ACCOUNT2 as shown in Figure 3.
A login account is how your ColdFusion server connects to a SQL Server through an ODBC connection. This is where your ODBC username and password come from when entered in the CF Admin.
2. On the SQL Server, the login account used for the ColdFusion ODBC account has to have EXECUTE rights on the extended stored procedures listed below, which are found in the SQL Server's master database.
Extended Stored Procedures
These rights are needed for OLE Automation that is performed in the stored procedures. You can grant "public" and "execute" or just give the login ID "execute" rights specifically.
Note: One alternative to executing a DTS package through a stored procedure would be the use of COM. If you're going to use only COM, these permissions aren't needed for the extended stored procedures. Please note that the testing for this article was performed on a ColdFusion 5 server. ColdFusion MX has been known to have COM issues, so beware.
For more information check out this link on OLE Automation: www.mssqlcity.com/Articles/General/OleAutSP.htm The stored procedures being used (see Figure 4) may already have been granted PUBLIC execute rights from your DBA, otherwise they need to be applied to your login ID.
3. Important note: When executing a DTS package in SQL Server that will be outputting information to a UNC path or network drive mapping from SQL Server, the following SQL Server services must have the proper permissions to your share(s).
A. SQLServer Agent: For the purpose of this guide, this service is primarily used when a scheduled SQL Server DTS package is executed and passes its user ID attached to the service.
B. MSSQLServer: Again, for the purpose of this guide, this is used when the DTS package is executed directly on the SQL Server, through a COM object, or when invoked through the stored procedure method that is using OLE Automation in the stored procedures we'll install later.
How to Map a Network Drive in Windows
You'll need to know how to map a network drive in Windows to a UNC path. If you're not familiar with the procedure, here's a quick guide.
Verify that you can access the share or UNC path from your Windows machine or server. Go to START>RUN and type in the UNC path to your share, e.g., \\MyServer\Folder. If you're logged in to your Windows system as ACCOUNT1 and the share only has rights for ACCOUNT2, you'll need to either have ACCOUNT1 added to the share or provide the password for ACCOUNT2. If you're passing a user ID other than the one you've used on your Windows logon, you'll need to map a network drive to the share. Please understand this is just for your ease of use and testing. Having a mapped drive can be a security risk, but it can also help provide a quick link to your data when testing.
Right click on "My Computer" and select "Map Network Drive..." (see Figure 5).
Once you enter your UNC path and select a drive letter, click on "different user name." You'll be prompted for the account user name you wish to pass. If you're using a domain ID the user name will be something to the effect of "MyDomainName\MyUserID" (see Figure 6).
That's it! Now you can access your share as easily as if it were a hard drive partition.
How to Create a Simple DTS Package in SQL Server
Complete this section logged on to SQL Server as the same account you've listed in the ColdFusion Admin datasource definition for the ODBC login account:
1. Open your SQL Server Enterprise Manager, and right-click in the table area of the sample "Northwind" database to generate a DTS package. Northwind is a default database that installs with SQL Server for testing. Select "All Tasks" and choose the "Export Data.." wizard (see Figure 7). Click "Next" when the popup window appears .
2. The option for "Use SQL Server authentication" should already be selected (see Figure 8).
Note: This user ID needs to be the same user name and password configured in your ColdFusion data-source definition for your ODBC connection to the SQL Server; it does not have to be the same as the account used to run the ColdFusion server service.
3. Select your file output destination preference and specify your UNC path for the output. As you can see in Figure 9, the server name is "SERVER." This is the same as \\MyServer in the examples above for creating a mapped drive to the share on your server. (You could select the mapped drive that was created in the previous step, but this is the preferred way to go. You really don't want to use mapped drives for security reasons.)
Note: If you're going to use a UNC path and the user name is different than the logged in server ID, you need to have mapped a network drive on Windows before you can properly reference the UNC path. Welcome to Windows permissions. (Don't fill in the username and password values, see Figure 9).
4. Press "Next" and follow the prompts. Choose what data you want to extract from the database. In this example I'm choosing to copy one table (see Figure 10).
5. As shown in Figure 11, I've selected the table "Customers" and I'm generating an Excel (XLS) file. The first row in the file will contain the table's column names by default.
6. On the next screen, make sure you have selected "Save DTS Package" and that the radio button for "SQL Server" is selected. This means that the DTS package will be saved inside the SQL Server (see Figure 12).
7. Name the DTS package. This is the name your ColdFusion code will later reference. Notice the option for "Use SQL Server authentication (see Figure 13)." The specified account should be the same account ColdFusion uses for its ODBC connection to your database.
Note: Use of good naming conventions is a good habit to form. It makes your code much easier to read when you reference packages that start with DTS_xxxx.
8. Your DTS package will run and output your file to the destination path. Go to the destination, confirm that an XLS file was created in the directory indicated containing the data from the table that was selected. If so, delete the created file (see Figure 14). 9. One last test, in the Enterprise Manager. Execute the DTS package manually. Go to the "Data Transformation Services" area, select Local Package, and in the right window pane, right click on the DTS package you just saved. Click "Execute Package" (see Figure 15). If this works, great, on to the next step. If it doesn't, you have some troubleshooting to do. Go back and review your previous steps.
To Be Continued...
Due to the length and detail of this article, Part 2 will be presented in next month's issue. It will address the following key areas:
SYS-CON Events announced today that Venafi, the Immune System for the Internet™ and the leading provider of Next Generation Trust Protection, will exhibit at @DevOpsSummit at 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Venafi is the Immune System for the Internet™ that protects the foundation of all cybersecurity – cryptographic keys and digital certificates – so they can’t be misused by bad guys in attacks...
Aug. 24, 2016 12:30 PM EDT Reads: 2,525
The 19th International Cloud Expo has announced that its Call for Papers is open. Cloud Expo, to be held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, brings together Cloud Computing, Big Data, Internet of Things, DevOps, Digital Transformation, Microservices and WebRTC to one location. With cloud computing driving a higher percentage of enterprise IT budgets every year, it becomes increasingly important to plant your flag in this fast-expanding business opportuni...
Aug. 24, 2016 12:00 PM EDT Reads: 3,827
DevOps at Cloud Expo, taking place Nov 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 19th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The widespread success of cloud computing is driving the DevOps revolution in enterprise IT. Now as never before, development teams must communicate and collaborate in a dynamic, 24/7/365 environment. There is no time to wait for long dev...
Aug. 24, 2016 11:00 AM EDT Reads: 2,076
SYS-CON Events announced today Telecom Reseller has been named “Media Sponsor” of SYS-CON's 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Telecom Reseller reports on Unified Communications, UCaaS, BPaaS for enterprise and SMBs. They report extensively on both customer premises based solutions such as IP-PBX as well as cloud based and hosted platforms.
Aug. 24, 2016 10:48 AM EDT Reads: 331
In today's uber-connected, consumer-centric, cloud-enabled, insights-driven, multi-device, global world, the focus of solutions has shifted from the product that is sold to the person who is buying the product or service. Enterprises have rebranded their business around the consumers of their products. The buyer is the person and the focus is not on the offering. The person is connected through multiple devices, wearables, at home, on the road, and in multiple locations, sometimes simultaneously...
Aug. 24, 2016 09:45 AM EDT Reads: 2,242
Pulzze Systems was happy to participate in such a premier event and thankful to be receiving the winning investment and global network support from G-Startup Worldwide. It is an exciting time for Pulzze to showcase the effectiveness of innovative technologies and enable them to make the world smarter and better. The reputable contest is held to identify promising startups around the globe that are assured to change the world through their innovative products and disruptive technologies. There w...
Aug. 24, 2016 09:36 AM EDT Reads: 250
Internet of @ThingsExpo, taking place November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 19th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The Internet of Things (IoT) is the most profound change in personal and enterprise IT since the creation of the Worldwide Web more than 20 years ago. All major researchers estimate there will be tens of billions devices - comp...
Aug. 24, 2016 09:00 AM EDT Reads: 3,494
For basic one-to-one voice or video calling solutions, WebRTC has proven to be a very powerful technology. Although WebRTC’s core functionality is to provide secure, real-time p2p media streaming, leveraging native platform features and server-side components brings up new communication capabilities for web and native mobile applications, allowing for advanced multi-user use cases such as video broadcasting, conferencing, and media recording.
Aug. 24, 2016 07:45 AM EDT Reads: 2,067
Data is the fuel that drives the machine learning algorithmic engines and ultimately provides the business value. In his session at Cloud Expo, Ed Featherston, a director and senior enterprise architect at Collaborative Consulting, will discuss the key considerations around quality, volume, timeliness, and pedigree that must be dealt with in order to properly fuel that engine.
Aug. 24, 2016 07:15 AM EDT Reads: 1,652
SYS-CON Events announced today that 910Telecom will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Housed in the classic Denver Gas & Electric Building, 910 15th St., 910Telecom is a carrier-neutral telecom hotel located in the heart of Denver. Adjacent to CenturyLink, AT&T, and Denver Main, 910Telecom offers connectivity to all major carriers, Internet service providers, Internet backbones and ...
Aug. 24, 2016 07:00 AM EDT Reads: 1,739
Amazon has gradually rolled out parts of its IoT offerings in the last year, but these are just the tip of the iceberg. In addition to optimizing their back-end AWS offerings, Amazon is laying the ground work to be a major force in IoT – especially in the connected home and office. Amazon is extending its reach by building on its dominant Cloud IoT platform, its Dash Button strategy, recently announced Replenishment Services, the Echo/Alexa voice recognition control platform, the 6-7 strategic...
Aug. 24, 2016 04:30 AM EDT Reads: 2,174
19th Cloud Expo, taking place November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy. Meanwhile, 94% of enterpri...
Aug. 24, 2016 03:30 AM EDT Reads: 2,926
Akana has announced the availability of version 8 of its API Management solution. The Akana Platform provides an end-to-end API Management solution for designing, implementing, securing, managing, monitoring, and publishing APIs. It is available as a SaaS platform, on-premises, and as a hybrid deployment. Version 8 introduces a lot of new functionality, all aimed at offering customers the richest API Management capabilities in a way that is easier than ever for API and app developers to use.
Aug. 24, 2016 02:00 AM EDT Reads: 1,404
Personalization has long been the holy grail of marketing. Simply stated, communicate the most relevant offer to the right person and you will increase sales. To achieve this, you must understand the individual. Consequently, digital marketers developed many ways to gather and leverage customer information to deliver targeted experiences. In his session at @ThingsExpo, Lou Casal, Founder and Principal Consultant at Practicala, discussed how the Internet of Things (IoT) has accelerated our abil...
Aug. 24, 2016 01:45 AM EDT Reads: 1,872
With so much going on in this space you could be forgiven for thinking you were always working with yesterday’s technologies. So much change, so quickly. What do you do if you have to build a solution from the ground up that is expected to live in the field for at least 5-10 years? This is the challenge we faced when we looked to refresh our existing 10-year-old custom hardware stack to measure the fullness of trash cans and compactors.
Aug. 24, 2016 01:00 AM EDT Reads: 1,588
The emerging Internet of Everything creates tremendous new opportunities for customer engagement and business model innovation. However, enterprises must overcome a number of critical challenges to bring these new solutions to market. In his session at @ThingsExpo, Michael Martin, CTO/CIO at nfrastructure, outlined these key challenges and recommended approaches for overcoming them to achieve speed and agility in the design, development and implementation of Internet of Everything solutions wi...
Aug. 24, 2016 12:30 AM EDT Reads: 1,880
Cloud computing is being adopted in one form or another by 94% of enterprises today. Tens of billions of new devices are being connected to The Internet of Things. And Big Data is driving this bus. An exponential increase is expected in the amount of information being processed, managed, analyzed, and acted upon by enterprise IT. This amazing is not part of some distant future - it is happening today. One report shows a 650% increase in enterprise data by 2020. Other estimates are even higher....
Aug. 24, 2016 12:00 AM EDT Reads: 2,834
I wanted to gather all of my Internet of Things (IOT) blogs into a single blog (that I could later use with my University of San Francisco (USF) Big Data “MBA” course). However as I started to pull these blogs together, I realized that my IOT discussion lacked a vision; it lacked an end point towards which an organization could drive their IOT envisioning, proof of value, app dev, data engineering and data science efforts. And I think that the IOT end point is really quite simple…
Aug. 23, 2016 11:30 PM EDT Reads: 2,224
"My role is working with customers, helping them go through this digital transformation. I spend a lot of time talking to banks, big industries, manufacturers working through how they are integrating and transforming their IT platforms and moving them forward," explained William Morrish, General Manager Product Sales at Interoute, in this SYS-CON.tv interview at 18th Cloud Expo, held June 7-9, 2016, at the Javits Center in New York City, NY.
Aug. 23, 2016 09:00 PM EDT Reads: 2,991
Internet of @ThingsExpo, taking place November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with the 19th International Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world and ThingsExpo Silicon Valley Call for Papers is now open.
Aug. 23, 2016 08:45 PM EDT Reads: 3,759