SQL1092N "ASPNET" does not have the authority to perform the requested command. Basic ODBC call to a dsn does not work.

Question

Don't shoot me for this, but I've posted this on experts-exchange as well. I just need an answer. Here's the question (and yes, i've searched, googled, and searched again):

I'm getting some very strange errors on boiler plate odbc connection code. The code connects to DB2 (which is always a joy to work with) and has never given me issues in the past. This is your typical work ghost....one day it just stopped working. This is happening on 2 separate instances of Windows 2003 servers. On my local machine the code works fine. The code is connecting via a system dsn setup via the IBM DB2 CONNECT components.

One error I get reads:

ERROR [08004] [IBM][CLI Driver] SQL1092N "ASPNET" does not have the authority to perform the requested command.

This happens even after I place the ASPNET account in both the DB2ADMINS and DB2USERS groups. I know the DB2USERS group should have permissions enough, but I tried DB2ADMINS just for kicks, yet neither of them work.

In my other applcation, which is using the exact same system dsn (well, it's trying to anyhow), The error message is simply blank....there isn't one. The stack trace reads:

at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
   at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.Odbc.OdbcConnection.Open()
   at code class ... line xxx            //abridged
   at form class ... line xxx             //abridged

I'm at an utter loss here...it worked....and now it doesn't...

Here is the odbc code:

String dsn = ConfigurationManager.AppSettings.Get("DSN");

String con = ConfigurationManager.ConnectionStrings[dsn].ToString(); 
// con evaluates to "DSN=MYSYSDSN"


OdbcConnection conn = new OdbcConnection(con);

OdbcCommand command = new OdbcCommand(Constants.SQL_GET_DATA, conn);

command.CommandType = CommandType.Text; 

OdbcParameter param1 = new OdbcParameter("@param1", param1value);

OdbcParameter param2 = new OdbcParameter("@param2", param2value); 


command.Parameters.Add( param1 );

command.Parameters.Add( param2 ); 

OdbcDataReader rdr = null; 

try

{ 

  conn.Open();    //errors here w/ above stack trace and an empty message string
  .....

Answer 1

The connection string/data source you're pulling in is using what in the Sql Server world we call a trusted connection or integrated authentication. This works just fine in testing, as the app will run with your credentials, and you have permissions to access the database.

However, when deployed to a server, the app runs using a special user account named "ASPNET". This account does not have permission to access your database. So there are two ways to fix it:

1. Give the ASPNET account rights to the database.
2. Use impersonation to make your app run with a different account.

You should really choose the 2nd option, as the ASPNET account is a local machine account rather than a domain account, and you don't want to be throwing permissions around like that. It also explains why adding the ASPNET account to your group doesn't work - it's a local account rather than a domain account (you're probably adding the wrong ASPNET).

Unfortunately, I can't help you set up impersonation as we use sql authentication where I'm at. But I can give some pointers on improving the code you posted:

String dsn = ConfigurationManager.AppSettings.Get("DSN");
String con = ConfigurationManager.ConnectionStrings[dsn].ToString(); 
// con evaluates to "DSN=MYSYSDSN"

using (OdbcConnection conn = new OdbcConnection(con))
using (OdbcCommand command = new OdbcCommand(Constants.SQL_GET_DATA, conn))
{
    command.Parameters.AddWithValue("@param1", param1value);
    command.Parameters.AddWithValue("@param2", param2value); 

    //no need for try/catch here unless you do logging at this level
    // instead, let it bubble up for the next level to decide how to handle

    conn.Open();
    using (OdbcDataReader rdr = command.ExecuteReader())
    {
        while (rdr.Read())
        {
           // do something
        }
    }
}  // no need to call conn.Close() here - 'using' block takes care of it