|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:
DevOps Summit 2015 New York, co-located with the 16th International Cloud Expo - to be held June 9-11, 2015, at the Javits Center in New York City, NY - announces that it is now accepting Keynote Proposals. 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 development cycles that produce software that is obsolete at launch. DevOps may be disruptive, but it is essential.
Dec. 18, 2014 01:45 PM EST Reads: 603
“In the past year we've seen a lot of stabilization of WebRTC. You can now use it in production with a far greater degree of certainty. A lot of the real developments in the past year have been in things like the data channel, which will enable a whole new type of application," explained Peter Dunkley, Technical Director at Acision, in this SYS-CON.tv interview at @ThingsExpo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
Dec. 18, 2014 11:30 AM EST Reads: 752
SYS-CON Events announced today that Windstream, a leading provider of advanced network and cloud communications, has been named “Silver Sponsor” of SYS-CON's 16th International Cloud Expo®, which will take place on June 9–11, 2015, at the Javits Center in New York, NY. Windstream (Nasdaq: WIN), a FORTUNE 500 and S&P 500 company, is a leading provider of advanced network communications, including cloud computing and managed services, to businesses nationwide. The company also offers broadband, phone and digital TV services to consumers primarily in rural areas.
Dec. 18, 2014 11:00 AM EST Reads: 2,076
The major cloud platforms defy a simple, side-by-side analysis. Each of the major IaaS public-cloud platforms offers their own unique strengths and functionality. Options for on-site private cloud are diverse as well, and must be designed and deployed while taking existing legacy architecture and infrastructure into account. Then the reality is that most enterprises are embarking on a hybrid cloud strategy and programs. In this Power Panel at 15th Cloud Expo (http://www.CloudComputingExpo.com), moderated by Ashar Baig, Research Director, Cloud, at Gigaom Research, Nate Gordon, Director of T...
Dec. 18, 2014 10:30 AM EST Reads: 2,212
The Internet of Things is not new. Historically, smart businesses have used its basic concept of leveraging data to drive better decision making and have capitalized on those insights to realize additional revenue opportunities. So, what has changed to make the Internet of Things one of the hottest topics in tech? In his session at @ThingsExpo, Chris Gray, Director, Embedded and Internet of Things, discussed the underlying factors that are driving the economics of intelligent systems. Discover how hardware commoditization, the ubiquitous nature of connectivity, and the emergence of Big Data a...
Dec. 18, 2014 10:15 AM EST Reads: 2,040
ARMONK, N.Y., Nov. 20, 2014 /PRNewswire/ -- IBM (NYSE: IBM) today announced that it is bringing a greater level of control, security and flexibility to cloud-based application development and delivery with a single-tenant version of Bluemix, IBM's platform-as-a-service. The new platform enables developers to build ap...
Dec. 18, 2014 10:00 AM EST Reads: 1,861
"BSQUARE is in the business of selling software solutions for smart connected devices. It's obvious that IoT has moved from being a technology to being a fundamental part of business, and in the last 18 months people have said let's figure out how to do it and let's put some focus on it, " explained Dave Wagstaff, VP & Chief Architect, at BSQUARE Corporation, in this SYS-CON.tv interview at @ThingsExpo, held Nov 4-6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
Dec. 18, 2014 10:00 AM EST Reads: 1,732
SYS-CON Events announced today that IDenticard will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. IDenticard™ is the security division of Brady Corp (NYSE: BRC), a $1.5 billion manufacturer of identification products. We have small-company values with the strength and stability of a major corporation. IDenticard offers local sales, support and service to our customers across the United States and Canada. Our partner network encompasses some 300 of the world's leading systems integrators and security s...
Dec. 18, 2014 09:30 AM EST Reads: 1,843
"People are a lot more knowledgeable about APIs now. There are two types of people who work with APIs - IT people who want to use APIs for something internal and the product managers who want to do something outside APIs for people to connect to them," explained Roberto Medrano, Executive Vice President at SOA Software, in this SYS-CON.tv interview at Cloud Expo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
Dec. 18, 2014 09:00 AM EST Reads: 1,160
Nigeria has the largest economy in Africa, at more than US$500 billion, and ranks 23rd in the world. A recent re-evaluation of Nigeria's true economic size doubled the previous estimate, and brought it well ahead of South Africa, which is a member (unlike Nigeria) of the G20 club for political as well as economic reasons. Nigeria's economy can be said to be quite diverse from one point of view, but heavily dependent on oil and gas at the same time. Oil and natural gas account for about 15% of Nigera's overall economy, but traditionally represent more than 90% of the country's exports and as...
Dec. 18, 2014 06:00 AM EST Reads: 657
The Internet of Things is a misnomer. That implies that everything is on the Internet, and that simply should not be - especially for things that are blurring the line between medical devices that stimulate like a pacemaker and quantified self-sensors like a pedometer or pulse tracker. The mesh of things that we manage must be segmented into zones of trust for sensing data, transmitting data, receiving command and control administrative changes, and peer-to-peer mesh messaging. In his session at @ThingsExpo, Ryan Bagnulo, Solution Architect / Software Engineer at SOA Software, focused on desi...
Dec. 17, 2014 11:15 PM EST Reads: 1,239
"At our booth we are showing how to provide trust in the Internet of Things. Trust is where everything starts to become secure and trustworthy. Now with the scaling of the Internet of Things it becomes an interesting question – I've heard numbers from 200 billion devices next year up to a trillion in the next 10 to 15 years," explained Johannes Lintzen, Vice President of Sales at Utimaco, in this SYS-CON.tv interview at @ThingsExpo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
Dec. 17, 2014 11:00 PM EST Reads: 1,317
"For over 25 years we have been working with a lot of enterprise customers and we have seen how companies create applications. And now that we have moved to cloud computing, mobile, social and the Internet of Things, we see that the market needs a new way of creating applications," stated Jesse Shiah, CEO, President and Co-Founder of AgilePoint Inc., in this SYS-CON.tv interview at 15th Cloud Expo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
Dec. 17, 2014 08:00 PM EST Reads: 1,279
SYS-CON Events announced today that Gridstore™, the leader in hyper-converged infrastructure purpose-built to optimize Microsoft workloads, will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. Gridstore™ is the leader in hyper-converged infrastructure purpose-built for Microsoft workloads and designed to accelerate applications in virtualized environments. Gridstore’s hyper-converged infrastructure is the industry’s first all flash version of HyperConverged Appliances that include both compute and storag...
Dec. 17, 2014 06:30 PM EST Reads: 1,181
Today’s enterprise is being driven by disruptive competitive and human capital requirements to provide enterprise application access through not only desktops, but also mobile devices. To retrofit existing programs across all these devices using traditional programming methods is very costly and time consuming – often prohibitively so. In his session at @ThingsExpo, Jesse Shiah, CEO, President, and Co-Founder of AgilePoint Inc., discussed how you can create applications that run on all mobile devices as well as laptops and desktops using a visual drag-and-drop application – and eForms-buildi...
Dec. 17, 2014 11:45 AM EST Reads: 1,422
We certainly live in interesting technological times. And no more interesting than the current competing IoT standards for connectivity. Various standards bodies, approaches, and ecosystems are vying for mindshare and positioning for a competitive edge. It is clear that when the dust settles, we will have new protocols, evolved protocols, that will change the way we interact with devices and infrastructure. We will also have evolved web protocols, like HTTP/2, that will be changing the very core of our infrastructures. At the same time, we have old approaches made new again like micro-services...
Dec. 16, 2014 11:45 PM EST Reads: 1,261
Code Halos - aka "digital fingerprints" - are the key organizing principle to understand a) how dumb things become smart and b) how to monetize this dynamic. In his session at @ThingsExpo, Robert Brown, AVP, Center for the Future of Work at Cognizant Technology Solutions, outlined research, analysis and recommendations from his recently published book on this phenomena on the way leading edge organizations like GE and Disney are unlocking the Internet of Things opportunity and what steps your organization should be taking to position itself for the next platform of digital competition.
Dec. 15, 2014 11:45 PM EST Reads: 1,630
The 3rd International Internet of @ThingsExpo, co-located with the 16th International Cloud Expo - to be held June 9-11, 2015, at the Javits Center in New York City, NY - announces that its Call for Papers is now open. The Internet of Things (IoT) is the biggest idea since the creation of the Worldwide Web more than 20 years ago.
Dec. 15, 2014 10:30 AM EST Reads: 6,856
As the Internet of Things unfolds, mobile and wearable devices are blurring the line between physical and digital, integrating ever more closely with our interests, our routines, our daily lives. Contextual computing and smart, sensor-equipped spaces bring the potential to walk through a world that recognizes us and responds accordingly. We become continuous transmitters and receivers of data. In his session at @ThingsExpo, Andrew Bolwell, Director of Innovation for HP's Printing and Personal Systems Group, discussed how key attributes of mobile technology – touch input, sensors, social, and ...
Dec. 15, 2014 10:00 AM EST Reads: 1,939
In their session at @ThingsExpo, Shyam Varan Nath, Principal Architect at GE, and Ibrahim Gokcen, who leads GE's advanced IoT analytics, focused on the Internet of Things / Industrial Internet and how to make it operational for business end-users. Learn about the challenges posed by machine and sensor data and how to marry it with enterprise data. They also discussed the tips and tricks to provide the Industrial Internet as an end-user consumable service using Big Data Analytics and Industrial Cloud.
Dec. 15, 2014 10:00 AM EST Reads: 1,611