setting up dsn for sql server 2016

[occasionalcoder]

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

[Steve Sommers]

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.

[occasionalcoder]

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.

[occasionalcoder]

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!

[carl type3]

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.

[occasionalcoder]

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

[Steve Sommers]

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.

[occasionalcoder]

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

[BKBK]

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

[ebcohen]

Satch,

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

[occasionalcoder]

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

[ebcohen]

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.

[carl type3]

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

EG

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.

[halbob]

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.

[ebcohen]

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.

[Steve Sommers]

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.

[carl type3]

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

[halbob · Accepted solution]

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.)