Hello,
I am tyring to add a level of security to my application with
using a username and password to authenticate to the database with
when using CFStoredProc to call a SQL Server 2005 Express Stored
Procedure. Right now the calls to the stored procedures work fine
if I don't include the database connection information. They will
also work if I use the "SA" connection information for all
instances of the SQL Server 2005 Express Server. I cannot get the
stored procedure calls to work when I use just the database owner
of the individual instance/database of this application though. I
think this would be the ideal way to do it security-wise because I
am setting this connection information in my application.cfm file
and if that file were to ever be compromised, then they would only
be able to access the instance/database of this application instead
of all the instances contained on the whole SQL Server 2005 Express
Server. Make sense?? Below is the code in my application.cfm and a
stored procedure call if that helps as well.
<!--- setting stored procedure connection info in
application.cfm --->
<CFSET application.username = "user1">
<CFSET application.password = "password">
<!--- STORED PROCEDURE CALL --->
<cfstoredproc datasource="#application.dsn#"
username="#application.username#" password="#application.password#"
procedure="sp_GetLocations">
<cfprocresult name="GetLocations">
</cfstoredproc>
The error I get is "Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Login failed
for user 'user1'"
I am pretty sure that the user is setup correctly on the SQL
Server side as well. I used the sp_grantdbaccess stored procedure
and the user showed up in the list of users for the database. Is
there something else that I need to do on the SQL Server side?
Perhpas make that user the dbowner or something??
Thanks in advance,
airnewhouse
2
Replies
AdChoices