using application role problem

[Alberto]

This is my code:
Dim miacnn As New SqlClient.SqlConnection("data source=<name server>;initial
catalog=<database>;persist security info=False;user
id=<userid>;password=<pwd>r;workstation id=DENTE;packet size=4096")

Dim miacmd As New SqlClient.SqlCommand("", miacnn)

Dim miord As SqlClient.SqlDataReader

miacnn.Open()

miacmd.CommandType = CommandType.Text

miacmd.CommandText = "EXEC sp_setapprole '<user>', '<password>'"

miacmd.ExecuteNonQuery()

miacmd.CommandText = "select * from login"

miord = miacmd.ExecuteReader

Do While miord.Read

Response.Write("<br>" & miord(0))

Loop

miord.Close()

miacnn.Close()

miacnn.Open()

miacmd.CommandType = CommandType.Text

miacmd.CommandText = "EXEC sp_setapprole '<user>', '<password>'"

miacmd.ExecuteNonQuery()

miacmd.CommandText = "select * from login"

miord = miacmd.ExecuteReader

Do While miord.Read

Response.Write("<br>" & miord(1))

Loop

miord.Close()

miacnn.Close()



In the second execution of "exec sp_setapprole..." I have an error of
generic net error.

Then I writea generic sql code, but i have always this error.

Please help me, thanks

 

[Mary Chipman]

This KB article deals with OLEDB issues with approles, which still
apply to ADO.NET:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q229564

The problem is when the connection with the approle activated gets
closed and returned to the pool and then reused. If you don't care
about connection pooling, then you can turn it off in your connection
string. Most people prefer to use regular database roles and keep
pooling for performance reasons.

-- Mary
MCW Technologies
http://www.mcwtech.com