Connecting to SQL Server 7 1

[philrm]

I need to connect to an SQL server (seperate pc) from a form where the user can enter a user name and password. These need to be passed to the SQL server to be used to set permissins on the database. I have an ODBC data source set up that works through Access. Is there anything else I need to set up in the connection in terms of the security type and user name/password combination to get this to work via a web app??? I have tried setting up different users with permissions on the database but keep getting errors?
how do I pass the user name and password to the SQL server????

Connection string used was
<cfquery name = cnnstr= Provider=sqloledb datasource=test
username=uername password=pwd>

this generated the following error

Error Diagnostic Information
ODBC Error Code = 28000 (Invalid authorization specification)

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '\'.

Data Source = &quot;TEST&quot;

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (2:1) to (2:47).

 

[scoopseven]

Hi,

I solved this by setting up a datasource on my ColdFusion server that points to the SQL server database on a separate machine. Hit the &quot;CF Settings&quot; button while creating the datasource and some advanced properties will appear. Specify the SQL username and password here and your datasource should work.

Hope this helps -scoop

 

[philrm]

The datasource would then be tied to that username and password if my understanding is correct. I need the user name and password to be entered by the user and then appropriate permissions applied to the database for that user/session

 

[JimThePilot]

You can do this by configuring a datasource, as Scoop7 suggested.
If you want a different user in each session, you can achieve this by specifying the username & password, together with the preconfigured datasource.
The default user in the setup will be overridden.

Doing it this way, you can test the properties of the datasource seperately of the permissions of the user.

Thus :

<cfquery name=&quot;NameOfQuery&quot; datasource=&quot;NameOfDatasourceWithDefaultUser&quot; username=&quot;UserWithDifferentRights&quot;
PASSWORD=&quot;PasswordofthisUser&quot;>

... some query ...
</cfquery>

Hope this helps,

J.