ColdFusion Datasource Connection Errors as Usual

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

1: 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. 

2: As for your using Windows auth for the sql server datasource, that's also known as "trusted authentication", as no username or password is entered in the cf dsn. Instead, the user running cf is what will be used to access the db. This is supported by both sql server drivers, and 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. That might be all you needed there. 

For the ms driver, you'd also need the additional connection string, integratedSecurity=true

3: As for your first error, if you get that with this setup, my guess is that's indicating that the user running the cf service has NOT yet been  identified in sql server as a permitted user for that database. That's something done in sql server.

When using/setting up such Windows auth for a db, it's indeed critical to know what is 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". In that case, the username to grant access to in sql server would be not "local system". When the dsn uses localhost or 127.0.01 as the server name, the formal Windows account name used for the sql operations would be "NT AUTHORITY\System". (If some other server name is used, it may be "nt authority\anonymous login".) 

It may well be that in your old cf instance, someone could have changed cf there to run as another user (per instructions in the cf lockdown guide, for example). In that case, that user would be used to connect to sql server. 

Either way, the first error seems to be indicating that the user running your current CF has not yet been granted permissions to the db (in sql server). As for how to grant permissions in sql server/ssms, I will leave that as something you can find readily online

Yet another option (in that second case) might seem to be that you could change the cf service to run as whatever user ran your old cf server. Just beware that beyond ensuring sql server is configured for that user, in changing the user running cf you also need to make sure to give THAT user permission to read /write the cf folders themselves, and ensure it has read access to your code, and write access to any other folders it may need to write to in your app. Again the cf lockdown guide discusses that.

4: (As an update, to my original comment, I'll add now that yet another option is to skip all the above and change to allow sql server login authentication rather than Windows auth, as discussed further in another comment here, and then just put a username password into the dsn--ensuring that user has permissions in sql server for your database.) 

As for the rest of what I've shared above, I'm not aware of any one resource that elaborates on all the points. This forum thread may serve future folks that way. 🙂 

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

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