Sql – Application Role connect to SQL Server with “Login Failed” error

netsql serverwinforms

I have a program which would use the Application Role to write data to a SQL Server 2005.

using (SqlConnection sqlCon = new SqlConnection(connectionString))
{
   SqlCommand sqlCommand = new SqlCommand();
   sqlCommand.Connection = sqlCon;
   sqlCommand.CommandType = CommandType.Text;
   sqlCommand.CommandText = "";
   sqlCommand.CommandText = "EXEC sp_setapprole 'name','password';";
   sqlCommand.CommandText += sqlComm;
   sqlCommand.CommandTimeout = 300;
   sqlCon.Open();

   int res = sqlCommand.ExecuteNonQuery();
}

I use this code to connect a SQL Server 2005 ServerA, it works well. Then I use the same code to connect anohter SQL Server 2005 ServerB, with the identical table, application role, it gave an error."Login Failed for domain\username" Does anyone meet it before?

System.Data.SqlClient.SqlException was
unhandled by user code
Message="Login failed for user
'domian\username'." Source=".Net
SqlClient Data Provider"
ErrorCode=-2146232060 Class=14
LineNumber=65536 Number=18456
Procedure="" Server="ServerB"
State=1
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior
runBehavior, SqlCommand cmdHandler,
SqlDataReader dataStream,
BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject
stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean
enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword,
Boolean ignoreSniOpenTimeout, Int64
timerExpire, SqlConnection
owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
host, String newPassword, Boolean
redirectedUserInstance, SqlConnection
owningObject, SqlConnectionString
connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString
connectionOptions, String newPassword,
Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString
connectionOptions, Object
providerInfo, String newPassword,
SqlConnection owningObject, Boolean
redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo,
DbConnectionPool pool, DbConnection
owningConnection)
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.SqlClient.SqlConnection.Open()
at ADSK.PSEB.ACRSubmitComponent.ACRSubmit.backgroundUploadWork_DoWork(Object
sender, DoWorkEventArgs e) in
C:\Documents and
Settings\lvlu\Desktop\Mulder\Upload\ADSK.PSEB.ACRSubmitComponent\trunk\ADSK.PSEB.ACRSubmitComponent\Form1.cs:line
897
at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs
e)
at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object
argument)

Best Answer

The error message really says it all:

SqlException was unhandled by user code 
Message="Login failed for user 'domain\username'." 
Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=14

From this, you should see that the user the you use to connect to your ServerB does not have an account on that second server, obviously.

It has nothing to do with your application role or whatever - your user "domain\username" just simply doesn't have access rights to the second server.

Add a login for that user, and grant him access and the necessary permissions on the databases needed, and you should be fine.

Marc

Related Topic