ColdFusion Datasource Connection Errors as Usual

[Laura32781154plht]

Hi All,

Running ColdFusion 2023 on Windows Pro 11.

MSSQL 2022 server installed.

Does CF "REQUIRE" jdbc driver to connect to SQL server or is it only an option? If it's required CF should clearly state that.

I know I've dealt with this before, but can't remember how I fixed it.

I downloaded the jar file for it ( mssql-jdbc-12.4.1.jre8.jar )  and put it in the lib folder in coldfusion. Still no go. 

I don't see it listed as a driver when creating the datasource.

I did restart CF and SQL server afterwards.

I'm logging into SQL server using Windows authentication. I don't recall seeing a choice during installation to do it any other way.

Web searches suggesting:

1. make sure TCP/IP is enabled: Yes, did that.

2. Get jdbc driver: Yes, did that.  Put in CF lib folder.

3. Add "AuthenticationMethod=ntlm" in the advanced settings Connection String CF settings: Did that.

4. Tried putting servername/username in that didn't help.

Still not working. Head is spinning at this point. 

Most recent error:

"Connection verification failed for data source: Test
java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database "Locations" requested by the login. The login failed."

If I select the MSSQL driver I get:

"Connection verification failed for data source: Test
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Kerberos Authentication is not supported by this SQLServer. The driver attempted Kerberos authentication because the AuthenticationMethod connect option was "auto" and no username/password was specified."

Can anyone please point me to good documentation or offer suggestions on how to fix this?

Thanks!

Laura

[Dave Watts]

CF does require JDBC, but all the drivers that come with CF are JDBC drivers. The MS SQL Server driver is a JDBC driver from DataDirect Connect for JDBC which is bundled with CF.

The easiest way to make this work is to configure SQL Server to support native SQL logins in addition to Windows Authentication. It's not the only way, but it's the easiest way and will likely be suitable for you.

Dave Watts, Eidolon LLC 

[Charlie Arehart]

I was writing my reply while Dave had offered his. Yep, his suggestion of switching to sql server auth is indeed another way to go. I meant to at least mention it, though I'd have added that I understand some prefer this "trusted authentication" approach where no password (or username) is stored in cf.

Then again, leaving cf running as local system has its own risks, as does setting sql server to permit that "user" access to databases.

Again, I explained all I did because it seemed the way Laura had things configured on a previous cf setup, that she was now trying to get running on a new one.

