Skip to main content
S
solaced
Known Participant
October 23, 2012
Question

SQL Server Connection with Windows Authentication

  • October 23, 2012
  • 1 reply
  • 10713 views

It is considered bad practice to allow other systems and users to connect to a database using the 'sa' login on SQL Server, so the best method would be to use Windows Authentication.... but how?

I have gone to Datasources in CF9 administrator, added a new Datasource, put in the correct server name and database names. I have left the username and password blank as per the instructions on Adobe's CF documentation:
http://help.adobe.com/en_US/ColdFusion/9.0/Admin/WSc3ff6d0ea77859461172e0811cbf364104-7fe5.html#WS7EBC2C2B-C1AE-45cf-9374-F468BC63AF0A

It simply will not connect, I get a notice along the lines of "login failed" for the database. If I put in 'SA' as the username and the password it will connect fine.

How can I allow CF to accces my SQL Server database through just Windows Authentication? My CF9 and SQL Server installation are on the same machine on the same hard drive.

I am running Windows Server 2008 R2 with SQL Server 2012 Enterprise.

This topic has been closed for replies.

1 reply

R
Reed_Powell-ttnmOb
Inspiring
October 23, 2012

Is the username that used in SQL Admin when you created Windows login the same login that the CF service is using to login?  By default, CF is going to be running under a service account, not a Windows login.

The other approach to not using sa is to create SQL (not Windows) logins in SQL Admin specifically for CF to use, and then specify those in CF Admin when defining the datasources.  If you have multiple CF servers, you might think about having a different login for each one, just to make things a little more obvious when looking at active queries on the SQL server.

-reed

S
solacedAuthor
Known Participant
October 23, 2012

Hi Reed, thanks for the reply.

Basically I have logged onto the server as the Administrator, installed SQL and CF9. Left everything as default values during installation. I'm a novice at server administration so I don't know what you mean when referring to CF's service account vs a Windows login. There is only one user account on the server which is the Administrator and thats it.

In CF9 I have created a new datasource using Microsoft SQL Server as the connection type. I put in the database name correctly and the server as 127.0.0.1 on 1433. I left everything else blank and it just won't connect - it says login failed. As soon as I put the sa as username and password in, it connects fine.

But I really don't wan't to use any login in SQL Server if I can avoid it and the instructions in the ColdFusion documentation make it seem that it should work with blank values. Is the CF documentation wrong?

R
Reed_Powell-ttnmOb
Inspiring
October 23, 2012

First off, let me say that in the past (pre v9) when I've tried to use Windows logins with datasources, it never worked.  But that doesn't mean that they finally got it working, just that I stopped trying.

What I was referring to in my post is that once you have CF installed, you need to go to the Windows Services screen, open each of the CF processes, click on the LOGIN tab, and change it from the default services login to be whatever WIndows login you have setup in Active Directory.  Then restart CF so that it's process is now running under that login with its credentials.  Then go to SQL Admin and setup that same login and give it access to the database you want to access.  Then go to CF Datasource menu and setup the datasource as you did previously.  If CF's support of Windows authentication for datasources does in fact now work, you should be in business.

If it doesn't work, then you'll need to do as I said earlier, and setup SQL logins for the CF servers to use, and then in the CF Datasource setup screens you would specify that login info in the same way that you would have given it the "sa" login info.

-reed

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