login failed for 'user' (Application Role)

I've created a database in SQL Express and I have a Windows form attempting to connect to it through SQL Authentication. Connection string:

private string connString = @."Data Source=.\sqlexpress;Initial Catalog=SQLTestDatabase;User ID=SearchAppRole; Password=password;";

The role I have added to the database is an Application Role. It has been added to the Database permissions with Grant checked for "Select" and "Authenticate".

If I test this with query analyzer, it returns expected results (if I remove Grant from 'Select', it fails)

sp_setapprole 'SearchAppRole', 'password'

select * from recipe

If I edit my connection string (for testing purposes) to use the sa account, the application can connect and run the Select statement:

private string connString = @."Data Source=.\sqlexpress;Initial Catalog=SQLTestDatabase;User ID=sa; Password=sa_password;";

However, I cannot get the application to successfully logon and run the select statement when using the user id and password of the Application Role. I get error:

System.Data.SqlClient.SqlException: Login failed for user 'SearchAppRole'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj

I can't find much information on Application Role...I just want one basic permission for the application as a whole. Any help is appreciated. Thanks.

You will first have to connect with a valid user on the database to set the application role. The user can be a dummy user having no access to the actual stored objects. But for using the command you will have to first open a connection to the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Ok. I'm sure this works, and I think I found an example of it here on these forums. However, I decided to just add a User to the database and work with that. I'm not sure that I really need to use Application Role and I don't know why it isn't more straightforward. Thanks.sql