Welcome Dialog

Welcome to the Community!

We have a brand new look! Take a tour with us and explore the latest updates on Adobe Support Community.


setting up dsn for sql server 2016

New Here ,
Dec 16, 2016 Dec 16, 2016

Copy link to clipboard

Copied

When I try to connect Coldfusion 2016 to SQL Server 2016, I get the following message:

Connection verification failed for data source: xxxx
java.sql.SQLInvalidAuthorizationSpecException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'xxxx'.
The root cause was that: java.sql.SQLInvalidAuthorizationSpecException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'xxxxx'.

In SQL Server, I have SQL Server Browser running, SQL Server is set up for TCP/IP, and port set at 1433.

In ColdFusion Administrator, I have server set as 127.0.0.1 and port set at 1433. In otherwords, everything that I could find on the net has been tried and I still get the same error message.. I've even tried various passwords i have associated with my computer/microsoft/coldfusion, etc....nothing seems to work.

Any help is greatly appreciated.

Thanks

Satch

TOPICS
Database access

Views

4.8K

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

correct answers 1 Correct answer

Community Beginner , Jul 10, 2017 Jul 10, 2017
For Carl and Satch,I got the two handshaking last week.  My original post was lacking as I only registered the "master" data file under the sa ownership. I had to reach way back to find resources from earlier years to make the connection work.  I will be posting my solutions soon. I made SnagIt captures of my steps. As mentioned above, create a user, I used CFUSER. Create a test data file and assign ownership to CFUSER.  Of course, there is much more.  Until I get organized, these informative li...

Likes

Translate

Translate
Advocate ,
Dec 16, 2016 Dec 16, 2016

Copy link to clipboard

Copied

It's fairly straight forward in the response: "Login failed for user 'xxxx'."

This means that the connectivity to your SQL server is working and the SQL server is responding back to ColdFusion (and your app) with an invalid username or password type of response. Connectivity issues to the SQL server will respond with something along the lines of "can't connect..." or "timeout connecting..." but you are not seeing this so connectivity is good.

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
New Here ,
Dec 16, 2016 Dec 16, 2016

Copy link to clipboard

Copied

I've used every user name and password combination, including the one's i wrote down when installing both CF and SQL Server....none of them work. Any ideas?

Thanks again.

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
New Here ,
Dec 16, 2016 Dec 16, 2016

Copy link to clipboard

Copied

I've created a new username and password, just to be sure that i didn't write the old one down wrong, and it doesn't work either.Nothin does....need help!

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
Guide ,
Dec 18, 2016 Dec 18, 2016

Copy link to clipboard

Copied

Hi Satch,

Is SQL enabled for SQL and Windows authentication?

SQL > Server Properties > Security > Server authentication

Is a SQL user created in?

SQL > Security > Logins

Does the particular database have Login (from above) user defined?

SQL > Databases > database > Security > Users

CF datasource user and pass should match with Logins.

It is perhaps worth mentioning CF2016 does not officially support to SQL 2016:

https://helpx.adobe.com/pdf/coldfusion2016-support-matrix.pdf

HTH, Carl.

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
New Here ,
Dec 18, 2016 Dec 18, 2016

Copy link to clipboard

Copied

Thanks Carl....

I've added the user under the particular database, still no luck. Maybe I'll dump SQL Server 2016 in favor of SQL Server 2012.

Thanks again

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
Advocate ,
Dec 19, 2016 Dec 19, 2016

Copy link to clipboard

Copied

Carl's question was not really answered - or I skimmed too quickly. Withing the SQL database, is the user Is the user you added use "SQL Server authentication" or "Windows authentication"?  CF "can" use either but SQL Server authentication is more inline to your question. Windows authentication requires attaching the user credentials to the CF service, not via the username & password fields in the cfquery tag.

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
New Here ,
Dec 19, 2016 Dec 19, 2016

Copy link to clipboard

Copied

Just to be sure that I haven't messed stuff up, i created a new database (other than the one i was trying). In SQL Server, after setting up the database, i click on security and then right click user and then new user. I added a user name.  I've tried  User Type with Login, i get an error message saying that my password is not a valid login or i do not have permission. Using User Type without login, it seems to accept it and creates the user name. But i can't access it through CF administrator.

The choices seem to be UserType with or without login. It makes no reference to SQL Server or Windows Authentication.

Thanks again.

Satch

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, 2016 Dec 19, 2016

Copy link to clipboard

Copied

