Can not sucessfully connect to a datsource on desktop (local instance) SQL Server ?

[seasonedweb]

ColdFusion 2016

SQL Server (SQLEXPRESS01)

See below- any help greatluy appreciated. A thorny issue.

[Dave Watts]

There are lots of potential problems you can have connecting to MS SQL Server. The problem you appear to be having is a network problem, rather than an authentication problem or an ODBC problem (you shouldn't even need an ODBC driver configured here). Take a look at your SQL Server Configuration Manager and make sure that you have TCP/IP enabled. I don't remember if there are any specific issues with using named instances, but you also need to make sure your instance has a specific port and make sure CF uses that. Ideally, you should be able to plug in an IP address for the database server and a port and it should work.

I see that you're using Windows authentication, and CF doesn't support that without some additional fiddling around, so you'll have to solve that afterwards. It's easier to configure mixed mode authentication in SQL Server instead (although not as secure).

Dave Watts, Eidolon LLC

[BKBK]

I agree with Dave: you don't need an ODBC connection. Though I can understand you trying it when other options fail.

Suggestion: in the Administrator, click on the "Edit" button next to the datasource "LaPoliceDesLauriers2021SQL" edit the settings as follows

CF Data Source Name: LaPoliceDesLauriers2021SQL
Database: LaPoliceDesLauriers2021SQL
Server: 127.0.0.1 (enter the IP address of the database server)
Port: 1434 (enter the port number of the database server)
User name: database-username
Password: database-password 

[seasonedweb]

I want to thank Dave & BKBK for your advice. I still have not been able to establish the SQL DB as a CFDatasource.

Here are a few more images- CFAdmin now throwing a different error. Thanks again!

[BKBK]

You apparently didn't fill the User name and Password. Refer to my last post.

[Dave Watts]

You're getting a connection failure, which means you've specified the wrong IP address and port. You need to go into SQL Server Configuration Manager and see what those are for this specific named instance.

Once you do that, you'll need to put in a username and password that works with SQL Server native authentication, as @BKBK mentioned.

Dave Watts, Eidolon LLC

[seasonedweb]

Greetings

Again, thank you for taking the time to try & solve this.

I never issued, as far as I know, a UN or PW for the SQL Server Express local instance.

Lots of conflicting info out there, but If I need to change windows authentication to mixed or strictly SQL authentication, I tried everything I know of to do that- still failed to add DB in CF admin. I have sucessfully connected to remote SQL servers for years with no issues- with the CF/SQL Hosting I have now, one just checks "cold fusion data source" and all is well.

I am surpised that it would be such an ordeal to work with a simple local SQL instance.

I am not concerned about security while working locally- I could just as easily uplodd the DB to my remote host, but I relly want to work on it locally before the app goe live.

BTW I still us Macromedia Homesite & been "delving lightly" in CF since it was called Cold Fusion (two words). I actually met the Allaire brothers in Cambridge, MA. I was very adept at hosting Access DB-driven apps fpr 10 yaers before I strted with SQL Server. But that's another story. I'm 70 y.o. so be kind...

I always stop/start SQL services after I make changes. Here is the latest:

[BKBK]

Two things:

1) You had used 1434 as port number in the Administrator. It appears the correct port is 1433.

2) If it still fails, follow Dave's suggestion. For example, on the database server, create a new user to represent ColdFusion. Then use the new User-Name and Password when creating the datascource in the Administrator.

[Dave Watts]

I just hope I'm as active as you are when I'm seventy!

The problem you're having is a connection failure. This doesn't have anything to do with authentication. It means you're knocking at the wrong door, so to speak. SQL Server makes this process pretty complicated if you're starting from scratch. By default, the listening port for SQL Server is TCP/1433. But if you have multiple instances, each instance on a server can have a different listening port, which is why it's important to know which instance is using which port. So, read through this:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-...

Once you get a different error, I suspect you'll get an authentication error because of the Windows authentication stuff discussed earlier. By default, I think, SQL Server only allows Windows authentication. This will allow you to connect (I'm guessing you're "Nu") but uses Windows credentials to connect, and those don't include a simple password. You'll need to allow "mixed mode authentication" which supports both Windows credentials and native SQL logins:

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

Dave Watts, Eidolon LLC

[BKBK]

seasonedweb: BTW I still us Macromedia Homesite & been "delving lightly" in CF since it was called Cold Fusion (two words). I actually met the Allaire brothers in Cambridge, MA. I was very adept at hosting Access DB-driven apps fpr 10 yaers before I strted with SQL Server. But that's another story. I'm 70 y.o. 

Hats off, Sir. What an inspiration you are.

I hope your datasource is now up and running.