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
    Detecting internal user threats in the Big Data eco-system is challenging and cumbersome. Many organizations monitor internal usage of the Big Data eco-system using a set of alerts. This is not a scalable process given the increase in the number of alerts with the accelerating growth in data volume and user base. Organizations are increasingly leveraging machine learning to monitor only those data elements that are sensitive and critical, autonomously establish monitoring policies, and to detect...
    To get the most out of their data, successful companies are not focusing on queries and data lakes, they are actively integrating analytics into their operations with a data-first application development approach. Real-time adjustments to improve revenues, reduce costs, or mitigate risk rely on applications that minimize latency on a variety of data sources. Jack Norris reviews best practices to show how companies develop, deploy, and dynamically update these applications and how this data-first...
    Intelligent Automation is now one of the key business imperatives for CIOs and CISOs impacting all areas of business today. In his session at 21st Cloud Expo, Brian Boeggeman, VP Alliances & Partnerships at Ayehu, will talk about how business value is created and delivered through intelligent automation to today’s enterprises. The open ecosystem platform approach toward Intelligent Automation that Ayehu delivers to the market is core to enabling the creation of the self-driving enterprise.
    SYS-CON Events announced today that Grape Up will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct. 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Grape Up is a software company specializing in cloud native application development and professional services related to Cloud Foundry PaaS. With five expert teams that operate in various sectors of the market across the U.S. and Europe, Grape Up works with a variety of customers from emergi...
    "We're a cybersecurity firm that specializes in engineering security solutions both at the software and hardware level. Security cannot be an after-the-fact afterthought, which is what it's become," stated Richard Blech, Chief Executive Officer at Secure Channels, in this SYS-CON.tv interview at @ThingsExpo, held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA.
    Consumers increasingly expect their electronic "things" to be connected to smart phones, tablets and the Internet. When that thing happens to be a medical device, the risks and benefits of connectivity must be carefully weighed. Once the decision is made that connecting the device is beneficial, medical device manufacturers must design their products to maintain patient safety and prevent compromised personal health information in the face of cybersecurity threats. In his session at @ThingsExpo...
    SYS-CON Events announced today that Massive Networks will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Massive Networks mission is simple. To help your business operate seamlessly with fast, reliable, and secure internet and network solutions. Improve your customer's experience with outstanding connections to your cloud.
    Everything run by electricity will eventually be connected to the Internet. Get ahead of the Internet of Things revolution and join Akvelon expert and IoT industry leader, Sergey Grebnov, in his session at @ThingsExpo, for an educational dive into the world of managing your home, workplace and all the devices they contain with the power of machine-based AI and intelligent Bot services for a completely streamlined experience.
    Because IoT devices are deployed in mission-critical environments more than ever before, it’s increasingly imperative they be truly smart. IoT sensors simply stockpiling data isn’t useful. IoT must be artificially and naturally intelligent in order to provide more value In his session at @ThingsExpo, John Crupi, Vice President and Engineering System Architect at Greenwave Systems, will discuss how IoT artificial intelligence (AI) can be carried out via edge analytics and machine learning techn...
    When shopping for a new data processing platform for IoT solutions, many development teams want to be able to test-drive options before making a choice. Yet when evaluating an IoT solution, it’s simply not feasible to do so at scale with physical devices. Building a sensor simulator is the next best choice; however, generating a realistic simulation at very high TPS with ease of configurability is a formidable challenge. When dealing with multiple application or transport protocols, you would be...
    SYS-CON Events announced today that Datera, that offers a radically new data management architecture, has been named "Exhibitor" of SYS-CON's 21st International Cloud Expo ®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Datera is transforming the traditional datacenter model through modern cloud simplicity. The technology industry is at another major inflection point. The rise of mobile, the Internet of Things, data storage and Big...
    SYS-CON Events announced today that GrapeUp, the leading provider of rapid product development at the speed of business, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Grape Up is a software company, specialized in cloud native application development and professional services related to Cloud Foundry PaaS. With five expert teams that operate in various sectors of the market acr...
    In the enterprise today, connected IoT devices are everywhere – both inside and outside corporate environments. The need to identify, manage, control and secure a quickly growing web of connections and outside devices is making the already challenging task of security even more important, and onerous. In his session at @ThingsExpo, Rich Boyer, CISO and Chief Architect for Security at NTT i3, discussed new ways of thinking and the approaches needed to address the emerging challenges of security i...
    SYS-CON Events announced today that CA Technologies has been named "Platinum Sponsor" of SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. CA Technologies helps customers succeed in a future where every business - from apparel to energy - is being rewritten by software. From planning to development to management to security, CA creates software that fuels transformation for companies in the applic...
    In his opening keynote at 20th Cloud Expo, Michael Maximilien, Research Scientist, Architect, and Engineer at IBM, discussed the full potential of the cloud and social data requires artificial intelligence. By mixing Cloud Foundry and the rich set of Watson services, IBM's Bluemix is the best cloud operating system for enterprises today, providing rapid development and deployment of applications that can take advantage of the rich catalog of Watson services to help drive insights from the vast t...
    There is only one world-class Cloud event on earth, and that is Cloud Expo – which returns to Silicon Valley for the 21st Cloud Expo at the Santa Clara Convention Center, October 31 - November 2, 2017. Every Global 2000 enterprise in the world is now integrating cloud computing in some form into its IT development and operations. Midsize and small businesses are also migrating to the cloud in increasing numbers. Companies are each developing their unique mix of cloud technologies and service...
    WebRTC is great technology to build your own communication tools. It will be even more exciting experience it with advanced devices, such as a 360 Camera, 360 microphone, and a depth sensor camera. In his session at @ThingsExpo, Masashi Ganeko, a manager at INFOCOM Corporation, will introduce two experimental projects from his team and what they learned from them. "Shotoku Tamago" uses the robot audition software HARK to track speakers in 360 video of a remote party. "Virtual Teleport" uses a...
    Recently, IoT seems emerging as a solution vehicle for data analytics on real-world scenarios from setting a room temperature setting to predicting a component failure of an aircraft. Compared with developing an application or deploying a cloud service, is an IoT solution unique? If so, how? How does a typical IoT solution architecture consist? And what are the essential components and how are they relevant to each other? How does the security play out? What are the best practices in formulating...
    Internet of @ThingsExpo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 21st 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 devic...
    In his session at @ThingsExpo, Arvind Radhakrishnen discussed how IoT offers new business models in banking and financial services organizations with the capability to revolutionize products, payments, channels, business processes and asset management built on strong architectural foundation. The following topics were covered: How IoT stands to impact various business parameters including customer experience, cost and risk management within BFS organizations.