Error Setting up SQL Server datasource in CF Admin: "Cannot open database XXXYYY requested by login"

[tobym6130]

On ColdFusion 2016 local developer edition running SQL Server 2022 I cannot set up my datasource and getting the following error: "Connection verification failed for data source: XXXYYY
java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database "aveglass" requested by the login. The login failed.
The root cause was that: java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database "aveglass" requested by the login. The login failed." I can query the database successfully directly in the SSMS. Checked permissions, other settings. Using Windows authentication.

[Charlie Arehart]

Your success in using windows auth in ssms doesn’t help, in that cf doesn't support that for authentication in talking to sql server.

But check your cf admin dsn: do you have a username and password? And is it different than your windows auth login you use to login to ssms? Have you by any chance tried logging into ssms with that?

If that fails or you don't know the password indicated in the cf admin dsn, jist check if sql server is configure to support BOTH Windows auth AND also SQL logins. By default, it's not. It's easily changed. See steps here., then test your cf dsn again. 

Let us know if that does or doesn't help. 

[tobym6130]

Reset for SQL Server and Windows Authenticaion and retsarted server. Not using a DSN username and password - letf blank. Still get message in CF Admin when verifying connection to the DB: Connection verification failed for data source: aveglass
java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database "aveglass" requested by the login. The login failed.
The root cause was that: java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database "aveglass" requested by the login. The login failed.

[Charlie Arehart]

Ok, if there's no username and password in the dsn  then you're attempting what's called trusted authentication. In that case, the user running cf needs to be indicated as a user authorized to access the db in sql server. How familiar are you with that?

If not at all, first what user is running your cf? If it's running as a windows service, see the rightmost column in the services panel. If instead you're starting cf from the command line, what user is that?

[tobym6130]

Thanks! Not familiar. All ColdFusion2016 Services are running - with "Local System" in the "Log On As" column in Windows Services. BTW I'm running both ColdFusion and SQL Server locally on my machine.

[tobym6130]

Thanks, Charlie! The username I login to CF Admin is "admin" should I use that username in SSMS for the datagase? Then what password? Same as for CF Admin? Or Windows login? Sorry to be so needy here, but I've tried almost everything I can think of.

[Dave Watts]

If you're using a native SQL login - which you probably should in most cases - you need to go into the SQL Server Management Studio, create a user, associate that user with a database login, and set the permissions for that database appropriately. This is actually easier than it sounds, and will let you manage permissions better - for example, it'll let you have multiple SQL Server accounts for the same database with different permissions, which can come in handy. A SQL Server account isn't as secure as Windows Authentication out of the box, but you can use an encrypted connection between your CF and database servers.

[Charlie Arehart]

No, you should NOT (at all) user the cf admin username/password.

This threaded interface is getting too narrow on a phone, so please see a new reply I've offered herez and share thoughts in reply to that there. 

[Charlie Arehart]

Toby, now that you've confirmed you're using cf running as local system, and the sql server is on the same machine--and assuming you prefer not to define a new sql login as Dave correctly posed as a option, there IS a way to get what you want. It involves two steps.

1) in the cf admin dsn, first make sure there's NO value in the username and password fields. Next, click the "advanced settings" button and in the "connection string" field add this:

AuthenticationMethod=ntlm

Submit that change. It will still fail to verify at first. 

2) The next step is done in sql server, where we need to add a new sql "login" for that "local system" account which is running the cf service. In ssms, go to the "security" tab for the server (not the "security" tab for a database), then right click and add a new login. The windows username is  NT AUTHORITY\SYSTEM, and while in that popup properties page give that user permission to the DB in question.

3) Now go back to the cf , and verify the dsn works (in the admin and your code). 

I do this regularly, and while many would decry it for security concerns, I'll contend they are diminished (though not eliminated) on a local machine.

4) Or just add a new sql login, again as Dave said, and give THAT user permission to the db, and put that user and its password into the cf admin. Different strokes for different folks.

I've done all this from memory, typing on a phone. Let us know of it works or not for you. 

[tobym6130]

Charlie - I tried what I think you suggested. Using Windows authentication. On the Server-level new login page, when entering NT AUTHORITY\SYSTEM as Login name getting the message that that server principal already exists. Tried using SQL Server authentication and entered new login/password. Back in CF Admin DSN, I did enter the new connection string you specified,  entered new login and password. No joy (login failed). So far no luck on trial and error any combination of options, getting "login failed" error when setting up DSN. Also in CF Admin just tried the NT AUTHORITY\SYSTEM username and a password I set up for that login and got error: An error occurred while trying to call the nativeInitialize2 method in the Type 2 security DLL. Please ensure that the DLL is correct for the driver version. Not sure what to do next. THANK YOU! for any and all help.

[Charlie Arehart]

You say, "Back in CF Admin DSN, I did enter the new connection string you specified, entered new login and password."

But I'd said specifically, "in the cf admin dsn, first make sure there's NO value in the username and password fields."

Try again, and let us know what happens next. 

[tobym6130]

