Hi everybody.

 Im trying to call the sproc 'sp_setapprole' with the esUtility class. After that i want to check if it was set correctly, so i execute 'Select User_Name()'.

 First heres the code:

esUtility esUtil = new esUtility();

            esParameter name = new esParameter("rolename", m_appRoleName); 
            esParameter pwd = new esParameter("password",txtPwd.Text);                      
            esParameter cookieCreate = new esParameter("fCreateCookie", true);
            esParameter cookieOut = new esParameter("cookie",esParameterDirection.Output,DbType.Binary, 8000);

            esParameters sqlParams = new esParameters();
            
            sqlParams.Add(name);
            sqlParams.Add(pwd);
            sqlParams.Add(cookieCreate);
            sqlParams.Add(cookieOut);

            esUtil.ExecuteReader(esQueryType.StoredProcedure, "sp_setapprole", sqlParams);
            
            System.Data.IDataReader idr = esUtil.ExecuteReader(esQueryType.Text, "SELECT USER_NAME()");
            if (idr.Read())
            {
                System.Console.WriteLine(idr.GetString(0));
            }

What happens is that everything seems to do ok, but 'Select User_Name()' always returns just 'dbo'. It should return the name of the application role. If i enter a wrong password i get an SqlException, so it seems if i enter the correct password the sproc itself runs without failure.

If i do the same thing without EntitySpaces it works:

SqlCommand com = sqlConn.CreateCommand();
            com.Parameters.Add(new SqlParameter("rolename", "LD23"));
            com.Parameters.Add(new SqlParameter("password", "ld23!"));

            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = "sp_setapprole";

            com.ExecuteNonQuery();

            comSelect = sqlConn.CreateCommand();

            comSelect.CommandType = CommandType.Text;
            comSelect.CommandText = "Select User_Name()";
            
            SqlDataReader sdr = comSelect.ExecuteReader();
            if (sdr.Read())
            {
                System.Console.WriteLine(sdr[0].ToString());
            }
            sdr.Close();

 Some other strange things:If i use 'ExecuteNonQuery' for calling the sproc i get an SqlException when i try to select the username, the sproc itself runs. 'A severe error occurred on the current command. The results, if any, should be discarded.'.
If i don´t try to get the username, i get this error at the next 'Database-Action'.
Sometimes i get this error even when using 'ExeceuteReader' at the next 'Database-Action' after selecting the(wrong) username. This Exception never causes a crash(I can re-run the command causing it, it works the second time), and the results i get seem ok(except the wrong user name).I assume this may have something to do with Sql-Connections and the way ES handles them, but im not sure at all. Any suggestions appreciated :)

Bye,

Joris

Whats the point of the cookie stuff? I notice you don't use that on the sample that works? Also, if you expect only a single value back use ExecuteScalar.  The ExecuteNonQuery() never returns any results, this is a straight pass through to the ADO.NET method, as are most of the others.

The cookie is for 'unsetting' the application role. Doesn´t change anything when i don´t use it.

But i found this in the SqlServer log:

2007-06-22 15:04:22.01 spid52      Error: 18059, Severity: 20, State: 1.
2007-06-22 15:04:22.01 spid52      The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.

And i found that this seems to happen in my sample also, but in the sample the username changed correctly. When i do something after that, the same SqlException is thrown.

I guess i research a bit before bothering you again ;) 

Update: Added "Pooling=False" to the Connection, i dont get errors or exceptions, not with Es, not within my sample.

But: the username won´t change using ES. The procedure is called, otherwise i wouldn´t get an exception when using a wrong password.

Instead of this:

esUtil.ExecuteReader(esQueryType.StoredProcedure, "sp_setapprole", sqlParams);

Did you try this?

esUtil.ExecuteNonQuery(esQueryType.StoredProcedure, "sp_setapprole", sqlParams);

Of course, I should have realized that, connection pooling will prevent that from working, but you can do this with the esTransactionScope to hold a connection open for a short time.

using(esTransactionScope scope = new esTransactionScope())
{
    esUtility esUtil = new esUtility();
    esParameters sqlParams = new esParameters();

    sqlParams.Add(new esParameter("rolename", m_appRoleName)); 
    sqlParams.Add(new esParameter("password",txtPwd.Text));                      
    sqlParams.Add(new esParameter("fCreateCookie", true));
    sqlParams.Add(new esParameter("cookie",esParameterDirection.Output,DbType.Binary, 8000));

    esUtil.ExecuteReader(esQueryType.StoredProcedure, "sp_setapprole", sqlParams);
            
    System.Data.IDataReader idr = esUtil.ExecuteReader(esQueryType.Text, "SELECT USER_NAME()");

    if (idr.Read())
    {
        System.Console.WriteLine(idr.GetString(0));
    }

    scope.Complete();
}

Notice the scope.Complete() call as well. Anything within an esTransactionScope using block will use the same physical database connection.