Security Roles (SQL Database 2005)

kuponutcom

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)

Brian Donahue

Hello,

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.

kuponutcom

i am using the exact same sql connection user/pass/ip address with both management studio and the sql toolkit c# code. in management studio i connect just fine. with the toolkit it produces the error i posted in my previous post to this thread.


any other ideas?

Brian Donahue

Because this involves remoting, apparently, have you tried going into the .NET Framework configuration wizard and adjusting the security policy for the local intranet zone to 'full trust'? Possibly, .NET security policy is preventing the SQL Compare assembly from doing any work.