Welcome!

You will be redirected in 30 seconds or close now.

ColdFusion Authors: Yakov Fain, Jeremy Geelan, Maureen O'Gara, Nancy Y. Nee, Tad Anderson

Related Topics: ColdFusion

ColdFusion: Article

ColdFusion and SQL Server Permission Integration

ColdFusion and SQL Server Permission Integration

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:

  • ASCII fixed field-length text files
  • ODBC data sources
  • SQL Server databases
  • Microsoft Excel spreadsheets
  • Microsoft Access databases
  • Microsoft FoxPro databases
  • dBase or Paradox databases
  • User-specified OLE DB data sources

    The DTS Import and DTS Export wizards allow the user to:

  • Copy an entire table, or the results of an SQL query, such as queries involving joins of multiple tables, or even distributed queries.
  • Build a query using the Query Builder within the wizard. This allows users inexperienced with the SQL language to build queries interactively.
  • Change the name, data type, size, precision, scale, and nullability of a column when copying the source to the destination (where a valid data-type conversion applies).
  • Specify transformation rules that govern how data is copied between columns of different data type, size, precision, scale, and nullability.
  • Transfer database objects such as users, roles, views, and stored procedures between computers running SQL Server 7.0.

    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
    A. sp_OACreate
    B. sp_OADestroy
    C. sp_OAMethod
    D. sp_OASetProperty
    E. sp_OAGetErrorInfo
    F. sp_OAGetProperty

    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:

  • How to create a stored procedure in SQL Server
  • Installing stored procedures
  • How to test SQL Server configurations
  • ColdFusion code for executing a DTS Package through OLE Automation via a stored procedure and COM object
  • Troubleshooting setup and configuration
  • Comments (0)

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


    @ThingsExpo Stories
    The essence of data analysis involves setting up data pipelines that consist of several operations that are chained together – starting from data collection, data quality checks, data integration, data analysis and data visualization (including the setting up of interaction paths in that visualization). In our opinion, the challenges stem from the technology diversity at each stage of the data pipeline as well as the lack of process around the analysis.
    Designing IoT applications is complex, but deploying them in a scalable fashion is even more complex. A scalable, API first IaaS cloud is a good start, but in order to understand the various components specific to deploying IoT applications, one needs to understand the architecture of these applications and figure out how to scale these components independently. In his session at @ThingsExpo, Nara Rajagopalan is CEO of Accelerite, will discuss the fundamental architecture of IoT applications, ...
    The IoTs will challenge the status quo of how IT and development organizations operate. Or will it? Certainly the fog layer of IoT requires special insights about data ontology, security and transactional integrity. But the developmental challenges are the same: People, Process and Platform. In his session at @ThingsExpo, Craig Sproule, CEO of Metavine, will demonstrate how to move beyond today's coding paradigm and share the must-have mindsets for removing complexity from the development proc...
    A strange thing is happening along the way to the Internet of Things, namely far too many devices to work with and manage. It has become clear that we'll need much higher efficiency user experiences that can allow us to more easily and scalably work with the thousands of devices that will soon be in each of our lives. Enter the conversational interface revolution, combining bots we can literally talk with, gesture to, and even direct with our thoughts, with embedded artificial intelligence, wh...
    SYS-CON Events announced today that Tintri Inc., a leading producer of VM-aware storage (VAS) for virtualization and cloud environments, will exhibit at the 18th International CloudExpo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, New York, and the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA.
    In his session at 18th Cloud Expo, Bruce Swann, Senior Product Marketing Manager at Adobe, will discuss how the Adobe Marketing Cloud can help marketers embrace opportunities for personalized, relevant and real-time customer engagement across offline (direct mail, point of sale, call center) and digital (email, website, SMS, mobile apps, social networks, connected objects). Bruce Swann has more than 15 years of experience working with digital marketing disciplines like web analytics, social med...
    SYS-CON Events announced today that EastBanc Technologies will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. EastBanc Technologies has been working at the frontier of technology since 1999. Today, the firm provides full-lifecycle software development delivering flexible technology solutions that seamlessly integrate with existing systems – whether on premise or cloud. EastBanc Technologies partners with p...
    SYS-CON Events announced today that BMC Software has been named "Siver Sponsor" of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2015 at the Javits Center in New York, New York. BMC is a global leader in innovative software solutions that help businesses transform into digital enterprises for the ultimate competitive advantage. BMC Digital Enterprise Management is a set of innovative IT solutions designed to make digital business fast, seamless, and optimized from mainframe to mo...
    SYS-CON Events announced today that MangoApps will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. MangoApps provides modern company intranets and team collaboration software, allowing workers to stay connected and productive from anywhere in the world and from any device. For more information, please visit https://www.mangoapps.com/.
    The IoT is changing the way enterprises conduct business. In his session at @ThingsExpo, Eric Hoffman, Vice President at EastBanc Technologies, discuss how businesses can gain an edge over competitors by empowering consumers to take control through IoT. We'll cite examples such as a Washington, D.C.-based sports club that leveraged IoT and the cloud to develop a comprehensive booking system. He'll also highlight how IoT can revitalize and restore outdated business models, making them profitable...
    SYS-CON Events announced today that Commvault, a global leader in enterprise data protection and information management, has been named “Bronze Sponsor” of SYS-CON's 18th International Cloud Expo, which will take place on June 7–9, 2016, at the Javits Center in New York City, NY, and the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Commvault is a leading provider of data protection and information management...
    Companies can harness IoT and predictive analytics to sustain business continuity; predict and manage site performance during emergencies; minimize expensive reactive maintenance; and forecast equipment and maintenance budgets and expenditures. Providing cost-effective, uninterrupted service is challenging, particularly for organizations with geographically dispersed operations.
    Customer experience has become a competitive differentiator for companies, and it’s imperative that brands seamlessly connect the customer journey across all platforms. With the continued explosion of IoT, join us for a look at how to build a winning digital foundation in the connected era – today and in the future. In his session at @ThingsExpo, Chris Nguyen, Group Product Marketing Manager at Adobe, will discuss how to successfully leverage mobile, rapidly deploy content, capture real-time d...
    SYS-CON Events announced today that ContentMX, the marketing technology and services company with a singular mission to increase engagement and drive more conversations for enterprise, channel and SMB technology marketers, has been named “Sponsor & Exhibitor Lounge Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York City, New York. “CloudExpo is a great opportunity to start a conversation with new prospects, but what happens after the...
    SoftLayer operates a global cloud infrastructure platform built for Internet scale. With a global footprint of data centers and network points of presence, SoftLayer provides infrastructure as a service to leading-edge customers ranging from Web startups to global enterprises. SoftLayer's modular architecture, full-featured API, and sophisticated automation provide unparalleled performance and control. Its flexible unified platform seamlessly spans physical and virtual devices linked via a world...
    IoT generates lots of temporal data. But how do you unlock its value? How do you coordinate the diverse moving parts that must come together when developing your IoT product? What are the key challenges addressed by Data as a Service? How does cloud computing underlie and connect the notions of Digital and DevOps What is the impact of the API economy? What is the business imperative for Cognitive Computing? Get all these questions and hundreds more like them answered at the 18th Cloud Expo...
    SYS-CON Events announced today Object Management Group® has been named “Media Sponsor” of SYS-CON's 18th International Cloud Expo, which will take place on June 7–9, 2016, at the Javits Center in New York City, NY, and the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA.
    What a difference a year makes. Organizations aren’t just talking about IoT possibilities, it is now baked into their core business strategy. With IoT, billions of devices generating data from different companies on different networks around the globe need to interact. From efficiency to better customer insights to completely new business models, IoT will turn traditional business models upside down. In the new customer-centric age, the key to success is delivering critical services and apps wit...
    Join us at Cloud Expo | @ThingsExpo 2016 – June 7-9 at the Javits Center in New York City and November 1-3 at the Santa Clara Convention Center in Santa Clara, CA – and deliver your unique message in a way that is striking and unforgettable by taking advantage of SYS-CON's unmatched high-impact, result-driven event / media packages.
    In his keynote at 18th Cloud Expo, Andrew Keys, Co-Founder of ConsenSys Enterprise, will provide an overview of the evolution of the Internet and the Database and the future of their combination – the Blockchain. Andrew Keys is Co-Founder of ConsenSys Enterprise. He comes to ConsenSys Enterprise with capital markets, technology and entrepreneurial experience. Previously, he worked for UBS investment bank in equities analysis. Later, he was responsible for the creation and distribution of life ...