|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:
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.
Nov. 28, 2014 05:00 PM EST Reads: 2,028
Cultural, regulatory, environmental, political and economic (CREPE) conditions over the past decade are creating cross-industry solution spaces that require processes and technologies from both the Internet of Things (IoT), and Data Management and Analytics (DMA). These solution spaces are evolving into Sensor Analytics Ecosystems (SAE) that represent significant new opportunities for organizations of all types. Public Utilities throughout the world, providing electricity, natural gas and water, are pursuing SmartGrid initiatives that represent one of the more mature examples of SAE. We have s...
Nov. 27, 2014 04:00 PM EST Reads: 2,040
The security devil is always in the details of the attack: the ones you've endured, the ones you prepare yourself to fend off, and the ones that, you fear, will catch you completely unaware and defenseless. The Internet of Things (IoT) is nothing if not an endless proliferation of details. It's the vision of a world in which continuous Internet connectivity and addressability is embedded into a growing range of human artifacts, into the natural world, and even into our smartphones, appliances, and physical persons. In the IoT vision, every new "thing" - sensor, actuator, data source, data con...
Nov. 27, 2014 04:00 PM EST Reads: 2,113
How do APIs and IoT relate? The answer is not as simple as merely adding an API on top of a dumb device, but rather about understanding the architectural patterns for implementing an IoT fabric. There are typically two or three trends: Exposing the device to a management framework Exposing that management framework to a business centric logic Exposing that business layer and data to end users. This last trend is the IoT stack, which involves a new shift in the separation of what stuff happens, where data lives and where the interface lies. For instance, it's a mix of architectural styles ...
Nov. 27, 2014 03:00 PM EST Reads: 2,137
The Internet of Things is tied together with a thin strand that is known as time. Coincidentally, at the core of nearly all data analytics is a timestamp. When working with time series data there are a few core principles that everyone should consider, especially across datasets where time is the common boundary. In his session at Internet of @ThingsExpo, Jim Scott, Director of Enterprise Strategy & Architecture at MapR Technologies, discussed single-value, geo-spatial, and log time series data. By focusing on enterprise applications and the data center, he will use OpenTSDB as an example t...
Nov. 27, 2014 03:00 PM EST Reads: 2,271
An entirely new security model is needed for the Internet of Things, or is it? Can we save some old and tested controls for this new and different environment? In his session at @ThingsExpo, New York's at the Javits Center, Davi Ottenheimer, EMC Senior Director of Trust, reviewed hands-on lessons with IoT devices and reveal a new risk balance you might not expect. Davi Ottenheimer, EMC Senior Director of Trust, has more than nineteen years' experience managing global security operations and assessments, including a decade of leading incident response and digital forensics. He is co-author of t...
Nov. 27, 2014 01:00 PM EST Reads: 2,161
The Internet of Things will greatly expand the opportunities for data collection and new business models driven off of that data. In her session at @ThingsExpo, Esmeralda Swartz, CMO of MetraTech, discussed how for this to be effective you not only need to have infrastructure and operational models capable of utilizing this new phenomenon, but increasingly service providers will need to convince a skeptical public to participate. Get ready to show them the money!
Nov. 27, 2014 11:00 AM EST Reads: 1,988
The Internet of Things will put IT to its ultimate test by creating infinite new opportunities to digitize products and services, generate and analyze new data to improve customer satisfaction, and discover new ways to gain a competitive advantage across nearly every industry. In order to help corporate business units to capitalize on the rapidly evolving IoT opportunities, IT must stand up to a new set of challenges. In his session at @ThingsExpo, Jeff Kaplan, Managing Director of THINKstrategies, will examine why IT must finally fulfill its role in support of its SBUs or face a new round of...
Nov. 27, 2014 10:00 AM EST Reads: 1,978
One of the biggest challenges when developing connected devices is identifying user value and delivering it through successful user experiences. In his session at Internet of @ThingsExpo, Mike Kuniavsky, Principal Scientist, Innovation Services at PARC, described an IoT-specific approach to user experience design that combines approaches from interaction design, industrial design and service design to create experiences that go beyond simple connected gadgets to create lasting, multi-device experiences grounded in people's real needs and desires.
Nov. 27, 2014 08:00 AM EST Reads: 1,974
Enthusiasm for the Internet of Things has reached an all-time high. In 2013 alone, venture capitalists spent more than $1 billion dollars investing in the IoT space. With "smart" appliances and devices, IoT covers wearable smart devices, cloud services to hardware companies. Nest, a Google company, detects temperatures inside homes and automatically adjusts it by tracking its user's habit. These technologies are quickly developing and with it come challenges such as bridging infrastructure gaps, abiding by privacy concerns and making the concept a reality. These challenges can't be addressed w...
Nov. 27, 2014 07:45 AM EST Reads: 2,082
The Domain Name Service (DNS) is one of the most important components in networking infrastructure, enabling users and services to access applications by translating URLs (names) into IP addresses (numbers). Because every icon and URL and all embedded content on a website requires a DNS lookup loading complex sites necessitates hundreds of DNS queries. In addition, as more internet-enabled ‘Things' get connected, people will rely on DNS to name and find their fridges, toasters and toilets. According to a recent IDG Research Services Survey this rate of traffic will only grow. What's driving t...
Nov. 27, 2014 07:00 AM EST Reads: 2,147
Connected devices and the Internet of Things are getting significant momentum in 2014. In his session at Internet of @ThingsExpo, Jim Hunter, Chief Scientist & Technology Evangelist at Greenwave Systems, examined three key elements that together will drive mass adoption of the IoT before the end of 2015. The first element is the recent advent of robust open source protocols (like AllJoyn and WebRTC) that facilitate M2M communication. The second is broad availability of flexible, cost-effective storage designed to handle the massive surge in back-end data in a world where timely analytics is e...
Nov. 27, 2014 06:45 AM EST Reads: 2,123
Scott Jenson leads a project called The Physical Web within the Chrome team at Google. Project members are working to take the scalability and openness of the web and use it to talk to the exponentially exploding range of smart devices. Nearly every company today working on the IoT comes up with the same basic solution: use my server and you'll be fine. But if we really believe there will be trillions of these devices, that just can't scale. We need a system that is open a scalable and by using the URL as a basic building block, we open this up and get the same resilience that the web enjoys.
Nov. 27, 2014 06:45 AM EST Reads: 2,209
We are reaching the end of the beginning with WebRTC, and real systems using this technology have begun to appear. One challenge that faces every WebRTC deployment (in some form or another) is identity management. For example, if you have an existing service – possibly built on a variety of different PaaS/SaaS offerings – and you want to add real-time communications you are faced with a challenge relating to user management, authentication, authorization, and validation. Service providers will want to use their existing identities, but these will have credentials already that are (hopefully) i...
Nov. 27, 2014 04:00 AM EST Reads: 1,799
"Matrix is an ambitious open standard and implementation that's set up to break down the fragmentation problems that exist in IP messaging and VoIP communication," explained John Woolf, Technical Evangelist at Matrix, in this SYS-CON.tv interview at @ThingsExpo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
Nov. 27, 2014 04:00 AM EST Reads: 1,870
P2P RTC will impact the landscape of communications, shifting from traditional telephony style communications models to OTT (Over-The-Top) cloud assisted & PaaS (Platform as a Service) communication services. The P2P shift will impact many areas of our lives, from mobile communication, human interactive web services, RTC and telephony infrastructure, user federation, security and privacy implications, business costs, and scalability. In his session at @ThingsExpo, Robin Raymond, Chief Architect at Hookflash, will walk through the shifting landscape of traditional telephone and voice services ...
Nov. 26, 2014 02:00 PM EST Reads: 2,122
Explosive growth in connected devices. Enormous amounts of data for collection and analysis. Critical use of data for split-second decision making and actionable information. All three are factors in making the Internet of Things a reality. Yet, any one factor would have an IT organization pondering its infrastructure strategy. How should your organization enhance its IT framework to enable an Internet of Things implementation? In his session at Internet of @ThingsExpo, James Kirkland, Chief Architect for the Internet of Things and Intelligent Systems at Red Hat, described how to revolutioniz...
Nov. 24, 2014 07:00 PM EST Reads: 2,284
Bit6 today issued a challenge to the technology community implementing Web Real Time Communication (WebRTC). To leap beyond WebRTC’s significant limitations and fully leverage its underlying value to accelerate innovation, application developers need to consider the entire communications ecosystem.
Nov. 24, 2014 12:00 PM EST Reads: 2,040
The definition of IoT is not new, in fact it’s been around for over a decade. What has changed is the public's awareness that the technology we use on a daily basis has caught up on the vision of an always on, always connected world. If you look into the details of what comprises the IoT, you’ll see that it includes everything from cloud computing, Big Data analytics, “Things,” Web communication, applications, network, storage, etc. It is essentially including everything connected online from hardware to software, or as we like to say, it’s an Internet of many different things. The difference ...
Nov. 24, 2014 11:00 AM EST Reads: 2,402
Cloud Expo 2014 TV commercials will feature @ThingsExpo, which was launched in June, 2014 at New York City's Javits Center as the largest 'Internet of Things' event in the world.
Nov. 24, 2014 09:00 AM EST Reads: 2,268