User.IsInRole throwing a A network-related or inst

问题:

I have this very basic MVC4 application conencted to a database via Object Entity Model. The connection between the application and database is fine, because a user is able to login and the below code in the Global.asax is working (which retrieves the user roles successfully from the database.

protected void Application_AuthenticateRequest(object sender, EventArgs args)
    {
        if (User != null)
        {

            IEnumerable<CommonLayer.Roles> roles = new BusinessLayer.Roles().getAllUserRoles(Context.User.Identity.Name);

            string[] rolesArray = new string[roles.Count()];
            for (int i = 0; i < roles.Count(); i++)
            {
                rolesArray[i] = roles.ElementAt(i).RoleName;
            }

            GenericPrincipal gp = new GenericPrincipal(Context.User.Identity, rolesArray);
            Context.User = gp;
        }
    }

The error is occuring on the following line:

@if(User.IsInRole("Administrator"))
    {
       <span>testtttt</span>
    }

Instead of the above, I also tried the following but still got the same error:

Roles.AddUserToRole("test.user","Administrator");

The exact error is:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Stack trace:

[SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5340635
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +244
   System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover) +5350895
   System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) +145
   System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) +922
   System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) +307
   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData) +518
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +5353705
   System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) +38
   System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +5355906
   System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +146
   System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +16
   System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +94
   System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +110
   System.Data.SqlClient.SqlConnection.Open() +96
   System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +75

[HttpException (0x80004005): Unable to connect to SQL Server database.]
   System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +130
   System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install) +89
   System.Web.Management.SqlServices.Install(String database, String dbFileName, String connectionString) +27
   System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile(String fullFileName, String dataDir, String connectionString) +386

回答1:

I had this same problem with an application that was previously using SimpleMembership but which had been changed to use a Single Sign-on based on Owin Authentication Middleware. Adding the following to my web.config resolved the issue for me:

<system.webServer>
  <modules>
    <remove name="RoleManager" />
  </modules>
</system.webServer> 

回答2:

Ran into a similar issue not long ago. Was working with a multiple projects solution. Users where able to authenticate without a problem; when they went to update their user profile they would get the error you mentioned.

In the end we found that Visual Studio had inserted a connection string into the app.config of the infrastructure project that pointed to the development environment database which was inaccessible from the production environment.

We removed the connection string setting from the App.config, re-deployed and life was peachy afterwards.

回答3:

Your error specifically states you tried to use a local SQL Express instance that requires it to create and attach the file to the server.

Here are some things you can check for:

• Does the account running the application have Read/Write access to the directory in question? (Defaults to App_Data)

• Do you have your connection strings correctly configured?

If you look at the method signature for InitializeDatabaseConnection (which is used to setup the role and membership providers), it is:

public static void InitializeDatabaseConnection(string connectionStringName, string userTableName, string userIdColumn, string userNameColumn, bool autoCreateTables);

The connection string expects a "regular" connection string, not one generated by Entity Framework's Db/Model first patterns.

I also notice you are using the user test.user. The SimpleMembershipProvider and SimpleRoleProvider use string concatenation to build the queries. The period you have in there could be causing the issues. Can you test it without the period?

回答4:

Looks like you are using the ASP.NET membership provider, which can specify a different connection string. Verify that you have it pointing to the correct connection string. See Configuring an ASP.NET Application to Use Membership

回答5:

Try to break on GetSqlConnection(...) in both cases to see what arguments are passed.

回答6:

The role provider you are using will take it's config from the machine.config file if you have not specified it in your web.config. This will cause your app to create a LocalSql database in App_Data. The connection string and provider details are specified in machine.config, so your web.config will only add or remove them. So for example your machine.config probably says:

<connectionStrings>
    <add name="LocalSqlServer" connectionString="..."/>
</connectionStrings>

And your web.config has this:

<connectionStrings>
    <add name="MyConnectionString" connectionString="..."/>
</connectionStrings>

Your application will be able to see and use both strings. To fix it you can clear the connection strings first like this:

<connectionStrings>
    <clear />
    <add name="MyConnectionString" connectionString="..."/>
</connectionStrings>

But that still leaves the underlying problem that your role provider needs to be setup in your web.config. To do this, add something like this:

<system.web>
    <roleManager>
        <providers>
            <clear/>
            <add name="AspNetSqlRoleProvider" connectionStringName="<your connection string name here>" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
        </providers>
    </roleManager>
</system.web>

The key part in here for you might just be the </clear> line that removes everything from the machine.config inheritance. Alternatively, you may get away with specifying the default provider like this:

<roleManager defaultProvider="MyProvider">
    <providers>
        <add name="MyProvider" ......>
        ...etc...