Highlighted

[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name

New Here ,
Aug 19, 2019

Copy link to clipboard

Copied

This is the query that I am trying to run and I am getting the above error

<CFPARAM NAME="Form.UserLogin" default=" " TYPE="string">

<CFPARAM NAME="Form.UserPassword" default=" " TYPE="string">

<CFQUERY NAME="GetUser" DATASOURCE="Blinkey">

SELECT *

FROM OWContacts

WHERE UserLogin  = '#Form.UserLogin#'

AND UserPassword = '#Form.UserPassword#'

</CFQUERY>

<cfdump var="#GetUser#">

This query is for the login page of the intranet system that I am responsible for.

I've read several threads trying to figure out the problem and as of yet haven't found a good answer.  The code works in CF9, but I am trying CF2018 on my test server and I keep getting the error.  I've checked the DSN information and the data source that is being called is marked as the default database for the login. 

Adobe Community Professional
Correct answer by Dave Watts | Adobe Community Professional

It's possible to use Windows authentication from CF to MS SQL Server, but for solving the immediate problem I recommend you configure SQL Server for mixed-mode authentication and create a user account in there for your application.

Dave Watts, Eidolon LLC

Views

1.4K

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

[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name

New Here ,
Aug 19, 2019

Copy link to clipboard

Copied

This is the query that I am trying to run and I am getting the above error

<CFPARAM NAME="Form.UserLogin" default=" " TYPE="string">

<CFPARAM NAME="Form.UserPassword" default=" " TYPE="string">

<CFQUERY NAME="GetUser" DATASOURCE="Blinkey">

SELECT *

FROM OWContacts

WHERE UserLogin  = '#Form.UserLogin#'

AND UserPassword = '#Form.UserPassword#'

</CFQUERY>

<cfdump var="#GetUser#">

This query is for the login page of the intranet system that I am responsible for.

I've read several threads trying to figure out the problem and as of yet haven't found a good answer.  The code works in CF9, but I am trying CF2018 on my test server and I keep getting the error.  I've checked the DSN information and the data source that is being called is marked as the default database for the login. 

Adobe Community Professional
Correct answer by Dave Watts | Adobe Community Professional

It's possible to use Windows authentication from CF to MS SQL Server, but for solving the immediate problem I recommend you configure SQL Server for mixed-mode authentication and create a user account in there for your application.

Dave Watts, Eidolon LLC

Views

1.4K

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
Aug 19, 2019 0
Adobe Community Professional ,
Aug 19, 2019

Copy link to clipboard

Copied

There are some things you are not saying, which may help us help you:

  • what "object" did it report was invalid? it could be the table name or the column names (or both)
  • is the sql server being called from CF2018 the exact same one called from CF9? And is it the same database? and using the same user defined in the CF Admin (of both CF9 and 2018)?

If you did change to using a new SQL Server instance, you may be assuming that things on the "new" sql server are configured the same as the "old", but they may not be.

  • for instance, it could be that user set in the CF Admin (for the DSN) is defined in the "new" server to not have the same default schema as was true for that user on the "old" server 
  • this is another reason why I'm asking what object it errors on. Or is there no named object?
  • have you tried just prefixing the table name with "dbo.", as in "dbo.OWContacts"? That's a traditional schema, which may work for such an old code base. But it may not work, fi your schema names are different (in sql server, for that database)
  • If that does work, then obviously you won't want to change all your code.  Again, the solution is to change that user in sql server to use that schema as their default for the table (or you may be able to change it for the table. I will leave others to elaborate on options)

If none of the above help, and especially if the error message doesn't identify the object, another option would be to copy this code into a new test page and run it from there. You could take out all but the cfquery, of course. And you could put in some sample values for the values that were to be passed in as variables. If you do that, do you get the same error?

  • what if you then take out the where clause? (you may want to change the "select *" to a "select count(*)", so that it doesn't return ALL rows and cols, but instead just a count of the number of rows)

Those are a few thoughts to try to get you started.

/Charlie (server troubleshooter, carehart.org)

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
Reply
Loading...
Aug 19, 2019 0
New Here ,
Aug 19, 2019

Copy link to clipboard

Copied

The error was on the table name OWContacts.  No, it is a different named database on a different server.  Yes, I have tried prefixing the able name, and I pulled the code and put it in a test page.  The data source that is being called is the default database in SQL and the schema is dbo.

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
Reply
Loading...
Aug 19, 2019 0
Adobe Community Professional ,
Aug 19, 2019

Copy link to clipboard

Copied

Ok,  but look in sql server: what is the default schema for that user (defined in the cf admin), for that database which the dsn points to.

You can also try logging into ssms with that same user, and try the same query there. If it fails there, it confirms that the problem is not about CF. (I realize you just want it solved. I'm saying I think the answer is in sql server setup, not cf setup.,)

If it doesn't happen there, then we're back to what in CF could be causing this. But let's wait and see.

/Charlie (server troubleshooter, carehart.org)

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
Reply
Loading...
Aug 19, 2019 0
Adobe Community Professional ,
Aug 20, 2019

Copy link to clipboard

Copied

Some suggestions:

1) Verify that there exists a table named OWContacts in the database that you registered in the Blinkey datasource. Is the name of the table perhaps OWContact ?  Was the database made case-sensitive?

2) Add try/catch as follows, and share the dump with the forum:

<cftry>

    <CFQUERY NAME="GetUser" DATASOURCE="Blinkey">

        SELECT *

        FROM OWContacts

        WHERE UserLogin  = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Form.UserLogin#">

        AND UserPassword = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Form.UserPassword#">

    </CFQUERY>

    <cfdump var="#GetUser#">

<cfcatch type="any">

    <cfdump var="#cfcatch#" >

</cfcatch>

</cftry>

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
Reply
Loading...
Aug 20, 2019 0
New Here ,
Aug 20, 2019

Copy link to clipboard

Copied

I realized after further study that the connection string that was in CF Admin was pointing to the master DB instead of Blinkey.  I changed that  and now I getting the error below:

  • Connection verification failed for data source: Blinkey
    java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database "Blinkey" requested by the login. The login failed.
    The root cause was that: java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database "Blinkey" requested by the login. The login failed.

I read somewhere that if you set the authentication to both Windows and SQL authentication (in SSMS) then you don't enter a username and password in CF Admin.

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
Reply
Loading...
Aug 20, 2019 0
Adobe Community Professional ,
Aug 20, 2019

Copy link to clipboard

Copied

Well, that error can mean a few things, and that feature has a little more to it than you say.

First, as for whether you have "both windows and sql auth" enabled, if you ARE specifying a username/password in the CF Admin DSN, but sql auth is NOT enabled, that will be a reason for login failure. So enabling sql auth may fix the problem.

As for if windows auth is enabled, then that means if you DO NOT specify a username/ps in the CF Admin, then CF will try to use the username that CF *runs as*. It's generically called "trusted authentication". 

Whichever the case, whatever user is then being used in CF to connect to the DB, that user has to be identified in SQL SERVER as having access to that DB.

Finally, if it IS using sql auth, and the user IS defined as having access to the DB, then make sure that password you have works. Try it in SSMS directly.

/Charlie (server troubleshooter, carehart.org)

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
Reply
Loading...
Aug 20, 2019 0
Adobe Community Professional ,
Aug 20, 2019

Copy link to clipboard

Copied

srouse72  wrote

I realized after further study that the connection string that was in CF Admin was pointing to the master DB instead of Blinkey.  I changed that  and now I getting the error below:

  • Connection verification failed for data source: Blinkey
    java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database "Blinkey" requested by the login. The login failed.
    The root cause was that: java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database "Blinkey" requested by the login. The login failed.

I read somewhere that if you set the authentication to both Windows and SQL authentication (in SSMS) then you don't enter a username and password in CF Admin.

Yes, you need to enter the SQL Server username and password. In any case, there seems to be confusion in your settings between database_name and datasource_name. The datasource name is the name you give the datasource in the ColdFusion Administrator. This name is arbitrary. You apparently chose the name Blinkey for this.

The database name, on the other hand, is the name of the database in SQL Server. That is, the database that contains the table OWContacts. The database may also be named Blinkey, of course

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
Reply
Loading...
Aug 20, 2019 0
New Here ,
Aug 20, 2019

Copy link to clipboard

Copied

Yes, I deliberately named the data source the same thing as my database.  However, I never set up a username and password for SQL Server.  In SSMS when I connect to the server it uses Windows Authentication but I've tried those credentials and it doesn't work.

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
Reply
Loading...
Aug 20, 2019 0
Adobe Community Professional ,
Aug 20, 2019

Copy link to clipboard

Copied

So you're confirming that you have taken the username and password that you were using in the CF Admin DSN (and which did not work), and you have used them in SSMS to try to login, and it is saying they do not work?

If so, then the problem is either that the password is wrong (and you need to change it), the username does not exist (and you need to create it), or it may exist (so that you will be able to login onec you correct the password) but it may not be associated as a valid user for the db in question.

All these are problems to be solved in SSMS (with a login that can let you manage such things).  Are you telling us you are pursuing those, or are you feeling stuck still?

/Charlie (server troubleshooter, carehart.org)

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
Reply
Loading...
Aug 20, 2019 0
New Here ,
Aug 20, 2019

Copy link to clipboard

Copied

I did not set up a login/password for the DSN. I did not set up a login/password for SQL server.

I use my Windows authentication for SSMS. 

If I need to set up a username and password in SQL then I'll do that.

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
Reply
Loading...
Aug 20, 2019 0
Adobe Community Professional ,
Aug 20, 2019

Copy link to clipboard

Copied

It's possible to use Windows authentication from CF to MS SQL Server, but for solving the immediate problem I recommend you configure SQL Server for mixed-mode authentication and create a user account in there for your application.

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
Reply
Loading...
Aug 20, 2019 1
Adobe Community Professional ,
Aug 20, 2019

Copy link to clipboard

Copied

You have two choices. The first is yes, you can create a username and password in SQL Server (and put that in the CF Admin), if that's how you had used it when things "worked" on CF9.

Or, if you feel that CF9 "never used a username and password", then you would be trying to use "trusted authentication", whereby CF would connect via windows auth to SQL Server (similar to how you do in SSMS). But for that to work, the Windows service running your CF2018 needs to be set to run as a specific user, and then THAT user needs to be defined in SQL Server and associated with that DB.

Beware that to change the CF service to run as a user, you will likely need to create that user (in Windows, on the CF machine). Then you will need to be sure to give permissions to that user for all things that CF needs to touch (the CF folder, your code folder, and any folders your code reads to/writes from). It's a pretty major change, and easy to have things go amiss.

And for that reason, most people don't do that, but instead they just create a new user in SQL Server, and associate that user with the DB, and then put that username and password into the CF Admin (after also making sure SQL Server is set to allow both windows and sql server authentication).

No, this stuff is not so easy. There are many moving parts--and often choices, as above. That makes it hard to offer answers in the size of a tweet--or if one favors them, and finds them, they often leave you lost because they don't account for all the different possibilities.

And yes, this is why consultants like me (and others here) exist and do this stuff for a living. But as you can see we also try to offer help for free here. It's just that sometimes, things would be far more easily solved in a single, brief remote screensharing solution, than in a lot of messages back and forth. But some people prefer that, and so help here, we do.

/Charlie (server troubleshooter, carehart.org)

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
Reply
Loading...
Aug 20, 2019 0
Adobe Community Professional ,
Aug 20, 2019

Copy link to clipboard

Copied

And as often happens, in the time I was writing my login message, DW chimed in with the twitter version of the answer that may seem to prove my last assertion wrong. 🙂 If that would have been enough, so be it.

/Charlie (server troubleshooter, carehart.org)

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
Reply
Loading...
Aug 20, 2019 0
New Here ,
Aug 20, 2019

Copy link to clipboard

Copied

Ok, I set SQL Server to mixed-mode authentication, and the database is attached to my agency user account (if that is what you mean about creating a user account for my application). Do I need to add a connection string to the advanced settings, or a validation query?  I really feel like I'm going around in circles.

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
Reply
Loading...
Aug 20, 2019 0
srouse72 LATEST
New Here ,
Aug 21, 2019

Copy link to clipboard

Copied

Thanks you Dave.  I created a new login and changed the database owner to the new login and it worked like a charm!

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
Reply
Loading...
Aug 21, 2019 0