[thelist] SQL Server: making the connection

Susan Wallace susanhw at vonl.com
Tue Nov 27 13:19:54 CST 2001


Greetings!

I was having a bit of a problem with a SQL server connection. After trial 
and error, I managed to configure this to work for me, but although I can 
tell you what I did, I can't tell you *why* it worked. I spent time looking 
online for the answer to my particular problem, but never did find a true 
overview in condensed form to explain the why's. What I would like to do is 
make this into a tip, but not without some details.

Following is an overview of my problem (which is apparently common) and the 
steps I took to correct it. What I would greatly appreciate is some input 
from some Cold Fusion / MSFT SQL Server gurus to fill in my blanks below. I 
will then compile it into at least a tip, perhaps an article. I didn't see 
one already on evolt.org.

Feel free to e-mail me off-list.


Environment: MSFT SQL Server 7, SP3, NT4, Cold Fusion Application Server 4.5
Requirements: from a web server, connect to a datasource on the SQL server 
- a physically separate machine.

Initial Problem:

  ODBC Error Code = 37000 (Syntax error or access violation)

  [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 
'user name'. Reason: Not associated with a trusted SQL Server connection.

Looked up Error, discovered that according to MSFT, this is a "known issue" 
I needed SP3 on the machine. Installed that. Same error.

Looked on google again, found information in a tech article that says the 
cause is:

TCP/IP and SQL Server authentication are not being used on both the Server 
and the client.
I went to Ent. Manager, in Security checked "Windows NT and SQL Server 
Auth" (NT Only was checked before)
Restarted the SQL service
Now functions properly.

Through all of that trial and error, I believe these to be the steps 
necessary to successfully do this right the first time:

(Assuming all required service packs have been installed on your SQL Server 
software)
STEPS:
1) Create Database on SQL Server
2) Create login account on the SQL server with proper permissions to access 
the database.
3) Make sure that the server is set to accept Windows NT and SQL Server 
Authentication
4) Setup ODBC connection using TCP/IP on the web server machine to the new 
datasource

QUESTIONS:
	a) When in this environment, obviously you do not want to give DBO rights 
to the login account. What are the *minimum* permissions required to Read, 
Write and Change records?)
	b) For security purposes, it made sense to me for this access account to 
be a SQL Account rather than a domain account. I didn't think there needed 
to be that overhead of validation within the Domain. Is this "the 
preferred" method? Most secure? Absolutely wrong? I don't know how to 
answer that, and don't want it to cause a problem.
	c) Initially, I setup the datasource in the CF Administrator, but 
discovered that by default, that was using Named Pipes. I had to change 
that in the ODBC Manager in Control Panel. I changed it to TCP/IP.

Named Pipes did not work for me, but admittedly I was "pushing buttons". (I 
can do that in my case because it's not a live server yet...) Most of the 
threads at forums.allaire.com said it had to be TCP/IP but no one explained 
why. To clarify, I understand what Named Pipes are, is this a limitation of 
an ODBC Connection in Cold Fusion? Or is it just not wise to use Named 
Pipes in a web environment?

Overall - where is the fault in my setup here?  :)

Thanks for your help!
Susan Wallace


  





More information about the thelist mailing list