[thelist] SQL Server: making the connection

[Chris Blessing]

I know nothing of CFML but I can offer some information regarding SQL
server...

It looks as though the fault was caused by not using NT/SQL Auth.  That's
about all I can see being a problem.  Regarding minimum permissions, you
should allow select, insert, update, and delete for minimum functionality
including read/write.  Whether you do this on a global/database/table/column
level is up to you.  Aside from that, sounds like you got things together
pretty well. ;)

Chris Blessing
webguy at mail.rit.edu
http://www.330i.net

> -----Original Message-----
> From: thelist-admin at lists.evolt.org
> [mailto:thelist-admin at lists.evolt.org]On Behalf Of Susan Wallace
> Sent: Tuesday, November 27, 2001 2:20 PM
> To: thelist at lists.evolt.org
> Subject: [thelist] SQL Server: making the connection
>
>
> 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
>
>
>
>
>
> ---------------------------------------
> For unsubscribe and other options, including
> the Tip Harvester and archive of TheList go to:
> http://lists.evolt.org Workers of the Web, evolt !