By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,572 Members | 963 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,572 IT Pros & Developers. It's quick & easy.

Problem with Connection based Transaction in SQL Helper class

P: n/a
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.Get PooledCon
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]

--
Regards,
Rahul Anand
Nov 16 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Rahul,

This is correct. I mean, if the username and password were stored in
the connection, then it would be a huge security hole (code that is not
under your control now has the credentials to perform database operations).

You need to store this information yourself, and then re-create the
connection string with that login information.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Rahul Anand" <ra************@rediffmail.com> wrote in message
news:17*****************************@phx.gbl...
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.Get PooledCon
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]

--
Regards,
Rahul Anand

Nov 16 '05 #2

P: n/a
Thanks Nicholas for quick help.

Does that mean, I can not use SQL Helper provided

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

function for transaction based execution?

As this function uses connection string from transaction
object.

Please clarify...

--
Thanks,
Rahul Anand
-----Original Message-----
Rahul,

This is correct. I mean, if the username and password were stored inthe connection, then it would be a huge security hole (code that is notunder your control now has the credentials to perform database operations).
You need to store this information yourself, and then re-create theconnection string with that login information.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Rahul Anand" <ra************@rediffmail.com> wrote in messagenews:17*****************************@phx.gbl...
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.Get PooledCon 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]

--
Regards,
Rahul Anand

.

Nov 16 '05 #3

P: n/a

And what is recommended option to store credentials for a
connection.

Do I need to make a windows user who can connect to SQL
Server database? Please give me details about the options
available....or a pointer where I can find details.

--
Thanks,
Rahul Anand
-----Original Message-----
Rahul,

This is correct. I mean, if the username and password were stored inthe connection, then it would be a huge security hole (code that is notunder your control now has the credentials to perform database operations).
You need to store this information yourself, and then re-create theconnection string with that login information.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Rahul Anand" <ra************@rediffmail.com> wrote in messagenews:17*****************************@phx.gbl...
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.Get PooledCon 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]

--
Regards,
Rahul Anand

.

Nov 16 '05 #4

P: n/a
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/de...connections.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: ----

Thanks Nicholas for quick help

Does that mean, I can not use SQL Helper provided

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

function for transaction based execution

As this function uses connection string from transaction
object

Please clarify..

-
Thanks
Rahul Anan
-----Original Message----
Rahul
This is correct. I mean, if the username and password were stored i
the connection, then it would be a huge security hole (code that is nounder your control now has the credentials to perform database operations) You need to store this information yourself, and then re-create thconnection string with that login information
Hope this helps
-- - Nicholas Paldino [.NET/C# MVP
- mv*@spam.guard.caspershouse.co
"Rahul Anand" <ra************@rediffmail.com> wrote in

messagnews:17*****************************@phx.gbl..
Getting SQL Exception when trying to implement

Connectio based Trasaction using SQL Helper class
I am using the follwing function to execute my store

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 o
code. The exception is caused b
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 " a

System.Data.SqlClient.ConnectionPool.GetConnectio
(Boolean& isInTransaction
a

System.Data.SqlClient.SqlConnectionPoolManager.Get PooledCo nection(SqlConnectionString options, Boolean&>> isInTransaction
at System.Data.SqlClient.SqlConnection.Open(
---
---
I found on tracing that "DiscoverSpParameterSet" usag

connection string from transaction objec
i.e. "Transaction.Connection.ConnectionString". I notice the connection string retrieved from Transaction objec
does not contain the "pwd: password;" part and so th
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.Ope 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]
--

Regards,
Rahul Anand
.

Nov 16 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.