i have an ASP .net application with sql server database, and i am wondering if you can give your ideas on how to troubleshoot the following issue:

i can insert / update / delete from any table, but i have one page that uses transactions to insert into different tables. the c# code is correct and very simple, but it fails.

i used the sql profiler to see how my app interacts with the DB, especially that the app is using stored procedures, i can catch the exec procedure statement and run it manually from SSMS and it works fine, but the same stored procedure fails from the application!!! which lead me to think that issue is coming from the user account and settings, i am no expert in sql server and wondering if anyone can explain how to verify the required settings for user account. thanks

EDIT:

in web.config here is how i reference my connection

<connectionStrings>
<add name="Conn" connectionString="Data Source=localhost;Initial Catalog=myDB;Persist
 Security Info=True;User ID=DbUser;Password=password1254_3" providerName="System.Data.SqlClient">
</connectionstring>

EDIT:

i will try to describe the process here:

1- i begin a transaction

2- i call a stored proc to insert (which succeeds) and return the scope identity ( that will be used in the next step)

3- i call another stored procedure to insert some more info + scope identity from step 2, which is a foreign key here

4- i get error, foreign key violation

5- transaction rolled back, now tables empty again...

thanks