CF21 SQL Server DSN Login Error with Windows Auth - Worked in CF16 and CF18

[Mike Collins_CF]

In CF2016 and CF2018 you could change the service owner for CF and then leave the SQL Server DSN username and password blank and it would connect using the service owner credentials, providing Windows Authentication \ NTLM support

This is quite handy when you have dozens of databases with many passwords.

If you try the same with CF2021 if throws the below error.

Connection verification failed for data source: testdsn2 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.

I tried all the AuthenticationMethod options in the connect string and none of them allow a connection like CF2016 and CF2018.

https://docs.progress.com/bundle/datadirect-connect-jdbc-51/page/AuthenticationMethod_6.html#Authent...

I also found this Progress error report which tells you to use AuthenticationMethod=ntlm, which I did try.

https://knowledgebase.progress.com/articles/Article/Can-t-connect-to-SQL-Server-using-Windows-Authen...

I am just sharing this here, I entered a bug too. 

[Charlie Arehart]

Yep, Mike, this is indeed a new issue in CF2021. And that's indeed the solution. FWIW, I had shared news of it in my "hidden gems in cf2021" talk, but I see I never created a blog post for it. 😞

And I just checked a couple of other forum thread discussions of the error and they didn't indicate this as a solution (in case you had maybe found those). So I just added it there.

The issue is that the progress/datadirect JDBC driver for SQL Server which CF implements had a change in behavior (in the version of the driver which took effect in CF2021), and now "ntlm" is no longer one of the "auto" options for authentication. So as you infer, one can get this password-less ("trusted") connection working by implementing a connectionstring (in the CF Admin DSN "advanced settings") with a value of: 

AuthenticationMethod=ntlm

BTW, Mike, you indicate that you had tried all the values in that first link and none worked, but one of them was indeed ntlm. 🙂

One other thing that may interest some readers: there is a technote on this change at the progresss site where they indicate this. More specifically, it notes "A driver change was made to removed NTLM from the driver's AUTO functionality in the 6.0 release because Microsoft points out that using NTLM is a security risk."  There's no more info at all about what that "risk" may be.

Instead, they point to an MS doc page on using ntlm with sql server connections, with a discussion of security risks. As it notes, using kerberos is more secure (and there are resources offered there with more on that, and setting up certs or access tokens). I wonder if the concern may have more to do with when the client (CF in our case) and the DB server are not in the same network, such that communications might be "over the internet". If anyone may have or may find more info on that, it cuold be helpful to gather here.

In the meantime, I may do a blog post to share the news above (for some who may not find it here).

[Mike Collins_CF · Accepted solution]

Thanks Charlie.

I went back and tested it again and AuthenticationMethod=ntlm DOES WORK.

I think I typo'd something when initially testing that option.

Security is always a concern, but doing it this way for local dev, at least, seems to offer more security as connections are made under specific users accounts which can offer better tracking then a shared login.

You also need to run the CF service under a Network\Domain user for this to work.

If you don't and just use a Local Service account you will get this error:

java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]A username was not specified and the driver could not establish a connection using NTLM (type 2) integrated security: java.lang.UnsatisfiedLinkError: com.ddtek.util.UtilType2DLLInterface.nativeInitialize3(JLjava/lang/String;ILjava/lang/String;Ljava/lang/String;Ljava/lang/String;)V

Thanks.

[Charlie Arehart]

Hey Mike, I just wanted to share an update and slight correction to the above. If I read you right, you said you were using this for local development, and you noted how to avoid that other error ("A username was not specified"),  one would "need to run the CF service under a Network\Domain user".

Well, I'll share for you and other readers interested in this that if you really were running CF and SQL Server on the same machine, and wanted to use this trusted authentication (AuthenticationMethod=ntlm), but did want to leave CF running as its default of the service running as "local system", you CAN avoid that second error.

You just need to add the user "NT AUTHORITY\SYSTEM" as a valid SQL SERVER login, and give that user permission to the DB in question. I do this regularly. Sure, one could argue that for security purposes we should run CF as a more restricted user, but again since you mentioned this in the context of local development, I just wanted to add this clarification for readers who might find this thread, while searching for either error.

One last thing: I noticed that your last comment had mistakenly left a space around the "=" in "AuthenticationMethod = ntlm". If one uses that, it would fail with that very same error in your previous comment. 🙂 So sad that these error messages can't be trusted to REALLY tell us what's amiss! Anyway, I edited your comment to remove those spaces. Again, thanks for starting the thread. Good stuff for us to help folks better understand. 

If you or anyone else may have more thoughts (or corrections if I err), great.

[chrisc42802438]

Hello, trying to implement this fix after moving from cf 2016 to 2021 but still getting an error. Should it still be working, I know there have been patches since then.

Thanks!

[Charlie Arehart]

What's the error? 

[chrisc42802438]

This is actually the same issue from yesterday you commented on.

https://community.adobe.com/t5/coldfusion-discussions/cf-2021-won-t-add-odbc-socket-connections-afte...

To tie up the loose thread, AuthenticationMethod=ntlm absolutely did work once I got the other patch/udpate issues resolved.

[Charlie Arehart]

Ah, OK. Thx.