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 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
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
.
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
.
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 ithe 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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:...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
| |