Thanks so much for help! I’ve tried all you suggested as well as Dave’s method and still cannot set up DSN in CF Admin (login fails – error below), so I thought I’d share settings in case you can see anything amiss. Would really appreciate knowing if you see any anomalies, missing properties, and/or have other suggestions. The submit error in CF Admin is:

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

SETTINGS:
In SSMS:
Server: DESKTOP-HA2Q3DT (SQL Server 16.0.1000.6 - DESKTOP-HA2Q3DT\jtm)
Login Properties/Login Name: DESKTOP-HA2Q3DT\jtm
Default Database: aveglass
Server Properties: Windows Authentication
Database Name: aveglass
Database Properties database owner: DESKTOP-HA2Q3DT\jtm
I am able to succesfully query the database “aveglass” directly in SSMS

In SQL Server Configuration Manager/protocols for SQLServer (32 bit):
TCP/IP: enabled
Shared Memory: enabled

In CF Admin:
CF datasource name: aveglass
Database: aveglass
Server: DESKTOP-HA2Q3DT
Port: 1433
Username: blank
Password: blank
Connection String: AuthenticationMethod=ntlm

[Charlie Arehart]

Did you ever give that NT AUTHORITY\System login permission to access the database, as I discussed in point 2 of my note on Thursday? Or did you stop when the login already existed? Proceed to do that, using any of many amply available online web resources (including sql server docs) discussing how to give an account permissions to a database.

Or you could proceed to add a new sql server login (as Dave had originally proposed), and again give THAT login permissions to your db, then use THAT username and pw in the cf admin dsn (removing the connection string for trusted auth). 

Either should work. Let us know how it goes.

If you remain stumped, note that you need not remain stuck or engage in such back and forth over days: I can help solve this via remote screenshare, likely in less that 15 minutes (my minimum billable amount). You also won't pay for time you don't find valuable. More at carehart.org/consulting. 

[tobym6130]

Be happy to do billable engagement - can you do 6:30 pm (Central) this evening? If so pls send instructions for connecting - Zoom or Teams works for me.

[tobym6130]

My email is jtm@macknet.com and phone is 630.399.1800. I'm in a meeting from now till 6:00 pm.

[Charlie Arehart]

Sent

[tobym6130]

Charlie - not yet received. jtm@macknet.com or 630.399.1800

[Charlie Arehart]

For others, I've called him to provide the meeting info. 

[tobym6130]

On Zoom with your meeting ID

[BKBK]

@tobym6130 and @Charlie Arehart , I am glad to see that you are going to join forces and try to solve this off-line. For the benefit of others, I shall now create a new MS SQL Server datasource in ColdFusion from scratch. In doing so, I shall show all the steps I use.  Anyone can then copy the steps in their own setup.

Requirements:

• SQL Server Management Studio (SSMS) has been installed to help manage MS SQL Server.
  My version is SQL Server Management Studio v. 20.
• ColdFusion has access to MS SQL Server's IP and port.
  My MS SQL Server's IP and port are 127.0.0.1 and 1433

Settings:

• (assumed) a database named 'aveglass' exists in MS SQL Server. 
• (ignored) a login exists for ColdFusion  in MS SQL Server.  For the present purposes I shall create a new login for ColdFusion in MS SQL Server

The steps:

1.  Start SQL Server Management Studio. Select Windows Authentication and click on Connect. thereby opening the main interface.
   
2.  To start off, we want to create a new login for ColdFusion on MS SQL Server. 
        Click on the [+] next to Security to expand. Right-click on Logins and select New Login.
   
3.  Enter CFonMSSQL as Login name. (This is the brand new login we wish to create for ColdFusion).
        Select SQL Server authentication.
        Enter the password and confirmation password. (Make sure you have a way to remember it).
        To avoid complications for the moment, uncheck the setting Enforce password policy.
         Click on OK.
   
4.  We now want to give the user CFonMSSQL access to the database aveglass.
        Click on the [+] next to Databases to expand. You will see that aveglass is one of the databases.  
   
        Click on the [+] next to aveglass, then on the [+] next to Security underneath. 
        Right-click on Users, and select New user...
   
5.  Enter CFonMSSQL as both the User name and the Login name, and press OK. This ensures that ColdFusion, as MS SQL user, now has access to the database aveglass.
   
   We're done with the SSMS settings, so close it.
6.  We now want to configure the ColdFusion datasource for the database aveglass. To do so, open the Data & Services page of the ColdFusion Administrator. "Add" a datasource with the following settings:
        - Data Source Name: XXXYYY
        -  Driver: Microsoft SQL Server
   
7.  Fill the remaining datasource settings as follows:
        Database: aveglass
        Server: 127.0.0.1 (the IP of the MS SQL Server that your ColdFusion server uses)
        Port: 1433 (the port to connect to MS SQL Server)
        User name: CFonMSSQL
        Password: (the password that you stored in SSMS for user CFonMSSQL)
   
8.  When I press the Submit button, I see that the new MS SQL datasource (named XXXYYY) has been created.