One of the most powerful features of ColdFusion is the ability to connect to a variety of back-end database systems right out of the box. My favorite, for a variety of reasons, is Microsoft SQL Server.
SQL Sever allows you to specify a database user in two ways; SQL Authentication and Windows Authentication. SQL Authentication is like creating a local user on your machine. Windows Authentication is a far more secure and manageable mechanism that relies on Active Directory. In many cases in the corporate world, this is the only way a user can gain access to a database. SQL Server also allows for a mixed mode which allows both authentication mechanisms and is what most shared hosting companies use to allow users to access the server remotely. ColdFusion can connect to SQL Server in either way, but the documentation is far from clear on how to do this using Windows Authentication. The following assumes you are using a relatively new version of CF (8,9,10) and Windows (7, 8, Server 2008 or 2012).
Connecting to SQL Server using SQL Authentication is simple. Go to the ColdFusion administrator, select Data Service >>DataSources and enter the name of the datasource and select Microsoft SQL Server in the dropdown, and then click add.
Once you are there, you will enter the name of the database, the server name or URL, the SQL username you were provided or that you created, the port number (1433 is the default), and the password. You can also enter a brief description, but it is not required. At this point you can hit submit. If all goes well you will get the OK that the datasource has been setup correctly. I won’t go into the advanced options, but if you plan on storing BLOB or CLOB data, you would need to press Show Advanced Settings.
You would think that using Windows Authentication would be as simple as adding the DOMAINUSERNAME in the username field and the other information and press submit. You would be wrong and eventually very frustrated. Connecting to SQL Server with Windows Authentication is only possible by the user that starts the ColdFusion Service in Windows.
By default, when you install ColdFusion, a local system account is used to start the ColdFusion service. In addition to starting the service and all CF related services, it also has full control of the ColdFusion installation folder (IMPORTANT to remember this). The documentation states to create a more secure and more auditable install you should create a local user that can run the service. To use Windows Authentication you will need to ask you Domain Administrator to create a service account in Active Directory that will be used to run the service. Once you have the username and password you will go to the Services module in Windows, select the main ColdFusion Application service (you may need to read the description).
Right click and select properties (or double click the service name) and then select the Log On tab. You will notice that Local System account is selected. Select this account and enter the domainusername and the password that your Domain Administrator provided. Hit Apply. You will receive two prompts if successful. The first will indicate that this user can now run the service and the second will tell you that this will not take affect until you restart the service. Do not restart the service yet. Note: You do not need to make changes to any other ColdFusion related services.
Now go to the root location of the ColdFusion installation (e.g. C:). Right click on the ColdFusion installation folder, select properties and then security Select Edit, then select Add to add a new user. Enter the domainusername of the account provided and click on Check Names. Once the username resolves, click OK. Now make sure the check box for Full Control is checked, click OK and wait for the permissions to propagate.
Close all the windows except Services. Restart the ColdFusion service. Verify that you can get to the administrator.
Provide the domain credentials, you just added, to your SQL Database Administrator and ask them to add the user to the database(s) you need access to.
Now follow the instructions above for connecting to the datasource. Do not enter a username and password. ColdFusion will now pass the Windows credentials it uses to the database server. If all goes well you will get the OK that the datasource has been setup correctly. If it fails, the error code will show the domainusername it attempted to use.
Note: I made the mistake of not assigning the user control of the installation folder and received an Apache Tomcat 500 server error with absolutely no indication of the real problem (lots of security failures). I am not real excited about giving this user full control of all the folders and files, and will look for the minimum required.
Thanks to the Adobe Forum and Reed Powell’s post for helping me figure this out (http://forums.adobe.com/message/4793999).