User.IsInRole throws a network-related or instance-specific error

Question

I have this very basic MVC4 application connected to a database through an Object Entity Model. The connection between the application and the database is excellent because the user can log in and the code below is in Global.asax (which successfully retrieves user roles 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 occurs 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"); 

Exact error:

 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 

Answer 1

I had the same problem with an application that previously used SimpleMembership , but was changed to use 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> 

Answer 2

Not so long ago a similar problem arose. Worked with the solution of several projects. Users who can authenticate without problems; when they went to update their user profile, they will get the error you specified.

In the end, we found that Visual Studio inserted a connection string into the app.config of the infrastructure project, pointing to a development environment database that was not available from the production environment.

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

Answer 3

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

Here are some things you can check:

• Does the account running the application have read / write access to the directory in question? (Default for App_Data)

• Are your connection strings configured correctly?

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

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

A connection string expects a “regular” connection string, not one generated by the first Entity Framework Db / Model templates.

I also notice that you are using the user test.user . SimpleMembershipProvider and SimpleRoleProvider use string concatenation to build queries. The period that you have can cause problems. Can you check it out without a period?

Answer 4

It looks like you are using an ASP.NET membership provider that can specify a different connection string. Make sure you point to the correct connection string. See Configuring an ASP.NET Application to Use Membership.

Answer 5

Try breaking GetSqlConnection (...) in both cases to see which arguments are passed.

Answer 6

The role provider that you use will accept the configuration from the machine.config file if you did not specify it in your web.config . This will force your application to create the LocalSql database in App_Data . The connection string and provider information are specified in machine.config , so your web.config will add or remove them. So for example, your machine.config probably says:

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

And your web.config has the following:

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

Your application will be able to see and use both lines . To fix this, you can first clear the connection strings as follows:

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

But this still leaves the main problem that your role provider should install 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 here for you can only be the </clear> , which removes everything from the machine.config inheritance. Alternatively, you may need to specify a default provider as follows:

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