Skip to main content
O
occasionalcoder
Participating Frequently
December 16, 2016
Answered

setting up dsn for sql server 2016

  • December 16, 2016
  • 2 replies
  • 6587 views

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

This topic has been closed for replies.
Correct answer halbob

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

2 replies

H
halbobCorrect answer
Inspiring
July 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 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.)

S
Steve Sommers
Legend
December 16, 2016

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.

O
occasionalcoderAuthor
Participating Frequently
December 16, 2016

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.

O
occasionalcoderAuthor
Participating Frequently
December 17, 2016

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!

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices