Options
Security Roles (SQL Database 2005)
kuponutcom
Posts: 17
are there any known additional security/authorization roles needed to be applied to the user you are trying to login as on SQL 2005, with a web app -- in addition to those used or needed for a console app?
when compiling and running the toolkit v5 sample, and providing the proper information for your databases to compare, it works just fine. however, i'm trying to develop a c# web app. when i run that exact same code (except i hide the "// Console.WriteLine" lines) within a web app environment, it dies out, constantly giving me this error:
System.Data.SqlClient.SqlException: Cannot open database <my db name here> requested by the login. The login failed.
Login failed for user '<username i am using here>'.
(i am using Visual Web Express 2005 c# web app, against Sql 2005 databases)
i have gone into sql 2005 and added every single role and ownership (except 2 whose names start with "deny") to the user trying to login to the database ('sa')... and it still gives me this error.
is this a roles issue?
... and if it isn't a roles issue associated with the user account, what could it be?
stack trace:
Server stack trace:
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.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 _16._1(SqlConnection )
at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
at _16._1.EndInvoke(IAsyncResult result)
at _16._1()
at RedGate.SQLCompare.Engine.Database.Register(ConnectionProperties connectionProperties, Options options)
when compiling and running the toolkit v5 sample, and providing the proper information for your databases to compare, it works just fine. however, i'm trying to develop a c# web app. when i run that exact same code (except i hide the "// Console.WriteLine" lines) within a web app environment, it dies out, constantly giving me this error:
System.Data.SqlClient.SqlException: Cannot open database <my db name here> requested by the login. The login failed.
Login failed for user '<username i am using here>'.
(i am using Visual Web Express 2005 c# web app, against Sql 2005 databases)
i have gone into sql 2005 and added every single role and ownership (except 2 whose names start with "deny") to the user trying to login to the database ('sa')... and it still gives me this error.
is this a roles issue?
... and if it isn't a roles issue associated with the user account, what could it be?
stack trace:
Server stack trace:
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.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 _16._1(SqlConnection )
at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
at _16._1.EndInvoke(IAsyncResult result)
at _16._1()
at RedGate.SQLCompare.Engine.Database.Register(ConnectionProperties connectionProperties, Options options)
Comments
If you can connect with Management Studio and not through your web application code, it may be worth looking into what the differences are that would prevent a connection. For instance, is the web application on a remote computer? If so, have you tried to connect using Management Studio from this computer? Maybe the surface area configuration does not allow remote connections to the SQL Server.
any other ideas?