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

Community Beginner ,
Dec 18, 2020 Dec 18, 2020

Copy link to clipboard

Copied

ColdFusion 2016

SQL Server (SQLEXPRESS01)

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

localsqlfail-1.jpglocalsqlfail-4.jpglocalsqlfail-3.jpglocalsqlfail-2.jpg

 

Views

156

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Dec 18, 2020 Dec 18, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Dec 19, 2020 Dec 19, 2020

Copy link to clipboard

Copied

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 

 

BKBK_0-1608385641974.png

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Dec 19, 2020 Dec 19, 2020

Copy link to clipboard

Copied

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!

localsqlfail-5.jpglocalsqlfail-6.jpglocalsqlfail-7.jpglocalsqlfail-8.jpg

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Dec 20, 2020 Dec 20, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Dec 20, 2020 Dec 20, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Dec 21, 2020 Dec 21, 2020

Copy link to clipboard

Copied

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:

 

 

 

 

 

localsqlfail-9.jpglocalsqlfail-10.jpglocalsqlfail-11.jpglocalsqlfail-14.jpglocalsqlfail-12.jpglocalsqlfail-13.jpg

 

 

 

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Dec 22, 2020 Dec 22, 2020

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Dec 22, 2020 Dec 22, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Dec 28, 2020 Dec 28, 2020

Copy link to clipboard

Copied

LATEST

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines