Problem with Connection based Transaction in SQL Helper class

[Rahul Anand]

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 SqlParameter
Try
cn.Open() ''''' Exception is raised here
-=-=-=-=-

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

StackTrace " at
System.Data.SqlClient.ConnectionPool.GetConnection
(Boolean& isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledCon
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 SqlTransaction
Dim conn As SqlConnection
conn = New SqlConnection
(ConfigAccess.ConnectionString())
If conn Is Nothing Then
Throw New Exception
ElseIf conn.State <> ConnectionState.Open
Then
conn.Open()
End If
Try
trans = conn.BeginTransaction()
Try
SqlHelper.ExecuteScalar
(trans, "procName1", param1, param2, param3)
SqlHelper.ExecuteScalar
(trans, "procName2", param1, param2, param3)
trans.Commit()
Catch ex As SqlException
trans.Rollback()
Throw ex
Catch ex As Exception
Throw ex
Finally
trans.Dispose()
trans = Nothing
End Try
Catch ex As Exception
Throw ex
Finally
If conn.State <>
ConnectionState.Closed Then
conn.Close()
conn.Dispose()
conn = Nothing
End If
End Try

[/SNIP]

 

[Cor Ligthert]

Hi Rahul,

I give some answers inline (probably not all), however I think this is more
a question for the newsgroup.

Adonet
<
Web interface:

<http://communities2.microsoft.com/communities/newsgroups/en-us/?dg=microsof
t.public.dotnet.framework.adonet>


I hope this helps, however there is a lot of guessing in my message?

Cor

-=-=-=-
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 SqlParameter
Try
cn.Open() ''''' Exception is raised here
-=-=-=-=-

When the exception is raised here you have or a wrong connection string, or
not a connection at all. And if so I would in this case first make a
connection with the designer and test that.

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

StackTrace " at
System.Data.SqlClient.ConnectionPool.GetConnection
(Boolean& isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledCon
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 SqlTransaction
Dim conn As SqlConnection
conn = New SqlConnection
(ConfigAccess.ConnectionString())

This set a new connection not a connection open

If conn Is Nothing Then
Throw New Exception

Therefore is in my opinion the two lines above useless

ElseIf conn.State <> ConnectionState.Open
Then
conn.Open()
End If


Try
trans = conn.BeginTransaction()
Try
SqlHelper.ExecuteScalar
(trans, "procName1", param1, param2, param3)
SqlHelper.ExecuteScalar
(trans, "procName2", param1, param2, param3)
trans.Commit()
Catch ex As SqlException
trans.Rollback()
Throw ex
Catch ex As Exception
Throw ex
Finally
trans.Dispose()
trans = Nothing
End Try
Catch ex As Exception
Throw ex
Finally
If conn.State <>
ConnectionState.Closed Then

This is useless again in my opinion.

conn.Close()
conn.Dispose()
conn = Nothing

The conn.Close closes the connection
The conn.Dispose closes the connection and removes the connection string

You have to choise from two of them where the dispose is advices for
networks with more than 100 connections.

Setting it to Nothing is useless after this all, and not advices to do.

End If
End Try

[/SNIP]

 

[Rahul Anand]

Hi Cor,

When the exception is raised here you have or a wrong
connection string, or
not a connection at all. And if so I would in this case
first make a
connection with the designer and test that.

I also think the same.
You are right in your guess...

As I have already stated:

The problem is basically in SQL Helper (DAAB)

http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnbda/html/daab-rm.asp

It uses Connection string extracted from SqlTransaction
object. But the connection string obtained from a
SqlTransaction object hides the "pwd: password" part. And
hence the connection can not be openned with this
connection string.

If anybody have used connection based transaction using
SQL Helper Class ? Please help...

Or is there any bug in SQL Helper ? Which does not allow
us to do so..

 

[Guest]

I found the root cause for this problem:

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


----- Rahul Anand wrote: ----

Hi Cor

When the exception is raised here you have or a wrong
connection string, o
not a connection at all. And if so I would in this case
first make
connection with the designer and test that

I also think the same
You are right in your guess..

As I have already stated

The problem is basically in SQL Helper (DAAB

http://msdn.microsoft.com/library/default.asp
url=/library/en-us/dnbda/html/daab-rm.as

It uses Connection string extracted from SqlTransaction
object. But the connection string obtained from a
SqlTransaction object hides the "pwd: password" part. And
hence the connection can not be openned with this
connection string

If anybody have used connection based transaction using
SQL Helper Class ? Please help..

Or is there any bug in SQL Helper ? Which does not allow
us to do so.

-
Thanks
Rahul Anan

 

[Chris Dunaway]

I found the root cause for this problem:

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.asp

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 ?

There is an overload to the ExecuteScalar method that takes a SqlConnection
rather than a connection string. Have you tried creating your own
connection object? Use that connection object to create the Transaction
and then use the ExecuteScalar method overload that takes both objects as
parameters.

Just a thought