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

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

    

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. 

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.