473,320 Members | 1,920 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Problem with Connection based Transaction in SQL Helper class

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
4 5593
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
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

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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...
7
by: Zachary Hilbun | last post by:
The below is some test code to help me learn how to update a dataset. It is supposed to read the value of UserCounter and write it back. The UserCounter is being read correctly as 0, is 1 when...
5
by: Jason Huang | last post by:
Hi, The SqlParameter myPM =new SqlParameter("@Address", txtAddress.Text) is working for update, but SqlParameter myPM =new SqlParameter ("@Address",SqlDbType.NVarChar,90,txtAddress.Text) is...
4
by: Prabhat | last post by:
How do I lock a particular record that one user has opened for editing? If I use the pessimistic type, can other users view the record (but not edit it) and return a message telling that another...
4
by: Rahul Anand | last post by:
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...
3
by: Sean Shanny | last post by:
To all, We are running postgresql 7.4.1 on an G5 with dual procs, OSX 10.3.3 server, 8GB mem, attached to a fully configured 3.5TB XRaid box via fibre channel. I think we have run into this...
0
by: Daniel Crespo | last post by:
Hi to all, I'm using adodb for accessing mysql and postgres. My problem relies on the mysql access. Sometimes, when I try to execute a query (using ExecTrans method below), I get this error:...
2
by: timpera2501 | last post by:
I am a newb to OOP programming and I'm only just starting with C#. I've done a TON of reading lately, and I'm currently in the process of modifying some of the function provided by the...
0
bartonc
by: bartonc | last post by:
This is a work in progress (current and active). There are some issues to resolve in supporting multiple connection types and I plan to add PySQLite to the mix. The this update is due to my...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.