Delete the datasource you created in the Administrator. Restart Coldfusion. Add the datasource anew.

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
New Here ,
Jan 05, 2017 Jan 05, 2017

Copy link to clipboard

Copied

Satch,

Did you ever sort this out? I am having the same problem.

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
New Here ,
Jan 05, 2017 Jan 05, 2017

Copy link to clipboard

Copied

Never did get it to work....will try later

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
New Here ,
Jan 06, 2017 Jan 06, 2017

Copy link to clipboard

Copied

I downgraded to SQL Server Express 2014 and still have the same problem...so it's not that.

I am on Windows Server 2012, ColdFusion 2016.

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
Guide ,
Jan 08, 2017 Jan 08, 2017

Copy link to clipboard

Copied

I see mention of Express edition. When using Express you need to enable TCP protocol.

EG

Capture.JPG

Tho SQL 2016 not on official support list I find it to test ok on the two environments I tried CF11 and CF2016.

Regards, Carl.

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 ,
Jul 05, 2017 Jul 05, 2017

Copy link to clipboard

Copied

I am using Cold Fusion Server 16 and Server 2016.  I was having the same issues as above.  I enabled the TCP/IP
and then followed a couple more steps that I found by a search.

  • In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
  • On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

Change Server Authentication Mode | Microsoft Docs

I also am using the default login set with the original setup.  Although I am still having some login issues, I have been able to successfully login with a couple of test data files.

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
New Here ,
Jan 06, 2017 Jan 06, 2017

Copy link to clipboard

Copied

I was having the same problem as Satch. I downgraded from SQL Server Express 2016 to SQL Server Express 2014, but still couldn't get the login to work. I got it going by following these directions mentioned above:

Enable Remote Connection on SQL Server 2008 Express - Linglom.com

Then I also created a local user account in SQL Server Security, and then added that account with DBO schema privs to the actual database I was trying to get to.

I can get into more detail if you need it. I am not sure I needed to downgrade the database...probably not.

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
Advocate ,
Jan 06, 2017 Jan 06, 2017

Copy link to clipboard

Copied

I use various SQL versions including 2016. Downgrading should not have been the issue. Since MSSQL 2008 remote connectivity comes disabled by default and the linked instructions should fix the issue -- although I'm still a little confused by the initial error message as it appears to indicate that the connectivity portion is working.

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
Guide ,
Jul 05, 2017 Jul 05, 2017

Copy link to clipboard

Copied

> It is perhaps worth mentioning CF2016 does not officially support to SQL 2016:
https://helpx.adobe.com/pdf/coldfusion2016-support-matrix.pdf

Change to make for above. Now I see CF2016 does offer SQL 2016 support.

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 ,
Jul 10, 2017 Jul 10, 2017

Copy link to clipboard

Copied

LATEST

For Carl and Satch,

I got the two handshaking last week.  My original post was lacking as I only registered the "master" data file under the sa ownership.

I had to reach way back to find resources from earlier years to make the connection work.  I will be posting my solutions soon. I made SnagIt captures of my steps.

As mentioned above, create a user, I used CFUSER. Create a test data file and assign ownership to CFUSER.  Of course, there is much more.  Until I get organized, these informative links will help get you to the final connectivity.

1) Enable Remote Connection on SQL Server 2008 Express - Linglom.com  (relates to Server 2008 but needed).

2)  http://www.paulsilver.co.uk/blog/2014/10/setting-up-coldfusion-sql-server-express-windows-8/

3)  Enable remote connections for SQL Server Express 2012 - Stack Overflow

4) How do I configure SQL Server Express to allow remote tcp/ip connections on port 1433?

5) Configure a Server to Listen on a Specific TCP Port | Microsoft Docs

TCP/IP must be enabled. TCP dynamic ports are key: Note: the following steps are excerpted from one of the blog entries.  Posting here for reference.

--------------------------

How do I configure SQL Server Express to allow remote tcp/ip connections on port 1433?

Run SQL Server Configuration Manager.


Go to SQL Server Network Configuration > Protocols for SQLEXPRESS.
Make sure TCP/IP is enabled.
So far, so good, and entirely expected. But then:

Right-click on TCP/IP and select Properties.
Verify that, under IP2, the IP Address is set to the computer's IP address on the local subnet.
Scroll down to IPAll.
Make sure that TCP Dynamic Ports is blank. (Mine was set to some 5-digit port number.)
Make sure that TCP Port is set to 1433. (Mine was blank.)
(Also, if you follow these steps, it's not necessary to enable SQL Server Browser, and you only need to allow port 1433, not 1434.)

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