(And I see that while I was writing this, Dave offered a reply to me. I'll respond to that separately.) 

[Charlie Arehart]

Laura, you're mixing many things together. 🙂 Let's try to untangle your knots. 

• there are indeed 2 ways to connect to sql server (and other db's) in cf: one is the driver provided by Adobe, the other is literally to use an "other" driver (a jar, like you attempted)
• So no, you don't NEED to obtain a driver. It's an option 
• As for your using Windows auth for the sql server datasource, yes, you'd add AuthenticationMethod=ntlm to the connection string box. Did you do that for the Adobe-provided sql server driver? Your second error suggests you did not.
• As for your first error, if you get that with this cf driver (or an "other" one), that's indicating that the user running the cf service is not identified in sql server as a permitted user for that database
• When using such Windows auth for a db, it's critical to know the user running the cf service (assuming cf is running as a service, as it would for production, at least)
• If you leave the cf service running as what's implemented by default, that's "local system", whereas in your old cf instance someone could have changed that to another user (per instructions in the cf lockdown guide, for example).
• If that's the case, the first error is indicating that the user running THIS CF (if it's still local system) has not been granted permissions to the db (in sql server). The username to grant access to in sql server would be not "local system" but the formal name, "NT AUTHORITY\System". 
• As for how to grant permissions in sql server/ssms, I will leave that as something you can find readily online
• Finally, another option would be to change the cf service to run as whatever it ran as in your old cf server. Just beware that in changing that, you need to make sure you give THAT user permission to read /write the cf folders themselves, and ensure it has read access to your code, a d write access to any other folders it may need to write to in your app. Again then cf lockdown guide discusses that.
• (As an update, to my original comment, I'll add now that yet another option is to change to using sql server login authentication rather than Windows auth, as discussed further in another comment here.) 

As for the rest of what I've shared above, I'm not aware of any one resource that elaborate on all the points. 

Feel free to ask follow-up questions, and let us know how it goes. 

[Dave Watts]

I'm not entirely sure, but I don't think you can use Local System to authenticate against a remote system. Local System / SYSTEM are security contexts rather than "real" user accounts like Administrator etc. Of course, I haven't tried it recently but I seem to recall having to use a user account instead of a security context for remote authentication.

Dave Watts, Eidolon LLC 

[Charlie Arehart]

Dave, yes, that may be so. Laura didn't indicate if her sql server was remote (on a separate machine from cf). Again, in production that would be recommended, but plenty of folks still run both cf and their db on one server.

And Laura, this is all the more reason why you could have difficulty finding a resource presenting all this. It's a lot to keep track of, and any one set of steps could be countered by another concern, calling for entirely different steps. 🙂 

[Laura32781154plht]

Yay,

Success!!!

I changed the SQL Server DB settings to mixed mode login.

I put "AuthenticationMethod=ntlm" in the Connection String section under advanced (found that in another one of your posts btw!)

Couple of screenshots in case anyone else stumbles across the same issue and aren't sure where to do what.

Thanks so much. 

This was the fastest fix of this issue ever!  I almost screamed out loud with joy when I saw it connected...LOLOL.

[Laura32781154plht]

Thanks to both Dave and Charlie!

Couldn't respond to Charlie directly after posting my reply to Charlie.  Adobe wouldn't let me.

Between the both of you, it made me think differently and viola.

[Laura32781154plht]

* Can't edit here.

I meant to say couldn't respond directly to Dave after responding to Charlie.

[Laura32781154plht]

Ok, err I spoke too soon.

Even though I got an ok a while ago, when I actually try to run my app. It's not connecting.

If I put the name of the actual database in the CF datasource settings instead of leaving it blank,

and/or the name of the server (same as computer name), and/or  username that is same as my windows username as well as the SQL user account name (I'm admin in both) I get an error.

"Connection verification failed for data source: Testing
java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database "Locations" requested by the login. The login failed."

This is all on my local machine.

Sorry, I should have waited to run an app before celebrating.  Sorry to bother you again.

Thanks if you can help.

[Laura32781154plht]

Got it fixed!

I had to add the role "sysadmin"  to the  "NT AUTHORITY\SYSTEM" account.

(Microsoft suggests adding the account.  But it already existed for me.)

"Testing Connection to SQL Server from a service under local system account."

Setting up access for Local System account in SQL Server:

In order for a windows service or application that runs under Local System account to connect to SQL Server, the Local System account (just like any other account) needs to be granted a login privilege and other appropriate permissions in SQL Server.

Scenario 1: If the windows service/application and SQL Server are on the same machine-

We need to grant login privilege and appropriate role for the ‘NT Authority\System’ account on the SQL Server (or Windows service) machine. For example, to create a login for NT Authority\System and grant it ‘sysadmin’ role we can run the T-SQL statement below in SQL Server Management Studio-

CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH

DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

Go

EXEC sp_addsrvrolemember 'NT AUTHORITY\SYSTEM', 'sysadmin';

GO

The login and the role can also be created from SQL Server Management Studio.

1. In SQL Server Management Studio, open Object Explorer and connect to the server instance in which to create the new login.

2. Right-click the Security folder under this instance, point to New, and then click Login.

3. On the General page, enter NT AUTHORITY\SYSTEM in the login name box.

4. Select Windows Authentication.

5. On the Server Roles Page, select ‘sysadmin’ and click OK

NOTE: We don’t need to necessarily grant sysadmin role – this is just an example. The exact role should be determined based on what the application/service requires.

[Dave Watts]

Whew, that's a lot of work. If you don't explicitly need to use Windows Authentication - which is what you're doing - it would be a lot easier to use a native SQL login (a/k/a mixed mode authentication). You can't disable Windows Authentication for Microsoft SQL Server, but native SQL logins are just usernames and passwords basically, and work just fine for use on a single machine or multiple machines.

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication...

Note that there are environments where you must use Windows Authentication because of security reasons, but those are pretty rare and clearly your environment should allow both.

Finally, you really don't need the sysadmin role in a CF application unless that app is actually managing the database: creating tables, dropping tables, etc. In your case, you just need SQL Data Manipulation Language (a/k/a "CRUD" for Create-Read-Update-Delete) permissions.

Dave Watts, Eidolon LLC

[Charlie Arehart]

FWIW, Laura, this adding of the NT AUTHORITY\System as a user for the db is what I was proposing in my first reply. I also added there the reasoning, and how things may differ for some. Glad you got it sorted out, and as always Dave's additional comments are valuable.