Problem using SQL Helper Class

[Guest]

Getting SQL Exception when trying to implement Connection
based Trasaction using SQL Helper class

I am using the follwing function to execute my stored
procs

-=-=-=
ExecuteScalar(ByVal transaction As SqlTransaction,
ByVal spName As String,
ByVal ParamArray parameterValues() As Object
-=-=-=

I traced my code to reach at the exception causing line of
code. The exception is caused by
function "DiscoverSpParameterSet" under SQL Helper Class

-=-=-=-=
Dim cn As New SqlConnection(connectionString

Dim cmd As SqlCommand = New SqlCommand(spName, cn
Dim discoveredParameters() As SqlParamete
Tr
cn.Open() ''''' Exception is raised her
-=-=-=-=

Exception details
Message "Login failed for user 'UserName'.
Source ".Net SqlClient Data Provider

StackTrace " at
System.Data.SqlClient.ConnectionPool.GetConnectio
(Boolean& isInTransaction
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledCo
nection(SqlConnectionString options, Boolean&
isInTransaction
at System.Data.SqlClient.SqlConnection.Open(
---
---

I found on tracing that "DiscoverSpParameterSet" usage
connection string from transaction object
i.e. "Transaction.Connection.ConnectionString". I noticed
the connection string retrieved from Transaction object
does not contain the "pwd: password;" part and so the
connection can not be openned

Am I doing anything wrong in my coding? Please help

Thanks in advance for your time and help

[SNIP

Dim trans As SqlTransactio
Dim conn As SqlConnectio
conn = New SqlConnectio
(ConfigAccess.ConnectionString()
If conn Is Nothing The
Throw New Exceptio
ElseIf conn.State <> ConnectionState.Open
The
conn.Open(
End I
Tr
trans = conn.BeginTransaction(
Tr
SqlHelper.ExecuteScala
(trans, "procName1", param1, param2, param3
SqlHelper.ExecuteScala
(trans, "procName2", param1, param2, param3
trans.Commit(
Catch ex As SqlExceptio
trans.Rollback(
Throw e
Catch ex As Exceptio
Throw e
Finall
trans.Dispose(
trans = Nothin
End Tr
Catch ex As Exceptio
Throw e
Finall
If conn.State <>
ConnectionState.Closed The
conn.Close(
conn.Dispose(
conn = Nothin
End I
End Tr

[/SNIP

-
Regards
Rahul Anan

 

[Val Mazur]

Hi,

How your connection string looks like? If initially connection was opened
successfully, then in a case if your connection string has a Persist
Security Info set to False, then, after establishing connection to the
database, all the security information (like user id and password) will be
removed from the connection string. What you could do is to use actual
opened connection instead of the connection string.

 

[Guest]

Thanks Val !! for your help
It is exactly the same thing happening

Since the "Persist Security Info" is false by default, the connection string obtained from SqlTransaction object hides the pwd part

http://msdn.microsoft.com/library/d.../cpguide/html/cpconsecureadonetconnections.as

I have checked my code by appending "Persist Security Info=True" to my connection string, in that case everything works fine

But as it will give access to security-sensitive information I think it is not a good option to do this

Does it mean the SqlHelper class provided as DAAB does not provide a secure method to write connection based transactions under which I can execute two different stored procs

-
Regards
Rahul Anan