sp_setapprole encryption and .NET

  • Thread starter Thread starter msnews.microsoft.com
  • Start date Start date
M

msnews.microsoft.com

If I run
exec sp_setapprole @rolename='app',@password={Encrypt
N'app'},@encrypt='odbc'
from MS query analyzer it sets up okay. I have a guest account in the
public role in the database I connect to run this.

If I am my .Net program and try to run it as follows
strConn="Initial Catalog=MyDB;Data Source=MyServer;user id=guest";
SqlConnection oConn=new SqlConnection(strConn);
oConn.Open();
SqlCommand oCmd=new System.Data.SqlClient.SqlCommand("exec sp_setapprole
@rolename='app',@password={Encrypt N'app'},@encrypt='odbc'",oConn);
iResult=(int)oCmd.ExecuteNonQuery();

It throws an error
'Encrypt' is not a recognized ODBC date/time extension option.

If I take out the Encrypt part, as shown below, it works fine.
SqlCommand oCmd=new System.Data.SqlClient.SqlCommand("exec sp_setapprole
@rolename='app',@password='app',@encrypt='odbc'",oConn);

Any ideas on the error?
 
This is the syntax shown in SQL BOL:
EXEC sp_setapprole 'Test', {Encrypt N 'pswd'}, 'odbc'

without using named parameters and specifying unicode constants. You
might try adjusting your syntax accordingly and see if that helps.

--Mary
 
Back
Top